Combo box question 2

[JR2913]

I have a combobox that provides a drop down list of users based on the following SQL:

SELECT [child surname] & ", " & [child forename] AS [Child Name], tblChildData.childID, tblChildData.[child surname], tblChildData.[child forename], tblChildData.Active
FROM tblChildData
WHERE (((tblChildData.Active)=True))
ORDER BY tblChildData.[child surname], tblChildData.[child forename];

The list is dependent on the actual record being active (a field in the data table called ACTIVE). However, I want to be able to give users the ability to decide whether they just want to view users whose records are active, or all records in the table. I have an optionbox on the form the combobox is on, the default value being 1 for active records, but I can't get the syntax right in the combobox SQL for choosing the alternative (all records) when the value of the optionbox becomes 2.

Can anyone help, please, as this is driving me mad!?

Many thanks

John R

 

[LonnieJohnson]

Do the values in the Active field default to No (False)? If so, then you just need to use the wildcard to get everything...


SELECT [child surname] & ", " & [child forename] AS [Child Name], tblChildData.childID, tblChildData.[child surname], tblChildData.[child forename], tblChildData.Active
FROM tblChildData
WHERE (((tblChildData.Active)='*'))
ORDER BY tblChildData.[child surname], tblChildData.[child forename];


If not, then you will need to include the possiblility of Nulls...


SELECT [child surname] & ", " & [child forename] AS [Child Name], tblChildData.childID, tblChildData.[child surname], tblChildData.[child forename], tblChildData.Active
FROM tblChildData
WHERE (((tblChildData.Active)='*' Or tblChildData.Active) Is Null))
ORDER BY tblChildData.[child surname], tblChildData.[child forename];



ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[Eupher]

Okay, give this a try:

Select Case Me!optMyGroup

     Case 1
          Me!cmbMyCombo.RowSource = "SELECT [child surname] & ', ' " _
          & "[child forename] AS [Child Name], tblChildData.childID, " _ 
          & "tblChildData.[child surname], tblChildData.[child forename], " _
          & "tblChildData.Active " _
          & "FROM tblChildData " _
          & "WHERE (((tblChildData.Active)=True)) " _
          & "ORDER BY tblChildData.[child surname], " _
          & "tblChildData.[child forename];"

     Case 2
          Me!cmbMyCombo.RowSource = "SELECT [child surname] & ', ' " _
          & "[child forename] AS [Child Name], tblChildData.childID, " _ 
          & "tblChildData.[child surname], tblChildData.[child forename], " _
          & "tblChildData.Active " _
          & "FROM tblChildData " _
          & "ORDER BY tblChildData.[child surname], " _
          & "tblChildData.[child forename];"

End Select

...where "optMyGroup" is the name of your option group, and "cmbMyCombo" is the name of your combo box.

HTH...

Ken S.

 

[JR2913]

Thanks Lonnie for responding so quickly

The Active field defaults to True.

I'm not clear, though, how the changes you suggest relate to the Optionbox variable that I want to include. Presumably there needs to be some VBA sitting behind the Before_update event of the combobox to take into account the setting of the Optionbox?

Regards

John R

 

[LonnieJohnson]

I'm sorry you will need to do a "Case Analysis" or "If Statement" as suggested by Ken.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[Eupher]

JR2913,

Sorry, I forgot to mention, you'll want to put this code in an event procedure in the After Update event of your option group.

Ken S.

 

[JR2913]

Thanks Ken - I can see what should happen here, but where does this go? It can't go in the Row Source of the combobox - if I put it in the Before-update event of the combobox, nothing happens, the list being empty.

Regards

John R

 

[LonnieJohnson]

Put it in the On Click event of the Option Box.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[JR2913]

Fantastic

What a duo - both have a star.

Many thanks, guys.

John R