Searching for records in a form (1 Viewer)

Taz

New member
Local time
Today, 15:44
Joined
Jan 2, 2000
Messages
8
Hi there. This problem probably requires a macro or code, but since it occurred on a form, I put it here.

There are problems with the "search" button on a form which contains various names of clientele. This form has several subforms that are linked, but I don't think this has any bearing on the problem.

I'd like the "search" button to put up a inputbox asking for the last name of the client, and then go to that record within that same form. But the Goto command in Code can only use integers indicating record number. The only problem is, new clients are entered every day, and the Form is linked to a Query which re-alphabetizes them (I don't know how to use a Refresh or Requery command syntactically). So the record number is never constant for a given Last Name.

I hope I'm making sense, but does anyone know of a way to jump to a record without manually searching for the name (in the query)and counting its record number? Thanks for reading.
 

Travis

Registered User.
Local time
Today, 08:44
Joined
Dec 17, 1999
Messages
1,332
You are correct that the Goto only uses an Number(Long). This is the current Index of that record in your recordset. What your search criteria needs to do is determine what record in you recordset do you want to move to and then pass it to the GoTo function.

Try this:

Public Sub SearchField(stFind as string)

With Me.RecordsetClone
.MoveFirst
.FindFirst "FieldToSearch = '" & stFind & "'"
If Not .NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, .AbsolutePosition + 1
Else
'Not Found
End If
End With

end sub

This will find the first value that meets your requirements. Try using the other Finds to see if you need the other functionalities.

The reason for the AbsolutePosition + 1 is that the AbsolutePosition is the Ordinal Position (Meaning it starts 0 to whatever) where the Records Position is measured from 1 to whatever.
 

Taz

New member
Local time
Today, 15:44
Joined
Jan 2, 2000
Messages
8
Travis, thanks for the help, although it's stuck with a MISSING OPERATOR syntax error, which happens right after typing in the last name.

Here's what I have: I turned your public sub into a function

Function SearchField(stFind As String)

With Me.RecordsetClone
.MoveFirst
.FindFirst "Last Name = '" & stFind & "'"
If Not .NoMatch Then
DoCmd.GoToRecord acDataForm, Me.Last_Name, acGoTo, .AbsolutePosition + 1
Else
'Not Found

End If
End With

End Function

This function is used by the Event of the Search button, which does

Dim Last As String

Last = InputBox("Type in the Last name of the Patient", "Patient Search", , 240, 240)

Run SearchField(Last)

I'm not sure where the operator should be in this; any ideas anyone? Thanks in advance.
 

Travis

Registered User.
Local time
Today, 08:44
Joined
Dec 17, 1999
Messages
1,332
I think I can help.

First the MISSING OPERATOR syntax error is caused by the .FindFirst "Last Name = '" & stFind & "'" line. I appoligize for not noting this but when you have a two or more word name for a field you have to use [ ] around the name.

it should look like this.
.FindFirst "[Last Name] = '" & stFind & "'"

I recieved another error in your code also, it deals with this line :

Run SearchField(Last)

Run is best used when calling from another application to the access proceedure. I would suggest using Call instead of Run.

Hope this helps
 

Taz

New member
Local time
Today, 15:44
Joined
Jan 2, 2000
Messages
8
Travis, hey thanks. It's working better though still not to its full extent. I made all the changes you suggested but an error message (again...) pops up saying

The Object 'Blah' is not Open.

Where Blah = the Last Name of the current patient I happen to have the form on. I think the program is interpreting the syntax of Me.Last_Name in the DoCmd.GoToRecord as the actual text box and not the field.

I apologize that it's the 3rd time I've asked for help, but it's getting close; everything else seems to be progressing fine, because it finds the match ok, but it won't go to that record.

Anywho, thanks in advance.
 

Travis

Registered User.
Local time
Today, 08:44
Joined
Dec 17, 1999
Messages
1,332
oh boy did I really screw up.

the me.Last_name should be me.name

Me.Name returns the name of the current form which of course is the Object we are moving records on. And of Course Me.Last_Name (our friend/Nemisus Copy Paste) is invalid syntax for the GOTO command. I'm sorry for the delay I have caused you.

Happy coding.
 

Taz

New member
Local time
Today, 15:44
Joined
Jan 2, 2000
Messages
8
Travis

Well, your magic worked. Thanks a bazillion. We've memorialized you in our credits. Have a good one.

Todd
 

Users who are viewing this thread

Top Bottom