Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Table and Form Help

    I have a field in a form that is named "fax number". I also have a field that is named "state". Depending on what state is entered in field "State" I would like to have the appropriate fax number automatically put in the "fax number" field. I'm assuming I would have to need a table with all the fax numbers that go to the appropriate state. What is the easiest way to achieve this? Any code help and direction would be appreciated. Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a table that associates State and Fax_Number:
    - Table name: Tbl_State_x_Fax
    - Columns: State (Text), Fax_Number (Text)

    2. In the module of the form and provided that the Textbox controls are named Fax_Number and State, you can use something like:

    Code:
    Private Sub State_AfterUpdate()
    
        Me.Fax_Number.Value = DLookup("Fax_Number", "Tbl_State_x_Fax", "State = '" & Me.State.Value & "'")
    
    End Sub
    Note: Refrain from including spaces or other non alphanumeric characters (the underscore is ok) in the names of the objects (Tables, Columns, Forms, Controls, etc.), sooner or later you'll have problems because of them. You can use: Fax_Number or FaxNumber to differenciate each word in a compound name.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by benz1984 View Post

    ...I'm assuming I would have to need a table with all the fax numbers that go to the appropriate state...
    Are you talking about one fax number per state, or multiple fax numbers per state?

    If the former, Sinndho's advice is spot on. If the latter, you'll need to use Cascading Comboboxes.

    Let us know!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2007
    Posts
    214
    If there are more than one fax number associated with the state, what would be the best way to design a popup that allows the user to select or "click on" which fax number they want to use?

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As I said, you'll need to use Cascading Comboboxes.

    The first Combobox will hold a list of the States. When a State is selected from this Combobox, the RowSource for the second (Fax Number) Combobox will be set to only include Fax Numbers from that State.

    Google 'MS Access' and 'Cascading Comboboxes' and you'll get about a gazillion hits, but doing this kind of thing, when you're new to Access, can be very confusing. Here's an example I ran up a while ago, for another poster, for doing the same thing with States and Cities; I believe I’ve substituted FaxNumbers for Cities, where appropriate:

    To begin with, you need a single Table for this, not separate FaxNumber and state Tables. Each Record in this Table needs to have both state and FaxNumber Fields. After you have that straight you can use this step-by-step:

    1. Place a Combobox on your Form
    2. When the Combobox Wizard comes up hit Cancel
    3. With the Combobox selected, go to Properties - Other
    4. In the Name Property enter cboStates
    5. Click on the Data Tab

    In the Row Source Property enter

    Code:
    SELECT DISTINCT tblStateFaxNumber.StateFieldName FROM tblStateFaxNumber ORDER BY [StateFieldName];

    Now go into the AfterUpdate event for your cboStates Combobox and use this code:

    Code:
    Private Sub cboStates_AfterUpdate()
    cboFaxNumber.RowSource = _
    "Select Distinct tblStateFaxNumber.FaxNumberFieldName " & _
        "FROM tblStateFaxNumber " & _
        "WHERE tblStateFaxNumber.StateFieldName = '" & cboStates.Value & "'" & _
        "ORDER BY tblStateFaxNumber.FaxNumberFieldName;"
    
    cboFaxNumber = ""
    
    End Sub


    Now repeat Steps 1-4, above, but in Step 4, instead of entering cboStates, enter the name cboFaxNumber .

    Replace tblStateFaxNumber with the actual name of your Table that contains both the FaxNumbers and States, and FaxNumberFieldName and StateFieldName with the actual names for these Fields from your Table.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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