Dlookup in Access 2003 - form control details

I've read many, many samples, still can't get this right.  Can someone explain all of the important details when using dlookup in a form control.  My example is 2 tables, they have a many/one relationship established.  It's a classic, I need "suppl_name" from the Suppliers table to be looked up in the "PC_Master" table-based form with the equal fields of the suppl_id that is in both tables.  In the supplier table the suppl_id is the primary key.  I've tried with both tables and a query.  I've tried putting the dlookup expression in both the default value box and the control source box of the text field on the form (lkupSuppl_Name).  Do I need the relationship?  Am I putting this in the wrong box?  I am getting the #Name? error message.  Any help would be greatly appreciated.
2thewebAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

2thewebAuthor Commented:
If you know of any detailed info on form controls, I'd appreciate the referral.  Thanks.
0
TextReportCommented:
Firstly don't use DLookup it is very inefficient.
Have your form Based on a query that joins the tables and thus you are able to use any field from both tables on the form.

With regards to the DLOOKUP check that you are refering to ControlNames and not FieldNames and check that the ControlNames are not the same as the field name.

Cheers, Andrew
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Online help will provide you with full syntax of the usage and such of all the Domain functions ...

DLookup is much faster in 2003 and 2007, and while I don't use them they are simple and quick, if used correctly. I'm not sure I"d base my form on a query, since that can cause some issues with data inserts/edits, but you might consider changing the control that shows the suppl_name to a Combo, which could lookup the value of the suppl_name based on the value of the suppl_id.

Basically, DLookup returns a value based on what you input:

=DLookup("FieldYouWantToReturn", "TableOrQueryName", "Criteria")

So you'd do something like this:

=DLookup("suppl_name", "Suppliers", "suppl_id=" & Me!suppl_id)

Note that if suppl_id is a Text value, you'll need to enclose it in single quotes:

=DLookup("suppl_name", "Suppliers", "suppl_id='" & Me!suppl_id & "'")
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

2thewebAuthor Commented:
Thats sounds good, I will try it.  As a follow-on, if I use the query approach, does that mean each time I add records to either table, I need to update/run the query?  And if so, is there a way to automatically do this when a record is added?  Thanks again, I will respond with results shortly.
0
2thewebAuthor Commented:
So the dlookup expression goes in the default value box in properties of the unbound text field?  And the unbound text box field name cannot be the same as the actual field from the foreign table?  PS, this is for display only.  The suppl_id value comes from a 3 column combobox.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, you form would automatically show your changes if you use a query. If you decide to go this way make sure your query is updateable else you won't be able to add/edit records

No, the DLookup would go in the ControlSource of the unbound textbox ... you can name the textbox CONTROL whatever you like. Note that you MUST include the preceeding = sign:

=DLookup(blah blah)

You should also wrap this in the Nz function, in case DLooup return a Null:

=Nz(Dlookup(blah blah))

Regarding your combo - which column in the combo contains the suppl_id? You must refer to that correctly, and remember that combo columns are zero-based, so:

MyCombo.Column(0) is the first column
MyCombo.Column(1) is the second column
etc

so I'd say something along these lines:

=Nz(DLookup("suppl_name", "Suppliers", "suppl_id=" & Me.YourCombo.Column(0)),"")
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One further note: The Default Value applies ONLY to new records ... it has no impact on existing records.
0
2thewebAuthor Commented:
Lemme try again...

Combo box that holds suppl_id value is named "cboFindSupplID" - the expression contained in that combobox is SELECT Suppliers.suppl_id, Suppliers.suppl_name, Suppliers.suppl_city FROM Suppliers ORDER BY [suppl_name]; The suppl_id value is in the 1st or Column 0.  The cboFindSupplID field populates the suppl_id field in the PC_Master ("contract table").

