RichardMGreen
Well-known Member
- Joined
- Feb 20, 2006
- Messages
- 2,177
Hi all
I've designed a form for data entry that has unbound text boxes, checkboxes and comboboxes on it.
I need to somehow loop through them and insert the relevant row of boxes into a table.
All the fields have logical names as follows:-
txt_StaffBand1, txt_StaffNamee1, cb_ShiftType1, txt_ShiftStart1, txt_ShiftEnd1, txt_TrainingStart1, txt_TrainingEnd1
The last 4 fields will have times in them and the receiving table has the field type defined as datetime with a format of short-time.
The receiving table has the same naming convention as the controls (minus the marker I've put on the for controll type).
There will be around 20 sets of fields (there's txt_StaffBand2, txt_StaffBand3, txt_StaffBand4, txt_StaffBand5, txt_StaffBand6..... and so on) and I somehow need to insert these into the receiving table (called tbl_ForecastShifts).
The only caveat is that if a staff band is empty then that row should not be inserted into the table (it will be completely blank).
I've tried writing some dynamic SQL in VBA to do the insert which looks like this:-
but it's complaining that I've got one of the types wrong (I've probably gone for either .Value or .Text which is the wrong one).
I did have a look at linking the form directly to the table and using some sort of offset to get everything in te right palce but I can't see that particular option.
Any help anyone can give will be greatly appreciated.
I've designed a form for data entry that has unbound text boxes, checkboxes and comboboxes on it.
I need to somehow loop through them and insert the relevant row of boxes into a table.
All the fields have logical names as follows:-
txt_StaffBand1, txt_StaffNamee1, cb_ShiftType1, txt_ShiftStart1, txt_ShiftEnd1, txt_TrainingStart1, txt_TrainingEnd1
The last 4 fields will have times in them and the receiving table has the field type defined as datetime with a format of short-time.
The receiving table has the same naming convention as the controls (minus the marker I've put on the for controll type).
There will be around 20 sets of fields (there's txt_StaffBand2, txt_StaffBand3, txt_StaffBand4, txt_StaffBand5, txt_StaffBand6..... and so on) and I somehow need to insert these into the receiving table (called tbl_ForecastShifts).
The only caveat is that if a staff band is empty then that row should not be inserted into the table (it will be completely blank).
I've tried writing some dynamic SQL in VBA to do the insert which looks like this:-
Code:
For Each ctl In Me.Controls
If ctl.Name = "txt_StaffBand" & recordcounter Then
If Me.txt_StaffBand1.Value <> "" Then
strSQL = "insert into tbl_ForecastShifts (Ward,StaffBand,StaffName,Date1,ShiftType1,ShiftStart1,ShiftEnd1," & _
"TrainingStart1,TrainingEnd1) select [A4] as Ward," & Me.txt_StaffBand1.Text & " as StaffBand1," & _
Me.txt_StaffName1 & " as StaffName1," & Me.dt_StartDate.Text & "as Date1," & Me.cb_ShiftType1.Text & _
" as ShiftType1," & Me.txt_ShiftStart1.Text & " as ShiftStart1," & Me.txt_ShiftEnd1.Text & _
" as ShiftEnd1," & Me.txt_TrainingStart1.Text & " as TrainingStart1," & Me.txt_TrainingEnd1.Text & " as TrainingEnd1,"
End If
End If
Next
but it's complaining that I've got one of the types wrong (I've probably gone for either .Value or .Text which is the wrong one).
I did have a look at linking the form directly to the table and using some sort of offset to get everything in te right palce but I can't see that particular option.
Any help anyone can give will be greatly appreciated.