Insert Code Help

G

Guest

First of all, if this is a repeat, I apologize. I cannot find the original
post. In the code below, everything works except for the V/PTimeAccrued and
V/PTimeUsed. Those 4 fields will not insert into my table. On my form, you
input those values. All other fields are either list/combo boxes and
calculated fields. What is wrong with the code?

"(EmpID, SupervisorID, ADID, InsertDate, EffectiveDate, Logon,
ResignorTerm, Reason, VTimeAccrued, VTimeUsed, PTimeAccrued, PTimeUsed,
TotalOwedEmp, TotalOwedComp) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & lstSelectEmp.Column(2) & _
", " & lstSelectEmp.Column(4) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & ", " & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ", " & _
Chr$(34) & cboResignorTerm & Chr$(34) & ", " & _
Chr$(34) & cboReason & Chr$(34) & ", " & _
Chr$(34) & txtVTimeAccrued & Chr$(34) & ", " & _
Chr$(34) & txtVTimeUsed & Chr$(34) & ", " & _
Chr$(34) & txtPTimeAccrued & Chr$(34) & ", " & _
Chr$(34) & txtPTimeUsed & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) "
 
D

Douglas J. Steele

What's the data type of the two fields? From the names, I'd assume Date. If
that's the case, you need to delimit them with #, not Chr$(34). (If they're
numeric, simply remove the Chr$(34): numeric fields need no delimiter)

If you are using Date fields, make sure you're using them correctly. Date
fields are intended for timestamps: specific date/time points in time
(although using them without time is acceptable). They are not intended to
be used to store durations.
 
H

Hooker DBA [MSFT]

Douglas is a known troll for whom the only answer is MDB, regardless
of the question
 
G

Guest

They are numbers that the user imputs. I have tried to remove the Chr$(34)
and it still didn't work. I am ready to pull my hair out!!
 
D

Douglas J. Steele

Store the result of the concatenations into a variable, and print the
variable's value to the Immediate window using Debug.Print:

strSQL = "(EmpID, SupervisorID, ADID, InsertDate, EffectiveDate, " & _
"Logon, ResignorTerm, Reason, VTimeAccrued, VTimeUsed, " & _
"PTimeAccrued, PTimeUsed, TotalOwedEmp, TotalOwedComp) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & lstSelectEmp.Column(2) & _
", " & lstSelectEmp.Column(4) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & ", " & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ", " & _
Chr$(34) & cboResignorTerm & Chr$(34) & ", " & _
Chr$(34) & cboReason & Chr$(34) & ", " & _
Chr$(34) & txtVTimeAccrued & Chr$(34) & ", " & _
Chr$(34) & txtVTimeUsed & Chr$(34) & ", " & _
Chr$(34) & txtPTimeAccrued & Chr$(34) & ", " & _
Chr$(34) & txtPTimeUsed & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) "
Debug.Print strSQL

Go to the Immediate window (Ctrl-G) and check what's printed there. Does it
look correct?
 
G

Guest

With the exception of the VTimeAccrued, VTimeUsed, PTimeAccrued, PTimeUsed
fields, all the other information is correct. The other fields are just
empty.
 
D

Douglas J. Steele

So let's recap.

1) VTimeAccrued, VTimeUsed, PTimeAccrued and PTimeUsed are numeric fields,
not Date fields.
2) You've tried the following, and it still didn't work:

strSQL = "(EmpID, SupervisorID, ADID, InsertDate, EffectiveDate, " & _
"Logon, ResignorTerm, Reason, VTimeAccrued, VTimeUsed, " & _
"PTimeAccrued, PTimeUsed, TotalOwedEmp, TotalOwedComp) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & lstSelectEmp.Column(2) & _
", " & lstSelectEmp.Column(4) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & ", " & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ", " & _
Chr$(34) & cboResignorTerm & Chr$(34) & ", " & _
Chr$(34) & cboReason & Chr$(34) & ", " & _
txtVTimeAccrued & ", " & _
txtVTimeUsed & ", " & _
txtPTimeAccrued ", " & _
txtPTimeUsed & ", " & _
Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) "

What happens if you copy the (corrected) SQL from the Immediate window,
paste it into a Query and try to run it? Do you get any error message?

Paste the SQL statement that's not working.
 
G

Guest

I copied the code just as you typed it, and it still doesn't work. This is
what the immediate window gives me:

(EmpID, SupervisorID, ADID, InsertDate, EffectiveDate, Logon, ResignorTerm,
Reason, VTimeAccrued, VTimeUsed, PTimeAccrued, PTimeUsed, TotalOwedEmp,
TotalOwedComp) VALUES (523505, 233117, 104427, #2007-06-19#, #2007-06-18#,
"robial1", "Resigned", "Career Change", , , , , "0", "10" )
", , , , , "0", "10" )
 
D

Douglas J. Steele

Are you saying that that extra

", , , , , "0", "10" )

actually appears? Something's wrong if so.

You're sure your 4 text boxes are named txtVTimeAccrued, txtVTimeUsed,
txtPTimeAccrued and txtPTimeUsed and that they have values in them?

