Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form from a table - i have a field Staff name and a field National
Insurance Number on the form;
because it is linked directly to the table and not been created through a
query i want a way that when i input the user name the NI number
automatically populates-i do have a separate table Staff which contains the
staff name and NI number, can anybody tell me how to do it - i know it is as
a link criteria but do i need to do it as code or can i put it into
properties of ni field
Thanks John
 
in the afterupdate of the username type something like this:

me.ninumber=dlookup('NInumber','Staff', "Username = '" & me.username & "'")

replace the fieldnames with your own fieldnames
note: first in dlookup is the field you are looking for
second in dlookup is tablename where to look
third in de dlookup is the wherestatement

After username= place single quote and double quotes
After me.username place double, single and double quotes again.

hth
 
Thanks for reply Maurice - here is what i wrote based on the fields but it
did not work - have i written it correctly cheers
me.national insurance number=dlookup('National Insurance number','Staff',
"member of staff = '" & me.member of staff &"'")

table is 'staff', fields on form are 'member of staff' and 'National
Insurance number'
 
Change it to the following:

me.[national insurance number]=dlookup("[National Insurance number]","Staff",
"[member of staff] = '" & me.[member of staff] &"'")

In this example I assume that the controls on your form are actually called
[national insurance number] and [member of staff]. You have to place them
between square brackets because of the spaces in the controlname. Always best
practice to not use spaces in your fieldnames, now you know why. Have a go
and let me know if it works..
 
Sorry Maurice but i am getting a message now:
'Microsoft Access cannot find the macro 'me'.
What does that mean?
Also should i change the field names to avoid the spaces?
John

Maurice said:
Change it to the following:

me.[national insurance number]=dlookup("[National Insurance number]","Staff",
"[member of staff] = '" & me.[member of staff] &"'")

In this example I assume that the controls on your form are actually called
[national insurance number] and [member of staff]. You have to place them
between square brackets because of the spaces in the controlname. Always best
practice to not use spaces in your fieldnames, now you know why. Have a go
and let me know if it works..

--
Maurice Ausum


Khartoum said:
Thanks for reply Maurice - here is what i wrote based on the fields but it
did not work - have i written it correctly cheers
me.national insurance number=dlookup('National Insurance number','Staff',
"member of staff = '" & me.member of staff &"'")

table is 'staff', fields on form are 'member of staff' and 'National
Insurance number'
Click to expand...
Click to expand...
 
Back
Top