Find correct record after user using down arrow

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

My cboFind will show [Last Name] and [First Name]. I use wizard to create
following code.

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboFind.BackColor = -2147483633
Me.Last_Name.SetFocus
End Sub

However, if use uses down arrow to move to different first name (same last
name), my record selector only points to the first match of last name.

How to let record point to correct record?

Thanks.
 
My cboFind will show [Last Name] and [First Name]. I use
wizard to create following code.

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboFind.BackColor = -2147483633
Me.Last_Name.SetFocus
End Sub

However, if use uses down arrow to move to different first
name (same last name), my record selector only points to the
first match of last name.

How to let record point to correct record?

Thanks.
Click to expand...
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND "
"[Firsat Name] = '" & Me![cboFind].column(1) & "'"

column() is a zero-based index into the combobox's columns. If
[First name] is not the second column, adjust accordingly.
 
Hi, Bob:

Thanks for your help. My [First Name] is 2nd column (right
after [Last Name]). When I use your

rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND
""[First Name] = '" & Me![cboFind].column(1) & "'"

it gave me following error message:
Run-time error '3077'
Syntax error in string in expression

- Song
Click to expand...

I left in too many quote marks when I cut and pasted. Try

rs.FindFirst "[Last Name] = '" & Me![cboFind]
& "' AND [First Name] = '" & Me![cboFind].column(1) & "'"

Sorry about that.

Bob Quintal said:
My cboFind will show [Last Name] and [First Name]. I use
wizard to create following code.

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboFind.BackColor = -2147483633
Me.Last_Name.SetFocus
End Sub

However, if use uses down arrow to move to different first
name (same last name), my record selector only points to the
first match of last name.

How to let record point to correct record?

Thanks.
Click to expand...
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND "
"[Firsat Name] = '" & Me![cboFind].column(1) & "'"

column() is a zero-based index into the combobox's columns.
If [First name] is not the second column, adjust accordingly.
Click to expand...
Click to expand...
 
Hi, Bob:

Thanks for your help. My [First Name] is 2nd column (right after [Last
Name]). When I use your

rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND ""[First Name] = '" &
Me![cboFind].column(1) & "'"

it gave me following error message:
Run-time error '3077'
Syntax error in string in expression

- Song

Bob Quintal said:
My cboFind will show [Last Name] and [First Name]. I use
wizard to create following code.

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboFind.BackColor = -2147483633
Me.Last_Name.SetFocus
End Sub

However, if use uses down arrow to move to different first
name (same last name), my record selector only points to the
first match of last name.

How to let record point to correct record?

Thanks.
Click to expand...
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND "
"[Firsat Name] = '" & Me![cboFind].column(1) & "'"

column() is a zero-based index into the combobox's columns. If
[First name] is not the second column, adjust accordingly.
Click to expand...
 
Works great! Thanks.

Bob Quintal said:
Hi, Bob:

Thanks for your help. My [First Name] is 2nd column (right
after [Last Name]). When I use your

rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND
""[First Name] = '" & Me![cboFind].column(1) & "'"

it gave me following error message:
Run-time error '3077'
Syntax error in string in expression

- Song
Click to expand...

I left in too many quote marks when I cut and pasted. Try

rs.FindFirst "[Last Name] = '" & Me![cboFind]
& "' AND [First Name] = '" & Me![cboFind].column(1) & "'"

Sorry about that.

Bob Quintal said:
My cboFind will show [Last Name] and [First Name]. I use
wizard to create following code.

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.cboFind.BackColor = -2147483633
Me.Last_Name.SetFocus
End Sub

However, if use uses down arrow to move to different first
name (same last name), my record selector only points to the
first match of last name.

How to let record point to correct record?

Thanks.

rs.FindFirst "[Last Name] = '" & Me![cboFind] & "' AND "
"[Firsat Name] = '" & Me![cboFind].column(1) & "'"

column() is a zero-based index into the combobox's columns.
If [First name] is not the second column, adjust accordingly.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top