Text box lookup

jlocke

Registered User.
Local time
Today, 01:04
Joined
Jul 23, 2002
Messages
31
I want to open a form to a specific record based on a value i enter on another form. The first screen a user sees they are asked to enter a lastname or phone number to find a patient. All i want is to have the form open to the patients record.

Please help driving me crazy.
 
If I was you I would create a combo box woth the PatientID as the bound column and then on the ClickEvent:

On Error GoTo ErrorHandler

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourForm"
stLinkCriteria = "[PatientID]=" & Me.combo.Column(0)
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit, acWindowNormal

Exit_Errorhandler:
Exit Sub

ErrorHandler:
MsgBox Err.Description
Resume Exit_Errorhandler
 
If you have a combo, Mark's suggestion will be fine. If you have two text fields, the following will be more like what you need:

stLinkCriteria = "[LastName] = " & Chr(034) & Me.txtSearchName & Chr(034) & " OR [PhoneNum] = " & Chr(034) & Me.txtPhoneNum & Chr(034)
 
here's my mdb

I have a form called "frm Lookup Main" on it i have a combo box, set to "on click" event, now when i select the users last name the form "frm_arcust99A" should open to the last name that the uset selected. I get nothing.. I've tried the code that Mark suggested and that helped sort of...
 

Attachments

Why not try my solution before posting again? I said if you had text fields you needed to use my suggestion. You knew your combo was a text field when I didn't. I was assuming that if you were using a combo, you would be using a numeric key. Since that is not the case, you need to surround your text values with quotes. And, since you will be inputting people's names, you need to worry about names like - O'Brien that contain single quotes. When a text field may contain single quotes, you need to surround your parameter with double quotes. A simple method of doing this is to use the chr(034) to return the character value of 034 which happens to be - "

Opening the form in the click event of the combo prevents the the other two text fields from being used. You should probably add a button and move the OpenForm code there.
 
jlocke,

It is worth taking note of what the "Female of the Forum" Pat Hartman has to say as the lady knows what she is talking about!!!!
 

Users who are viewing this thread

Back
Top Bottom