Run Time Error (again!)

[Guest]

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

 

[Douglas J. Steele]

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\#") &
")"

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Hi Dave,

Shouldn't it be

Format(txtInsertDate, "\#yyyy\-mm\-dd\#) & "," & Format(txtEffectiveDate,
"\#yyyy\-mm\-dd\#") & ")"

 

[Guest]

Sorry, missed one closing quote

Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & "," & Format(txtEffectiveDate,
"\#yyyy\-mm\-dd\#") & ")"

 

[Guest]

Yes

--
Dave Hargis, Microsoft Access MVP

Sorry, missed one closing quote

Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & "," & Format(txtEffectiveDate,
"\#yyyy\-mm\-dd\#") & ")"

 

[Guest]

What is the better way?

Yes

 

[Guest]

Sorry, I don't understand the question.

 

[Guest]

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?

 

[Guest]

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
--
Dave 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?

 

[Guest]

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

 

[Douglas J. Steele]

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