Run Time Error (again!)

Yes

--
Dave Hargis, Microsoft Access MVP


"Ofer Cohen" wrote:

Sorry, missed one closing quote

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


--
Good Luck
BS"D


"Ofer Cohen" wrote:

Hi Dave,

Shouldn't it be

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


--
Good Luck
BS"D


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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

"Klatuu" wrote:

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
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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.

"Klatuu" wrote:

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.

--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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?

"Douglas J. Steele" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ajhome" wrote in message
...
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

What is the better way?

"Klatuu" wrote:

Yes

--
Dave Hargis, Microsoft Access MVP


"Ofer Cohen" wrote:

Sorry, missed one closing quote

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


--
Good Luck
BS"D


"Ofer Cohen" wrote:

Hi Dave,

Shouldn't it be

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


--
Good Luck
BS"D


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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

"Klatuu" wrote:

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
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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.

"Klatuu" wrote:

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.

--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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?

"Douglas J. Steele" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ajhome" wrote in message
...
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

Sorry, I don't understand the question.
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

What is the better way?

"Klatuu" wrote:

Yes

--
Dave Hargis, Microsoft Access MVP


"Ofer Cohen" wrote:

Sorry, missed one closing quote

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


--
Good Luck
BS"D


"Ofer Cohen" wrote:

Hi Dave,

Shouldn't it be

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


--
Good Luck
BS"D


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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

"Klatuu" wrote:

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
--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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.

"Klatuu" wrote:

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.

--
Dave Hargis, Microsoft Access MVP


"ajhome" wrote:

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?

"Douglas J. Steele" wrote:

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ajhome" wrote in message
...
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

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?

"Klatuu" wrote:

Sorry, I don't understand the question.
--
Dave Hargis, Microsoft Access MVP

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


"ajhome" wrote:

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?

"Klatuu" wrote:

Sorry, I don't understand the question.
--
Dave Hargis, Microsoft Access MVP

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/Ap****ueBadWord.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"ajhome" wrote in message
...
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