Lastname Lookup Field

[Gigi0614]

Hi!
I've got a Patient_INFO table that has information about patients. The key is PatientID. I have a lastname, firstname, SSN, ect....

How can I design my table & forms so that the user can
do a lookup on lastname?

I'm not quite sure how to do that...but I think you have to build the table to allow for that, right?

 

[SkipVought]

Gigi0614,

Tou want to design each relation (table) with careful thought and sound design principles. Once designed, a simple query could be performed on ANY column value or combination of column values.

If performance is an issue, Last Name or any other column that is not a key value can be assigned an Index in the Design View.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[willir]

Gigi

Unbound combo or list boxes are great for this type of thing.

Add a combo box to the form, and use the wizard...
- Find a record on my form based on the value I select...
- Choose your field, in your case lastname
Side bar - Although you selected lastname, Access will select patientid and lastname so that it can find a specific record based on the primary key.
- On the next screen, "Hide key column" will be checked - this is good.
- The last page of the wozard will prompt you for the caption on the label.

After this is over, look at the combo box in design mode with the properties window open. (from the menu, "View" -> "Properties")

Select the combo box. On the "Data" tab of the properties window you will see...
ControlSource: empty
RowSource: a select statement. Click on this field, and then click on the command button "..." that will appear to the right. This will open the Query Builder. Notes that Access has added two fields PatientID and lastname. Add the field firstname. Close and save the query builder.
BoundColumn: will be 1, patientID

Now click on the "Format" tab, you will see...
ColumnCount: 2 Change to 3 to accommodate firstname.
ColumnWidths: 0";1" Change to 0":1";1" to accommodate firstname
Sidebar: A column with 0" will be "invisible"
ListWidth Either 1" or Auto. Change to 2" to accommodate firstname

Now click on the "Event" tab, you will see "[Event Procedure]" for the AfterUpdate event. Click on the AfteyrUpdate field, and then click on the "..." command button to open the Visual Basic coding window. You will something along the lines of...

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

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[PatientID] = " & Str(Me![Combo7])
    Me.Bookmark = rs.Bookmark
End Sub

Pretty cool stuff. My only beef about the wizards that they do not allow you to determine the name of the sub routine. In the depicted example, the control is named Combo7. A more meaningful name would be cmbFindPatient - BUT dont change this.

Close the VBA coding window, and click on the "Other" tab. The Name field will depict the same name as seen in the coding window.

Save and test the combo box. The name selected in the combo box should be retrieved AfterUpdate event.

Richard

 

[Wes1961]

Richard....

Before you click on "[Event Procedure]" for the AfterUpdate event. In properties for the combo box, click the tab "Other", change the field called "name" from combo7 to your more meaningful name like "cmbFindPatient".

Now when you can click the "[Event Procedure]" and the wizard will use this name in creating the code.

 

[willir]

Thanks Wes - I am aware of this - in fact, I break out of the Wizard about 90% of the time to do this little tweak -- using meaningful names is important to me.

However, for this solution, I got lazy and just gave a solution that required less work for the user. As you know, changing the combox box control name AFTER letting wizard complete it's work will generate problems.

So I know how to address this issue. But how do you tell a person who looks like a "newbie" how to do all this without re-writing a book on Access?

...And by the looks of it, this may be a moot point since it appears Gigi never followed up on their post.

Richard

 

[Wes1961]

Richard...sorry for even assuming I could offer you advise....after spending some time reading other threads I realize that I will never be the "guru" you are. It's great knowing that there are people out there like you that take the time to help people. Cheers!

 

[willir]

Wes Ahh shucks.

No need to be sorry. First, your point was quite valid. Second, it is very difficult to "read minds" from afar -- it is very difficult, especially with this type of example, why I chose to do something one way when it makes good programming sense to do it another. Third, I am always learning -- learned two things today from Tek-Tips so I have a long way to go before becoming a geru. But you are right in that I hope that I help some people with their issues.

And lastly, I always consider it very good character to own up to ones mistakes.

Take care.
Richard