ThinWhiteDude
Technical User
Not sure if this should be in the Forms forum, or SQL, so I’ll start here.
I am using an unbound form to create a pseudo-crosstab for data entry. Here is a sketch of the layout:
There are 18 txtContractor, 3 txtCategory, and 20 txtObservType controls. These are populated by looping through values in 3 tables (tblContractors, tblCategories, tblObservationTypes) when the form loads. They are locked and disabled.
User is walking around the plant, observing activities of various contractors employed by the company. User has a sheet in front of him with scores he’s/she’s written while observing. He/she goes into the database and enters those numbers in the txtC#Score# textboxes. The txtC1Score1 – txtC18Score20 are named to correspond to their Contractor and Observation Type. They are the only unlocked, enabled textbox controls
I have a “Done” command button which, On Click, I would like to append the appropriate records to tblAuditObservations, which has the following fields: lngObservNum, strAuditNumber, strObservationCategory, strObservationType, strContractor, and lngScore. Every txtC#Score# that has a value represents a full record in the table. (As you can see more than one Contractor could be scored in any observation type).
To accomplish this, I’ve written some code that works, but I know is so cumbersome it boggles the mind. Here is some of it:
strAuditNumber will come from a single textbox on the form, assigned at form open, and is the same for all records. I've hard coded that only for testing.
This appends X records to the table quite nicely, but as you can imagine, I don’t want to have to repeat this 360 times (and that’s not even checking for nulls yet!)
I am hoping it is possible to loop through these controls to append the records all at once. I am more than willing to rename, or tag the controls if suggested (I haven’t even created them all yet, in anticipation of such changes) to accomplish this.
As always, I very much appreciate any help or nudge or direction you can give me on this.
TWD
I am using an unbound form to create a pseudo-crosstab for data entry. Here is a sketch of the layout:
Code:
txtContractor1 txtContractor2 txtContractor3
txtCategory1
txtObservType1 txtC1Score1 txtC2Score1 txtC3Score1
txtObservType2 txtC1Score2 txtC2Score2 txtC3Score2
txtObservType3 txtC1Score3 txtC2Score3 txtC3Score3
txtObservType4 txtC1Score4 txtC2Score4 txtC3Score4
txtCategory2
txtObservType5 txtC1Score5 txtC2Score5 txtC3Score5
txtObservType6 txtC1Score6 txtC2Score6 txtC3Score6
txtObservType7 txtC1Score7 txtC2Score7 txtC3Score7
txtObservType8 txtC1Score8 txtC2Score8 txtC3Score8
txtObservType9 txtC1Score9 txtC2Score9 txtC3Score9There are 18 txtContractor, 3 txtCategory, and 20 txtObservType controls. These are populated by looping through values in 3 tables (tblContractors, tblCategories, tblObservationTypes) when the form loads. They are locked and disabled.
User is walking around the plant, observing activities of various contractors employed by the company. User has a sheet in front of him with scores he’s/she’s written while observing. He/she goes into the database and enters those numbers in the txtC#Score# textboxes. The txtC1Score1 – txtC18Score20 are named to correspond to their Contractor and Observation Type. They are the only unlocked, enabled textbox controls
I have a “Done” command button which, On Click, I would like to append the appropriate records to tblAuditObservations, which has the following fields: lngObservNum, strAuditNumber, strObservationCategory, strObservationType, strContractor, and lngScore. Every txtC#Score# that has a value represents a full record in the table. (As you can see more than one Contractor could be scored in any observation type).
To accomplish this, I’ve written some code that works, but I know is so cumbersome it boggles the mind. Here is some of it:
Code:
strSQL = "INSERT INTO tblAuditObservations"
strSQL = strSQL + "(strAuditNumber, strObservationCategory, strObservationType, strContractor,lngScore)"
strSQL = strSQL + " values ('1-05'," & "'" & Me!txtCat1 & "'," & "'" & Me!txtType1 & "'," & "'" & Me!txtContractor1 & "'," & Me!txtC1Score1 & ")"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO tblAuditObservations"
strSQL = strSQL + "(strAuditNumber, strObservationCategory, strObservationType, strContractor,lngTypeScore)"
strSQL = strSQL + " values ('1-05'," & "'" & Me!txtCat1 & "'," & "'" & Me!txtType2 & "'," & "'" & Me!txtContractor1 & "'," & Me!txtC1Score2 & ")"
DoCmd.RunSQL strSQLstrAuditNumber will come from a single textbox on the form, assigned at form open, and is the same for all records. I've hard coded that only for testing.
This appends X records to the table quite nicely, but as you can imagine, I don’t want to have to repeat this 360 times (and that’s not even checking for nulls yet!)
I am hoping it is possible to loop through these controls to append the records all at once. I am more than willing to rename, or tag the controls if suggested (I haven’t even created them all yet, in anticipation of such changes) to accomplish this.
As always, I very much appreciate any help or nudge or direction you can give me on this.
TWD
