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

WHERE clause in an INSERT in VBA/Access

markmcgookin
Expert 100+
P: 648
Hi Guys,

Bit of a problem here, would REALLY appreciate some help.

I have a table tblBooking, and I want to insert a number of values into it.

This is my code so far (I have a few redundant Dims, but I don't think they are causing any problems)

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. jID = "GangJob"
  8. gang_no = Me.txtGang
  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 & "',[Start Time],[Start Date],[jobActive])WHERE gangNo = '" & gang_no & "' "
  15. DoCmd.RunSQL (strSQL)
  16.  
When I remove the WHERE clause the code works, is it posible to use this code to add the input values into multiple records?

The idea is the data input will be put into the booking table, for all the users with gangNo (input from txtGang)

The whole database is available for you to look at here

http://www.cmdclan.co.uk/Temp/jr_system.mdb

The button is on frmForGangBook form.

Thanks guys, really stressing out about this! :S
Jan 10 '07 #1
Share this Question
Share on Google+
20 Replies


markmcgookin
Expert 100+
P: 648
Infact, reading that, I know the code is wrong,

the WHERE would be for reading the table... like update where, not what I want!

Haha, could anyone help me out? I assume I need a loop here.
Jan 10 '07 #2

markmcgookin
Expert 100+
P: 648
Me again, sorry!

(I'm not doing this to move up or anything, just keep thinking of ways to work my query better!)

Here is some Pseudocode:

i) Read in gangNo
ii) Request Details to be input with each record Start Time, Start Date
iv) Then for each user that has that gang no, create a record in tblBooking Including:

UserID (from tblUser); JobId="GANG"; startTime (read in); Start Date(Read In); bookingStatus="Active"

I just have no idea how to do this! and my deadline is friday morning.

again, you can DL the BD at www.cmdclan.co.uk/Temp/jr_system.mdb

it is the button on frmGangBook that I want this to

Cheers again, sry for replying to my own thread :S
Jan 10 '07 #3

ADezii
Expert 5K+
P: 8,597
Hi Guys,

Bit of a problem here, would REALLY appreciate some help.

I have a table tblBooking, and I want to insert a number of values into it.

This is my code so far (I have a few redundant Dims, but I don't think they are causing any problems)

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. jID = "GangJob"
  8. gang_no = Me.txtGang
  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 & "',[Start Time],[Start Date],[jobActive])WHERE gangNo = '" & gang_no & "' "
  15. DoCmd.RunSQL (strSQL)
  16.  
When I remove the WHERE clause the code works, is it posible to use this code to add the input values into multiple records?

The idea is the data input will be put into the booking table, for all the users with gangNo (input from txtGang)

The whole database is available for you to look at here

http://www.cmdclan.co.uk/Temp/jr_system.mdb

The button is on frmForGangBook form.

Thanks guys, really stressing out about this! :S
If gang_no is a Numeric Value shouldn't the SQL should read:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],'" & jID & "',[Start Time],[Start Date],[jobActive])WHERE gangNo = " & gang_no 
  2. DoCmd.RunSQL (strSQL)
Jan 10 '07 #4

markmcgookin
Expert 100+
P: 648
Hey,

so glad to get someone helping, I feel I am VERY clise now :D

My code is different now, it reads

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnEngBook_Click()
  2.  
  3. Dim gang As String
  4. gang = Me.txtGang.Value
  5. Dim rs As DAO.Recordset
  6. Dim sqlStr As String
  7. Dim startT As String
  8. Dim startD As String
  9. Dim job As String
  10.  
  11. job = "Gang"
  12. booking = "Active"
  13.  
  14. startD = InputBox("Input a start date dd.mm.yy", "Input Start Date")
  15.  
  16. startT = InputBox("Input a start Time hh.mm", "Input Start Time")
  17.  
  18. sqlStr = "SELECT tblUser.userID, tblUser.gangNo FROM tblUser WHERE gangNo=" & gang
  19.  
  20. Set rs = CurrentDb.OpenRecordset(sqlStr)
  21.  
  22. Do While Not rs.EOF
  23.  
  24. sqlStr2 = "INSERT INTO tblBooking(userID, jobID, jobType, startTime, startDate,bookingStatus) Values (str(&rs!userID)&)&, job, job, startT, startD, booking)"
  25.  
  26. rs.MoveNext
  27. Loop
  28.  
  29. End Sub
  30.  
It now reads in the values (successfully) and when i run i get run time err 3464 Data type mismatch in criteria expression, hi-lighting my Set rs = CurrentDb.OpenRecordset(sqlStr) row.

Pls Help! hehe

PS - Everything in tblBooking is a string, some have masked input values but they are all strings, even the date (due to someone else on the team changing it for their section, as it is only a record of time/date is doesnt matter too much)
Jan 10 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
This might help a little.
sqlStr has been changed a little - as has sqlStr2.
I also added code to execute sqlStr2 rather than just drop it on the floor...
Anyway, see how it goes.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnEngBook_Click()
  2.  
  3. Dim gang As String
  4. gang = Me.txtGang.Value
  5. Dim rs As DAO.Recordset
  6. Dim sqlStr As String
  7. Dim startT As String
  8. Dim startD As String
  9. Dim job As String
  10.  
  11. job = "Gang"
  12. booking = "Active"
  13.  
  14. startD = InputBox("Input a start date dd.mm.yy", "Input Start Date")
  15.  
  16. startT = InputBox("Input a start Time hh.mm", "Input Start Time")
  17.  
  18. sqlStr = "SELECT userID, gangNo " & _
  19.          "FROM tblUser " & _
  20.          "WHERE gangNo='" & gang & "'"
  21.  
  22. Set rs = CurrentDb.OpenRecordset(sqlStr)
  23.  
  24. Do While Not rs.EOF
  25.  
  26. sqlStr2 = "INSERT INTO tblBooking(" & _
  27.           "userID, jobID, jobType, " & _
  28.           "startTime, startDate,bookingStatus) " & _
  29.           "Values (str(" & rs!userID & "), job, job, startT, startD, booking)"
  30. DoCmd.RunSQL sqlStr2
  31. rs.MoveNext
  32. Loop
  33.  
  34. End Sub
Jan 11 '07 #6

markmcgookin
Expert 100+
P: 648
Hey,

I have uploaded the db with the new code in the button, however when I try to create the booking, instead of reading in a the values already gathered it brings up pop ups for everything.

Would you have a look at it for me?

It'l finish uploading in 10 mins (00.50am)

Cheers guys,

So nice to see other IT Insomniacs!
Jan 11 '07 #7

markmcgookin
Expert 100+
P: 648
Hey,

I have uploaded the db with the new code in the button, however when I try to create the booking, instead of reading in a the values already gathered it brings up pop ups for everything.

Would you have a look at it for me?

It'l finish uploading in 10 mins (00.50am)

Cheers guys,

So nice to see other IT Insomniacs!
NB ** It is uploaded now, frmForGangBook :D
Jan 11 '07 #8

markmcgookin
Expert 100+
P: 648
NB ** It is uploaded now, frmForGangBook :D
I have changed this statement to read

Expand|Select|Wrap|Line Numbers
  1. sqlStr2 = "INSERT INTO tblBooking(" & _
  2.           "userID, jobID, jobType, " & _
  3.           "startTime, startDate, bookingStatus) " & _
  4.           "Values ('" & rs!userID & "', job, job, startT, startD, booking)"
and now i dont get the popup for UserID but i do get job, job, startT, etc..

So it is not passing the variables from above in the code down, I think it is just whatever is surrounding the values job,job etc etc... I have tried a load o different variations, like ' " job " ' / " ' job ' " / [job] / "&job&" etc etc

But can't seem to find one that works, the only one that works is the userID which is coming from the recordset (" & rs!userID & "')

Any ideas?

Mark
Jan 11 '07 #9

Expert 5K+
P: 8,435
I have changed this statement to read
Expand|Select|Wrap|Line Numbers
  1. sqlStr2 = "INSERT INTO tblBooking(" & _
  2.           "userID, jobID, jobType, " & _
  3.           "startTime, startDate, bookingStatus) " & _
  4.           "Values ('" & rs!userID & "', job, job, startT, startD, booking)"
and now i dont get the popup for UserID but i do get job, job, startT, etc..

So it is not passing the variables from above in the code down, I think it is just whatever is surrounding the values job,job etc etc... I have tried a load o different variations, like ' " job " ' / " ' job ' " / [job] / "&job&" etc etc

But can't seem to find one that works, the only one that works is the userID which is coming from the recordset (" & rs!userID & "')Mark
If userID is a string, it really should have single-quotes around it. If numeric, OK.

