Reply to Message

I have a word form with about 300 fields (so it won't fit in 1 table - I use 3 instead). Currently I have it working where on submission, it opens 3 recordsets in a row, and fills in fields with part of the form in each case. The primary key in table 1 (replication id) is duplicated in a "child_id#" field in the other two tables. When I try to use a similar method to pull the data back into the form, the first part of the form fills in OK, but it can't find the 2nd and 3rd part. I am using the following method to get the data:
'enter the id to fill in the form:
vRPT_ID = ActiveDocument.FormFields("RPT_ID").Result

vGetSql = "SELECT EE_REPORT_DATA1.* " & _
"FROM EE_REPORT_DATA1 " & _
"WHERE EE_REPORT_DATA1.[ID]='" & vRPT_ID & "';"

vRecordSet.Open vGetSql, vConnection, adOpenKeyset, adLockOptimistic


With vRecordSet
If Not .EOF Then

'Yes then fill in the form to the database stored values

If vRecordSet("ID") "" Then vRPT_ID = vRecordSet("ID")
If vRecordSet("Date") "" Then vText1date = vRecordSet("Date")
If vRecordSet("Report") "" Then vText2Report = vRecordSet("Report")
'etc. etc.

'Fill form field's bookmarks

ActiveDocument.FormFields("RPT_ID").Result = vRPT_ID
ActiveDocument.FormFields("Text1date").Result = vText1date
ActiveDocument.FormFields("Text2Report").Result = vText2Report
ActiveDocument.FormFields("Text3Toolname").Result = vText3Toolname

'all going well so far

Else
MsgBox "Sorry, No matching records found!"
End If
End With

vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing

'DB SECTION 2

'Make a connection to the database (copy & paste)
vConnection.ConnectionString = _
"data source=" & vPath & "\" & vFileName & ";" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'Open the connection for data exchange (copy n paste)
vConnection.Open
'Test Connection and let user knows it is working. ?
'Change if this message is annoying
vConnectionState = vConnection.State
If vConnectionState 1 Then
MsgBox "Cannot connect to the database!", vbCritical
End If

vGetSql2 = "SELECT EE_REPORT_DATA2.* " & _
"FROM EE_REPORT_DATA2 " & _
"WHERE EE_REPORT_DATA2.[CHILDID2]='" & vRPT_ID & "';"

vRecordSet.Open vGetSql2, vConnection, adOpenKeyset, adLockOptimistic

'if a match is not found then show it
With vRecordSet
If Not .EOF Then

'fill in the form to the database stored values

If vRecordSet("CHILDID2") "" Then vID_PROP = vRecordSet("CHILDID2")
If vRecordSet("C1") "" Then vDropdown1 = vRecordSet("C1")

etc. etc.

The first part of the form fills in fine, and then it can't find the 2nd table stuff. The CHILDID2 is identical to the vRPT_ID. I know there has to be a better way to do this than sequential access to tables, but if I can get it to work this way it would be better for now - any ideas?

Thanks,

John