VBA for MS Access Insert Records with Sequential Field

[MontgomeryPete]

Hello:

I new to VBA, and I'm not sure if I have a code problem, or if I am calling the routine incorrectly. We want to generate tickets with varied data on them, but need to keep the numbers in sequential order for accounting purposes.

I have built simple form to let the user input ticket information, plus enter the starting and ending number of the range of tickets to be printed.

Here is the code for the function:

Option Compare Database

    Function CreateTickets() As Boolean

        Dim db As Database
        Dim LInsert As String
        Dim LTicketCreate As String
        Dim LLoop As Integer

        On Error GoTo Err_Execute

        Set db = CurrentDb()

        LLoop = 1

        'Create number of records based on Ticket Number value
        While LLoop <= StartingNumber

            'Get next TicketNumber value (sequential number)
            LTicketCreate = StartingNumber
            If LTicketCreate = "" Then
            GoTo Err_Execute
            End If

            'Create new record
            LInsert = "Insert into Tickets TicketNumber,   CompanyName, AddressStreet, AddressCity, Telephone,"
            LInsert = LInsert & " Comment, TicketComment, TicketValue) VALUES"
            LInsert = LInsert & TicketNumber
            LInsert = LInsert & CompanyName
            LInsert = LInsert & AddressStreet
            LInsert = LInsert & AddressCity
            LInsert = LInsert & Telephone
            LInsert = LInsert & Comment
            LInsert = LInsert & TicketComment
            LInsert = LInsert & TicketValue

            db.Execute LInsert, dbFailOnError

            LLoop = LLoop + 1
        Wend

        Set db = Nothing

        CreateTickets = True

        Exit Function

Err_Execute:
        'An error occurred
        CreateTickets = False
        MsgBox "An error occurred while trying to add new Ticket records."
   

End Function

I am calling the function via a private sub which is trigered by the OnClick event attached to a button on the form. The above code complies with no errors but will not run.

Any suggestions?

Thanks

 

[PHV]

Your INSERT SQL statement has lot of syntax errors ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Debug.Print your LInsert variable.

Take the result string from the Immediate Window and past it in the SQL in MS Access to test its correctness.

FIX IT and then CODE IT.

BTW, MS Access VBA questions -- forum705

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MontgomeryPete]

Thanks for the tips, PHV and SkipVought. This was a good exercise (and lesson), although it helps more when I got into the right forum!

Pete