 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2007Run Time Error (again!)
Would someone please tell me what is wrong with this line of coding: CurrentDb.Execute "INSERT INTO tblMovement (InsertDate) " & _ "VALUES (" & txtInsertDate & ")" I am getting a syntex error. The value of the txtInsertDate is =Now() and I am trying to insert that date into a field to create an Insert Date. Is there a better way to accomplish this? Thanks, aj Dates need to be delimited with #, and need to be in mm/dd/yyyy format (or an unambiguous format such as yyyy-mm-dd or dd mmm yyyy) CurrentDb.Execute "INSERT INTO tblMovement (InsertDate) " & _ "VALUES (" & Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & ")"  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Would someone please tell me what is wrong with this line of coding: > CurrentDb.Execute "INSERT INTO tblMovement (InsertDate) " & _ [quoted text clipped - 7 lines] > Thanks, > aj Thank you so much! I have a bunch of questions. I am so new to VBA. If I were to give you my email, would you be willing to listen to my questions and point me in the right direction? > Dates need to be delimited with #, and need to be in mm/dd/yyyy format (or > an unambiguous format such as yyyy-mm-dd or dd mmm yyyy) [quoted text clipped - 14 lines] > > Thanks, > > aj Douglas will likely not do that, nor will most of the regular poster here. If you have additional questions, post them on this site. The reason is others may also benefit from the questions and answers. SignatureDave Hargis, Microsoft Access MVP > Thank you so much! I have a bunch of questions. I am so new to VBA. If I > were to give you my email, would you be willing to listen to my questions and [quoted text clipped - 18 lines] > > > Thanks, > > > aj OK! I just don't want to be a pain with so many questions! I have an insert statement with about 7 fields. How can I do that better? Also, how do I continue coding on the next line without getting an error message. > Douglas will likely not do that, nor will most of the regular poster here. > If you have additional questions, post them on this site. The reason is [quoted text clipped - 22 lines] > > > > Thanks, > > > > aj Post the code for the Insert and we can have a look. If you need to continue a line of code on to the next physical line, you can use the underscore character. This is a good thing, so you don't have to scroll left and right to read your code. Example: If MsgBox("This is a Life or Death Issue, So Choose Carefully", _ vbQuestion + vbYesNo, "Make A Decision") = vbMaybe SignatureDave Hargis, Microsoft Access MVP > OK! I just don't want to be a pain with so many questions! I have an insert > statement with about 7 fields. How can I do that better? Also, how do I [quoted text clipped - 26 lines] > > > > > Thanks, > > > > > aj CurrentDb.Execute "INSERT INTO tblMovement (EmpID, SupervisorID, CurrentSupervisorID, CurrentADID, ADID, InsertDate, EffectiveDate) " & _ "VALUES (" & lstSelectEmp.Column(0) & ", " & cboNewSupervisor.Column(0) & ", " & cboCurrentSupervisor.Column(0) & ", " & cboCurrentSupervisor.Column(2) & ", " & cboNewSupervisor.Column(2) & ", " & Format(txtInsertDate, "\#yyyy\-mm\-dd\#", " & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ")" It works as long as I don't add the last field. Is there a limit to the amount of fields you can insert? Thanks, aj > Post the code for the Insert and we can have a look. If you need to continue > a line of code on to the next physical line, you can use the underscore [quoted text clipped - 35 lines] > > > > > > Thanks, > > > > > > aj Just a minor syntax Error error is here v Format(txtInsertDate, "\#yyyy\-mm\-dd\#", " & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ")" Should be Format(txtInsertDate, "\#yyyy\-mm\-dd\#", & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ")" Don't worry about how many questions you ask here. The more you ask the more you learn. Next thing you know, you will see someone with a question you know the answer to. Go ahead and answer it. SignatureDave Hargis, Microsoft Access MVP > CurrentDb.Execute "INSERT INTO tblMovement (EmpID, SupervisorID, > CurrentSupervisorID, CurrentADID, ADID, InsertDate, EffectiveDate) " & _ [quoted text clipped - 49 lines] > > > > > > > Thanks, > > > > > > > aj Hi Dave, Shouldn't it be Format(txtInsertDate, "\#yyyy\-mm\-dd\#) & "," & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ")" SignatureGood Luck BS"D > Just a minor syntax Error error is here v > Format(txtInsertDate, "\#yyyy\-mm\-dd\#", " & Format(txtEffectiveDate, [quoted text clipped - 62 lines] > > > > > > > > Thanks, > > > > > > > > aj Sorry, missed one closing quote Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & "," & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ")" SignatureGood Luck BS"D > Hi Dave, > [quoted text clipped - 69 lines] > > > > > > > > > Thanks, > > > > > > > > > aj Yes SignatureDave Hargis, Microsoft Access MVP > Sorry, missed one closing quote > [quoted text clipped - 74 lines] > > > > > > > > > > Thanks, > > > > > > > > > > aj What is the better way? > Yes > [quoted text clipped - 76 lines] > > > > > > > > > > > Thanks, > > > > > > > > > > > aj Sorry, I don't understand the question. SignatureDave Hargis, Microsoft Access MVP > What is the better way? > [quoted text clipped - 78 lines] > > > > > > > > > > > > Thanks, > > > > > > > > > > > > aj I asked if there was a better way to accomplish what I was trying to do, and I thought you responded by saying yes. So, I am hoping you will tell me how to do it better. Also, is it possible to have 2 insert statments in one procedure? > Sorry, I don't understand the question. You can have as many insert statments as you want in a procedure. I think my yes answer meant it is the best way to do it. One thing I would advise is that when using the CurrentDb.Execute method is that you always use the dbFailOnError parameter. The Execute method does not go through the Access UI. It goes directly to Jet. This means that if the SQL in the Execute fails, no error will be returned unless you use the dbFailOnError. So, CurrentDb.Excute strSQL, dbFailOnError SignatureDave Hargis, Microsoft Access MVP > I asked if there was a better way to accomplish what I was trying to do, and > I thought you responded by saying yes. So, I am hoping you will tell me how > to do it better. Also, is it possible to have 2 insert statments in one > procedure? > > > Sorry, I don't understand the question. Would someone please tell me what is wrong with this code: CurrentDb.Execute "INSERT INTO tblMovement (EmpID, SupervisorID, CurrentSupervisorID, CurrentADID, ADID, InsertDate, EffectiveDate, User) " & _ "VALUES (" & lstSelectEmp.Column(0) & ", " & cboNewSupervisor.Column(0) & ", " & cboCurrentSupervisor.Column(0) & ", " & cboCurrentSupervisor.Column(2) & ", " & cboNewSupervisor.Column(2) & ", " & Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & _ "," & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & txtUser & ")" Get an error message too few parameters. Thanks, Aj Is User a text field? If so, you need quotes around the value you're inserting. As well, User is a bad choice for a field name: it's a reserved word, and using reserved words for your own purposes can lead to problems. If you cannot (or will not) change the name, at least put square brackets around it. CurrentDb.Execute "INSERT INTO tblMovement (EmpID, SupervisorID, CurrentSupervisorID, CurrentADID, ADID, InsertDate, EffectiveDate, User) " & _ "VALUES (" & lstSelectEmp.Column(0) & ", " & _ cboNewSupervisor.Column(0) & ", " & _ cboCurrentSupervisor.Column(0) & ", " & _ cboCurrentSupervisor.Column(2) & ", " & _ cboNewSupervisor.Column(2) & ", " & _ Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & _ "," & Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & _ ", " & Chr$(34) & txtUser & Chr$(34) & ")" (I'm assuming the ID fields are all numeric. If not, you'll need quotes there too) For a good discussion of what names to avoid, see what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Would someone please tell me what is wrong with this code: > [quoted text clipped - 15 lines] > Thanks, > Aj |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.