Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA for MS Access Insert Records with Sequential Field

Status
Not open for further replies.

MontgomeryPete

Instructor
Apr 3, 2004
57
US
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:
Code:
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

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

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



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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top