Results 1 to 8 of 8
  1. #1
    Joined
    Oct 2001
    Location
    AZ
    Age
    39
    Posts
    575

    Question Using DLookup properly

    I'm just learning about DLookup and I'm stuck. I'm wanting the txtAddress field to auto-populate dependent upon what is chosen from a combo box cboVendor. I've setup the DLookup as far as I know, but how do I then bind that result to the text box?

    Code:
    dim selVendorAddress as String
    selVendorAddress = DLookup("[ADDRESS]", "VENDOR", "[cboVendor]='" & [cboVendor] & "'")
    I would assume just slapping in this would work,
    Code:
    txtAddress.value = selVendorAddress
    but that doesn't work. Help!

  2. #2
    Joined
    Oct 2001
    Location
    Southern Ontario
    Age
    45
    Posts
    13,194

    Re: Using DLookup properly

    I think your problem is caused by one of two things. Either you are not using the right event, or you should be using the .text property instead of the .value for the text box. Here's what I would write:

    Code:
    Private Sub cboField_Click()
    
        dim selVendorAddress as String
    
        selVendorAddress = DLookup("[ADDRESS]", "VENDOR", "[cboVendor]='" & [cboVendor] & "'")
        txtAddress.text = selVendorAddress
    
    End Sub
    PS. I think you are talking about Visual Basic 6. Note I've never actually heard of DLookup before, but that's how I'd try and add it in based on your criteria.

  3. #3
    Joined
    Oct 2001
    Location
    AZ
    Age
    39
    Posts
    575

    Re: Using DLookup properly

    This is the exact code, but it will not work at all.
    Code:
    Private Sub cboVendor_Change()
        Dim vendorLookup As String
        vendorLookup = DLookup("[ADDRESS]", "VENDOR", "[cboVendor]='" & [cboVendor] & "'")
        txtAddress.Text = vendorLookup
    
    End Sub

  4. #4
    Joined
    Oct 2001
    Location
    Southern Ontario
    Age
    45
    Posts
    13,194

    Re: Using DLookup properly

    Did you try cboVendor_Click()? I seem to remember having some trouble with the _Change() event.

  5. #5
    Joined
    Oct 2001
    Location
    AZ
    Age
    39
    Posts
    575

    Re: Using DLookup properly

    I moved the code to cboVendor_Click() and it sort of works, but now it won't find any records in that field. It keeps giving me null answers. I've made sure I'm choosing a vendor that has an address, but no go. Any ideas?

  6. #6
    Joined
    Oct 2001
    Location
    AZ
    Age
    39
    Posts
    575

    Re: Using DLookup properly

    Note:

    I changed vendorLookup to a variant (as per Access Help), but that didn't help. I spelled out where the combo box resides (forms![PO Master]!cboVendor)... that just gives me blank responses in the Immediate window. I don't get a null, I get "empty".

  7. #7
    Joined
    Oct 2001
    Location
    AZ
    Age
    39
    Posts
    575

    Re: Using DLookup properly

    RESOLVED:

    I took a totally different route, and honestly don't know why I didn't do it in the first place. I changed all of the fields I needed to be dynamically changed to listboxes. Then, for the rowSource on each box, I put in SELECT 'fieldname' FROM VENDOR where VENDOR = [forms]![PO Master]![cboVendor]. After that, I went into the cboVendor_OnClick(), dimmed controls for each one of the listboxes, set each control to its corresponding listbox on the form, then told it to requery each one. Works like a charm!

  8. #8
    Joined
    Oct 2001
    Location
    Southern Ontario
    Age
    45
    Posts
    13,194

    Re: Using DLookup properly


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •