Need VBA Help to Append!

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
G

GoBrowns!

Hi;

I am trying to create a "submit" button that will append the form fields to
a table. The user will click "submit", get a message box to make sure they
REALLY want to submit, then if they click yes the data will be submitted.

I am getting syntax errors in my code, and I can't figure out what is wrong.
I wrote the code two different ways.....

Here it is.....

This part seems to work....

Private Sub cmdSubmit_Click()
'Asks user one more time if they are sure they want to submit,
'then appends form data to table and clears form.

Dim strLastChance As String
Dim strAppend As String
Dim strSQL As String

strLastChance = MsgBox( _
Prompt:="Are you sure you want to submit record?", _
Buttons:=vbYesNo + vbExclamation, _
Title:="Confirm Submission Decision")

If vbYes Then

Here is Option #1 for after my "if" line...

strSQL = "INSERT INTO tblAttendanceRecords(EmployeeID, EmployeeName, Date," _
VacationTimeUsed, SickTimeUsed, ProperDocumentationFiled, Tardy,
TardyTime, MissedPunch, _
Occurrence, OccurrenceDescription, OccurrenceValue,
NonOccurrenceDescription, _

AdditionalComments)VALUES("&me.txtEmployeeID&","&me.cboEmployeeName&", _
"&me.datDate&","&me.intVacationUsed&","&me.intSickUsed&", _
"&me.ckProperDoc&","&me.ckTardy&","&me.cboTimeTardy&", _
"&me.ckMissedPunch&","&me.ckOccurrence&, _
"&me.cboTypeOccurrence&","&me.txtOccurrenceValue&", _
"&me.cboNonOccurrence&","&me.txtAdditionalComments&")";

DBEngine(0)(0).Execute strSQL


And Option #2.....

strAppend = "INSERT INTO tblAttendanceRecords(txtEmployeeID as
EmployeeID," _
cboEmployeeName as EmployeeName, _
datDate as Date, _
intVacationUsed as VacationTimeUsed, _
intSickUsed as SickTimeUsed, _
ckProperDoc as ProperDocumentationFiled, _
ckTardy as Tardy, _
cboTimeTardy as TardyTime, _
ckMissedPunch as MissedPunch, _
ckOccurrence as OccurrenceDescription, _
cboTypeOccurrence as Occurrence, _
txtOccurrenceValue as OccurrenceValue, _
cboNonOccurrence as NonOccurrenceDescription, _
txtAdditionalComments as AdditionalComments)";

DoCmd.RunSQL strAppend

Will either of these options work??? If so, how do I fix one of them to
append the info???

So stumped... thanks for the help!!!!!!!
 
Option 1. I would use a different syntax. I don't recognize what you are
doing, it may be just fine. I think you need some single quotes and hash
marks to delimit your string and date fields. I assume (for this example)
that
textmployeeId is numeric, = 454678
txtemployeename is string = 'John Smith'
datDate is Date type = #05/05/2009#

also be sure to put a space after you commas.

Your Code---------------------
VALUES("&me.txtEmployeeID&","&me.cboEmployeeName&", _
"&me.datDate&","&me.intVacationUsed&","&me.intSickUsed&", _
"&me.ckProperDoc&","&me.ckTardy&","&me.cboTimeTardy&", _
"&me.ckMissedPunch&","&me.ckOccurrence&, _
"&me.cboTypeOccurrence&","&me.txtOccurrenceValue&", _
"&me.cboNonOccurrence&","&me.txtAdditionalComments&")";
Click to expand...
-----------------------------------------------

My suggestions

"Select " & me.txtemployeeid & ", '" & me.cboEmployeeName & "', #" &
me.datDate & "#, " etc etc


Notice single quotes around the employee name and Hash marks around the
date, no delimiters around employee Id (a numeric field)

One thing to do once you have written the code and get an error. Go the the
immediate mode in your VBA editor
and print strSQL then check to see that all of the values you entered are
set in the proper delimiters and the commas and spaces are all right

In this example = Select 454678, 'john smith', #05/05/2009#,
 
Tom;

I tried that, and I am still getting errors..... Here is my new code:

strSQL = "INSERT INTO tblAttendanceRecords(EmployeeID, EmployeeName,
Date," _
VacationTimeUsed, SickTimeUsed, ProperDocumentationFiled, Tardy,
TardyTime, MissedPunch, _
Occurrence, OccurrenceDescription, OccurrenceValue,
NonOccurrenceDescription, _
AdditionalComments)VALUES(" &me.txtEmployeeID& ",'"
&me.cboEmployeeName& "' , _
#" &me.datDate& "#," &me.intVacationUsed& "," &me.intSickUsed& "
, _
" &me.ckProperDoc& "," &me.ckTardy& "," &me.cboTimeTardy& " , _
" &me.ckMissedPunch& "," &me.ckOccurrence& " , _
'" &me.cboTypeOccurrence& "'," &me.txtOccurrenceValue& " , _
'" &me.cboNonOccurrence& "','" &me.txtAdditionalComments& "')";

DBEngine(0)(0).Execute strSQL

I keep getting a message telling me that it is expecting the end of the
statement... and in the VBA window it highlights "VacationTimeUsed."

I am not sure how to use a SELECT query instead... would I just change
INSERT INTO to SELECT? I tried that, and it didn't work. Would I need to
change VALUES to something too?

Also, what is the syntax for check boxes? Anything with "ck" at the
beginning is a check box.

THANKS!!!!!!!!!!!!!!!
 
You seem to be using the continuation lines incorrectly.

strSQL = "INSERT INTO tblAttendanceRecords(EmployeeID, EmployeeName, Date,"
_
& "VacationTimeUsed, SickTimeUsed, ProperDocumentationFiled, Tardy,
TardyTime, MissedPunch, " _
& "Occurrence, OccurrenceDescription, OccurrenceValue,
NonOccurrenceDescription, " _

Each line must end with closing quotes (you don't do that in any of the
lines except the first). the continuation line starts with ampersand
followed by opening quotes (you don't use opening quotes on any line but the
first), Also use more spaces. I usually put a space before the
Underline, before and after any ampersand and after the commas.

Once you get an error. Choose debug, go to the immediate mode and print
your strSQL. Examine it to see if it says what you expect it to say

Copy and past that into a message here. Let's look at what the SQL
statement actually says.
 
I really have no idea if what I am doing is what you asked me to do, but....

I added the quotations, spaces, and changed VALUE to SELECT. I am still
getting syntax errors. Here is my actual code:

If vbYes Then
strSQL = "INSERT INTO tblAttendanceRecords(EmployeeID, EmployeeName,
Date," _
& "VacationTimeUsed, SickTimeUsed, ProperDocumentationFiled,
Tardy, TardyTime, MissedPunch, " _
& "Occurrence, OccurrenceDescription, OccurrenceValue,
NonOccurrenceDescription, " _
& "AdditionalComments)SELECT(" &me.txtEmployeeID& ",'"
&me.cboEmployeeName& "', " _
#" &me.datDate& "# , " &me.intVacationUsed& " , "
&me.intSickUsed& " , " _
" &me.ckProperDoc& " , " &me.ckTardy& " , " &me.cboTimeTardy& "
, " _
" &me.ckMissedPunch& "," &me.ckOccurrence& " , " _
'" &me.cboTypeOccurrence& "'," &me.txtOccurrenceValue& " , " _
'" &me.cboNonOccurrence& "','" &me.txtAdditionalComments& "')";

DBEngine(0)(0).Execute strSQL


I opened the immediate window, put in "Print.strSQL", and hit enter. Access
then gives me a fatal error and shuts down.

Sorry for getting myself all confused... I just can't figure out the syntax
errors are with this code or why it won't work.

Thanks!!!!!!!!!!!!!!
 
Without seeing the sql that is generated it is hard to see what is going
wrong
WHERE do you get the error? If it is in any of the lines you have included
here then your process of building the strSQL is still faulty. Look at the
part that is highlighted to try to see where it is going wrong.

