Table insert 1

[bdjb]

Hello,
I'm using Access 2003. I'm using the dlast command to find the last value in a table, which works. I'm storing it a variable. What is the proper syntax to then insert that variable into a different table?
Currently I have
strSQLApp2 = "INSERT INTO Courses (....Training_ID) VALUES (....'& strLastValue &')"

It all works except the strLastValue. A msg box shows '& strLastValue &'

thanks in advance

 

[PWise]

strSQLApp2 = "INSERT INTO Courses (....Training_ID) VALUES (....'" & strLastValue & "')"

 

[bdjb]

Thank you for your reply. When I add the " ", It runs, but I still don't get any data. I have a textbox set to show the output of storing the lookup, it gives me the correct data, but when I try to insert it into the table, I get nothing. (The textbox showing the isert command give '' for that field.)

Any idea?

 

[PWise]

what type of field is Training_ID is it text or numeric

 

[bdjb]

The table i am reading the field from is autonumber, the field I'm trying to write to is number.

 

[PWise]

try

strSQLApp2 = "INSERT INTO Courses (....Training_ID) VALUES (...." & strLastValue & ")"

 

[bdjb]

Thanks,
I still get 0, although the txtbox before and after my isert show the correct data. What data type should I make the variable when I declare it?

 

[PWise]

what is your complete code

 

[bdjb]

Private Sub Assign_Individual_Click()
'This shows what training one employee attended on the after Training Form
'declare variables for holding data
Dim strSQLApp1 As String
Dim strSQLApp2 As String
Dim strLastValue As Single
Dim x As Integer
x = 0
Dim recordcountnum As Integer

'Assign recordcountnum to the number of records present.
recordcountnum = Me.RecordsetClone.RecordCount

DoCmd.SetWarnings False
If Forms!frmSearch_Detail_After!BPML <> "" Then

strSQLApp1 = "INSERT INTO Training_Taken (Employee_Number, Job_Title, Train_Course, trnDate, trnTime, AC, Training_Status, Course) VALUES (" & Forms!frmSearch_Detail_After!employee & ", '" & Forms!frmSearch_Detail_After!Job_Title & "', '" & Forms!frmSearch_Detail_After!BPML & "','" & Forms!frmSearch_Detail_After!trnDate & "', '" & Forms!frmSearch_Detail_After!trnTime & "', '" & "A" & "' , '" & Forms!frmSearch_Detail_After!Training_Status & "', '" & Forms!frmSearch_Detail_After!Course & "')"
strSQLApp2 = "INSERT INTO Courses (Course, Description, Hours, trainer, Training_ID) VALUES ('" & Forms!frmSearch_Detail_After!Course & "', '" & Forms!frmSearch_Detail_After!description & "', '" & Forms!frmSearch_Detail_After!Hours & "', '" & Forms!frmSearch_Detail_After!Trainer & "', " & strLastValue & ")"

Else

strSQLApp1 = "INSERT INTO Training_Taken (Employee_Number, Job_Title, Train_Course, trnDate, trnTime, AC, Training_Status, Course, Description, Hours, trainer) VALUES (" & Forms!frmSearch_Detail_After!employee & ", '" & Forms!frmSearch_Detail_After!Job_Title & "', '" & Forms!frmSearch_Detail_After!Individual & "','" & Forms!frmSearch_Detail_After!trnDate & "', '" & Forms!frmSearch_Detail_After!trnTime & "','" & "A" & "' , '" & Forms!frmSearch_Detail_After!Training_Status & "', '" & Forms!frmSearch_Detail_After!Course & "')"
strSQLApp2 = "INSERT INTO Courses (Course, Description, Hours, trainer, Training_ID) VALUES ('" & Forms!frmSearch_Detail_After!Course & "', '" & Forms!frmSearch_Detail_After!description & "', '" & Forms!frmSearch_Detail_After!Hours & "', '" & Forms!frmSearch_Detail_After!Trainer & "', '" & strLastValue & "')"

End If

MsgBox strSQLApp1
DoCmd.RunSQL strSQLApp1

strLastValue = DLast("Training_Taken.Training_ID", "Training_Taken", "Training_ID is not null")
MsgBox strLastValue

MsgBox strSQLApp2
DoCmd.RunSQL strSQLApp2
MsgBox strLastValue

DoCmd.SetWarnings True
MsgBox "The training has been assigned"

Training_Status.Value = 1

End Sub

 

[PWise]

Private Sub Assign_Individual_Click()
'This shows what training one employee attended on the after Training Form
'declare variables for holding data
    Dim strSQLApp1 As String
    Dim strSQLApp2 As String
    Dim strLastValue As Single


DoCmd.SetWarnings False
If Forms!frmSearch_Detail_After!BPML <> "" Then

    strSQLApp1 = "INSERT INTO Training_Taken (Employee_Number, Job_Title, Train_Course, trnDate, trnTime, AC, Training_Status, Course) VALUES (" & Forms!frmSearch_Detail_After!Employee & ", '" & Forms!frmSearch_Detail_After!Job_Title & "', '" & Forms!frmSearch_Detail_After!BPML & "','" & Forms!frmSearch_Detail_After!trnDate & "', '" & Forms!frmSearch_Detail_After!trnTime & "', '" & "A" & "' , '" & Forms!frmSearch_Detail_After!Training_Status & "', '" & Forms!frmSearch_Detail_After!Course & "')"

Else

    strSQLApp1 = "INSERT INTO Training_Taken (Employee_Number, Job_Title, Train_Course, trnDate, trnTime, AC, Training_Status, Course, Description, Hours, trainer) VALUES (" & Forms!frmSearch_Detail_After!Employee & ", '" & Forms!frmSearch_Detail_After!Job_Title & "', '" & Forms!frmSearch_Detail_After!Individual & "','" & Forms!frmSearch_Detail_After!trnDate & "', '" & Forms!frmSearch_Detail_After!trnTime & "','" & "A" & "' , '" & Forms!frmSearch_Detail_After!Training_Status & "', '" & Forms!frmSearch_Detail_After!Course & "')"


End If

MsgBox strSQLApp1
DoCmd.RunSQL strSQLApp1

strLastValue = DLast("Training_Taken.Training_ID", "Training_Taken", "Training_ID is not null")
strSQLApp2 = "INSERT INTO Courses (Course, Description, Hours, trainer, Training_ID) VALUES ('" & Forms!frmSearch_Detail_After!Course & "', '" & Forms!frmSearch_Detail_After!Description & "', '" & Forms!frmSearch_Detail_After!Hours & "', '" & Forms!frmSearch_Detail_After!Trainer & "', " & strLastValue & ")"

MsgBox strLastValue

MsgBox strSQLApp2
DoCmd.RunSQL strSQLApp2
MsgBox strLastValue

DoCmd.SetWarnings True
MsgBox "The training has been assigned"

Training_Status.Value = 1

End Sub

 

[bdjb]

Well! That certainly cleaned up my code, and it works perfectly! Thank you very much, have a star!