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

    Question Unanswered: Multiple Entries Per Field and Record

    Hi All,

    I have a databse, in which I have a form where I enter data. I then have a button that I click to generate a letter (report) based on the record I'm currently viewing. There is a field on the report "Fax Number" so that I can simply look at the report and fax it to the appropriate number. The fax number that is generated is specific to the "State" in which the report shows. The state is generated when the user enters it in the form.

    My question is, there are handful of states that have more than one fax number. Some just have one, so that is simple and it is displayed on the report. How can I have a prompt come up, or some way for the database to know that because let's say we have a Kansas record, that there are three different fax numbers for this state, and the user can select which one they need, then it is generated on the report.

    Can this be done? I have a table called "States" and that is where the fax numbers are coming from, however obviously when the state has more than one fax, I have no way to enter more than one fax per state in this table and I would the user to have a choice which number to use when running the report.

    I hope this makes sense and someone can share some code / example of how to make this work. Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Redesign the database so that you have 2 tables:
    Table 1
    - Name: States
    - Column 1: StateID Autonumber
    - Column 2: StateName Text
    Table2
    - Name: Faxes
    - Column 1: FK_States Number (Long)
    - Column 2: FaxNumber Text

    2. Create a relationship between both tables:
    States.StateID (One) --> Faxes.FK_States (Many)

    3. You could now use a combobox to select both the state and the fax number:
    Combo
    - Name: SelectState
    - ColumnCount: 2
    - ColumnWidth: (select width for each column)
    - BoundColumn: 1
    - RowSourceType: Table/Query
    - RowSource:
    Code:
    SELECT States.StateName, Faxes.FaxNumber FROM States INNER JOIN Faxes ON States.StateID = Faxes.FK_States ORDER BY States.StateName;
    - To retrieve the state name from the combo, use:
    Code:
    StateName = Me.SelectState.Value
    or:
    Code:
    FaxNumber = Me.SelectState.Column(0)
    - To retrieve the fax number from the combo, use:
    Code:
    FaxNumber = Me.SelectState.Column(1)
    Have a nice day!

Posting Permissions

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