 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2007Insert Code Help
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) & " ) " 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.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > First of all, if this is a repeat, I apologize. I cannot find the > original [quoted text clipped - 21 lines] > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " Douglas is a known troll for whom the only answer is MDB, regardless of the question On Jun 18, 11:22 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > 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 [quoted text clipped - 36 lines] > > - Show quoted text - 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!! > 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 [quoted text clipped - 30 lines] > > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ > > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " 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? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > They are numbers that the user imputs. I have tried to remove the > Chr$(34) [quoted text clipped - 40 lines] >> > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ >> > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " With the exception of the VTimeAccrued, VTimeUsed, PTimeAccrued, PTimeUsed fields, all the other information is correct. The other fields are just empty. > Store the result of the concatenations into a variable, and print the > variable's value to the Immediate window using Debug.Print: [quoted text clipped - 64 lines] > >> > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ > >> > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " 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. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > With the exception of the VTimeAccrued, VTimeUsed, PTimeAccrued, PTimeUsed > fields, all the other information is correct. The other fields are just [quoted text clipped - 75 lines] >> >> > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ >> >> > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " 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" ) > So let's recap. > [quoted text clipped - 103 lines] > >> >> > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ > >> >> > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " 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") & ", " & _ SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I copied the code just as you typed it, and it still doesn't work. This is > what the immediate window gives me: [quoted text clipped - 124 lines] >> >> >> > Chr$(34) & txtTotalOwedEmp & Chr$(34) & ", " & _ >> >> >> > Chr$(34) & txtTotalOwedComp & Chr$(34) & " ) " 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 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 & " ) " SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Good Morning, sorry for the delay I have been out sick. When I modified > the [quoted text clipped - 30 lines] > > CurrentDb.Execute strSQL, dbFailOnError 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. > Obviously txtVTimeAccrued, txtVTimeUsed, txtPTimeAccrued and txtPTimeUsed > contain zero-length strings rather than Null. (If they're text variables, [quoted text clipped - 69 lines] > > > > CurrentDb.Execute strSQL, dbFailOnError 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. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Here is what the immediate window gave me: > [quoted text clipped - 88 lines] >> > >> > CurrentDb.Execute strSQL, dbFailOnError 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, > 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 [quoted text clipped - 92 lines] > >> > > >> > CurrentDb.Execute strSQL, dbFailOnError Try Me.Undo twice. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Thank you for all of your help!! I have just one more question. What > code [quoted text clipped - 111 lines] >> >> > >> >> > CurrentDb.Execute strSQL, dbFailOnError 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 > Try > [quoted text clipped - 117 lines] > >> >> > > >> >> > CurrentDb.Execute strSQL, dbFailOnError |
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.