Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: custom counter

  1. #1
    Join Date
    Mar 2003
    Posts
    36

    Unanswered: custom counter

    Hi Im trying to get a custom counter going as per http://support.microsoft.com/default...Ben-us%3B88159

    Im getting a compile error "user-defined type not defined" Its a microsoft access 2000 database the highlighted bit of code is the dim db part, see below.

    Function Next_Custom_Counter()

    On Error GoTo Next_Custom_Counter_Err

    Dim MyDB As Database
    Dim MyTable As Table


    Is this a referneces thing? Im just learning a bit about VB now! Any advice even re another way to increment my invoice number automatically would be great thanks

    Thanks

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Dim MyTable As Table

    This is not correct - what are you trying to do?
    Create a recordset or perhaps do something with the TableDef collection?

    Matt

  3. #3
    Join Date
    Mar 2003
    Posts
    36
    Originally posted by Rockey
    Dim MyTable As Table

    This is not correct - what are you trying to do?
    Create a recordset or perhaps do something with the TableDef collection?

    Matt
    Hi

    This is the problem with my copy and paste programming. I dont understand most of the code. All i need is a way to increment and assign a number to a record as its invoicenumber on printing the invoice report. But heres the full code from MS.

    Function Next_Custom_Counter ()

    On Error GoTo Next_Custom_Counter_Err

    Dim MyDB As Database
    Dim MyTable As Table

    Dim NextCounter As Integer
    ' If the control is large, you may need to make this a Double.

    '================================================= =============
    ' Open table and get the next available number, increment value
    ' by 10 and save the number back into the table.
    '================================================= =============

    Set MyDB = CurrentDB()
    Set MyTable = MyDB.OpenTable("Counter Table")

    MyTable.Edit
    NextCounter = MyTable("Next Available Counter")

    '================================================= ==============
    ' The next line can be changed to conform to your custom counter
    ' preferences. This example only increments the value by +10
    ' each time.
    '================================================= ==============

    MyTable("Next Available Counter") = NextCounter + 10
    MyTable.Update

    MsgBox "Next available counter value is " & Str$(NextCounter)
    Next_Custom_Counter = NextCounter

    Exit Function

    '================================================= ===============
    ' The following error routine should be replaced with a custom
    ' error routine. This example only resumes execution if an error
    ' occurs. If a record locking error occurs, this is fine. But any
    ' non-record locking error will result in an infinite loop.
    '================================================= ===============

    Next_Custom_Counter_Err:
    Msgbox "Error " & err & ": " & error$
    If ERR <> 0 then Resume
    End
    End Function

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    http://support.microsoft.com/default...Ben-us%3B88159

    The information in this article applies to:
    Microsoft Access 1.0
    Microsoft Access 1.1
    Microsoft Access 2.0


    Let me take a look at this and get back to you in a few....
    Matt

  5. #5
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    But first -

    >All i need is a way to increment and assign a number to a record as its invoicenumber on printing the invoice report. But heres the full code from MS.

    Random number???

    Please be more specific if you can.

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Here's the function in DAO:

    Public Function Next_Custom_Counter()

    On Error GoTo PROC_ERR

    ' Declare variables
    Dim MyDB As DAO.Database
    Dim MyTable As DAO.Recordset
    Dim strSQL As String
    Dim NextCounter As Integer

    ' Build our SQL string
    strSQL = "SELECT [Next Available Counter] From [Counter Table]"

    ' Set our database and recordset objects
    Set MyDB = CurrentDb
    Set MyTable = MyDB.OpenRecordset(strSQL)

    ' Allow for Edits
    MyTable.Edit

    ' Grab the Next Available Counter
    NextCounter = MyTable.Fields("Next Available Counter")

    ' Update the value in the table
    MyTable.Fields("Next Available Counter") = NextCounter + 10
    MyTable.Update

    ' Display and return value
    MsgBox "Next available counter value is " & Str$(NextCounter)
    Next_Custom_Counter = NextCounter

    ' Clean up
    MyTable.Close
    Set MyTable = Nothing
    MyDB.Close
    Set MyDB = Nothing

    PROC_EXIT:
    Exit Function
    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT


    End Function
    Last edited by Rockey; 04-16-03 at 14:03.

  7. #7
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Here's an ADO version:

    Public Function Next_Custom_Counter_ADO()

    On Error GoTo PROC_ERR

    ' Declare variables
    Dim rs As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim strSQL As String
    Dim NextCounter As Integer

    ' Build our SELECT SQL string
    strSQL = "SELECT [Next Available Counter] From [Counter Table]"

    ' Set our connection object
    Set conn = CurrentProject.Connection

    ' Grab the current value of [Next Available Counter]
    With rs
    ' Set the connection
    .ActiveConnection = conn

    ' Open the recordset
    .Open strSQL, , adOpenKeyset, adLockOptimistic

    ' Grab the Next Available Counter
    NextCounter = rs![Next Available Counter]
    End With

    ' Build our UPDATE SQL string
    strSQL = "UPDATE [Counter Table] SET [Counter Table].[Next Available Counter] = [Next Available Counter]+10"

    ' Execute the Update SQL
    conn.Execute strSQL

    ' Clean up
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing

    ' Display and return value
    MsgBox "Next available counter value is " & Str$(NextCounter)
    Next_Custom_Counter_ADO = NextCounter

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
    End Function

  8. #8
    Join Date
    Mar 2003
    Posts
    36
    Wow? Thanks Rockey. Pardon my ignorance what ADO and DAO and which is recommended.

    Its not a random number. The number is 200345 at the moment. I need to be able to assign 200346 to the next invoice. And 200347 to the one after that.

  9. #9
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Change the code to increment by one vs. ten.

    (ADO) ActiveX Data Object
    (DAO) Data Access Objects

    Recommended? Talk to two and get two answers.

    The main difference that I have found DAO is a connected access tool and ADO is a dis-connected access tool. It connects only for very short times any time it needs to get/put data into the database, then disconnects. ADO doesn't stay connected, so it requires less over head.

    ADO is ActiveX object and has nothing to do with Access. It can be used with Access, but, it can be used with any language that supports ActiveX objects. Microsoft created it as kind of a "universal" data access.

    Whether one should use it, depends on your point of view and needs. I find DAO more useful but I am also more comfortable with it. Often I'll use both in a project.

    ADO is a newer technology and again is "universal" - so if I had to pick one for you to learn --> ADO.

    Let me know if you have troubles.

    Matt

  10. #10
    Join Date
    Mar 2003
    Posts
    36
    Okay, so I might leave doing 2 till later Ive pasted in the dao versio. Ive made a simple form with the onclick property of a command button set to =Next_Custom_Counter().

    So i click the command button and up pops the message but then Im stuck in a loop of messages. "OK" and then "Resume without error" and then "OK"

    You get the idea. All I need to do now is set the value in my invoicenumber field equal to the number.

  11. #11
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    change

    ' Grab the Next Available Counter
    NextCounter = rs![Next Available Counter]

    Me![invoicenumber field] = rs![Next Available Counter]

    comment out
    MsgBox "Next available counter value is " & Str$(NextCounter)
    to avoid this message box


    Confused about your last post...

    Can you paste all of your code for the click event of the button.

    Matt

  12. #12
    Join Date
    Mar 2003
    Posts
    36
    So!! never mind the simple form thing heres the guts of it. Below is my module so far.

    Public Function Next_Custom_Counter()

    On Error GoTo PROC_ERR

    ' Declare variables
    Dim MyDB As DAO.Database
    Dim MyTable As DAO.Recordset
    Dim strSQL As String
    Dim NextCounter As Integer

    ' Build our SQL string
    strSQL = "SELECT [Next Available Counter] From [Counter Table]"

    ' Set our database and recordset objects
    Set MyDB = CurrentDb
    Set MyTable = MyDB.OpenRecordset(strSQL)

    ' Allow for Edits
    MyTable.Edit

    ' Grab the Next Available Counter
    NextCounter = rs![Next Available Counter]

    ' Update the value in the table
    MyTable.Fields("Next Available Counter") = NextCounter + 1
    MyTable.Update

    ' Display and return value

    Next_Custom_Counter = NextCounter

    ' Clean up
    MyTable.close
    Set MyTable = Nothing
    MyDB.close
    Set MyDB = Nothing

    PROC_EXIT:
    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
    Exit Function

    End Function


    I have a big ugly form for doing orders and it has a button called print that has this code already.

    Private Sub Command85_Click()


    Dim Msg, Style, Title, Help, Ctxt, Response, MYSTRING
    Msg = "Do you want to mark this order as invoiced and print the invoice ?" ' Define message.
    Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
    Title = "Confirm your actions" ' Define title.
    Help = "DEMO.HLP" ' Define Help file.
    Ctxt = 1000 ' Define topic
    ' context.
    ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then ' User chose Yes.

    Forms![CustomerAcc_rev2]![ordersrev2].Form![InvoiceDate] = Date
    Forms![CustomerAcc_rev2]![ordersrev2].Form![Invoiced] = True

    DoCmd.OpenReport "Invoice 2", acViewNormal
    Else ' User chose No.
    MYSTRING = "No" ' Perform some action.
    End If


    my invoicenumber field is on a subform and is refered to as Forms![CustomerAcc_rev2]![ordersrev2].Form![Invoicenumber]

    SO now the final bit. I want to hit a button that sets my invoicenumber to our counter.

    Thanks

  13. #13
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Change
    Forms![CustomerAcc_rev2]![ordersrev2].Form![InvoiceDate] = Date

    To
    Forms![CustomerAcc_rev2]![ordersrev2].Form![InvoiceDate] = Next_Custom_Counter()


    -------------------------------------
    >So i click the command button and up pops the message but then Im stuck in a loop of messages. "OK" and then "Resume without error" and then "OK"

    ??????????????????????????????

  14. #14
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Here you go:

    ' Grab the Next Available Counter
    NextCounter = rs![Next Available Counter]

    Should be
    ' Grab the Next Available Counter
    NextCounter = MyTable![Next Available Counter]



    and
    Forms![CustomerAcc_rev2]![ordersrev2].Form![InvoiceDate] = Next_Custom_Counter()

  15. #15
    Join Date
    Mar 2003
    Posts
    36
    okay. So Ive added a line

    Forms![CustomerAcc_rev2]![ordersrev2].Form![InvoiceNumber] = Next_Custom_Counter()

    to the onclick event.

    But on clicking after my message Im getting an error message box saying object required. When I OK that I get a blank message with just an ok button. When I okay that I get a resume without error message. When I okay that Im back to the blank one etc and around we go! Any ideas?

Posting Permissions

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