Dlookup won't work

marshallb

Registered User.
Local time
Today, 21:54
Joined
Mar 19, 2010
Messages
13
I am trying to use dlookup to pull SSN's from a different table based on the last name. This is on a splitform. I have included the expression I am trying to use. I have been wrestling with this for a week or so and stumbled accrss this site. Can anyone help me?

=DLookUp("[SSN]","tblPesonalInfo","[Last Name]=Form![Last Name]")
 
Firstly, you need to reference the control on the form correctly.
The collection is Forms! and you need to include the form name.

Secondly since last name is a text string you will need to surround it in quotes.

Code:
=DLookUp("[SSN]","tblPesonalInfo","[Last Name]='" & Forms![formname]![Last Name] & "'")
 
Thanks for the fast response. I put the form name in and i am still getting an error. When I look at the info on the error the circular reference is highlighted. I have cut and pasted the string to ensure that I have it right. The form and table are used to track taskings to my unit. Quite often we have to switch taskings between people. I am trying to set this up so that when I pull the name up it will fill in the SSN (to start with). That way I can quickly change the personal info associated with the tasking by putting a new name in. Am I going about this the right way?

=DLookUp("[SSN]","tblPesonalInfo","[Last Name]='" & [Forms]![frmTaskings]![Last Name] & "'")
 
I'm not clear on the bigger picture but I expect you can use a join between the tables to achieve your goal. This is much more efficient than a DLookUp.
 
My database is used to track Name, SSN, DOB, etc. I am trying to incorporate tracking my members taskings and deployments as well. I had originally built it to use the SSN as the primary key. Unfortunately if I need to move a tasking from one person to another I have to retype all of the tasking data onto the other person. I was trying to set it up so that if I needed to change the tasking form one person to another I could change the last name and all the other data (first name, ssn, etc.) would automatically update. I am self taught in access and am not sure what is the best way to accomplish this.
 
You need to change the database design.

The person should only appear in the task tabbel as an FK.
The aspects of the person are stored in a Person table with a PersonID PK.
All aspects of the task are stored in a task table with a TaskID PK.

The task only required the PersonID and the rest of the ingormation is displayed on the from from the Person table.

You might need a many-to-many relationship between the person and the task if it is attached to more than one person.
The relationship is done via a join table. This has two fields. An FK to the PersonID and an FK to the TaskID.
 
I feel a little ashamed to admit this but you are talking over my head a little. I am not sure what an FK and PK are.

The tasks are only assigned to one person at a time. I think I may sanitize a copy of the db so that I can post it if neccesary. Until I get this figured out I may just set up lookup wizards for each box(lastname, firstname, ssn etc...).
 
Doing a lookup for a ssn based on a surname is fraught with danger. What happens if you have more than one person with the same surname "Smith", "Jones", etc. I think you need to be more specfic about the criteria you are using.

A working Example:

Dim StrName as String
Dim strSSN As String

StrName = "Smith"
strSSN = DLookup("[SSN]","TblNames","[Surname]=" & StrName & "'")

This is looking in the table TblNames for the SSN in the field SSN where the Surname = 'Smith'

DLookup finds the first instance so if the Smith you want is the second one then it will never find it.
 
I didn't think of that. I do indeed have several people with the same last name. I think I may just go with a lookup field for each column. Will begin working on those. Thank you both for your help.
 
From your description I am quite certain that you are approaching this entirely the wong way. You only need a very simple query.

PK is Primary Key. It is the field that identifies a specific record in a table.
FK is Foreign Key. It is a reference to a PK from another table.

The FK in the Person field of the task table identifies the record in the Person table that is connected to that task. The details of that person are not repeated in the Task table but pulled directly from the Person table using the PK-FK join.

This structure is fundamental to database design and you will struggle until you grasp it. Search the forum for "normalization".
 
I have read some on normalization and i think i have a basic grasp. What i am still unsure of is how to get one table to pull data from another table based on certain information (such as SSN). i will post a sanatized copy of my db so that you can take a look if you are so inclined (if i can get it below he size limit). I got this db from another air Force unit and I am trying to adapt it to my needs. Once again thank you for your help.
 

Users who are viewing this thread

Back
Top Bottom