first thoughts on the code you sent me USE MORE SPACES
tblAttendanceRecords (EmployeeID (space before parentheses)

"additional comments) SELECT " (space before and after select--no
parentheses before after this section)

&me.txtEmployeeID& ",'" (space after the comma)

space before and after ALL of the ampersands

txtAdditionalComments& "')";

no semicolon at the end (or put it inside the quotation marks)

EVERY continuation line MUST begin with an AMPERSAND (many of them after
select start with opening Quotes or single quotes or # ALL wrong).

You don't seem to understand how to concatenate a long string. practice
this elsewhere with a simplier example.

In a practice form (a simple form with one button that has code behind it
three textboxes one string, one numeric and one date) you should then write
the code to make strSQL using those textboxes to insert those three text
boxes into three fields in your table. don't execute the SQL just practice
building the strSQL!

Create your strsql and then put the line "debug.print strSQL" which will
print the string in the immediate window after you have built your string.
write your code to create the string. Run the code. Check the results in
the immediate window.
Do this until you can build strings that come out the way you want with
delimiters etc.

Then go back to your form. replace the execute command with a debug.print.
Fix your code until the strSQL is correct, then put the execute command back


I won't read this mail again until Monday. Have a good weekend and good
luck

Go Packers!!
 
I am so, so lost and FRUSTRATED!!

I have been over this with a fine tooth comb, trying to figure out what the
syntax error may be. I employed all the suggestions that you listed, and I am
still getting syntax errors. I cannot even get to a point where I can use the
immediate window to get the SQL.

Here is the code as it stands:

If strLastChance = vbYes Then
strSQL = "INSERT INTO tblAttendanceRecords ( EmployeeID,
EmployeeName, Date, " _
& " VacationTimeUsed, SickTimeUsed, ProperDocumentationFiled,
Tardy, TardyTime, MissedPunch, " , _
& " Occurrence, OccurrenceDescription, OccurrenceValue,
NonOccurrenceDescription, " , _
& " AdditionalComments ) " SELECT " &me.txtEmployeeID& " , '"
&me.cboEmployeeName& "' , _
& #" & me.datDate & "# , " & me.intVacationUsed & " , " &
me.intSickUsed & " , _
& " & me.ckProperDoc & " , " & me.ckTardy & " , " &
me.cboTimeTardy & " , _
& " & me.ckMissedPunch & " , " & me.ckOccurrence & " , _
& '" & me.cboTypeOccurrence & "' , " & me.txtOccurrenceValue & "
, _
& '" & me.cboNonOccurrence & "' , '" & me.txtAdditionalComments
& "'

Again, thanks so much for your help (and patience!). Everything I know about
Access I had to learn on my own, so my knowledge is patchy at best. Thanks so
much for taking the time to help me get this working properly.
 
GoBrowns! said:
EmployeeName, Date, " _
Click to expand...

Date is a reserved word. For now trying inserting square brackets
around the above Date although that might not work. Or put in
tblAttendanceRecords.Date.

However I would rename that field as soon as possible which will
require finding all the occurrences of that field. See the Scan and
replace utilities section at the Microsoft Access third party
utilities, products, tools, modules, etc. page at my website.
http://www.granite.ab.ca/access/thirdparty.htm

Tony
 
"I cannot even get to a point where I can use the
immediate window to get the SQL."
Click to expand...

I see multiple problems with concatenation and use of delimiters in your
strSQL=

incorrect end of line
" , _
& "'
"' , _

Incorrect beginning of line
& #" & me.
& '" & me.cbo
& " & me.

You need to make a simplier example and get used to the techniques, or you
will just get frustrated trying to fix what this big string. See my
previous post.

Tom
 
Great news!

I backtracked and decided to do this as an Append query, then link it to the
Form using VBA. Here is my VBA code:

Private Sub cmdSubmit_Click()
'Asks user one more time if they are sure they want to submit,
'then appends form data to table and clears form.

Dim strLastChance As String
Dim strAppend As String
Dim strSQL As String

strLastChance = MsgBox( _
Prompt:="Are you sure you want to submit record?", _
Buttons:=vbYesNo + vbExclamation, _
Title:="Confirm Submission Decision")

If strLastChance = vbYes Then
DoCmd.OpenQuery ("qryAppendNewRecords")
End If
End Sub

Here is my SQL from the Append query:

INSERT INTO tblAttendanceRecords ( EmployeeID, EmployeeName, EventDate,
VacationTimeUsed, SickTimeUsed, ProperDocumentationFiled, Tardy, TardyTime,
MissedPunch, Occurrence, OccurrenceDescrption, OccurrenceValue,
NonOccurenceDescription, AdditionalComments )
SELECT Forms!frmAttendenceTrack!txtEmployeeID AS EmployeeID,
Forms!frmAttendenceTrack!cboEmployeeName AS EmployeeName,
Forms!frmAttendenceTrack!datDate AS EventDate,
Forms!frmAttendenceTrack!intVacationUsed AS VacationUsed,
Forms!frmAttendenceTrack!intSickUsed AS SickUsed,
Forms!frmAttendenceTrack!ckProperDoc AS ProperDoc,
Forms!frmAttendenceTrack!ckTardy AS Tardy,
Forms!frmAttendenceTrack!cboTimeTardy AS TimeTardy,
Forms!frmAttendenceTrack!ckMissedPunch AS MissedPunch,
Forms!frmAttendenceTrack!ckOccurence AS Occurrence,
Forms!frmAttendenceTrack!cboTypeOccurence AS TypeOccurrence,
Forms!frmAttendenceTrack!txtOccurenceValue AS OccurrenceValue,
Forms!frmAttendenceTrack!cboNonOccurence AS NonOccurrence,
Forms!frmAttendenceTrack!txtAdditionalComments AS AdditionalComments;

Here is my question. When I append, the information that is placed into
"EmployeeName" is wrong. It shows the Employee ID, not the name. This
information comes from a combo box in my form. This combo box is kind of
tricky, because in its original form is comes across as "EmployeeID
EmployeeName". I tweaked the formatting of the combo box so that it only
shows the EmployeeName. I am not sure if that is why the Append query is
giving me the wrong info, I am not sure.

Any ideas on how to get the Append query to give me the correct information
for EmployeeName?

THANKS AGAIN FOR ALL YOUR HELP!!!!! I'm almost there!!!!!!!!!!
 
Additionally...

What would a code to clear out the fields in the form look like? Is that
even possible?
 
you have probably tweeked the combo box to show the name while it is
actually storing the ID. I guess that the ID is in column(0) and the name
is in column(1) of the rowsource query underling your combo box. If that is
the case then try

Forms!frmAttendenceTrack!cboEmployeeName.column(1) AS EmployeeName,
 
I changed it and I am getting errors that the variable is not defined?

When I place it in the SQL as ![Column(1)] I dont get an error, but the
field is blank after appending.
 
Back
Top