none
choosing option from combobox which searches specific table, and having different fields from that table being inserted into other text boxes RRS feed

  • Question

  • I am trying to create a program to run my business. So far, I spend 10 hrs almost getting it to do what I want and then deleting it to start a new one because i mess it up. I have a little access knowledge and very little vba knowledge. I have a customer table which basically has contact info (CustomerTable). I have a form to input all info (CustomerInputForm). I have another table for insurance company contact info (InsuranceForm), which displays Name, Phone, and Fax. I have a combobox on CustomerInputForm that chooses from all the insurance companies in the insurancTable. I also have a text box called InsPhone and InsFax in the the customerInputForm. I want to be able to choose the insurance company from the combo box and then on the after update have it fill in the phone number and fax into the next 2 text boxes. I know nothing about code. My code is, Try not to laugh, something like this:

    If Me.CmboIns.Value = Tables!InsuranceTable!InsName.Value Then
    Tables!InsuranceTable!InsPhone.Value = Me.InsPhone.Value

    End If

    I also tried .text

    .


    • Edited by overdhill1 Friday, May 4, 2012 12:59 AM
    Friday, May 4, 2012 12:51 AM

Answers

  • Let's say the Insurance table has these fields:

    InsuranceID (AutoNumber, primary key)
    InsuranceName (Text - I wouldn't use Name as field name; Name is a VBA property of many objects)
    InsurancePhone (Text)
    InsuranceFax (Text)

    In your Customer table, include a number field InsuranceID. This field will link to the InsuranceID field in the Insurance table.

    On your customer input form, place a combo box, and set the following properties for this combo box:

    Column Count: 4 ' originally had 2
    Column Widths: 0";2";0";0"
    Control Source: InsuranceID
    Row Source Type: Table/Query
    Row Source: the name of the Insurance table
    Name: cboInsuranceID

    This combo box will store the InsuranceID number but it will display the InsuranceName field.

    Place a text box on the form with label Phone and with Control Source:

    =[cboInsuranceID].[Column](2)

    Place another text box on the form with label Fax and with Control Source:

    =[cboInsuranceID].[Column](3)


    Regards, Hans Vogelaar


    Friday, May 4, 2012 9:11 AM

All replies

  • Let's say the Insurance table has these fields:

    InsuranceID (AutoNumber, primary key)
    InsuranceName (Text - I wouldn't use Name as field name; Name is a VBA property of many objects)
    InsurancePhone (Text)
    InsuranceFax (Text)

    In your Customer table, include a number field InsuranceID. This field will link to the InsuranceID field in the Insurance table.

    On your customer input form, place a combo box, and set the following properties for this combo box:

    Column Count: 4 ' originally had 2
    Column Widths: 0";2";0";0"
    Control Source: InsuranceID
    Row Source Type: Table/Query
    Row Source: the name of the Insurance table
    Name: cboInsuranceID

    This combo box will store the InsuranceID number but it will display the InsuranceName field.

    Place a text box on the form with label Phone and with Control Source:

    =[cboInsuranceID].[Column](2)

    Place another text box on the form with label Fax and with Control Source:

    =[cboInsuranceID].[Column](3)


    Regards, Hans Vogelaar


    Friday, May 4, 2012 9:11 AM
  • Awesome, it took me a few minutes, but I got it. It kept only showing InsPhone in txtbox1 but no InsFax in txtbox2. Once I changed the cboBox column count to 3 it started working perfectly.  

    I am like a lava lamp. Not too bright but a lot of fun to watch.

    Friday, May 4, 2012 12:09 PM
  • Sorry, the column count should have been 4.

    Regards, Hans Vogelaar

    Friday, May 4, 2012 1:19 PM