Using Multiple Combo Boxes on one Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
 
Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.
 
thanks, I tought it would be something simple. I also took out "STR" as my
CLNum is numeric and Salesdate is a Date. I thought all combo boxes were
strings so I had put it in.

NOW, though, the first search works fine, but when I go try another, my
subform doesn't show up. Do I have to do a requery or something? Should I
clear out the combo boxes when I do the first search to "invite" the user to
another search?

thanks so much. I'm learning - step by step.
sara

Dan Artuso said:
Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.



--
HTH
Dan Artuso, Access MVP


sara said:
I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
Click to expand...
Click to expand...
 
Hi Sara,
I'm not sure what you mean. The code is moving your main form to a particular
record, right? And this causes your subform to display detail records for that main form record?
That's the way it should work. So, what do you mean by the subform doesn't show up?
Do you mean you can't even see it?

--
HTH
Dan Artuso, Access MVP


sara said:
thanks, I tought it would be something simple. I also took out "STR" as my
CLNum is numeric and Salesdate is a Date. I thought all combo boxes were
strings so I had put it in.

NOW, though, the first search works fine, but when I go try another, my
subform doesn't show up. Do I have to do a requery or something? Should I
clear out the combo boxes when I do the first search to "invite" the user to
another search?

thanks so much. I'm learning - step by step.
sara

Dan Artuso said:
Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.



--
HTH
Dan Artuso, Access MVP


sara said:
I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
Click to expand...
Click to expand...
Click to expand...
 
Sorry - I'm in Boston and the Red Sox were causing bleary eyes - I wasn't
specific enough. Here goes:

I open the form and choose client number and date. I get the requested
record.

Then I choose ANOTHER client number and date, and nothing happens. The
subform disappears and doesn't redisplay. I also notice that when I choose
another client the SaleDates in the drop down don't change - they stay with
the Dates from the prior client. So maybe the reason I don't see the subform
is that there's no matching record.

SO, I tried first switching the combo boxes - instead of showing all Clients
in the first box, and all their sales records (by date) in the second, I am
showing all SalesDates on file in the first and I want just the clients with
those dates in the second.

