473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

WHERE clause in an INSERT in VBA/Access

markmcgookin
648 Expert 512MB
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
20 11405
markmcgookin
648 Expert 512MB
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
648 Expert 512MB
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
8,834 Expert 8TB
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
648 Expert 512MB
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
32,556 Expert Mod 16PB
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
648 Expert 512MB
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
648 Expert 512MB
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
648 Expert 512MB
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
Killer42
8,435 Expert 8TB
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
648 Expert 512MB
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
32,556 Expert Mod 16PB
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
648 Expert 512MB
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
648 Expert 512MB
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
648 Expert 512MB
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
32,556 Expert Mod 16PB
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
Killer42
8,435 Expert 8TB
...
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
willakawill
1,646 1GB
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
32,556 Expert Mod 16PB
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
648 Expert 512MB
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
648 Expert 512MB
Ahah! Going behind my back, huh? :D
All's fair in love and Access :D
Jan 15 '07 #21

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

Similar topics

2
by: Chris | last post by:
I have tableA, defined as: field1 varchar2(10), field2 varchar2(10), field3 varchar2(10) I have host variables defined as: v1 pic x(10) varying v2 pic x(10) varying
1
by: t123 | last post by:
I have a select count() that seams be ignoring one clause. Data: Table: ID CA-Char CB-Int 1 dsfsd 6 2 dsfsd 0 3 dsfsd 1 4 sdrtt 1 SQL is:
5
by: Rachel Weeden | last post by:
I'm working on an ASP Web application, and am having syntax issues in a WHERE statement I'm trying to write that uses the CInt Function on a field. Basically, I want to select records using...
4
by: Barry Edmund Wright | last post by:
I would really appreciate your assistance. I am using Access 2000 to create a form that Lists Names and Addresses based on a number of selection criteria one of which is a combo box cboPCZip. All...
7
by: Swinky | last post by:
Mr. Browne's copy code on his web site has saved me. I have been struggling to copy a record with several related sub-form tables. I found code on his web site that copies a sub-form table,...
2
by: rdemyan via AccessMonster.com | last post by:
I want to verify that the following SQL insert statement is correct: sSQL = "INSERT INTO (FIELD1, FIELD2, FIELD3, FIELD4, FIELD5)" & _ " SELECT A.FIELD1, A.FIELD2, A.FIELD3, A.FIELD4, A.FIELD5"...
0
by: webgirl | last post by:
I'm relatively new to SQL Server & looking for some guidance, if possible. I've been reading lots of different things & am a bit confused about some basics. I have an Access Project with SQL...
3
by: =?Utf-8?B?cGlja2VkYW5hbWU=?= | last post by:
I am so frustrated, as is USUAL using Visual Studio. NONE, NOOOOONE of my aspx files have associated designer.vb files available (And yes, they all have the .vb code behind files). All of my aspx...
2
by: vabh | last post by:
I am using access connection like- a = TextBox2.Text.Remove(1) a = TextBox2.Text.Remove(1) c = TextBox3.Text.Remove(1) b = TextBox4.Text TextBox5.Text = a + c + "...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.