Confusing Invoice Sequential Numbering with Year and "BW"

[breakingme10]

So it's a wrecker service and I am trying to narrow down a file cabinet full of wrecker invoices (98% of which have no unique id to them whatsoever, no invoice number nothing) and I want to be able to add an invoice number to those that don't have one based on the year. for example 2011-BW00001, 2011-BW00002 then 2012-BW00099, 2012-BW00100 etc. I want to use vba to generate this, then only advice i've gotten is to store the year in one field the "BW" in another and the sequence number in another. and with a wrecker invoice there are PLENTY of fields already I don't want to have to add that many more! Especially when the "BW" (Barnett Wrecker) is always the same. I've been using a spreadsheet invoice to calculate the total charges for the wrecker customers but i'd like to just migrate all that to a database because the spreadsheet is getting too large. below i've attached the spreadsheet and database. Database is in the very very beginning stages. Just started it yesterday.
WreckerInvoice.pdf
WreckerDatabase.zip

[ItsMe]

What is the purpose of the query UpdateTowCharges? I do not see it being used anywhere.

When I look at your Relationships window I see a circular reference with tblTows, tblOwners, and tblVehicles. There is not a need for a relationship between tblTows and tblVehicles. There is not a need for the field "Vehicle" in tblTows. This is duplication of data. Use a query and JOIN's to retrieve relative VehicleID records.

I see some use of Key fields and then I see tables that are not normalized that could be if you used existing Keys.

I see public functions but I do not see them being called from anywhere using VBA. If your question is specific to concatenating literal text to a long integer variable I suppose the user defined functions would be the way. It is pretty confusing to me. I do not see any example of you attempting to concatenate. Perhaps you can explain what you have tried and focus on that or restate your question.

[breakingme10]

those were code samples i found somewhere, not relevent anymore. UpdateTowCharges will be used on the Tow form to update the charge fields. right now if i click it, it will just update the charge totals and i have to refresh the form to see them. As i said, this database was started yesterday. i'm trying to keep it simple, but i'm having trouble.

[breakingme10]

i'm working with three different databases for my company right now, so my brain is all but fried. i'm having trouble with two of them, the third works perfectly of course because it was the first one i started. now i'm just full of ideas and out of room in my head for them. I should note that many vehicles have "Unknown" for the owner. A lot of the time we only have vehicle information and no owner information.

[ItsMe]

I would suggest using VBA vs. using a Macro in your Command Button. You may discover you have better control over your events and what you are able to do/execute.

Having said that, a Main Form/Sub Form arrangement may afford you some benefits.

[breakingme10]

instead of a split form? ok i will try that. but on the sequencing of the invoice numbers, i really need some advice. i went ahead and added a field called invoicenumprefix and then invoicenumseq but the Dmax won't work right. i also want to be able to set the invoice number manually because i am entering in some older invoices to clear out a file cabinet.

[ItsMe]

The Invoice Numbers are a Candidate Key. They are a good candidate for a Primary Key. However, I would not use them as a PK. I would use Autonumber as a PK. So, out of all of the Candidate Keys, Autonumber for the win.

[breakingme10]

And what would be the best way to incorporate this...

If [LineItem]=2 Then 
[Qty]=((Nz([StorageEndDateTime, Now()))-[StorageStartDateTime])+1 
Else
If [LineItem]=3 Then
[Qty]=([EndMileage]-[StartMileage])
Else
If [LineItem]=1 Then
[Qty]=1

In a before update event?

[ItsMe]

I would test it in a click event. After I knew it worked, I may use it in the Before Update of a form. It depends on when the user needs it and when you want the value stored in the table, if that is the objective, seems like qty is a field in a table.