Find First / Find Next

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

Guest

I have built a form with a combo box that drops down the qry list. We now
need a Find Command that will search for a member that is in the qry list
from the form. I have created the Find Command Button using Wizard. But I
cannot find the members that are in the qry list. I know they are there.
It keeps coming up as "No record found". Here is the code that I have:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub

Am I missing something here. If so, please tell me what I need to put in.

Thank you.
 
I have built a form with a combo box that drops down the qry list. We now
need a Find Command that will search for a member that is in the qry list
from the form. I have created the Find Command Button using Wizard. But I
cannot find the members that are in the qry list. I know they are there.
It keeps coming up as "No record found". Here is the code that I have:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub

Am I missing something here. If so, please tell me what I need to put in.
Click to expand...

This is crufty OLD Access95 code. The wizards are way behind the time.

I'd suggest not using the wizard in this case. You need to be sure of
a couple of things:

- The Combo Box *must* be unbound, that is it should have no Control
Source property. (If it does, it will overwrite the value of that
field rather than finding a new record).

- Its Bound Column should be the field that you want to search. This
will very likely be a numeric key; if you've been misled into using
the Lookup Wizard on your table you may be using the text value
instead of the numeric ID.

Try code like:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's recordsource query
rs.FindFirst "[fieldname] = " & Me!Find_Command_Click
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark ' synch to the found record
End If
Set rs = Nothing ' clean up after yourself

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub


Of course, replace [fieldname] with the actual name of the field that
you want to search.

John W. Vinson[MVP]
 
Hi John,

The line where you have "rs.FindFirst "[Name] = " & Me!Find_Command_Click"
Does not work. So I did a little fixing of what I thought would be right -
is now coming up with a "Syntax error (missing operator) in expression".
Here is the code I have so far:

Private Sub Testing_2_Click()
On Error GoTo Err_Testing_2_Click

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's recordsource query
rs.FindFirst "[Name] = " & Me![MemberID-Combo]
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark ' synch to the found record
End If
Set rs = Nothing ' clean up after yourself

Exit_Testing_2_Click:
Exit Sub

Err_Testing_2_Click:
MsgBox Err.Description
Resume Exit_Testing_2_Click

End Sub

I've created another button that I'm using as a test. (Testing_2 button).
I cannot find where the problem lies. I did make the MemberID-Combo field
unbound like you said.

Also, will this work for finding Next as well?

John Vinson said:
I have built a form with a combo box that drops down the qry list. We now
need a Find Command that will search for a member that is in the qry list
from the form. I have created the Find Command Button using Wizard. But I
cannot find the members that are in the qry list. I know they are there.
It keeps coming up as "No record found". Here is the code that I have:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub

Am I missing something here. If so, please tell me what I need to put in.
Click to expand...

This is crufty OLD Access95 code. The wizards are way behind the time.

I'd suggest not using the wizard in this case. You need to be sure of
a couple of things:

- The Combo Box *must* be unbound, that is it should have no Control
Source property. (If it does, it will overwrite the value of that
field rather than finding a new record).

- Its Bound Column should be the field that you want to search. This
will very likely be a numeric key; if you've been misled into using
the Lookup Wizard on your table you may be using the text value
instead of the numeric ID.

Try code like:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's recordsource query
rs.FindFirst "[fieldname] = " & Me!Find_Command_Click
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark ' synch to the found record
End If
Set rs = Nothing ' clean up after yourself

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub


Of course, replace [fieldname] with the actual name of the field that
you want to search.

John W. Vinson[MVP]
Click to expand...
 
You had mentioned to change the combo box to "Unbound". I did this with my
normal Find_command button but now my macro won't work. It's saying that I
need the "MemeberID-Combo" to be listed as "Bound" inorder for the macro to
work.

melwester said:
Hi John,

The line where you have "rs.FindFirst "[Name] = " & Me!Find_Command_Click"
Does not work. So I did a little fixing of what I thought would be right -
is now coming up with a "Syntax error (missing operator) in expression".
Here is the code I have so far:

Private Sub Testing_2_Click()
On Error GoTo Err_Testing_2_Click

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's recordsource query
rs.FindFirst "[Name] = " & Me![MemberID-Combo]
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark ' synch to the found record
End If
Set rs = Nothing ' clean up after yourself

Exit_Testing_2_Click:
Exit Sub

Err_Testing_2_Click:
MsgBox Err.Description
Resume Exit_Testing_2_Click

End Sub

I've created another button that I'm using as a test. (Testing_2 button).
I cannot find where the problem lies. I did make the MemberID-Combo field
unbound like you said.

Also, will this work for finding Next as well?

John Vinson said:
I have built a form with a combo box that drops down the qry list. We now
need a Find Command that will search for a member that is in the qry list
from the form. I have created the Find Command Button using Wizard. But I
cannot find the members that are in the qry list. I know they are there.
It keeps coming up as "No record found". Here is the code that I have:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub

Am I missing something here. If so, please tell me what I need to put in.
Click to expand...

This is crufty OLD Access95 code. The wizards are way behind the time.

I'd suggest not using the wizard in this case. You need to be sure of
a couple of things:

- The Combo Box *must* be unbound, that is it should have no Control
Source property. (If it does, it will overwrite the value of that
field rather than finding a new record).

- Its Bound Column should be the field that you want to search. This
will very likely be a numeric key; if you've been misled into using
the Lookup Wizard on your table you may be using the text value
instead of the numeric ID.

Try code like:

Private Sub Find_Command_Click()
On Error GoTo Err_Find_Command_Click

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's recordsource query
rs.FindFirst "[fieldname] = " & Me!Find_Command_Click
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark ' synch to the found record
End If
Set rs = Nothing ' clean up after yourself

Exit_Find_Command_Click:
Exit Sub

Err_Find_Command_Click:
MsgBox Err.Description
Resume Exit_Find_Command_Click

End Sub


Of course, replace [fieldname] with the actual name of the field that
you want to search.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
You had mentioned to change the combo box to "Unbound". I did this with my
normal Find_command button but now my macro won't work. It's saying that I
need the "MemeberID-Combo" to be listed as "Bound" inorder for the macro to
work.
Click to expand...

Since I have no trace of a notion what your macro might be doing, I
cannot comment. However... a bound combo box WILL OVERWRITE THE VALUE
in the current record, even if it has code to move to another record.
I really, really think that you don't want to do that.
melwester said:
Hi John,

The line where you have "rs.FindFirst "[Name] = " & Me!Find_Command_Click"
Does not work. So I did a little fixing of what I thought would be right -
is now coming up with a "Syntax error (missing operator) in expression".
Here is the code I have so far:

Private Sub Testing_2_Click()
On Error GoTo Err_Testing_2_Click

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone ' get the Form's recordsource query
rs.FindFirst "[Name] = " & Me![MemberID-Combo]
Click to expand...
Click to expand...

If [Name] is a Text field you must include the syntactically required
quote marks; Chr(34) is the " delimiter character:

rs.FindFirst "[Name] = " & Chr(34) & Me![MemberID-Combo] & Chr(34)

But I'm not certain this is the problem. What is the Bound Column of
the MemberID_Combo? Is it an ID, or a name? What field are you
searching - a MemberID or a name? They must match; if your combo
retrieves an ID, then you need to search your table for an ID.

Note that Name is a reserved word. A Form has a Name property; a
textbox has a Name property; even a table has a Name property. Using
Name as a Name gets Access confused. You might want to change this
name to MemberName.

John W. Vinson[MVP]
 
Back
Top