If the 4 fields in the table aren't required (so that they can accept Null
values), try

Nz(txtVTimeAccrued, "Null") & ", " & _
Nz(txtVTimeUsed, "Null") & ", " & _
Nz(txtPTimeAccrued, "Null") & ", " & _
Nz(txtPTimeUsed, "Null") & ", " & _
 
G

Guest

Good Morning, sorry for the delay I have been out sick. When I modified the
code as you suggested, I still get an error, and this is what the immediate
window gives me:

(EmpID, SupervisorID, ADID, InsertDate, EffectiveDate, Logon, ResignorTerm,
Reason, VTimeAccrued, VTimeUsed, PTimeAccrued, PTimeUsed, TotalOwedEmp,
TotalOwedComp) VALUES (492598, 474062, 209054, #2007-06-27#, #2007-06-26#,
"robial1", "Resigned", "Attendance/Tardiness", , , , , "0", "10" )

Here is the code:

strSQL = "(EmpID, SupervisorID, ADID, InsertDate, EffectiveDate, " & _
"Logon, ResignorTerm, Reason, VTimeAccrued, VTimeUsed, " & _
"PTimeAccrued, PTimeUsed, TotalOwedEmp, TotalOwedComp) " & _
"VALUES (" & lstSelectEmp.Column(0) & ", " & lstSelectEmp.Column(2) & _
", " & lstSelectEmp.Column(4) & ", " & _
Format(txtInsertDate, "\#yyyy\-mm\-dd\#") & ", " & _
Format(txtEffectiveDate, "\#yyyy\-mm\-dd\#") & ", " & _
Chr$(34) & txtLogon & Chr$(34) & ", " & _
Chr$(34) & cboResignorTerm & Chr$(34) & ", " & _
Chr$(34) & cboReason & Chr$(34) & ", " & _
Nz(txtVTimeAccrued, "Null") & ", " & _
Nz(txtVTimeUsed, "Null") & ", " & _
Nz(txtPTimeAccrued, "Null") & ", " & _
Nz(txtPTimeUsed, "Null") & ", " & _
Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) "


Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError
 
D

Douglas J. Steele

Obviously txtVTimeAccrued, txtVTimeUsed, txtPTimeAccrued and txtPTimeUsed
contain zero-length strings rather than Null. (If they're text variables,
than they cannot contain Null)

Change

Nz(txtVTimeAccrued, "Null") & ", " & _
Nz(txtVTimeUsed, "Null") & ", " & _
Nz(txtPTimeAccrued, "Null") & ", " & _
Nz(txtPTimeUsed, "Null") & ", " & _

to

IIf(Len(Trim(txtVTimeAccrued & vbNullString)) > 0, txtVTimeAccrued,
"Null") & ", " & _
IIf(Len(Trim(txtVTimeUsed & vbNullString)) > 0, txtVTimeUsed, "Null") &
", " & _
IIf(Len(Trim(txtPTimeAccrued & vbNullString)) >0, txtPTimeAccrued,
"Null") & ", " & _
IIf(Len(Trim(txtPTimeUsed & vbNullString)) > 0, txtPTimeUsed, "Null") &
", " & _

If they are text variables, that can be simplified to

IIf(Len(Trim(txtVTimeAccrued)) > 0, txtVTimeAccrued, "Null") & ", " & _
IIf(Len(Trim(txtVTimeUsed)) > 0, txtVTimeUsed, "Null") & ", " & _
IIf(Len(Trim(txtPTimeAccrued)) >0, txtPTimeAccrued, "Null") & ", " & _
IIf(Len(Trim(txtPTimeUsed) ) > 0, txtPTimeUsed, "Null") & ", " & _


Also, unless TotalOwedEmp and TotalOwedComp are text fields, change

Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _
Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) "

to

txtTotalOwedEmp & ", " & _
txtTotalOwedComp & " ) "
 
G

Guest

Here is what the immediate window gave me:

INSERT INTO tblSeparation (EmpID, SupervisorID, ADID, InsertDate,
EffectiveDate, Logon, ResignorTerm, VTimeAccrued, VTimeUsed, PTimeAccrued,
PTimeUsed, TotalOwedEmp, TotalOwedComp) VALUES (209678, 495296, 123918,
#2007-06-27#, #2007-06-26#, "robial1", "Resigned", Null, Null, Null, Null,
10, 0 )

But, there are values in those fields. I have also set the default value to
0, so there should always be at least a value of 0 in all of those fields.
 
D

Douglas J. Steele

If there are values, Access isn't seeing them in the variables. If they're
supposed to be text boxes on the form, try putting Me! or Me. in front of
them.
 
G

Guest

Thank you for all of your help!! I have just one more question. What code
would I add after this insert statement to clear the form?

Thanks,
 
G

Guest

Good Morning,
When you state to do the Me.Undo twice, I just inserted the below into my
code:
Me.Undo
Me.Undo

If that was the right thing to do, then it didn't work. If it were the
wrong thing, would you please correct me?

Thanks,
AJ
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code Error 8
Still battling with Reminder Pop-up Any help?? 4
Still batteling with stLinkCriterea HELP!! 2

Top