Sync 2 combo box with 1 textbox RRS feed

  • Question

  • I am working on Access table about travel of drivers, using one table named "Cities", inside have 3 fields (From, To, #Kilometers), have records inside, (Fields and records show below)

    Fields         From          To           # Kilometeres

                        Chicago       New York       1,271.4

                        Miami          New Orleans  1,393.9

    Using a form named "Transactions" have link to Transactions table, this table have fields (Date, From, To, #Driver, #Kilometers) where enter each travel of driver, the fields From, To have combo box related Lookup wizard related form Cities table of from, to. On the form Transactions if I select the on the  first combo box (From) Chicago and in the second combo box (To) Ney York the text box #Kilometers show the 1,271.4 and after select any other cities in From and To, show the #Kilometers records on the table Ctities.

    Tuesday, March 14, 2017 3:16 AM

All replies

  • > this table have fields (Date, From, To, #Driver, #Kilometers)

    The #Kilometers (apart from the use of funny characters which will cause notational difficulties) is redundant and should be removed. It's already available in the Cities table.

    Also, "Date" is a reserved word and should never be used for an object name. Change to TrevelDate or some such.

    Also, "From" is a reserved word. Don't go there. Alligators on your path.

    -Tom. Microsoft Access MVP

    Tuesday, March 14, 2017 5:52 AM
  • Hi Miguel_Angel_2000,

    it looks like you want something like below.

    code on button click:

    Private Sub Command6_Click()
    Dim rs As Recordset
    Dim frm, too As String
    frm = Me.Combo0.Value
    too = Me.Combo2.Value
    Set rs = CurrentDb.OpenRecordset("SELECT distance FROM distance_data where from='" & frm & "' and to='" & too & "';")
    Do While Not rs.EOF
       Me.Text4.Value = rs("distance")
    End Sub

    Table design:

    you can modify it as per your requirement.

    I agree with the suggestion given by Tom van Stiphout (MVP) regarding object names. you need to avoid reserved keywords.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 15, 2017 6:14 AM