Cascading prompts in a form for a query

  • Thread starter Thread starter KumbiaKid
  • Start date Start date
K

KumbiaKid

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?
 
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, 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.



KumbiaKid said:
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?
Click to expand...
Click to expand...
 
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.



KumbiaKid said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Hi Wayne,

Thanks. I don't need to query the user because if the user changes the
selection in the first combo box, the info in the second will be wrong. By
putting your suggestion,
Me.cboListDates = ""
in the On Got Focus event for the first combo box and a suggestion by
scubadiver in an earlier post, subject "Refresh Listbox in Subform",
Me.cboListDates.Requery
in the On Got Focus event for the second combo box, all is well and works.
It seems both of these are required because although the Me.cboListDates = ""
clears the displayed contents of the second combo box, it does not force the
query to re-run with the new filter from the first combo box. If I use only
Me.cboListDates.Requery event, the display in the second combo box is not
cleared and continues to display the now incorrect result of the original
query even though the contents of the combo box drop-down list get updated.
Sounds complicated, but it all now works. Thanks to both of you.

Cheers,
KumbiaKid

Wayne-I-M said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top