Help with Dlookup

  • Thread starter Thread starter David Ehrenreich
  • Start date Start date
D

David Ehrenreich

Hello,

I need help with Dlookup. I work in a office where we
have these sign-in cards that have a social security
numbers and first and last names. It also has some other
misc information. Since we have tons of these cards that
need to be entered. I would like to be able to enter the
social security and if it had been entered before it would
fill in the First and Last name. I once had a procedure
that was similar that looked like this, but I cant figure
out how to change it to make it work.

If Not IsNull(DLookup("ssn", "CardInfoTbl", _
"DSS=True AND ssn=" & Me.ssn)) _
Then
MsgBox "This Student has had prior DSS Services"

This code showed a msgbox whenever a certain ssn had DSS
chkbox checked
 
David said:
Hello,

I need help with Dlookup. I work in a office where we
have these sign-in cards that have a social security
numbers and first and last names. It also has some other
misc information. Since we have tons of these cards that
need to be entered. I would like to be able to enter the
social security and if it had been entered before it would
fill in the First and Last name. I once had a procedure
that was similar that looked like this, but I cant figure
out how to change it to make it work.

If Not IsNull(DLookup("ssn", "CardInfoTbl", _
"DSS=True AND ssn=" & Me.ssn)) _
Then
MsgBox "This Student has had prior DSS Services"

This code showed a msgbox whenever a certain ssn had DSS
chkbox checked
Click to expand...


Check your ssn field in the table. I'll bet that it's a
Text type field and, if so, then you quotes around it.

If Not IsNull(DLookup("ssn", "CardInfoTbl", _
"DSS=True AND ssn='" & Me.ssn & "'"))
 
The ssn feild is a number feild

The code I showed worked, I'm trying to figure out how to
change it so that if a social security number has been
used then the first name and last appear in their feilds.

Thank you for your input sofar

David Ehrenreich
 
The ssn feild is a number feild

The code I showed worked, I'm trying to figure out how to
change it so that if a social security number has been
used then the first name and last appear in their feilds.
Click to expand...

Sorry, I thought the problem was that you were not getting
getting the message box.

Try just changing the DLookup to retrieve the name instead
of the ssn:

Dim varPersonName As Variant
varPersonName = DLookup("namefield", "CardInfoTbl", _
"DSS=True AND ssn=" & Me.ssn)
If Not IsNullvarPersonName ) Then
Me.sometextbox = varPersonName
End If
 
First of all, dlookup is very inefficient, so if there are 'tons' of
these records a recordset would make much more sense. Say you have a
textbox: txtSSN, a text box to hold found names, txtName, and a
command button, cmdFind.

Code the cmdFind click event as follows: (this is for an ADP.
connection is different (I think) for Access back end.)

Sub FindSSN()
Dim rs As adodb.Recordset
Dim myVal As Integer
Set rs = New adodb.Recordset
myVal = Me("txtSNN") 'Ideally trim, and also validate for a valid
number
rs.Open "select * from CardInfoTbl where SSN =" & myVal,
CurrentProject.Connection, adOpenForwardOnly
If rs.EOF And rs.BOF Then
Me("txtName") = "no records!"
Else
Me("txtName") = rs("MyName")
End If
Set rs = Nothing
End Sub

Peter
 
Back
Top