Combox Issue (1 Viewer)

[depawl52]

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.

 

[XPS35]

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.
................
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 assume that the invoice number is assigned via some code. In any case, we need to understand that in order to be able to say something sensible about this problem.

 

[depawl52]

So sorry. The invoice number is entered manually. The user is given the last sequential invoice number on the form. For example, the user is given Invoice Number 00003 and told to enter the next sequential number. I realize this is not optimal, the next thing on my list to fix. Still trying to get a functional trimmed down database to upload.

 

[depawl52]

So my pared down sample db is attached.
As you can see in tblInvoice there are 2 existing Invoice Numbers (00001 and 00002). And in tblInvoiceService, there are 2 InvoiceServiceID's (996 and 994) with no Invoice Number yet assigned.
So upon opening NewInvoiceF and entering Invoice Number 00003, then clicking on the ADD Service Button, NewInvoiceServiceF has the combobox with the 2 InvoiceServiceID's. But clicking on 994, and then opening tblInvoiceService, you can see that Invoice Num 00003 is instead assigned to InvoiceServiceID 996.
Been struggling with this for some time now, and any suggestions would be most appreciated.

 

[arnelgp]

here try and test your db.
check the code on both forms.
on form NewInvoiceServiceF, i renamed the InvoiceNum textbox to InvoiceNumT and make it Unbound.
see all the codes.

 

[XPS35]

I don't understand your data model. By working with two tables, you give the impression that one invoice can include several services. However, I couldn't figure out how to enter it.

 

[LarryE]

Your model did not include a customer table which I assume you left out on purpose, but your design is not a relational database design anyway. Here is your model in a relational database design. Please note the Primary key and Foreign key fields in each table that relate to one another. I didn't even have to look at the query or form to know the model you presented won't work. Since this is (or was) apparently a class term project, please study the design first and then create your queries and forms and reports accordingly.

 

[depawl52]

Thanks so much for the input and suggestions guys. Your proposed solution(s) appear to work perftectly.