By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Copy Command using Cascading Combo Boxes in a Subform

P: 1
I am building a database to track attendance. I have one main form with multiple subforms. Within one of the subforms name sfrDailyAttendance, I also have cascading combo boxes (cboCategory and cboException) to filter difference absences (ie.Regular, Overtime, etc). cboCategory is filtered as follows:

[Forms]![frmMain]![sfrDailyAttendance].[Form].[Category].

Because I have the subform set up as a continuous form, I needed to add a text box (txtException) on top of one of the combo boxes (cboException), so that when a new Category was selected, it did not reset previous Exceptions selected. tblDailyAttendance does not have a field called txtException, which I thought was the reason, however, when I added it in the table, I still received errors.

The copy button is used to increment the date if it a consecutive absence for the same reason, and have been given advice to add the following code:

strSQL = "INSERT INTO tblDailyAttendance (StartTime, EndTime, Category, Exception, " & _
"Reason, ExceptionHrs) "
strSQL = strSQL & "VALUES(#" & Me.Date + 1
strSQL = strSQL & "#,#" & Me.StartTime & "#,#" & Me.EndTime & "#," & Me.Category & _
"," & Me.Exception & "," & Me.Reason & "," & Me.ExceptionHrs & ", " & Me.Description & ");"
CurrentDb.Execute strSQL

DoCmd.RunCommand acCmdRecordsGoToLast

Everything seems to be populating correctly when I step through the code, however, I get "Syntax error (missing operator) in query expression 'Paid OT'" Paid OT is a value populated in the cboException, which also appears in txtException. I'm thinking that I need to add a WHERE clause, but not sure

Any additional assistance would be great. Thanks
Aug 17 '07 #1
Share this Question
Share on Google+
2 Replies


JConsulting
Expert 100+
P: 603
I am building a database to track attendance. I have one main form with multiple subforms. Within one of the subforms name sfrDailyAttendance, I also have cascading combo boxes (cboCategory and cboException) to filter difference absences (ie.Regular, Overtime, etc). cboCategory is filtered as follows:

[Forms]![frmMain]![sfrDailyAttendance].[Form].[Category].

Because I have the subform set up as a continuous form, I needed to add a text box (txtException) on top of one of the combo boxes (cboException), so that when a new Category was selected, it did not reset previous Exceptions selected. tblDailyAttendance does not have a field called txtException, which I thought was the reason, however, when I added it in the table, I still received errors.

The copy button is used to increment the date if it a consecutive absence for the same reason, and have been given advice to add the following code:

strSQL = "INSERT INTO tblDailyAttendance (StartTime, EndTime, Category, Exception, " & _
"Reason, ExceptionHrs) "
strSQL = strSQL & "VALUES(#" & Me.Date + 1
strSQL = strSQL & "#,#" & Me.StartTime & "#,#" & Me.EndTime & "#," & Me.Category & _
"," & Me.Exception & "," & Me.Reason & "," & Me.ExceptionHrs & ", " & Me.Description & ");"
CurrentDb.Execute strSQL

DoCmd.RunCommand acCmdRecordsGoToLast

Everything seems to be populating correctly when I step through the code, however, I get "Syntax error (missing operator) in query expression 'Paid OT'" Paid OT is a value populated in the cboException, which also appears in txtException. I'm thinking that I need to add a WHERE clause, but not sure

Any additional assistance would be great. Thanks
This sounds pretty simple. When you insert a text value using the code above, the proper format is

'" & me.somevalue & "'

let me break it out so you can see it better

' " & me.somevalue & " '

where the tick marks go outside the quotes.

J
Aug 18 '07 #2

JConsulting
Expert 100+
P: 603
I am building a database to track attendance. I have one main form with multiple subforms. Within one of the subforms name sfrDailyAttendance, I also have cascading combo boxes (cboCategory and cboException) to filter difference absences (ie.Regular, Overtime, etc). cboCategory is filtered as follows:

[Forms]![frmMain]![sfrDailyAttendance].[Form].[Category].

Because I have the subform set up as a continuous form, I needed to add a text box (txtException) on top of one of the combo boxes (cboException), so that when a new Category was selected, it did not reset previous Exceptions selected. tblDailyAttendance does not have a field called txtException, which I thought was the reason, however, when I added it in the table, I still received errors.

The copy button is used to increment the date if it a consecutive absence for the same reason, and have been given advice to add the following code:

strSQL = "INSERT INTO tblDailyAttendance (StartTime, EndTime, Category, Exception, " & _
"Reason, ExceptionHrs) "
strSQL = strSQL & "VALUES(#" & Me.Date + 1
strSQL = strSQL & "#,#" & Me.StartTime & "#,#" & Me.EndTime & "#," & Me.Category & _
"," & Me.Exception & "," & Me.Reason & "," & Me.ExceptionHrs & ", " & Me.Description & ");"
CurrentDb.Execute strSQL

DoCmd.RunCommand acCmdRecordsGoToLast

Everything seems to be populating correctly when I step through the code, however, I get "Syntax error (missing operator) in query expression 'Paid OT'" Paid OT is a value populated in the cboException, which also appears in txtException. I'm thinking that I need to add a WHERE clause, but not sure

Any additional assistance would be great. Thanks


as far as the where part...you want to insert the record into a specific person's records..you'll probably want to use

Where ID = " & me.ID & "

where the ID number corresponds to the ID field you use in the table.

J
Aug 18 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.