Dynamic SQL Composition 1

[BGumm]

I'm creating a program which, in one functional requirement, asks the user to enter a number, say n, into a form. This number will be used to append 'n' records to an existing table, where the primary keys are JobID and RowID (being {1,2,...,n}). JID is computed as the concatenation of date, time, and a text string (currently " - 001" but will later be replaced by the username).

I tried a 'For count = 1 to RID / Next' loop, and when that didn't work, I went down to trying to just inserting a single row using the following (which is also not working):

Public Function JID() As String
JID = Date & " " & Time() & " - 001"
End Function

Public Sub Command6_Click()
Dim tempSQL As String
tempSQL = "INSERT INTO [tbl Job Temp] (JobID) SELECT "
tempSQL = tempSQL & JID
tempSQL = tempSQL & " As JobID"
DoCmd.RunSQL tempSQL
End Sub

Can anyone help? Thanks!

::Bryan

PS - in the underlying table, JobID is a "Text" field.

 

[PHV]

tempSQL = "INSERT INTO [tbl Job Temp] (JobID) VALUES ('"
tempSQL = tempSQL & JID & "')"

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

 

[BGumm]

This does the trick - thanks!

The next problem is back in the For . . . Next loop.

Public Sub Command6_Click()
    Dim tempSQL As String
    Dim ctr As Integer
    Dim count As Integer
    ctr = 1
    count = 1
    For ctr = 1 To NumGps
        tempSQL = ""
        tempSQL = "INSERT INTO [tbl Job Temp] (JobID,LineID) VALUES ('"
        tempSQL = tempSQL & JID & "',count)"
        DoCmd.RunSQL tempSQL
        count = count + 1
    Next
End Sub

Instead of inserting the value of count as it was when the loop was iterated, all values are 4 (when a 3 is entered for NumGps). Any ideas why?

PS - when I replace 'count' with another variale (have tried several different ones), it doesn't work at all, leading me to believe count, in this case, is a built-in variable?

 

[PHV]

Replace this:
tempSQL = tempSQL & JID & "',count)"
with this:

tempSQL = tempSQL & JID & "'," & count & ")"

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