Code using 2 Combo Boxes

[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

 

[tina]

your basic problem is a missing And:

rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "And [SaleDate] =" &
Str(Me![cboclSaleDate])

however, if SaleDate is a Date data type, you probably need something more
like

rs.FindFirst "[clNum] =" & Str(Me![cboClNum]) & "And [SaleDate] = #" &
Str(Me![cboclSaleDate] & "#")

hth

 

[Guest]

Sara,

Like Tina said, you were missing an 'And'. But you don't need to convert the
values to strings. Since (it appears) that the value in [clNum] is a number,
no delimiters are necessary. However, the date in [SaleDate] does need the
delimiter '#' as Tina said.
I also used Dim rs As Recordset.

After you do a Find or Seek, you should check to see if records were found
before you try to do something with the dataset.

Below is your sub I modified:
'-----------------------------
Private Sub CboclSaleDate_AfterUpdate()

' Find the record that matches the control.'

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[clNum] =" & Me![cboClNum] & "And [SaleDate] = #" &
Me![cboclSaleDate] & "#"

If Not rs.BOF and Not rs.EOF then
Me.Bookmark = rs.Bookmark
Else
'notify
Msgbox "No records found that matched the parameters!"
End If

' or you could use rs.NoMatch
' the Not .NoMatch is TRUE if records were found
'If Not rs.NoMatch then
' Me.Bookmark = rs.Bookmark
'End If

'cleanup - if you create it, destroy it - Important!!
rs.Close
Set rs = Nothing
End Sub
'----------------------

HTH
Steve