Incremental Times INSERT INTO statement via VBA

[GrandMasterTuck]

[SOLVED] Incremental Times INSERT INTO statement via VBA

Hi folks, I'm adding code to a button that sets some TempVars and then executes the following function:

Function AddAvails()
    TempVars("TheTime").Value = "00:00"
Do While [TempVars]![IncNumber] > 0
Dim SQL As String
    SQL = "INSERT INTO tblRangeAvailable (COFID, COFDate, COFTime, COFMaxParticipants) " & _
          "VALUES (" & [TempVars]![TheCOFID] & ", " & [TempVars]![TheDate] & ", " & [TempVars]![TheTime] & ", " & [TempVars]![TheParts] & ");"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    TempVars("IncNumber").Value = [TempVars]![IncNumber] - 1
    TempVars("TheTime").Value = [TempVars]![TheTime] + [TempVars]![TheIncrement]
    Loop
End Function

The temp vars are set as follows:

TheCOFID is the ID number of the Appointment date, the button's macro fetches this value from a field on the form

TheDate is the date listed for the ID number selected, also a field on the form fetched by the macro

TheTime is set by the Function as "00:00" (short date format)

TheParts is set by a third field on the form, which lists the maximum number of participants (default is five)

TheIncrement is set by a fourth field on the form, in which the user enters a number (in minutes) between each appointment

IncNumber is a calculated field, basically it takes the number 1440 (minutes in one 24 hour period) and divides by TheIncrement's value to get the number of 'slots' for the day that will be available.

But when I run this code, I get a Run-Time error 3134 that highlights the DoCmd.RunSQL statement

Any idea why? I'm stumped...

EDIT: Basically, I want the button to add records to a table; The number of records I want it to add is determined by the number 1440 divided by the temp var TheIncrement; The first record's time I want to be 00:00 (midnight) and for each additional record, add TheIncrement to it, so if TheIncrement is 60, the first record will be 00:00, the next will be 01:00, the next will be 02:00 and so on; and I want it to simply write the other values directly into the rest of the fields without changing anything.

And It doesn't work... dang it all... so I'm missing something or not doing something correctly. Any help would be massively appreciated... thanks again!

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

Also, values for fields with a text data type need ' delimiters, date/time fields need #. More here:

http://www.baldyweb.com/BuildSQL.htm

 

[GrandMasterTuck]

Ah. Thanks for that, pbaldy, that helped me figure out why it wasn't working. As it turns out, the parsed string ended up malformed. Here is the correction, and this one works perfectly.

Function AddAvails()
    TempVars("TheTime").Value = [COLOR="Red"][B]#12:00:00 AM#[/B][/COLOR]
Do While [TempVars]![IncNumber] > 0
Dim SQL As String
    SQL = "INSERT INTO tblRangeAvailable (COFID, COFDate, COFTime, COFMaxParticipants) " & _
          "VALUES (" & [TempVars]![TheCOFID] [COLOR="red"][B]& ", #"[/B][/COLOR] & [TempVars]![TheDate] [B][COLOR="red"]& "#, #"[/COLOR][/B] & [TempVars]![TheTime] [COLOR="red"][B]& "#, "[/B][/COLOR] & [TempVars]![TheParts] & ");"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True
    TempVars("IncNumber").Value = [TempVars]![IncNumber] - 1
    TempVars("TheTime").Value = DateAdd("n", [TempVars]![TheIncrement], [TempVars]![TheTime])
    Loop
End Function

1. I had to set the TempVars!TheTime value to a correctly formatted time with AM notation

2. I had to add the "#" symbols to either side of the Date and Time references in the VALUES portion of the SQL statement. This code makes it work perfectly, and it creates the exact number of records I need with the incremented times perfectly calculated. Thanks for everything, and I hope this helps others who might be thinking about implementing something like this on a form!

 

[pbaldy]

Happy to help!