Hi need a little help with how to get an SQL insert string to recognise VB variable as the insert values.
Basically I have a Project Management Database I am working on.
Each project has 281 standard steps split into 8 gates.
The powers that be have a meeting every Monday to discuss and update project paths, after this meeting they would like to capture gate completion status data and generate a weekly KPI.
I already have a query that tracks the gate status of each project but needed a way to count these and add to a table to create a historic weekly record of progress.
I decided to go for a completion form at the end of the meeting to pass the command to generate and capture the appropriate data.
I have written the following code to calculate and pass the information to my table, the VB variables capture the appropriate data but my SQL values statement does not pass the info into the table, instead I get the enter parameters box.
Any help would be greatley recieved
Basically I have a Project Management Database I am working on.
Each project has 281 standard steps split into 8 gates.
The powers that be have a meeting every Monday to discuss and update project paths, after this meeting they would like to capture gate completion status data and generate a weekly KPI.
I already have a query that tracks the gate status of each project but needed a way to count these and add to a table to create a historic weekly record of progress.
I decided to go for a completion form at the end of the meeting to pass the command to generate and capture the appropriate data.
I have written the following code to calculate and pass the information to my table, the VB variables capture the appropriate data but my SQL values statement does not pass the info into the table, instead I get the enter parameters box.
Code:
Private Sub Command2_Click()
Dim strSQL As String
Dim TStatus1 As Double
Dim NDStatus1 As Double
Dim DStatus1 As Double
Dim ODStatus1 As Double
Dim CStatus1 As Double
TStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry")
NDStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'NOT DUE'")
DStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'DUE'")
ODStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'OVER DUE'")
CStatus1 = DCount("[1 STATUS]", "CurrentLaunch_Qry", "[1 STATUS] = 'COMPLETE'")
strSQL = "INSERT INTO GateKPI_Tbl (TSTATUS1, NDSTATUS1, DSTATUS1, ODSTATUS1, CSTATUS1)" & _
"VALUES (TStatus1, NDStatus1, DStatus1, ODStatus1, CStatus1);"
DoCmd.RunSQL strSQL
Any help would be greatley recieved