Solved

Retrieve information from table in an unbound text box

Posted on 2011-02-22
10
276 Views
Last Modified: 2012-05-11
Hi all,

What would I set an unbound text box control source to in order to get the third column from tbl_table1 where the text box employee matches the employee name in the table?

If there is nothing in the column then it should just remain blank.

Thanks

Chris
0
Comment
Question by:smods
  • 5
  • 5
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34952077
use a dlookup() function

=nz(dlookup("ColumnName","tbl_table1","EmployeeName='" & me.textEmployeeName & "'"))
0
 

Author Comment

by:smods
ID: 34952236
Thanks capricorn1

The text box is just returning #Name?

I can't work out why.  I've amended the code as per my field names

=nz(DLookUp("Employee_Number","tbl_CashierDetails","Cashier='" & me.textCashier & "'"))
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34952340
sorry

=nz(DLookUp("Employee_Number","tbl_CashierDetails","Cashier='" & Forms!NameOfForm!textCashier & "'"))


change nameOFForm with the actual name of the form
0
 

Author Comment

by:smods
ID: 34952491
No worries! I have done this and it works when you open the subform directly but not when you open the main form that the subform is in.  I still get #Name?

What would be the reason for this?

Regards

Chris
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34952617
i didn't see you mentioned a subform in your original post..??
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:smods
ID: 34952716
Grrr, my bad! Sorry

Is there a way around this?  Points increased to 500
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34952776
ok,

where is the textbox that you want to display the  "Employee_Number" located?
where is the textbox  "textCashier" located?

what is the name of Main form? the name of the subform?
0
 

Author Comment

by:smods
ID: 34952911
The text box where I want to display the employee number is on the subform.

The text box textCashier is also located on the subform.

The main form is called frm_New_KPI_Main

The subform in the main database window is called frm_New_KPI_Sub but the actual name of the subform when viewed inside the main form is called frm_KPI_Sub

Regards

Chris
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34953015
test this

=nz(DLookUp("Employee_Number","tbl_CashierDetails","Cashier='" & Forms![frm_New_KPI_Main]![frm_KPI_Sub].Form!textCashier & "'"))
0
 

Author Closing Comment

by:smods
ID: 34953075
Brilliant

Thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now