Sorry about the confusion - I have so many zillions of bits of "stuff" in the
databases I create and look after I tend to give all "stuff" REALLY simple
names - that is why I called them 1stComboNameSearch and 2ndComboNameSearch.
Anyway I far as I can see - tell me if I'm wrong
You have 2 cascading combos on a form
That is the 1st combo selects something and this then passes a filter to the
2nd.
Your combos work fine "unless" a user select something in the 1st combo and
then 2nd and then (for some reason) goes back and select something else in
the 1st.
And you want to stop them doing this
Right so far ?
If so ? the obvious thing to do (and as far as I can see, the simplest)
would be to check that the 2nd combo is empty (null) when the 1st combo is
used - you could use a set row source or just reset the 2nd combo to null (if
there is anything already selected).
Ok up to now (if I'm going down the wrong road let me know)
I will call the 2 combos on the form
Cbo1stCombo
And
Cbo2ndCombo
Change the names to what they really are when you do anything to your form.
You need to add some code Cbo1stCombo so that a user will know that if they
select anything then this will reset the Cbo2ndCombo - and give them the
choice of continuing or cancelling what they are doing – but of course you
don’t want to ask them any questions if there is nothing selected in the
2ndCombo
Open your form in design view and right click the 1stCombo
Put this in the OnGotFocus event
Private Sub Cbo1stCombo_GotFocus()
Dim msg, style, title, response
If Not IsNull([Cbo2ndCombo]) Then
msg = "Do you want to reset the 2nd combo?." & vbCrLf & "Click Yes
to continue" & vbCrLf & "or No to cancel"
style = vbYesNo
title = " Some title here "
responce = MsgBox(msg, style, title)
If responce = vbYes Then
Me.Cbo2ndCombo = ""
End If
End If
End Sub
As you can see I have given the option for the user to choose to ignore the
warning (they can still choose No then still change the Cbo1stCombo_
If you are sure that you do want to give users this choose then just get rid
of the choise by putting something like this
Private Sub Cbo1stCombo_GotFocus()
Dim msg, style, title, response
If Not IsNull([Cbo2ndCombo]) Then
msg = "Do you want to reset the 2nd combo?." & vbCrLf & "Click Yes
to continue" & vbCrLf & "or No to cancel"
style = vbYesNo
title = " Some title here "
responce = MsgBox(msg, style, title)
If responce = vbYes Then
Me.Cbo2ndCombo = ""
Else
Me.Cbo2ndCombo.setFocus
End If
End If
End Sub
Note the 4th and 5th line from the bottom has changed.
Hope this helps
--
Wayne
Manchester, England.
KumbiaKid said:
Wayne, thanks. I'm having a bit of trouble figuring out what your
"Me.2ndComboNameSearch" refers to and that's my fault for not including more
detail in my initial post. The form with the two combo boxes is
frmSelListName.
My first combo box is named cboListNames with a data source of a query that
does a select distinct on one column, ListName, of a table. The second combo
box, named cboListDates, gets its data from a second column (ListDate) in the
same table using the following query named qryUniqueDateList:
SELECT DISTINCT SpeciesLocations.ListDate
FROM SpeciesLocations
WHERE (((SpeciesLocations.ListName)=[Forms]![frmSelListName]![cboListNames]))
ORDER BY SpeciesLocations.ListDate;
At present, neither combo has an event except for the one you suggest, but I
haven't got the syntax right on that. If you can help, I'd appreciate it.
Thanks again,
KumbiaKid
Wayne-I-M said:
You don't say if you are searching for text or a number but something like
this will do
Private Sub 1stComboName_GotFocus()
Me.2ndComboNameSearch = ""
End Sub
You can add to this like this
Private Sub 1stComboName_GotFocus()
If Not IsNull([2ndComboName]) Then
MsgBox "The 2nd combo name will be re-set", vbOKOnly, "Back to 1st combo"
Me.2ndComboNameSearch = ""
Else
Me.2ndComboNameSearch = ""
End If
End Sub
You can add to this again with a yes/no option in the message box
you can add to this ......
etc
etc
--
Wayne
Manchester, England.
:
I have a query that requires the user to specify two parameters which I
obtain from a form with two combo boxes and a "Run Query" button. I use a
combo box with a query as its data source for the first parameter. I then use
another combo box, on the same form, with another query as its data source.
The second query is filtered on the selection in the first combo box so that
the second combo box displays only records related to what the user selected
in the first combo box. This all works fine except that if the user makes a
selection in the first box, then drops down the list in the second box, then
goes back to the first box and changes the selection there, the filter on the
query that populates the second box is not updated. How can I force the query
that provides the data for the second combo box to refresh if the user makes
a change in the first combo box?
