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!
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:
- Name: SelectState
- ColumnCount: 2
- ColumnWidth: (select width for each column)
- BoundColumn: 1
- RowSourceType: Table/Query
SELECT States.StateName, Faxes.FaxNumber FROM States INNER JOIN Faxes ON States.StateID = Faxes.FK_States ORDER BY States.StateName;