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) -
Dim username As String
-
Dim jID As String
-
Dim Tvalue As String
-
Dim LDate As String
-
Dim jobActive As String
-
-
jID = "GangJob"
-
gang_no = Me.txtGang
-
Tvalue = Time
-
LDate = Date
-
jobActive = "Active"
-
-
Dim strSQL As String
-
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],'" & jID & "',[Start Time],[Start Date],[jobActive])WHERE gangNo = '" & gang_no & "' "
-
DoCmd.RunSQL (strSQL)
-
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
20 11405
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.
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
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) -
Dim username As String
-
Dim jID As String
-
Dim Tvalue As String
-
Dim LDate As String
-
Dim jobActive As String
-
-
jID = "GangJob"
-
gang_no = Me.txtGang
-
Tvalue = Time
-
LDate = Date
-
jobActive = "Active"
-
-
Dim strSQL As String
-
strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],'" & jID & "',[Start Time],[Start Date],[jobActive])WHERE gangNo = '" & gang_no & "' "
-
DoCmd.RunSQL (strSQL)
-
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: - strSQL = "INSERT INTO tblBooking(userID, jobID, startTime, startDate, bookingStatus) Values([username],'" & jID & "',[Start Time],[Start Date],[jobActive])WHERE gangNo = " & gang_no
-
DoCmd.RunSQL (strSQL)
Hey,
so glad to get someone helping, I feel I am VERY clise now :D
My code is different now, it reads -
Private Sub btnEngBook_Click()
-
-
Dim gang As String
-
gang = Me.txtGang.Value
-
Dim rs As DAO.Recordset
-
Dim sqlStr As String
-
Dim startT As String
-
Dim startD As String
-
Dim job As String
-
-
job = "Gang"
-
booking = "Active"
-
-
startD = InputBox("Input a start date dd.mm.yy", "Input Start Date")
-
-
startT = InputBox("Input a start Time hh.mm", "Input Start Time")
-
-
sqlStr = "SELECT tblUser.userID, tblUser.gangNo FROM tblUser WHERE gangNo=" & gang
-
-
Set rs = CurrentDb.OpenRecordset(sqlStr)
-
-
Do While Not rs.EOF
-
-
sqlStr2 = "INSERT INTO tblBooking(userID, jobID, jobType, startTime, startDate,bookingStatus) Values (str(&rs!userID)&)&, job, job, startT, startD, booking)"
-
-
rs.MoveNext
-
Loop
-
-
End Sub
-
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)
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. - Private Sub btnEngBook_Click()
-
-
Dim gang As String
-
gang = Me.txtGang.Value
-
Dim rs As DAO.Recordset
-
Dim sqlStr As String
-
Dim startT As String
-
Dim startD As String
-
Dim job As String
-
-
job = "Gang"
-
booking = "Active"
-
-
startD = InputBox("Input a start date dd.mm.yy", "Input Start Date")
-
-
startT = InputBox("Input a start Time hh.mm", "Input Start Time")
-
-
sqlStr = "SELECT userID, gangNo " & _
-
"FROM tblUser " & _
-
"WHERE gangNo='" & gang & "'"
-
-
Set rs = CurrentDb.OpenRecordset(sqlStr)
-
-
Do While Not rs.EOF
-
-
sqlStr2 = "INSERT INTO tblBooking(" & _
-
"userID, jobID, jobType, " & _
-
"startTime, startDate,bookingStatus) " & _
-
"Values (str(" & rs!userID & "), job, job, startT, startD, booking)"
-
DoCmd.RunSQL sqlStr2
-
rs.MoveNext
-
Loop
-
-
End Sub
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!
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
NB ** It is uploaded now, frmForGangBook :D
I have changed this statement to read - sqlStr2 = "INSERT INTO tblBooking(" & _
-
"userID, jobID, jobType, " & _
-
"startTime, startDate, bookingStatus) " & _
-
"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
I have changed this statement to read - sqlStr2 = "INSERT INTO tblBooking(" & _
-
"userID, jobID, jobType, " & _
-
"startTime, startDate, bookingStatus) " & _
-
"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... -
sqlStr2 = "INSERT INTO tblBooking (" & _
-
"userID, jobID, jobType, " & _
-
"startTime, startDate, bookingStatus) " & _
-
"Values ('" & rs!userID & "', " _
-
& job & "', '" _
-
& job & "', '" _
-
& startT & "', '" _
-
& startD & "', '" _
-
& booking & "')"
Keeping mind, of course, that if any fields are defined as Date/Time in Access, they need # delimiters rather than single quotes.
If userID is a string, it really should have single-quotes around it. If numeric, OK.
How about this variation... -
sqlStr2 = "INSERT INTO tblBooking (" & _
-
"userID, jobID, jobType, " & _
-
"startTime, startDate, bookingStatus) " & _
-
"Values ('" & rs!userID & "', " _
-
& job & "', '" _
-
& job & "', '" _
-
& startT & "', '" _
-
& startD & "', '" _
-
& 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?
NeoPa 32,556
Expert Mod 16PB
When it goes to Debug mode after crashing : - Note and post the full error message
- Debug.Print sqlStr2 then Copy/Paste it into your post.
When it goes to Debug mode after crashing :- Note and post the full error message
- 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)
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: -
-
Private Sub btnARLogOff_Click()
-
-
Dim rs As DAO.Recordset
-
Dim sqlStr As String
-
Dim sqlStr2 As String
-
Dim username As String
-
-
booking = "Inactive"
-
-
username = Nz(Forms!frmLogin!txtMembername, "")
-
-
sqlStr = "SELECT bookingStatus" & _
-
"FROM tblBooking" & _
-
"WHERE userID= '" & username & "'"
-
-
Set rs = CurrentDb.OpenRecordset(sqlStr)
-
-
Do While Not rs.EOF
-
-
sqlStr2 = "UPDATE tblBooking SET bookingStatus ='" & booking & "' "
-
-
DoCmd.RunSQL sqlStr2
-
rs.MoveNext
-
-
Loop
-
End Sub
-
-
Hehe... ignore this one.... missed out a space DOH!
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! -
-
Option Compare Database
-
-
Private Sub btnEngBook_Click()
-
-
Dim gang As String
-
gang = Me.txtGang.Value
-
Dim rs As DAO.Recordset
-
Dim sqlStr As String
-
Dim startT As String
-
Dim startD As String
-
Dim job As String
-
-
job = "Gang"
-
Booking = "Active"
-
-
startD = InputBox("Input a start date dd.mm.yy", "Input Start Date")
-
-
startT = InputBox("Input a start Time hh.mm", "Input Start Time")
-
-
sqlStr = "SELECT userID, gangNo " & _
-
"FROM tblUser " & _
-
"WHERE gangNo='" & gang & "'"
-
-
Set rs = CurrentDb.OpenRecordset(sqlStr)
-
-
Do While Not rs.EOF
-
-
bookingID = DLookup("[numberOfBookings]", "tblBookingNumber", "bookingNumberId = 'dummy'")
-
bookingID = bookingID + 1
-
-
sqlStr2 = "INSERT INTO tblBooking (bookingID, " & _
-
"userID, jobID, jobType, " & _
-
"startTime, startDate, bookingStatus) " & _
-
"VALUES ('" & bookingID & "', '" & rs!userID & "', '" _
-
& job & "', '" _
-
& job & "', '" _
-
& startT & "', '" _
-
& startD & "', '" _
-
& Booking & "')"
-
-
DoCmd.RunSQL sqlStr2
-
-
Dim strSQL3 As String
-
strSQL3 = "UPDATE tblBookingNumber SET numberOfBookings = '" & bookingID & "' WHERE bookingNumberId = 'dummy' "
-
DoCmd.RunSQL (strSQL3)
-
-
rs.MoveNext
-
-
Loop
-
-
MsgBox "Booking created for Gang"
-
-
End Sub
-
-
-
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!
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.
...
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.
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.
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.
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)
Ahah! Going behind my back, huh? :D
All's fair in love and Access :D
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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:
|
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...
|
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...
|
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,...
|
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"...
|
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...
|
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...
|
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 + "...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
| |