I have a database for a small repair shop. When repairs are made, staff members enter a service ticket, which has a Service ID autonumber. At a later date, this Service ID # is added to an Invoice. The Invoice Number is not an autonumber (not my design or choice).
This is done on a form with a combobox that contains all of the Service ID #’s not assigned to an Invoice Number. When a Service ID # is selected from the combobox, the Invoice Number from the current form is added to the Service ID table.
This works fine so long as there is only one Service ID # in the combobox (no Invoice Number assigned). If there is more than one Service ID #, the Invoice Number gets assigned to the largest Service ID #, regardless of which Service ID # is selected in the combobox.
For example, on the form for Invoice Number 0022, if the combox shows Service ID #’s 5 and 6 in the dropdown, and # 5 is selected, Invoice Number 0022 gets assigned to Service ID 6 and not 5.
I realize I probably need to supply an example db, but I’ve been trying to to skim it down to the essentials and remove all the other “stuff” that’s in the database, but so far I’ve not been able to retain the functionality of this portion (due to table relationships, etc).
I’ve been hoping someone here may be able to point me in the right direction, or perhaps suggest a better way of doing this.
As always, thanks in advance for any input.
This is done on a form with a combobox that contains all of the Service ID #’s not assigned to an Invoice Number. When a Service ID # is selected from the combobox, the Invoice Number from the current form is added to the Service ID table.
This works fine so long as there is only one Service ID # in the combobox (no Invoice Number assigned). If there is more than one Service ID #, the Invoice Number gets assigned to the largest Service ID #, regardless of which Service ID # is selected in the combobox.
For example, on the form for Invoice Number 0022, if the combox shows Service ID #’s 5 and 6 in the dropdown, and # 5 is selected, Invoice Number 0022 gets assigned to Service ID 6 and not 5.
I realize I probably need to supply an example db, but I’ve been trying to to skim it down to the essentials and remove all the other “stuff” that’s in the database, but so far I’ve not been able to retain the functionality of this portion (due to table relationships, etc).
I’ve been hoping someone here may be able to point me in the right direction, or perhaps suggest a better way of doing this.
As always, thanks in advance for any input.