I would like 3 lookup fields on the form frmPC_Master to display the name, city and state of the supplier next to the suppl_id number that is found from the cboFindSupplID combobox.  (The supplier id is very long and makes no sense on it's own).

The unboound textbox is named lkupSuppl_Name and I am using this expression in the control source
=Nz(DLookUp("suppl_name","Suppliers","suppl_id=" & [Me].[cboFindSupplierID].[Column](0)),"")
I am still receiving the #Name? response on the frmPC_Master.

Am I really stupid to not get this?


0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Actually, the simplest way is to use the combo's Columns to do this. Since the combo is bound, you'd probably want to do this on the Form_Current event, which will fire whenever the user moves from record to record. First, though, I'd add the SupplierState to your combo box columns, even if you hide it, so change the RowSource for cboFindSupplID to:

SELECT Suppliers.suppl_id, Suppliers.suppl_name, Suppliers.suppl_city, Suppliers.suppl_state FROM Suppliers ORDER BY [suppl_name];

Note that you will need to change "suppl_state" to match the column name in your Suppliers table.

Now, make sure to adjust the ColumnCount in the combo to 4. If you don't want to show the State column, set the ColumnWidths property appropriately (a 0 value will hide the column). Now do this:

Sub Form_Current()
  me.txtSupplierName = Me.cboFindSupplID.Column(1)
  Me.txtSupplierCity = Me.cboFindSupplID.Column(2)
  Me.txtSupplierState = Me.cboFindSupplID.Column(3)
End Sub

Note that if you allow users to change this value you'd need to do the same thing in the AfterUpdate event of cboFindSupplID ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
2thewebAuthor Commented:
I placed the Sub Form_Current() code in my form.  I had to add "Private" in front of the Sub Form_Current() statement or I received an error message (I'm attached the forms code).  I adjusted the columns in the combobox to 4.  All four field appear with the values.  I'm still receiving the #Name? error.  The name of my textbox (unbound?) is txtSupplierName and the control source is =Me.cboFindSupplID.Column(1) - is that correct?  Do I have to set any limit to list boxes or anything.  I think I need to increase the point value of this question.  But I'm not giving up.
rr_Close_PC_Master_Form_Click:
    MsgBox Err.Description
    Resume Exit_Close_PC_Master_Form_Click
    
End Sub
 
Private Sub Opens_Menu_1_Click()
On Error GoTo Err_Opens_Menu_1_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_Menu_1"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Opens_Menu_1_Click:
    Exit Sub
 
Err_Opens_Menu_1_Click:
    MsgBox Err.Description
    Resume Exit_Opens_Menu_1_Click
    
End Sub
Private Sub Combo134_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[pc_id] = '" & Me![Combo134] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Private Sub Form_Current()
Me.txtSupplierName = Me.cboFindSupplID.Column(1)
Me.txtSupplierCity = Me.cboFindSupplID.Column(2)
Me.txtSupplierState = Me.cboFindSupplID.Column(3)
End Sub
 
    
Private Sub SavePCMaster_record_Click()
    ' Save Form holding current records
 
On Error GoTo Err_SavePCMaster_record_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_SavePCMaster_record_Click:
    Exit Sub
 
Err_SavePCMaster_record_Click:
    MsgBox Err.Description
    Resume Exit_SavePCMaster_record_Click
    
End Sub

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Remove the ControlSource from the txtSupplierName, txtSupplierCity and txtSupplierState controls.
0
2thewebAuthor Commented:
I removed the control source from the unbound text box txtSupplierName, and the error is gone, but nothing is displayed.  How many points should I chang this to? LOL, Jane
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Set a breakpoint in your code in the Form_Current event (place your cursor on a line and press F9), then run your code and see what's happening ... you can use the Immediate window at this point to see what, exactly, is in the Columns of your combo. To do that, while in Break mode, type this in the Immediate window:

?Me.cboFindSupplID.Column(1)

and press the Enter key ...
0
2thewebAuthor Commented:
When I hit run in my vb code screen, it asks for a macro name?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You have to "run" your code ... that is, open the form and navigate through the records ... the breakpoint will throw you into the VB edit window, where you can examine the variable values and such.
0
2thewebAuthor Commented:
I tried this and couldn't get the VB window to open.  I am home now and won't be able to do anything until i go back to work tomorrow morning - VERY EARLY!  Is there any way I can send you some of the forms code?  I have a number of these comboboxes, so if I can get this to work, it would be a great example.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can zip your file and post them to www.ee-stuff.com, then post a link back here for the download.
0
2thewebAuthor Commented:
That sounds great.  I'll shoot off a copy tomorrow morning!  Thanks again.
0
2thewebAuthor Commented:
Well, I'm still struggling with the lookup fields.  I got sidetracked with another project last week.  I have attached below the code from the "level 1 menu" (temporary name).  That leads to the form frmPCMaster.  The drop down combo that gets the supplid value originates there.  In the same form frmPCMaster (also exported and enclosed) is where I want to display the value of the suplier name and the supplier city and state.  Please take a look and let me know if I can make this work.
'This is the first menu that i've created
 
Option Compare Database
 
Private Sub Open_frmPCMaster_to_a_specific_pc_Click()
On Error GoTo Err_Open_frmPCMaster_to_a_specific_pc_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    
   stDocName = "frmPC_Master"
    
    stLinkCriteria = "[pc_id]=" & "'" & Me![cboPCNumber] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
   'Clear the value of the stLinkCriteria
   
    cboPCNumber = ""
 
Exit_Open_frmPCMaster_to_a_specific_pc_C:
    Exit Sub
    
    
 
Err_Open_frmPCMaster_to_a_specific_pc_Click:
    MsgBox Err.Description
    Resume Exit_Open_frmPCMaster_to_a_specific_pc_C
    
End Sub
Private Sub cboPCNumber_AfterUpdate()
 
End Sub
 
 
Private Sub Exit_out_of_Menu_1_Click()
On Error GoTo Err_Exit_out_of_Menu_1_Click
 
 
    DoCmd.Close
 
Exit_Exit_out_of_Menu_1_Click:
    Exit Sub
 
Err_Exit_out_of_Menu_1_Click:
    MsgBox Err.Description
    Resume Exit_Exit_out_of_Menu_1_Click
    
End Sub
Private Sub AddNewPC_Click()
On Error GoTo Err_AddNewPC_Click
 
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_AddNewPC_Click:
    Exit Sub
 
Err_AddNewPC_Click:
    MsgBox Err.Description
    Resume Exit_AddNewPC_Click
    
End Sub
Private Sub OpenSupplierForm_Click()
On Error GoTo Err_OpenSupplierForm_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmSuppliers"
    
    stLinkCriteria = "[suppl_id]=" & "'" & Me![cboFindSupplier] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    'Clear the value of the stLinkCriteria
    cboFindSupplier = ""
 
Exit_OpenSupplierForm_Click:
    Exit Sub
 
Err_OpenSupplierForm_Click:
    MsgBox Err.Description
    Resume Exit_OpenSupplierForm_Click
    
End Sub
 
Private Sub OpenFormPCMaster_Click()
On Error GoTo Err_OpenFormPCMaster_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmPC_Master"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_OpenFormPCMaster_Click:
    Exit Sub
 
Err_OpenFormPCMaster_Click:
    MsgBox Err.Description
    Resume Exit_OpenFormPCMaster_Click
    
End Sub

Open in new window

Copy-of-frmPC-Master.txt
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm confused at this point ... if you can provided a .zipped copy of your database to www.ee-stuff.com, perhaps we could help a bit more.
0
2thewebAuthor Commented:
Where on www.ee-stuff.com does it go?  Can I include it below as a file?  The mdb file is 2,400kb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure ... you could probably do so if you Zip the file and then change the .zip file extension to something the Attach File will accept (like .png, for example).
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, please writeout a concise few sentences as to EXACTLY what the problem is, and what steps I can take to reproduce that problem in your downloaded database.
0
2thewebAuthor Commented:
Please let me know if this is not clear.

I have supplier data in the table "Suppliers"
I want to lookup the values of suppl_name, suppl_city, suppl_state by matching the suppl_id in the tables Suppliers and PCMaster.  I only want to display the name, city and state [one text control is under the Supplier ID#, Name, City, State label header on the form next to the suppl_id control]  I don't want to store the info anywhere else on the PCMaster form.

The frmPCMaster has a drop down box where the user can select the supplier number (the drop down box displays the name, city and state for reference).  The supplier number is then stored in the PC Master table.  What I think I'm trying to do is to create 3 unbound control text boxes that hold/display the value of the supplier name, city and state at the top of the form for reference.

I have created a relationship (Suppliers (one) to PC Master (many)), I don't understand what field actually contains the suppl_id value because the combo box gives them another name (?) - cboFindSupplID - (?)  I don't get the !Me[fieldname] and [fieldname.value] variable parameters.

I've attached the mdb file as a zipped file below, the ext has been changed to .hlp  Thanks again.  
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There is no file attached to this.
0
2thewebAuthor Commented:
I didn't offer enough points for all the help I received.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.