Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25

    Unanswered: Selecting item from combo box and putting data in other fields on a form

    I have a combo box on my form that lookups a field from a table.

    There is also other fields on this table, and I would like to be able to put this data into some locked textboxes on the form once the unique ID which is the lookup on the combo box is selected.

    I've tried [txtVehicleLicNum] = DLookup("VehicleLicense", "Vehicles", "[myId]=""" & [cboVehicleNum] & """") on an event and

    = DLookup("VehicleLicense", "Vehicles", "[myId]=""" & [cboVehicleNum] & """") for the control source but no dice.

    Wouldn't dlookup be the best way to attack this problem? Attatched is a copy of my database if this helps make things any clearer. (It's from Access 2002 but I can save it as 97 if you need 97)
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Lith,

    If you change the AfterUpdate event of your combo to this, the two fields beside the combo (license number and make) will be updated.
    Code:
    Private Sub cboVehicleNum_AfterUpdate()
       txtVehicleLicNum = DLookup("LicenseNum", "Vehicles", "VehicleNum=" & Nz(cboVehicleNum, 0))
       txtVehicleMake = DLookup("Make", "Vehicles", "VehicleNum=" & Nz(cboVehicleNum, 0))
    End Sub
    If you prefer to use a recordset, try it this way.
    Code:
    Private Sub cboVehicleNum_AfterUpdate()
       Dim rst As DAO.Recordset
       'open up our row using some SQL mixed with the value of our combo box
       Set rst = CurrentDb.OpenRecordset("SELECT * FROM Vehicles WHERE VehicleNum=" & Nz([cboVehicleNum], 0))
       If Not rst.EOF Then
          txtVehicleLicNum = rst!LicenseNum
          txtVehicleMake = rst!Make
       End If
       rst.Close  'close the recordset
       Set rst = Nothing
    End Sub

  3. #3
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25
    Wow you are amazing, honestly sincerly just THANK YOU, this had been driving me insane.

    Now just a question to help further my understanding.

    "VehicleNum=" & Nz(cboVehicleNum, 0))

    VehicleNum is the PK and so that's why it's used there? What does the Nz do and is the zero just so it starts at the top of combo box.

    I felt my thanks could not be properly expressed with words

    http://www.ileet.org/thankyou.jpg

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    I luv the image, thanks!!

    VehicleNum is the PK in your table - it uniquely identifies a record in the vehicle table. It is also the value of your combo box.

    To display the LicenseNum and Make for the VehicleNum selected by the combo, we lookup the record that matches the selection, using VehicleNum as the key.

    The Nz function turns a null value to something else, specified by the second parameter (in this case zero). If you select nothing, then the lookup will return empty values, since the PK in an autonumber and can't be zero.

Posting Permissions

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