creating a control to go to a specific record on a form

[vangx222]

I'm new to Microsoft Access. I am currently using Access 2003. I want to create a form that I can enter a person's name and then click a go button and it would direct me directly to that person's record on another form that I have for them. How do I do this?

I know that the command button wizard can direct me to a certain form in my database, but I'm not sure on how to make it direct me to a CERTAIN RECORD on that other form.

Thanks.

 

[fneily]

See thread702-1357811
and look at the first code listing in the first post. Modify it for your needs.

 

[PHV]

Another way is to simply follow the combo wizard (3rd option) in the another form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vangx222]

Thanks for the help! I tried both suggestions.

After clicking the command button an "Enter Parameter Value" popup appears. Is their a reason for this and how can I get rid of it? When I pressed Cancel for that it still directed me to the right form and correct record that I needed even without entering the value for the "Enter Parameter Value" box.

 

[vangx222]

Maybe a more specific explanantion of what I'm trying to achieve would be this, if anyone has any tips please let me know.

I am constantly entering new patients into my database. Is there a way I can create a FIND form without having the find section be directly on the form that I want to look for the patient's records?

For example, I have a form called frmVISIT that contains the patients visit information with their name at the top.

I want to create a NEW form where I can type in, let's say their last name and then click a command button and have it either open me that person's record in that frmVISIT form OR have it give me a message stating that no such patient exists if their is not a record for that patient yet in the frmVISIT form.

Is this possible?

Thank you so much.

 

[PHV]

Have a look at the DLookUp function and the 4th argument of the DoCmd.OpenForm method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vangx222]

PHV,
Please bare with me and my minimal Access code understanding.

What do you mean by the DLookUp function?

Here's my code:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MAINfrm"

stLinkCriteria = "[MRN]=" & "'" & Me![MRN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

 

[PHV]

The DLookUp advice was for this requirement:
give me a message stating that no such patient exists

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vangx222]

PH or anyone willing to help,
How would I set up the code for the DLookUp function if in my form I have,

A field to enter in a First Name
a field to enter in a Last Name
a field to enter in a Birthday
and a field with a unique number identifier for each patient

I want to create an unbound field where, when I enter the unique identifier number, if there already exists a patient with that number then this unbound field would automatically retrieve the person's first name.

However if there's no one with that identifier, then a message would appear displaying that there's no record for that number.

I tried this putting this code in an unbound text field in the Control Source

=DLookup("[FirstName]", "[tblPatient]", "[MRN] = Forms![MAINfrm]![MRN]")

apparently what goes where. Is my code saying what I want at all?

 

[DPROMICS]

I am constantly entering new patients into my database. Is there a way I can create a FIND form without having the find section be directly on the form that I want to look for the patient's records?"

Perhaps you might want to rethink how you set up the Access app so that the work flow process is "smoother".

I created an Access App for a cardiogly office where the database was based on patients. Patient clinic visits were tied to the patient. The patients table had a one to many relationship to the patient clinic visits table.

The main form used TABS to facilitate getting at patients and entering patient clinic visit data. On the PATIENT'S TAB there is a list box that displayed all patients in alphabetical order by default. It also had a set of command buttons that corresponded to the alphabet. Clicking on the "A" command button caused only patient's who's last name started with "A" to be displayed, in alphabetical order in the list box. Clikcing on th e"B" command button cause only patients whose last name started with B, etc. That made looking up a patient easier. I also had two text boxes, one for the patient's last name and one for the patient's first name. One could enter only the last name and then click on the FIND PATIENT command button which would then cause a list of all patients with that last name to be displayed in the list box. Then one could click on a patient in that list and the patient would be the currently selected patient. If a first name was also entered and the command button was clicked on the specific patient's name would be displayed in the list box. If no patient existed with that name the list box was empty. On the main form was also a command button labeled ADD NEW PATIENT. Clicking on that command button would open up a pop-up form where one could enter a new patient's name and demographic information. When the new patient's data was saved the pop-up form closed and the new patient was the only name displayed in the patients list box on the main form.

Once a patient was selected one could click on the CLINIC VISIT TAB on the main form to go to the clinic visit section where clinic visit information for that patient could be entered.

The clinicans found that organization and "work flow" of the Access app useful, easy to learn, and more importantly, to use.

You can see a picture of the Access program's main form here:

http://www.promicscorp.com/promics-ecmr-program.htm

. There is no advertising on that web page. Only a picture of how I organized my Access app for the patient clinic visit medical records needs of that cardiology office.

Also, you might find it useful to look at the sample NORTHWINDS Access app and database that comes with Access. Looking at how it is setup and how it does things might be helpfull as you progress from an Access newbee to an Access geru. ;>)

Best regards,