Writing records to a table

[JonnyB]

Hi all,

I've got some code to write a record to a table but its got me confused, it works fine but instead of taking the values from the system its giving me popups to fill in the values manually. It should be taking userName, date and time from the system.

Any ideas what I've done wrong,

Here's the code?

Private Sub btnEngBook_Click()
 
Dim username As String
Dim jID As String
Dim Tvalue As String
Dim LDate As String
 
 
username = Nz(Forms!frmLogin!txtMembername, "")
jID = "job1"
Tvalue = Time
LDate = Date
 
Dim strSQL As String
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],[jID],[Tvalue],[LDate],[Active])"
DoCmd.RunSQL (strSQL)
 
 
End Sub

[willakawill]

Hi all,

I've got some code to write a record to a table but its got me confused, it works fine but instead of taking the values from the system its giving me popups to fill in the values manually. It should be taking userName, date and time from the system.

Any ideas what I've done wrong,

Here's the code?

Private Sub btnEngBook_Click()

Dim username As String
Dim jID As String
Dim Tvalue As String
Dim LDate As String


username = Nz(Forms!frmLogin!txtMembername, "")
jID = "job1"
Tvalue = Time
LDate = Date

Dim strSQL As String
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],[jID],[Tvalue],[LDate],[Active])"
DoCmd.RunSQL (strSQL)


End Sub

Hi. I wonder is this a direct paste of your code? It will not work as is. I don't see what [Active] is supposed to be and I will assume it is another variable not shown in your code:

Dim strSQL As String
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values( " _
   & username & "," & jID & ",'" & Tvalue & "','" & LDate & "'," & Active & ")"
DoCmd.RunSQL (strSQL)

[JonnyB]

Hi willakawill,

It is a direct paste of my script and I had missed the active variable from it, I pasted in what you posted which is still bringing up popups to ask for the parameters. Its a job booking sytem and I want the user to be able to just click book and it fill the record in automatically with userID, jobID, startTime, startDate and set jobStatus to active.

I've uploaded it to here so you can take a look http://www.celestepoker.com/Temp/jr_system.mdb . Its got a login script to start with so if you enter username as u1 and password as 00 then click "Create Booking" then "Book" you'll see what i'm tryin to achieve.

Hope you can help.

[willakawill]

Oops. Forgot that this is Access

Dim strSQL As String
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values( " _
   & username & "," & jID & ",#" & Tvalue & "#,#" & LDate & "#," & Active & ")"
DoCmd.RunSQL (strSQL)

make sure that each of these variables contains a value.

[JonnyB]

Cheers for your help,

I got it working perfectly thanks to you.

Here's the solution just incase it'll help any other newbies like me

Dim username As String
Dim jID As String
Dim Tvalue As String
Dim LDate As String
Dim jobActive As String
 
username = Nz(Forms!frmLogin!txtMembername, "")
jID = "job1"
Tvalue = Time
LDate = Date
jobActive = "Active"
 
Dim strSQL As String
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values(  '" & username & "','" & jID & "',#" & Tvalue & "#,#" & LDate & "#,'" & jobActive & "')"
DoCmd.RunSQL (strSQL)

[willakawill]

Cheers for your help,

I got it working perfectly thanks to you.

Here's the solution just incase it'll help any other newbies like me

Dim username As String
Dim jID As String
Dim Tvalue As String
Dim LDate As String
Dim jobActive As String

username = Nz(Forms!frmLogin!txtMembername, "")
jID = "job1"
Tvalue = Time
LDate = Date
jobActive = "Active"

Dim strSQL As String
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values( '" & username & "','" & jID & "',#" & Tvalue & "#,#" & LDate & "#,'" & jobActive & "')"
DoCmd.RunSQL (strSQL)

You are very welcome. When you post code here place it within code tags because it makes it easier to read. Just highlight the text that represents your code and click on the # at the top of the submission box