By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Writing records to a table

P: 16
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnEngBook_Click()
  2.  
  3. Dim username As String
  4. Dim jID As String
  5. Dim Tvalue As String
  6. Dim LDate As String
  7.  
  8.  
  9. username = Nz(Forms!frmLogin!txtMembername, "")
  10. jID = "job1"
  11. Tvalue = Time
  12. LDate = Date
  13.  
  14. Dim strSQL As String
  15. strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],[jID],[Tvalue],[LDate],[Active])"
  16. DoCmd.RunSQL (strSQL)
  17.  
  18.  
  19. End Sub
Jan 8 '07 #1
Share this Question
Share on Google+
5 Replies


100+
P: 1,646
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values( " _
  3.    & username & "," & jID & ",'" & Tvalue & "','" & LDate & "'," & Active & ")"
  4. DoCmd.RunSQL (strSQL)
Jan 8 '07 #2

P: 16
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.
Jan 8 '07 #3

100+
P: 1,646
Oops. Forgot that this is Access

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values( " _
  3.    & username & "," & jID & ",#" & Tvalue & "#,#" & LDate & "#," & Active & ")"
  4. DoCmd.RunSQL (strSQL)
make sure that each of these variables contains a value.
Jan 8 '07 #4

P: 16
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

Expand|Select|Wrap|Line Numbers
  1. Dim username As String
  2. Dim jID As String
  3. Dim Tvalue As String
  4. Dim LDate As String
  5. Dim jobActive As String
  6.  
  7. username = Nz(Forms!frmLogin!txtMembername, "")
  8. jID = "job1"
  9. Tvalue = Time
  10. LDate = Date
  11. jobActive = "Active"
  12.  
  13. Dim strSQL As String
  14. strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate,bookingStatus) Values(  '" & username & "','" & jID & "',#" & Tvalue & "#,#" & LDate & "#,'" & jobActive & "')"
  15. DoCmd.RunSQL (strSQL)
Jan 8 '07 #5

100+
P: 1,646
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
Jan 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.