Append Query (Access 2K)

Topic

New Reply

I have an invoicing form with a Cmd button called new which when clicked opens a new record with todays date and increases the invoice number (number field double) by via a makro SetValue DMax(“[Invoice Number]”,”Invoice”)+1. I am trying to do some invoices automatically every month using an append query. This works perfectly other than it adds one to the last invoice number for all the new record set. In other words if the last invoice was 1000 and there are 50 new records to append they will all have an invoice number of 1001. I presume I need to wrirte some code to tell it to look at each new record one at a time but my knowledge of code is very limited. Hope someone can help. JohnMichael.

Reply | Quote

Replies

The DMax process doesn’t work very well when you use an append query, as it calculates the value before it adds the record, and then keeps the same value for all records added. You could solve this in code, but if you are a learner, the process would take considerable time. I would suggest you consider an autonumber field for your invoice number – Access takes care of all the arithmetic for you, and the append query doesn’t even need to reference the invoice number. Bear in mind that will leave some holes in your number sequence when someone starts to add a new invoice manually, and then cancels the process, but it makes life much simpler. I would also suggest you put a primary key on the table which uses the Invoice field. Hope this helps.

Reply | Quote

Thanks for replying to my append query problem WendellB but I really need to use code because I don’t know the implications of changing to an autonumber. There are 5000 invoices in my invoice table and the database comprises of 95 tables, although not all related! I might solve one problem and create a whole lot more. Anyway the knowledge could be usefull because I use the DMax +1 on quiet a few of my forms. Thanks once again. JohnMichael.

Reply | Quote

Ninety-five tables seems rather excessive for a novice developer. Did you inherit this database?

Reply | Quote

Hi Charlotte,
No I didn’t inherit the database but have built it very slowly since 1995 and in that time have managed without code. The present database actually comprises of 80 tables that are used regulary the others are backups or for test purposes, 170 queries, 67 forms and 86 reports. One reason you might consider the database is quiet big is because I am using one database for every office task from accounts to motor reports. Possibly I need to split them up into different databases? When I say one database it’s actually two because I have my tables seperate to the queries, forms and reports.
After reading a book (in the last two weeks) by J R Carter on Access, SQL and Vb I now know a bit more about code and SQL and with the aid of a book or by copying similar code and them modifying it to suit I am sure I could make something happen, I am just not sure what! Its not really the lines of code,within reason, but where to put it and how to start. I don’t know enough to see the big picture.
With regard to my Append Query problem. At the moment I click a comand button which runs a query that asks me for the month number, I enter the number click ok and approx 50 (at this time) new invoices are added to my invoice table. They are correct in every way, descriptive text, cost, date customer etc except for the invoice number which at this time is null (but could be the last number plus one for all the new invoices). I envisage adding code to this command button to run another query on the invoice table after the new records have been added to look for Null invoice numbers. Then do something like Do While Not EOF etc. The problem is what instructions do I put between this and Loop to make it add the correct invoice number to these new records? Thanks JohnMichael.

Reply | Quote

<>
The VBA code to put after you add the invoice records is:
Dim dbs as DAO.Database, rs as DAO.Recordset
Set dbs = CurrentDB
Set rs = dbs.OpenRecordset(“SELECT Max(InvoiceNo) as MaxInvNo FROM Table”)
Dim lngInvNo as Long
lngInvNo = rs!MaxInvNo
Set rs = dbs.OpenRecordset(“SELECT * FROM Table WHERE InvoiceNo is Null”)
Do While Not rs.EOF then
rs.Edit
lngInvNo = lngInvNo + 1
rs!InvoiceNo = lngInvNo
rs.Update
rs.MoveNext
Loop
Set rs = Nothing
Set dbs = Nothing

This assumes that there is no one inputting invoices while this function runs.
Hope this helps you.
Pat

Reply | Quote

<>

Which is why this approach is a bad idea. Even if you lock the table, it can still be a problem when someone starts to add an invoice between the time you run the append query and the time you run the VBA code. What concerns do you have about autonumber fields? They are one of the basic components of Access, and intended to solve this very problem among others.

Reply | Quote

Is this a batch function to post all last months invoices to the invoice table? If so, is there a date in the input table where the invoice data resides?
If there is, you could amend your code to test the invoice date for last month and just transfer those records which would leave any current months invoices in the input table. Then my code is still relevant.

If the invoice number is to be a transparent number to the outside world then Wendell’s original suggestion is sound and the easiest way to go.

Pat

Reply | Quote

To be perfectly frank, if you don’t know much about code, then using code to try to solve your problem would cause a great deal more grief than changing the Invoice Number field to an autonumber. You can change an existing field to an autonumber if it is a long integer, and if there are no weird values in it. That can be determined by doing a sort on the Invoice Number and looking at both ends of the result. As a general rule, using autonumbers for a field that has external meaning is discouraged, but in this case the number is simply a reference number that identifies a specific invoice. You should be able to test the process in a test version of your database and determine if it causes any problems – I’ve not personally encountered any other than a fairly obscure bug in the original release of Access 2000 that was fixed in Service Release SR-1a.

To solve this issue using code would require using VBA and DAO or ADO. That requires that you understand not only the syntax of VBA, but the Access object model as well, and there are significant differences depending on which data access method you choose. It would be possible for someone to construct an example set of code for you, but without knowing the exact table structure involved, it would require you doing extensive modifications to the code to make it work in your situation. Finally, doing it in code means you can no longer use an append query to add your 50 or so records, each must be added individually in a loop, and would be significantly slower – that in turn raises the possibility of someone adding an invoice manually at the same time you are running the automatic process and causing a crash because of duplicate indexes. (There are ways around that, but they involve using substantially more complex code and multiple tables.)

IMHO, the bottom line is that you are going to need to scramble to learn Access and the care and feeding of databases, so you should focus on the less painful solutions while you work to get up to speed on the intricacies of code.

Reply | Quote