How about this variation...
Expand|Select|Wrap|Line Numbers
  1. sqlStr2 = "INSERT INTO tblBooking (" & _
  2.           "userID, jobID, jobType, " & _
  3.           "startTime, startDate, bookingStatus) " & _
  4.           "Values ('" & rs!userID & "', " _
  5.           & job & "', '" _
  6.           & job & "', '" _
  7.           & startT & "', '" _
  8.           & startD & "', '" _
  9.           & booking & "')"
Keeping mind, of course, that if any fields are defined as Date/Time in Access, they need # delimiters rather than single quotes.
Jan 11 '07 #10

markmcgookin
Expert 100+
P: 648
If userID is a string, it really should have single-quotes around it. If numeric, OK.

How about this variation...
Expand|Select|Wrap|Line Numbers
  1. sqlStr2 = "INSERT INTO tblBooking (" & _
  2.           "userID, jobID, jobType, " & _
  3.           "startTime, startDate, bookingStatus) " & _
  4.           "Values ('" & rs!userID & "', " _
  5.           & job & "', '" _
  6.           & job & "', '" _
  7.           & startT & "', '" _
  8.           & startD & "', '" _
  9.           & booking & "')"
Keeping mind, of course, that if any fields are defined as Date/Time in Access, they need # delimiters rather than single quotes.
Hey, thanks for the help,

Getting a syntax error now, (Missing Operator) in query expression 'Gang', 'Gang' etc...

When I mouse over all the values in sqlStr2 in the debugger, the appropriate values are in there, I just can't see why this is kicking up!

Any ideas?
Jan 11 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
When it goes to Debug mode after crashing :
  1. Note and post the full error message
  2. Debug.Print sqlStr2 then Copy/Paste it into your post.
Jan 11 '07 #12

markmcgookin
Expert 100+
P: 648
When it goes to Debug mode after crashing :
  1. Note and post the full error message
  2. Debug.Print sqlStr2 then Copy/Paste it into your post.

Err-

"Run-time error '3075'

Syntax error (missing operator) in query expression 'Gang', 'Gang', '12.11.43', '12:35', 'Active')'.

This line is hi-lighted in the debugger

DoCmd.RunSQL sqlStr2

and this is sqlStr2:

sqlStr2 = "INSERT INTO tblBooking (" & _
"userID, jobID, jobType, " & _
"startTime, startDate, bookingStatus) " & _
"VALUES ('" & rs!userID & "', " _
& job & "', '" _
& job & "', '" _
& startT & "', '" _
& startD & "', '" _
& booking & "')"

I have updated the db online, incase you want to test and see for yourself, but it's cool if you dont want to download others files! (i didn't quite get whaty you meant when you said debug.print)
Jan 11 '07 #13

markmcgookin
Expert 100+
P: 648
I am also getting the exact error on another button,

Do you think it is my recordset loop? as the SQL seems fine. I think it's just a syntax error, but as I don't know what it should be, I can't seem to fix it.

Same error for a different button, here is the code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnARLogOff_Click()
  3.  
  4. Dim rs As DAO.Recordset
  5. Dim sqlStr As String
  6. Dim sqlStr2 As String
  7. Dim username As String
  8.  
  9. booking = "Inactive"
  10.  
  11. username = Nz(Forms!frmLogin!txtMembername, "")
  12.  
  13. sqlStr = "SELECT bookingStatus" & _
  14.          "FROM tblBooking" & _
  15.          "WHERE userID= '" & username & "'"
  16.  
  17. Set rs = CurrentDb.OpenRecordset(sqlStr)
  18.  
  19. Do While Not rs.EOF
  20.  
  21. sqlStr2 = "UPDATE tblBooking SET bookingStatus ='" & booking & "' "
  22.  
  23. DoCmd.RunSQL sqlStr2
  24. rs.MoveNext
  25.  
  26. Loop
  27. End Sub
  28.  
  29.  
Hehe... ignore this one.... missed out a space DOH!
Jan 11 '07 #14

markmcgookin
Expert 100+
P: 648
Hi folks,

