Error Message with Invoice code (2000)

Topic

New Reply

Hi All,

I’m presently working with code very nicely supplied to me by Pat for assigning an invoice number to an invoice. I have a field on frmBilling called InvoiceNo. When frmBilling loads the code is as follows:

Dim dbs As DAO.Database, rs As DAO.Recordset, lngInvoiceNo As Long
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(“Control”)
rs.Edit
rs!NextAvailableInvoiceNo = rs!NextAvailableInvoiceNo + 1
lngInvoiceNo = rs!NextAvailableInvoiceNo
rs.Update
TextInvoiceNo = lngInvoiceNo

The form opens fine, however there are two problems. InvoiceNo has a zero in it and the next number in the table should be a 2. When I go to save the invoice I get the following error:

“user-defined type not defined” and ……dbs As DAO.Database….. is hightlighted in yellow.

I’m outta my leaque with this one and not sure where to go next.

Thanks,
Leesha

Reply | Quote

Replies

Have you set a reference to the DAO library? To check:

• Activate the Visual Basic Editor (Alt+F11)
• Select Tools | References…
• See if “Microsoft DAO 3.6 Object Library” is among the checked references at the top of the list. If not, scroll the list until you see it, then check the corresponding box.
• If there are any references starting with “MISSING:”, clear their check boxes.
• Click OK.
  [/list]If this doesn’t help, post back.

Reply | Quote

(Edited by Leesha on 24-Jun-03 21:12. )

Hi Hans,

Ok, the reference you mentioned wasn’t checked (a new area for me to learn about0 and there were no references labeled “missing”. I tried the form again and this time the following area is hightlighted but the error message now reads “The Microsoft Jet database engine cannot find the input table or query ‘control’. make sure it exits and that its name is spelled correctly…….set rs = dbs.openRecordset (“control”)

Leesha

Reply | Quote

Hi Leesha,

If you look closely at Pat’s post 267358 (hmm, this is starting to sound like Postman Pat), you’ll see that he intended you to create a special purpose table named Control with one numeric field named NextAvailableInvoiceNo. This table will be used to hold the invoice number, and this table is opened as a recordset in dbs.OpenRecordset(“Control”).

Reply | Quote

AHHHHHHHHHHHHHHH ……………. I didn’t get that out of it!!!! Will try that in a bit. Unfortunately I have to fly to a meeting and will need to wait to play. Groan………………..

Thanks!!! You are such a patient man!

Leesha

Reply | Quote

Quick question, do I link field InvoiceNumber in tblBilling to field NextAvailableInvoiceNo is table Control???? I’m assuming I do otherwise how would InvoiceNo get updated. I don’t dare do anything without advice on this one!!

Thanks,
Leesha

Reply | Quote

No, you shouldn’t link the Control table to other tables. It is just used in the code provided by Pat to retrieve and update the latest Invoice Number. That code should be invoked when creating a new invoice. Read his post – it’s all in there!

Reply | Quote

>>>Read his post – it’s all in there!

Uhm, I’m trying, really I am!!! If it were in medical jargon I’d be all set

Thanks again,
Leesha

Reply | Quote

That code should be placed in the BeforeUpdate event of the form.
When this form goes to add a new record to the table (or for that matter updates a record for the table) it executes the BeforeUpdate event just prior.
What you will have to do in that event is to execute this code only for a new record,
eg.
If Me.NewRecord Then
‘ code goes here
End If

The table Control is a table of 1 record that is used to record the latest Invoice number used. You don’t link to this table, it is only used to update the NextAvailableInvoiceNo field by 1 and for you to use that value as the next Invoice number to be used.
Hans has already explained this.
If you need any more help with this, please post back. I’m not the best at explaining myself.

Reply | Quote

Hi Pat!!

Rest assured its not your explanation its my brain!! BTW you must’ve been reading my mind as I was having a problem with the newly assigned invoice number changing on me if the record was updated while the form was open. I was determined to get it on my own (stubborn female) and I tried using the following:

If Me.TextInvoiceNo.Value > 0 Then
Exit Sub
Else:
(this is where you code is)
End if

It seems to work! Of course if your suggestion would be more secure I’ll gladly change it.

Thanks!

Leesha

Reply | Quote

As long as the code is in the BeforeUpdate event of the form it should be ok.
Your version should work although I don’t know what would happen if the Control was null.

Reply | Quote

It’s in the before update section just as instructed. As for null – I noticed that the InvoiceNo actually shows in the field after the record is saved. So, any changes made before the record was saved weren’t an issue. It was only if the record was saved and then updated again and then saved that I noted the InvoiceNo increasd by 1. That is why I wrote the code as >0.

Leesha

Reply | Quote

That’s good, it will also work if you test for a NewRecord, this is the value of the NewRecord property.

Reply | Quote

Thanks!! I’ve stored both versions in my notes. It’s nice to have options. I’m tickled to have gotten something to work on my own!

Of course I would NEVER have figured out how to develop the invoice code on my own and GREATLY appreciate you sharing yours with me!!!!!!!!!

Thanks again,
Leesha

Reply | Quote

Another thought, I wasn’t sure as to whether I should include InvoiceNo from tblNextAvailableInvoiceNumber or not (Pat’s instructions didn’t tell me to do so and I didn’t want to mess things up) so I didn’t. When I started getting error messages I tried adding the above mentioned field to the query but continue to get the same error so I’ve taken it out.

Thanks,
Leesha

Reply | Quote