Dlookup in Access 2003 - form control details

If you know of any detailed info on form controls, I'd appreciate the referral.  Thanks.

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

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 & "'")

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.

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.

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)),"")

One further note: The Default Value applies ONLY to new records ... it has no impact on existing records.

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?

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

Select allOpen in new window

Remove the ControlSource from the txtSupplierName, txtSupplierCity and txtSupplierState controls.

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

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 ...

When I hit run in my vb code screen, it asks for a macro name?

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.

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.

You can zip your file and post them to www.ee-stuff.com, then post a link back here for the download.

That sounds great.  I'll shoot off a copy tomorrow morning!  Thanks again.

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

Select allOpen in new window

Copy-of-frmPC-Master.txt

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.

Where on www.ee-stuff.com does it go?  Can I include it below as a file?  The mdb file is 2,400kb

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).

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.

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.  

There is no file attached to this.

I didn't offer enough points for all the help I received.