Input value in next field if check box is checked

[aaronram]

I have an Word form with 400+ bookmarked check boxes. I need to keep track as to which check box is checked, but do not care if the box is not checked. I tried to keep track of each check box individually, but ran into the 255 field limit. Since experience shows that there is a maximum of 50 checked boxes in any one form, is there a way to have Access populate the next field in the table with a set value corresponding to the box that was checked? That is, if the first checked box in the Word form is labeled "UNS56", with 56 identifying which box number it is, I want Access to make field "Box1" have a value of "192.5" (the value is a unique reference to an internal statute) . Then if the second checked box is "UNS102", field "Box2" should have a value of "191.36" and so on. If it makes any difference, each of the 400+ questions have multiple options available, but I am only concerned if one particular option is picked.

Below is a shortened version of my code that imported the individual records:


Sub GetWordData()
Dim appWord As Word.Application
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
Dim file As String
Dim dfol As String




file = InputBox("Enter the name of form" & _
"you want to import", "Import Form")
strDocName = "C:\Documents and Settings\aaronr\Desktop\Form\SampleForms\" & file
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Documents and Settings\aaronr\Desktop\Form\Form1.accdb;"
rst.Open "tblForms", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!OPID = doc.FormFields("OPID").Result
!OPName = doc.FormFields("OPname").Result
!InspDate = doc.FormFields("Date").Result
!UNS1 = doc.FormFields("UNS1").Result
!UNS2 = doc.FormFields("UNS2").Result
!UNS3 = doc.FormFields("UNS3").Result
!UNS4 = doc.FormFields("UNS4").Result
!UNS5 = doc.FormFields("UNS5").Result
!UNS6 = doc.FormFields("UNS6").Result

.Update
.Close

End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close

dfol = "C:\Documents and Settings\aaronr\Desktop\Form\SampleForms\Imported\" & file
Name strDocName As dfol

MsgBox "Form Imported!"



Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing

End Sub



Thank you

[aaronram]

I figured it out using the following code. Does anyone see any inefficiency?

Thank you

Sub GetWordData()
Dim appWord As Word.Application
Dim cnn As New ADODB.Connection
Dim rsti As New ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean
Dim file As String
Dim dfol As String
Dim fnew As String
Dim Num As Integer
Dim Cit(1 To 50) As String
Dim i As Integer
Dim Cellvalue As String

On Error GoTo ErrorHandling


file = InputBox("Enter the name of form" & _
"you want to import", "Import Form")
strDocName = "C:\Documents and Settings\aaronr\Desktop\Form\SampleForms\" & file
Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Documents and Settings\aaronr\Desktop\Form\Form1.accdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic
rsti.Open "Table2", cnn, _
adOpenKeyset, adLockOptimistic
For i = 1 To 407
dfol = doc.FormFields("UNS" & i).Result
If dfol = True Then
Num = Num + 1
Cit(Num) = DLookup("Code", "Table2", "ID = " & i)
End If
Next i

With rst
.AddNew
!OPID = doc.FormFields("OPID").Result
!OPName = doc.FormFields("OPname").Result
!SYSName = doc.FormFields("SYSname").Result
!InspDate = doc.FormFields("Date").Result
!Citation1 = Cit(1)
!Citation2 = Cit(2)
!Citation3 = Cit(3)
!Citation4 = Cit(4)
!Citation5 = Cit(5)
!Citation6 = Cit(6)
!Citation7 = Cit(7)
!Citation8 = Cit(8)
!Citation9 = Cit(9)
!Citation10 = Cit(10)
!Citation11 = Cit(11)
!Citation12 = Cit(12)
!Citation13 = Cit(13)
!Citation14 = Cit(14)
!Citation15 = Cit(15)
!Citation16 = Cit(16)
!Citation17 = Cit(17)
!Citation18 = Cit(18)
!Citation19 = Cit(19)
!Citation20 = Cit(20)
!Citation21 = Cit(21)
!Citation22 = Cit(22)
!Citation23 = Cit(23)
!Citation24 = Cit(24)
!Citation25 = Cit(25)
!Citation26 = Cit(26)
!Citation27 = Cit(27)
!Citation28 = Cit(28)
!Citation29 = Cit(29)
!Citation30 = Cit(30)
!Citation31 = Cit(31)
!Citation32 = Cit(32)
!Citation33 = Cit(33)
!Citation34 = Cit(34)
!Citation35 = Cit(35)
!Citation36 = Cit(36)
!Citation37 = Cit(37)
!Citation38 = Cit(38)
!Citation39 = Cit(39)
!Citation40 = Cit(40)
!Citation41 = Cit(41)
!Citation42 = Cit(42)
!Citation43 = Cit(43)
!Citation44 = Cit(44)
!Citation45 = Cit(45)
!Citation46 = Cit(46)
!Citation47 = Cit(47)
!Citation48 = Cit(48)
!Citation49 = Cit(49)
!Citation50 = Cit(50)



.Update
.Save

.Close

End With
doc.Close
appWord.Quit
cnn.Close

fnew = "C:\Documents and Settings\aaronr\Desktop\Form\SampleForms\Imported\" & file
Name strDocName As fnew

MsgBox "Imported with Citations:"


Cleanup:
Set rst = Nothing
Set rsti = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub