How to make one combo box required based on selection in another combo box

[pbaldy]

What is that supposed to be/do? You appear to have it in the wrong argument (window mode). OpenArgs is one more comma, wherecondition is earlier:

http://www.baldyweb.com/wherecondition.htm

[Etoimos]

Thanks Paul, that sheds a bit more light on it. I'm trying to do basically what you linked to, but I need to narrow the record selection down using my three combo boxes for Event, Date, and Run number. So something like this?

DoCmd.OpenForm "Run Sheet", , , "Event = '" & Me.EventLookupCombo
& "'"

But I need to add a

 
"ExecutionDate = '" & Me.DateLookupCombo
& "'"

and

 
"RunNumber = '" & Me.RunLookupCombo
 & "'" 

[pbaldy]

Concatenate " AND " between each condition.

"Event = '" & Me.EventLookupCombo & "' AND SecondField = '"...

[Gicu]

In your RunResultData you have and ID as the primary key. I suspect that what you are trying to do is use your three cascading combo boxes to isolate one specific record you are trying to open/view. So in that case the last combo box should not return a "run number" but the actual ID of the record. That is easily achieved if you bind the combo to the ID, set its column count to 2 (or more as needed) and set the width of the first column to 0. So the RowSource of the RunLookupCombo would be something like this: SELECT DISTINCT ID, RunNumber FROM RunResultsDate WHERE...the conditions for the preceding two combos. What I am trying to say is that you only need to pass an ID to the openform WhereCondition:

DoCmd.OpenForm "Run Sheet", , , "[ID]= " & Me.RunLookupCombo

Cheers,

[ArviLaanemets]

You can do this with single combo. Have the RowSource for EventLookupCombo like

SELECT rrd.ID, Left(e.Event & Space(20),20) & ": " & Format(er.ExecutionDate,"yyyymmdd") & ": " & CStr(er.RunNumber) FROM 
(Events e INNER JOIN EventRun er ON e.EventID = er.EventID) INNER JOIN RunResultsDate rrd ON rrd.ID = er.EventRunID
ORDER BY 2 ASC

Set ColumnCout = 2, BoundColumn = 1, ColumnWidths = "0,2.5" for EventLookupCombo.

Combo's selection list is ordered alphabetically. To avoid the list order to be scrambled, all column entries except the last column must be of same width (in my example 20 characters, 8 characters, and whatever). When you select the combo, and start type the event name, the 1st possible selection starting with entered string is activated.

DoCmd.OpenForm "Run Sheet", , , "[ID]= " & Me.RunLookupCombo

[Gicu]

@ ArviLaanemets: Please have a look at posts # 69,70,71

Cheers,

[Etoimos]

Concatenate " AND " between each condition.

"Event = '" & Me.EventLookupCombo & "' AND SecondField = '"...

I tried adding this code to the On Click for my button:

DoCmd.OpenForm "Run Sheet", , , "Event = '" & Me.EventLookupCombo & "' AND ExecutionDate = '" & Me.DateLookupCombo & "' AND RunNumber = '" & Me.RunLookupCombo & "'"

That will open my Run Sheet form, but it is a blank form with none of the data displayed.

[Etoimos]

In your RunResultData you have and ID as the primary key. I suspect that what you are trying to do is use your three cascading combo boxes to isolate one specific record you are trying to open/view. So in that case the last combo box should not return a "run number" but the actual ID of the record. That is easily achieved if you bind the combo to the ID, set its column count to 2 (or more as needed) and set the width of the first column to 0. So the RowSource of the RunLookupCombo would be something like this: SELECT DISTINCT ID, RunNumber FROM RunResultsDate WHERE...the conditions for the preceding two combos. What I am trying to say is that you only need to pass an ID to the openform WhereCondition:

DoCmd.OpenForm "Run Sheet", , , "[ID]= " & Me.RunLookupCombo

Cheers,

I played around with this and it broke my combo boxes. I'm going to mess with it some more and see if I screwed something else up.

[Gicu]

In your concatenation you treat all as text with the wrapping in single quotes. For dates use # and nothing is needed for numbers:
DoCmd.OpenForm "Run Sheet", , , "Event = '" & Me.EventLookupCombo & "' AND ExecutionDate =#'" & Me.DateLookupCombo & "# AND RunNumber = " & Me.RunLookupCombo & ""

Cheers,

[Etoimos]

I had the # for the date, but it was still messing up. Maybe it was the single quote for the number in the last part that was hosing it. Thanks.

[Etoimos]

Does the red single quote in this code need to be removed with the # sign is added?

DoCmd.OpenForm "Run Sheet", , , "Event = '" & Me.EventLookupCombo  & "' AND ExecutionDate =#'" & Me.DateLookupCombo & "# AND  RunNumber = " & Me.RunLookupCombo & ""

[Etoimos]

I did remove that extra single quote and the Run Sheet form still opens up with no data in it.

[Gicu]

Can you put a break in code on the line and inspect the values of the three combos? Are the combos related (cascading) or are they independent of each other? If latest are you sure there is a record that satisfies all three? And I also remember that your form opens by default in DataAdd mode meaning it will be blank because is at a new record, so you need to change the mode in the OpenForm.

Cheers,

[Etoimos]

Can you put a break in code on the line and inspect the values of the three combos? Are the combos related (cascading) or are they independent of each other? If latest are you sure there is a record that satisfies all three? And I also remember that your form opens by default in DataAdd mode meaning it will be blank because is at a new record, so you need to change the mode in the OpenForm.

Cheers,

They are cascading and they do filter down to an actual record. I just tried adding the ReadOnly code and I don't think I have it in the right spot. This is the code I used:

DoCmd.OpenForm "Run Sheet", , , acFormReadOnly, "Event = '" & Me.EventLookupCombo & "' AND ExecutionDate = #" & Me.DateLookupCombo & "# AND RunNumber = " & Me.RunLookupCombo & ""

[Gicu]

The DataMode argument goes after the WhereCondition:https://docs.microsoft.com/en-us/off...docmd.openform

DoCmd.OpenForm "Run Sheet", , , "Event = '" & Me.EventLookupCombo & "' AND ExecutionDate = #" & Me.DateLookupCombo & "# AND RunNumber = " & Me.RunLookupCombo & "",acFormReadOnly

But it would be much easier if the last combo would return the unique record ID instead of the run number, you would only have to reference one control.

Cheers,