well, I got it! haha, there was a missing ' in the SQL query, it was hard to find because of the staggered code. Here is the finished code for anyone wanting to see!

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Private Sub btnEngBook_Click()
  5.  
  6. Dim gang As String
  7. gang = Me.txtGang.Value
  8. Dim rs As DAO.Recordset
  9. Dim sqlStr As String
  10. Dim startT As String
  11. Dim startD As String
  12. Dim job As String
  13.  
  14. job = "Gang"
  15. Booking = "Active"
  16.  
  17. startD = InputBox("Input a start date dd.mm.yy", "Input Start Date")
  18.  
  19. startT = InputBox("Input a start Time hh.mm", "Input Start Time")
  20.  
  21. sqlStr = "SELECT userID, gangNo " & _
  22.          "FROM tblUser " & _
  23.          "WHERE gangNo='" & gang & "'"
  24.  
  25. Set rs = CurrentDb.OpenRecordset(sqlStr)
  26.  
  27. Do While Not rs.EOF
  28.  
  29. bookingID = DLookup("[numberOfBookings]", "tblBookingNumber", "bookingNumberId = 'dummy'")
  30. bookingID = bookingID + 1
  31.  
  32. sqlStr2 = "INSERT INTO tblBooking (bookingID, " & _
  33.           "userID, jobID, jobType, " & _
  34.           "startTime, startDate, bookingStatus) " & _
  35.           "VALUES ('" & bookingID & "', '" & rs!userID & "', '" _
  36.           & job & "', '" _
  37.           & job & "', '" _
  38.           & startT & "', '" _
  39.           & startD & "', '" _
  40.           & Booking & "')"
  41.  
  42. DoCmd.RunSQL sqlStr2
  43.  
  44. Dim strSQL3 As String
  45. strSQL3 = "UPDATE tblBookingNumber SET numberOfBookings = '" & bookingID & "' WHERE bookingNumberId = 'dummy' "
  46. DoCmd.RunSQL (strSQL3)
  47.  
  48. rs.MoveNext
  49.  
  50. Loop
  51.  
  52. MsgBox "Booking created for Gang"
  53.  
  54. End Sub
  55.  
  56.  
  57.  

I'd really like to thank everyone who helped out, especially NeoPa and mccarthy for putting up with my innane PMs (Against the rules! Sorry!)

Going away and writing a similar function for another button, which was a smaller chunk of code allowed me to find the error there and fix it, so when I came back to the main one I saw what was wrong. Thanks again people!
Jan 11 '07 #15

NeoPa
Expert Mod 15k+
P: 31,186
Looks like you got it in before deadline too Mark. Good going.
Pleased to hear it's all going well now.
Jan 11 '07 #16

Expert 5K+
P: 8,435
...
I'd really like to thank everyone who helped out, especially NeoPa and mccarthy for putting up with my innane PMs (Against the rules! Sorry!)
Ahah! Going behind my back, huh? :D

Seriously, it's good to see you've got the problem solved.

Going away and writing a similar function for another button, which was a smaller chunk of code allowed me to find the error there and fix it, so when I came back to the main one I saw what was wrong. Thanks again people!
Very sound debugging technique. If you can duplicate the problem in a simplified form, it makes it much easier to understand what's going on. Even if you can't, the effort often gives you a different perspective on the problem.
Jan 11 '07 #17

100+
P: 1,646
Looks like you got it in before deadline too Mark. Good going.
Pleased to hear it's all going well now.
Seems to be a very common problem configuring SQL strings and getting the correct syntax for including different variable types. Might be meat for a sticky.
Jan 13 '07 #18

NeoPa
Expert Mod 15k+
P: 31,186
Like (Quotes (') and Double-Quotes (") - Where and When to use them.) you mean?
Yes, this is linked to in the new (Tutorials on Access and VBA) sticky.
More will be added to this in time Will, as and when time can be found.
This is the newest area of the forum which, in time, we expect to be one of its greatest assets.
In this case (this particular problem) people have difficulty even when they know the rules. It seems it's just too much to cope with for many people's brains.
Jan 13 '07 #19

markmcgookin
Expert 100+
P: 648
Yeah a sticky or area for this kind of thing would be very useful to reference with compile errors like that. With areas such as "SQL Insert syntax, Passing values to SQL Code, Passing Date values to SQL etc etc"

It was my own fault because I simply copied and pasted a chunk of code without checking it, and the "& _" staggered lines for ease of posting meant that a quick scan over everything didn't reveal the error.

There is a name for that kind of debugging/testing, I remember reading about it in my trusty VB Bible by David Schnider, but I can't be bothered to read all 400 pages again just to ifnd what it is called. Feel free to PM me with ideas! ^.^

However, another thankyou goes out to everyone who posted, the demo of the project went very very well, and has really increased my confidence with VB for writing my dissertation later this month (A Windows Mobile 5 PDA Application with SQL server CE)
Jan 15 '07 #20

markmcgookin
Expert 100+
P: 648
Ahah! Going behind my back, huh? :D
All's fair in love and Access :D
Jan 15 '07 #21

Post your reply

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