Please help easy for you (1 Viewer)

[Febrero127]

Someone please help with this issue. I just need to understand how to do it once and after that I can move forward. Something simple:

-I have a table with employee information (userid, name, shift, etc...)

-I have a form with 2 text box (user id and name)

- how can i make my 2nd text box auto populate after i have typed in a user id ?
What vba code/query/module should i have and where do i put this at?

I have other stuff that i need to do but this should help me out to understand better.

Thanks,

 

[CJ_London]

have you considered using a combobox to find the userid from the name?

here are a few suggestions

1. Combobox called cbousername with
rowsource ="Select userid, username from tblemployee"
columncount=2
boundcolumn=1
columnwidths=0;2

2.Combobox called cbouserid with
rowsource ="Select userid, username from tblemployee"
columncount=2
boundcolumn=1
columnwidths=1;2

plus a separate textbox with controlsource as =me.cboUserid.column(2)

3.textbox called userid
plus separate textbox with controlsource as =dlookup("username","tblEmployees","userid=" & me.userid)

4. if you want to use vba then in the userid textbox after update event put

dim rst as recordset
 
set rst=currentdb.openrecordset("select username from tblemployees where userid=" & me.userid)
me.username=rst!username
set rst=nothing

5. or instead you put

me.username=dlookup("username","tblEmployees","userid=" & me.userid)

 

[Rand]

in the second box = DLookup("[name]", "[your table]", "[id]=" & textbox1)
or bind the form to the table or in the first text box use a procedure after update
and use the dlookup in code
oopz someone beat me to it

 

[Febrero127]

dim rst as recordset

set rst=currentdb.openrecordset("select username from tblemployees where userid=" & me.userid)
me.username=rst!username
set rst=nothing


Thank you CJ London. It is working now.

What about if I had a ODBC Connection and I need to use a table from there. What would I place instead of

set rst=currentdb.openrecordset

 

[CJ_London]

providing your ODBC is showing a linked table and you have properly identified the uniqueid column (which you do when creating the linked table) then there is no change.

If you do not have a linked table, then you need to modify the sql used in the openrecordset command to include the connection string - or you can use ADODB and set the connection string there.

For more information on this, search for 'connection strings' on the web - it will vary depending on what server you are connecting to