Starting with a list of downtime codes and their associated descriptions, I made up another table that had two columns for each code: a duration in minutes, and a bit field indicating whether this particular downtime halted production. Often if a production line is down, several pieces of equipment are fixed or adjusted, so adding them up doesn't give a correct perspective on how much time production overall had stopped.

The resulting table had 176 of each field, or 352 columns total. Access would not populate one form with all the fields. I had to break it up into three. For each column, Access creates two controls: a textbox and a label, for a total of over 700 controls.

I was a bit dismayed to find that the bit field was imported as a textbox, since what I wanted was a checkbox. I looked through the Access configuration options to see if I could alter this and I didn't see anything. I tried importing the table into an .MDB (I was working on an SQL .ADP project) and got the same results with the wizard. I vaguely remember looking up various objects and their associated properties and finding a way to script the changes, so I started rooting around and found what I was looking for in the Access object.

What I had was two text boxes and two labels when what I wanted was a textbox, a checkbox, and one label. The wizard had put all of them on separate lines, I wanted the two fields immediately adjacent to each other. I used a combination of wizards and plain old form editing to get what I was after.

The following block of code contains a number of functions, each of which contains in turn several different options. One shows how I scanned through one of the forms looking for any label that had the string "Halt_Label" in it. Every time I found one of these the code deletes the label.

An example of a macro that calls these routines is:

         Runcode HaltControlWizard("frmDowntime", 3)

The functions and their various options are:

Dim i As Integer

Dim ControlName As String

Public Function DurationControlWizard(Formname As String, SelectedOperation As Integer) As Integer
DurationControlWizard = 0
If Not (Forms(Formname).CurrentView = 0) Then Exit Function
For i = 0 To Forms(Formname).Controls.Count - 1
ControlName = Forms(Formname).Controls(i).Name
Debug.Print ControlName
If InStr(Forms(Formname).Controls(i).Name, "Duration") > 0 Then
Select Case SelectedOperation
Case Is = 1:
If InStr(ControlName, "Duration_Label") > 0 Then
Forms(Formname).Controls(i).Caption = Left$(Forms(Formname).Controls(i).Caption, Len(Forms(Formname).Controls(i).Caption) - 8)
End If
Case Is = 2:
If InStr(ControlName, "Duration_Label") > 0 Then
Forms(Formname).Controls(i).Caption = Replace(Forms(Formname).Controls(i).Caption, "_", " ")
End If
' Case Is = 3:
' If InStr(ControlName, "Halt_Label") > 0 Then
' Access.DeleteControl FormName, ControlName
' End If
End Select
DoEvents
End If
Next i
End Function

Public Function HaltControlWizard(Formname As String, SelectedOperation As Integer) As Integer
HaltControlWizard = 0
If Not (Forms(Formname).CurrentView = 0) Then Exit Function
For i = 0 To Forms(Formname).Controls.Count - 1
ControlName = Forms(Formname).Controls(i).Name
Debug.Print ControlName
If InStr(Forms(Formname).Controls(i).Name, "Halt") > 0 Then
Select Case SelectedOperation
Case Is = 1:
Debug.Print Forms(Formname).Controls(i).Top
Debug.Print Forms(Formname).Controls(i).Left
Access.CreateControl Formname, acCheckBox, acDetail, "", ControlName, Forms(Formname).Controls(i).Left, Forms(Formname).Controls(i).Top
Case Is = 2:
If InStr(ControlName, "Duration_Label") > 0 Then
Forms(Formname).Controls(i).Caption = Left$(Forms(Formname).Controls(i).Caption, Len(Forms(Formname).Controls(i).Caption) - 8)
End If
Case Is = 3:
If InStr(ControlName, "Halt_Label") > 0 Then
Access.DeleteControl Formname, ControlName
End If
Case Is = 4:
If InStr(ControlName, "Halt") > 0 Then
Access.DeleteControl Formname, ControlName
End If
End Select
DoEvents
End If
Next i
End Function

Public Function CheckControlWizard(Formname As String, SelectedOperation As Integer) As Integer
CheckControlWizard = 0
If Not (Forms(Formname).CurrentView = 0) Then Exit Function
For i = 0 To Forms(Formname).Controls.Count - 1
ControlName = Forms(Formname).Controls(i).Name
Debug.Print ControlName
If Left(ControlName, 5) = "Check" Then
Forms(Formname).Controls(i).Name = "chk" & Forms(Formname).Controls(i).ControlSource
DoEvents
End If
Next i
End Function
 

The CreateControl and DeleteControl operators are the key to this solution.

I defined several macros to initiate the various chunks of code. The target form (the form being modified) has to be open in Design Mode, so it isn't possible to launch the functions from the form itself, or if it is I wouldn't recommend it. If the form is not open or not open in design mode, the execution fails.

Once I had deleted the labels for the bit columns the next function I ran (using CreateControl) plopped a checkbox on top of the textbox that was bound to the same column. At this stage of the game the checkbox is called Checknnn, and the textbox has the same name as the underlying column. Two different fields are bound to the same column.

I deleted the text boxes by simply highlighting them and deleting them. At this point the checkboxes are below their associated interval textbox.

I then resized the text boxes and the labels so that there was a vacant area for me to shift the checkbox. I then selected the checkboxes and shifted them into position.

After that I scanned all the duration labels, and replaced the underscore in each with a blank.

The last step in this was to rename the checkbox to 'chk' plus the underlying column name.

A lot of this was trial and error. Anyone messing with this is advised to make test copies of their forms, and run their code on the test copies first.

The code above was cut from an Access module and pasted in without any attempt to make it pretty. It should be possible for the reader to highlight that code and paste it back into Access, but if not paste it into notepad and get any residual HTML out of it first. Either that, or just print it out and key it back in.

Some sections are commented out because they are duplicated elsewhere. I may, at some point, use these blocks of code for other functionality.

Good luck.