Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Error Message with Invoice code (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Message with Invoice code (2000)

    Have you set a reference to the DAO library? To check:
    <UL><LI>Activate the Visual Basic Editor (Alt+F11)
    <LI>Select Tools | References...
    <LI>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.
    <LI>If there are any references starting with "MISSING:", clear their check boxes.
    <LI>Click OK.[/list]If this doesn't help, post back.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    <P ID="edit" class=small>(Edited by Leesha on 24-Jun-03 21:12. )</P>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

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Message with Invoice code (2000)

    Hi Leesha,

    If you look closely at Pat's <post#=267358>post 267358</post#> (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").

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    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

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error Message with Invoice code (2000)

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    >>>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 <img src=/S/nurse.gif border=0 alt=nurse width=19 height=20>

    Thanks again,
    Leesha

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Error Message with Invoice code (2000)

    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.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    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

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Error Message with Invoice code (2000)

    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.

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    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

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Error Message with Invoice code (2000)

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

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Message with Invoice code (2000)

    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! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •