[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

How do I lookup a value in another table from an expression?

I am currently writing an Access 2003 database for tracking patient scores on various tests. I have a Patients table and a Test table where each record in the Test table has a "patientid" which is a foreign key back to the Patients table.

I am writing the data entry screen for the Test table, but I would like to display some additional information in that form about the patient (full name, ID, DOB, etc). I was thinking I could use expressions to take the patientid from the current Test record, look up the corresponding Patient record, then return the name. Problem is, I don't know how to "look up" values like this in the Expression Builder. So far, all I have is something like this:

[Patients]![PatientName]

But all that returns is "#Name?". How do I get it to look up the value for the correct record?
0
medium_grade
Asked:
medium_grade
  • 11
  • 8
1 Solution
 
mbizupCommented:
If this table is not in your query, one approach would be DLookup:

DLookup("PatientName", "YourTableName", "PatientID = " & PatientID)
0
 
mbizupCommented:
Alternatively, and this may be faster than Dlookup...

Use a User Defined Function with recordset code:

Function GetPatientName(lngID as Long)
      Dim rs as Dao.recordset
      set rs = currentdb.openrecordset("SELECT PatientName FROM YourTableName WHERE PatienID = " & lngID, dbopendynaset)
      If rs.recordcount > 0 then
          GetPatientName = rs!PatientName
      Else
          GetPatientName = Null
      End if
      rs.close
      set rs = nothing
end function

And add a field to your query.  In the query's design view:
      PatientName: GetPatientName([PatientID])
         
0
 
medium_gradeAuthor Commented:
Include the quotes around the field/table names?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mbizupCommented:
<Include the quotes around the field/table names?>

Yes.
0
 
medium_gradeAuthor Commented:
Getting "#Error" when using DLookUp. Any ideas? How do I find out specifically what's wrong?
0
 
medium_gradeAuthor Commented:
Are the comparison operators different for strings than they are for numbers? The field is question is a TEXT field (Integration with legacy data. Don't ask).
0
 
mbizupCommented:
<Are the comparison operators different...>
Not the comparison operators, but the delimiters used around the field in the criteria.

Numeric: No Delimiters
Text: Quotes
Date:  Pound signs (#)

Since your ID field is text (look closely for embedded single quotes):

DLookup("PatientName", "YourTableName", "PatientID = '" & PatientID & "'")

Also, if this is a control source for a textbox, etc on your form, you need to include an = sign:
= DLookup("PatientName", "YourTableName", "PatientID = '" & PatientID & "'")
0
 
mbizupCommented:
Finally, as a sanity check, make sure that you have replaced YourTableName with the actual table name.
0
 
medium_gradeAuthor Commented:
Got it!

=DLookUp("[PatientName]","Patients","[PatientID] = '" & [PatientID] & "'")

Works like a charm! Thanks!
0
 
mbizupCommented:
Excellent!! :-)

If you're up trying some VBA, give the function I posted a shot as well.  That is a good alternative, and if you ever need to use this as part of a query (with many records), you will find the function has much better performance.
0
 
medium_gradeAuthor Commented:
Thanks again :-)
0
 
medium_gradeAuthor Commented:
Sorry to be a bother, but I tried defining the function as you mentioned before. What I did was to create a Module called "GetPatientName" and in it, I defined the following function:

Function GetPatientName(PatientID As String)
      Dim rs As DAO.Recordset
      Set rs = CurrentDb.OpenRecordset("SELECT PatientName FROM Patients WHERE PatienID = " & PatientID & "'", dbOpenDynaset)
      If rs.RecordCount > 0 Then
          GetPatientName = rs!PatientName
      Else
          GetPatientName = Null
      End If
      rs.Close
      Set rs = Nothing
End Function

Then, in my form, I have a Text box which has the following expression:

=GetPatientName([PatientID])

But all text box displays is "#Name?". What should I be looking for?
0
 
mbizupCommented:
Hey... You caught me at a good time :-)

<What I did was to create a Module called "GetPatientName">
<Function GetPatientName(PatientID As String)>

That is your issue right there.

Access gets hoplessly confused if you have two functions, modules, variables etc given the same name within the same scope.

add "mod" as a prefix to your module name and you should be fine.
(modGetPatientName versus GetPatientName, which is the function)
0
 
mbizupCommented:
As an aside, the following link has the Reddick VBA naming conventions that are used by most of the regulars here at EE:
http://www.xoc.net/standards/rvbanc.asp#Access

It basically involves prepending all of your names with meaningful tags to distinguish them.
eg:
cboYourComboName
txtYourTextBox
lstYourListBox

They use 'bas' for code modules. Many of the Experts here (myself included) use "mod" instead.

Also, although it doesn't look like an issue for you, these naming conventions specifically avoid spaces and special characters of any kind.

Sticking to these conventions can help avoid major headaches for you as a developer.
0
 
medium_gradeAuthor Commented:
Perfect. Thanks.

I am now getting the following error when I try to launch the form:

Runtime error '3061':
Too few parameters. Expected 1

When I press "Debug" it takes me to the following line:

Set rs = CurrentDb.OpenRecordset("SELECT PatientName FROM Patients WHERE PatienID = '" & PatientID & "'", dbOpenDynaset)

Does the OpenRecordset function take anymore parameters?
0
 
mbizupCommented:
No... your syntax is good.

This error usually means Access is having trouble resolving a field name.

Take a look at how you've spelled "PatienID" (no 't') in the WHERE clause...  :0
0
 
mbizupCommented:
Correction... I guess that was my typo originally. LOL!
0
 
medium_gradeAuthor Commented:
Ha! No worries. I should have caught that myself. I'm just very new to Access and I froze.

Thanks.
0
 
mbizupCommented:
You're doing fine :-)

I couldn't tell you how many Access beginners run away screaming at the very mention of "VBA".

Glad to help out, and enjoy the rest of your weekend!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now