Inserting unbound fields into a table

[RichardMGreen]

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:-

    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.

 

[RichardMGreen]

Hi all

I think I've solved my initial problem by using a multiple items form (didn't even know they existed).
I'm going to link it to a query instead of a table (I hope) but now I've got a slightly different problem.

I've got a field called ShiftType1 which is a lookup to a table called tbl_ShiftType.
It shows the code values for the shifts I want.

In the same table, I've got the start/end times for the shifts.

What I want to do is, when I select the shift code in ShiftType1, I want to populate StartTime and EndTime automatically from tbl_ShiftType.

I tried doing it as a lookup on the table itself but that only gives me a combobox with all the times in it.

Is it possible to do what I want?

 

[Micron]

Lookups are just not the way to go for what you are doing (if you are doing it in tables), then again, I am not entirely clear on what you are doing given the terminology you are using. However, if you are doing it on a form control bound to a table or query, that's different. As for .value vs .text, the default property for a textbox is value, so it is enough to say "strStaffband = me.txtStaffBand". Text is a property that may or may not be the same as the value. If a control does not get updated, the value property is the last value (which could be null) while the text is what is in the control before it is updated.

You could do two DLOOKUPs on the table, one for each required value, or a sql statement will retrieve the values you want. Something like:
SELECT tbl_ShiftType.StartTime, tbl_ShiftType.EndTime FROM tbl_ShiftType WHERE tblShiftType = "thevalueinyourcontrol;"
How you use this depends on what you're doing. As noted, I am not clear on this - Sorry.

 

[RichardMGreen]

Hi Micron

Thanks for advice (and apologies for the delay in responding).
I've got that sorted but now have a different issue so I'm going to start a new thread.