Same problem. The first selection works fine, but when I choose another
date, the client list doesn't change. This is the qry code behind the combo
boxes (I'm thinking this is the problem?)

Date Combo Box:
SELECT [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].SalesDate
ORDER BY [T_CLSales].SalesDate DESC;

ClNum Combo Box:
SELECT [T_CLSales].CLNum, [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].CLNum, [T_CLSales].SalesDate
HAVING ((([T_CLSales].SalesDate)=[Forms]![frmCLSales]![cboCLSaleDate]))
ORDER BY [T_CLSales].CLNum, [T_CLSales].SalesDate DESC;

I think I'm pretty good at finding the area where the problem is occurring;
I just am not good enough (yet, I hope!) to fix it.

Thanks
Sara

Dan Artuso said:
Hi Sara,
I'm not sure what you mean. The code is moving your main form to a particular
record, right? And this causes your subform to display detail records for that main form record?
That's the way it should work. So, what do you mean by the subform doesn't show up?
Do you mean you can't even see it?

--
HTH
Dan Artuso, Access MVP


sara said:
thanks, I tought it would be something simple. I also took out "STR" as my
CLNum is numeric and Salesdate is a Date. I thought all combo boxes were
strings so I had put it in.

NOW, though, the first search works fine, but when I go try another, my
subform doesn't show up. Do I have to do a requery or something? Should I
clear out the combo boxes when I do the first search to "invite" the user to
another search?

thanks so much. I'm learning - step by step.
sara

Dan Artuso said:
Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.



--
HTH
Dan Artuso, Access MVP


I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
Click to expand...
Click to expand...
Click to expand...
 
Hi,
I'm going to assume that the queries are giving you the results you want.
So all you have to do is make sure you requery the ClNum Combo Box in the
Afterpdate event of the Date Combo Box:

Me.yourClNumCombo.Requery

substitute the correct combo name.

--
HTH
Dan Artuso, Access MVP


sara said:
Sorry - I'm in Boston and the Red Sox were causing bleary eyes - I wasn't
specific enough. Here goes:

I open the form and choose client number and date. I get the requested
record.

Then I choose ANOTHER client number and date, and nothing happens. The
subform disappears and doesn't redisplay. I also notice that when I choose
another client the SaleDates in the drop down don't change - they stay with
the Dates from the prior client. So maybe the reason I don't see the subform
is that there's no matching record.

SO, I tried first switching the combo boxes - instead of showing all Clients
in the first box, and all their sales records (by date) in the second, I am
showing all SalesDates on file in the first and I want just the clients with
those dates in the second.

Same problem. The first selection works fine, but when I choose another
date, the client list doesn't change. This is the qry code behind the combo
boxes (I'm thinking this is the problem?)

Date Combo Box:
SELECT [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].SalesDate
ORDER BY [T_CLSales].SalesDate DESC;

ClNum Combo Box:
SELECT [T_CLSales].CLNum, [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].CLNum, [T_CLSales].SalesDate
HAVING ((([T_CLSales].SalesDate)=[Forms]![frmCLSales]![cboCLSaleDate]))
ORDER BY [T_CLSales].CLNum, [T_CLSales].SalesDate DESC;

I think I'm pretty good at finding the area where the problem is occurring;
I just am not good enough (yet, I hope!) to fix it.

Thanks
Sara

Dan Artuso said:
Hi Sara,
I'm not sure what you mean. The code is moving your main form to a particular
record, right? And this causes your subform to display detail records for that main form record?
That's the way it should work. So, what do you mean by the subform doesn't show up?
Do you mean you can't even see it?

--
HTH
Dan Artuso, Access MVP


sara said:
thanks, I tought it would be something simple. I also took out "STR" as my
CLNum is numeric and Salesdate is a Date. I thought all combo boxes were
strings so I had put it in.

NOW, though, the first search works fine, but when I go try another, my
subform doesn't show up. Do I have to do a requery or something? Should I
clear out the combo boxes when I do the first search to "invite" the user to
another search?

thanks so much. I'm learning - step by step.
sara

:

Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.



--
HTH
Dan Artuso, Access MVP


I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
Click to expand...
Click to expand...
Click to expand...
 
YES!! It works. You're a genius. (I would be embarrassed if my queries
hadn't worked, and was VERY glad I had tested them and checked that before
posting)
Now I'm going to read up on some of the other combo box posts and see what
else I can learn.

Many thanks,
sara

Dan Artuso said:
Hi,
I'm going to assume that the queries are giving you the results you want.
So all you have to do is make sure you requery the ClNum Combo Box in the
Afterpdate event of the Date Combo Box:

Me.yourClNumCombo.Requery

substitute the correct combo name.

--
HTH
Dan Artuso, Access MVP


sara said:
Sorry - I'm in Boston and the Red Sox were causing bleary eyes - I wasn't
specific enough. Here goes:

I open the form and choose client number and date. I get the requested
record.

Then I choose ANOTHER client number and date, and nothing happens. The
subform disappears and doesn't redisplay. I also notice that when I choose
another client the SaleDates in the drop down don't change - they stay with
the Dates from the prior client. So maybe the reason I don't see the subform
is that there's no matching record.

SO, I tried first switching the combo boxes - instead of showing all Clients
in the first box, and all their sales records (by date) in the second, I am
showing all SalesDates on file in the first and I want just the clients with
those dates in the second.

Same problem. The first selection works fine, but when I choose another
date, the client list doesn't change. This is the qry code behind the combo
boxes (I'm thinking this is the problem?)

Date Combo Box:
SELECT [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].SalesDate
ORDER BY [T_CLSales].SalesDate DESC;

ClNum Combo Box:
SELECT [T_CLSales].CLNum, [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].CLNum, [T_CLSales].SalesDate
HAVING ((([T_CLSales].SalesDate)=[Forms]![frmCLSales]![cboCLSaleDate]))
ORDER BY [T_CLSales].CLNum, [T_CLSales].SalesDate DESC;

I think I'm pretty good at finding the area where the problem is occurring;
I just am not good enough (yet, I hope!) to fix it.

Thanks
Sara

Dan Artuso said:
Hi Sara,
I'm not sure what you mean. The code is moving your main form to a particular
record, right? And this causes your subform to display detail records for that main form record?
That's the way it should work. So, what do you mean by the subform doesn't show up?
Do you mean you can't even see it?

--
HTH
Dan Artuso, Access MVP


thanks, I tought it would be something simple. I also took out "STR" as my
CLNum is numeric and Salesdate is a Date. I thought all combo boxes were
strings so I had put it in.

NOW, though, the first search works fine, but when I go try another, my
subform doesn't show up. Do I have to do a requery or something? Should I
clear out the combo boxes when I do the first search to "invite" the user to
another search?

thanks so much. I'm learning - step by step.
sara

:

Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.



--
HTH
Dan Artuso, Access MVP


I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
Click to expand...
Click to expand...
Click to expand...
 
You're welcome, glad I could help. Not sure about the genius part though :-)

--
HTH
Dan Artuso, Access MVP


sara said:
YES!! It works. You're a genius. (I would be embarrassed if my queries
hadn't worked, and was VERY glad I had tested them and checked that before
posting)
Now I'm going to read up on some of the other combo box posts and see what
else I can learn.

Many thanks,
sara

Dan Artuso said:
Hi,
I'm going to assume that the queries are giving you the results you want.
So all you have to do is make sure you requery the ClNum Combo Box in the
Afterpdate event of the Date Combo Box:

Me.yourClNumCombo.Requery

substitute the correct combo name.

--
HTH
Dan Artuso, Access MVP


sara said:
Sorry - I'm in Boston and the Red Sox were causing bleary eyes - I wasn't
specific enough. Here goes:

I open the form and choose client number and date. I get the requested
record.

Then I choose ANOTHER client number and date, and nothing happens. The
subform disappears and doesn't redisplay. I also notice that when I choose
another client the SaleDates in the drop down don't change - they stay with
the Dates from the prior client. So maybe the reason I don't see the subform
is that there's no matching record.

SO, I tried first switching the combo boxes - instead of showing all Clients
in the first box, and all their sales records (by date) in the second, I am
showing all SalesDates on file in the first and I want just the clients with
those dates in the second.

Same problem. The first selection works fine, but when I choose another
date, the client list doesn't change. This is the qry code behind the combo
boxes (I'm thinking this is the problem?)

Date Combo Box:
SELECT [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].SalesDate
ORDER BY [T_CLSales].SalesDate DESC;

ClNum Combo Box:
SELECT [T_CLSales].CLNum, [T_CLSales].SalesDate
FROM [T_CLSales]
GROUP BY [T_CLSales].CLNum, [T_CLSales].SalesDate
HAVING ((([T_CLSales].SalesDate)=[Forms]![frmCLSales]![cboCLSaleDate]))
ORDER BY [T_CLSales].CLNum, [T_CLSales].SalesDate DESC;

I think I'm pretty good at finding the area where the problem is occurring;
I just am not good enough (yet, I hope!) to fix it.

Thanks
Sara

:

Hi Sara,
I'm not sure what you mean. The code is moving your main form to a particular
record, right? And this causes your subform to display detail records for that main form record?
That's the way it should work. So, what do you mean by the subform doesn't show up?
Do you mean you can't even see it?

--
HTH
Dan Artuso, Access MVP


thanks, I tought it would be something simple. I also took out "STR" as my
CLNum is numeric and Salesdate is a Date. I thought all combo boxes were
strings so I had put it in.

NOW, though, the first search works fine, but when I go try another, my
subform doesn't show up. Do I have to do a requery or something? Should I
clear out the combo boxes when I do the first search to "invite" the user to
another search?

thanks so much. I'm learning - step by step.
sara

:

Hi,
Think of it as a Where clause without the word Where.
You're missing the AND operator
rs.FindFirst "[clNum] ='" & Str(Me![cboClNum]) & "' And [SaleDate] =#" & Str(Me![cboclSaleDate] & "#")

I've added in the # as the delimiter for dates. Numbers require no delimiter.
For strings you have to delimit with quotes. I've added them for your clNum because
you're treating it as a string not a number. If that's not the case, remove the single quotes.

Be aware you can get into trouble if your criteria contains a single quote itself and even
more trouble if the criteria contains both single and double quotes, but for now try the above.



--
HTH
Dan Artuso, Access MVP


I have 2 combo boxes that I want the user to use to select data for the
subform display (Client Number and SaleDate).
I created the form with the wizard for the first combo box, and have tried
to modify the code to include the second.

I get the error: Syntax Error (Missing operator) in Expression.
on the rs.FindFirst statement.

Probably simple, but there's no one here I can ask to look at this.

Code Below:
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "[SaleDate] =" &
Str(Me![cboclSaleDate])
Me.Bookmark = rs.Bookmark

End Sub
Many thanks,
sara
Click to expand...
Click to expand...
Click to expand...
 
Back
Top