Vba Insert into tbl

[Pete Printer]

Hi

This code works fine and inserts the 3 values into the table.

DoCmd.RunSQL "INSERT INTO JobsStaffAvail ( tblJobsID, tblStaffAvailID, bookedBy )" _
    & "VALUES(Forms!frmbookstafftojob.JobsID ,Forms!frmbookstafftojob.subfrmStaffAvail.Form.staffid ,'TestData')"

However i need to replace the TestData value with the current user details.
i created a Variable BookedBy as string and set it thus.

BookedBy = Environ$("username")

The variable is set fine
i have tried replacing TestData with BookedBy and this throws error end of statement required.
Samething happens when i try

 '(Environ$("username"))'

Thanks for looking at this.:banghead:
Pete

 

[Minty]

Are you sure this works ? I didn't think it would interpret the references to your forms

DoCmd.RunSQL "INSERT INTO JobsStaffAvail ( tblJobsID, tblStaffAvailID, bookedBy )" _
    & "VALUES(Forms!frmbookstafftojob.JobsID ,Forms!frmbookstafftojob.subfrmStaffAvail.Form.staffid , BookedBy)"

It's much better for debugging to build the SQL string separately ;

BookedBy = Environ$("username")
sSql = "INSERT INTO JobsStaffAvail ( tblJobsID, tblStaffAvailID, bookedBy ) " 
sSql = sSql & "VALUES (" & Forms!frmbookstafftojob.JobsID & " , " 
sSql = sSql &  Forms!frmbookstafftojob.subfrmStaffAvail.Form.staffid & " , '"
sSql = sSql &  BookedBy & "' );"

Debug.Print sSql  [COLOR="YellowGreen"] ' This will show you what it's created with your values[/COLOR]

CurrentDb.Execute sSql, dbSeecChanges

 

[arnelgp]

DoCmd.RunSQL "INSERT INTO JobsStaffAvail ( tblJobsID, tblStaffAvailID, bookedBy )" _
& "VALUES(" & Forms!frmbookstafftojob.JobsID & "," & _
Forms!frmbookstafftojob.subfrmStaffAvail.Form.staffid & ",'" & _
Environ("username") & "')"

 

[JANR]

Re: Vba Insert into tbl
Are you sure this works ? I didn't think it would interpret the references to your forms

DoCmd.RunSQL "INSERT INTO JobsStaffAvail ( tblJobsID, tblStaffAvailID, bookedBy )" _
& "VALUES(Forms!frmbookstafftojob.JobsID ,Forms!frmbookstafftojob.subfrmStaffAvail.Form.staffid , BookedBy)"

Yes Minty, you can do it like OP did, provided you use DoCmd.RunSql and not execute methode. RunSQL gets evalueted by Access at runtime and not dumped into the engine like Execute does.

Janr

 

[Pete Printer]

Minty
Thanks for the prompt reply.
You code works fine thank you.
i can see the logic in building the statement this way.
That is very useful advice as well.

Best regards
Pete

 

[Pete Printer]

JANR
thanks for that its working fine now.

Great forum this is
Pete