Transfer MS Word Form data to MS Access Tables

[clarencelai]

I'm trying to import data from the MS Word Form to multiple access tables in a one to many relationship.

The MS Word Form contained the applicant’s personal details (Table CustDetails) and the training sessions applied (Table Sessions).

With the help of the below article posted on Bytes, I managed to do it successfully.
https://bytes.com/topic/access/answers/773541-word-form-access-tables-data-transfer

However, I have other challenges.

The MS Word Form I used to collect data contained a table of records where the applicants need to enter information such as date, time and session names – information about the sessions they have applied. The users may apply for multiple sessions.

The MS Word Form I have designed has catered up to 10 sessions for the users to enter.

I have assigned simple content control names to the 10 sets of records to be collected.

Session Name - SessionName01 to SessionName10
Session Date - SessionDate01 to SessionDate10
Session Time - SessionTime01 to SessionTime10

The Tables and fields as follows:
1. CustDetails
a. [ID] - AutoNumber/Primary Key
b. [CoName]
c. [CustName]
d. [Phone]
e. [email]

2. Sessions
a. [SessionID] - AutoNumber/PrimaryKey
b. [ID] - LONG/Links to CustDetails.[ID] {MANY to 1}
c. [SessionName]
d. [SessionDate]
e. [SessionTime]

Currently, I am adding records to the Sessions table by duplicating the “.AddNew” statement for record 1 to 10 (in actual fact, the maximum sessions can go up to 20).

Dim CompanyName As String
Dim CustomerName As String
Dim Telephone As String
Dim EmailAdd As String
Dim SessionName01 As String
Dim SessionName02 As String
Dim SessionName03 As String
Dim SessionName04 As String
Dim SessionName05 As String
Dim SessionDate01 As Date
Dim SessionDate02 As Date
Dim SessionDate03 As Date
Dim SessionDate04 As Date
Dim SessionDate05 As Date
Dim SessionTime01 As Date
Dim SessionTime02 As Date
Dim SessionTime03 As Date
Dim SessionTime04 As Date
Dim SessionTime05 As Date
Dim lngSessionID As Long
 
Set cnn = CurrentProject.Connection
 
rst.Open "CustDetails", cnn, adOpenKeyset, adLockOptimistic
rstEvents.Open "Sesssions", cnn, adOpenKeyset, adLockOptimistic
 
‘************************************************************
‘ add records to CustDetails table
With rst
.AddNew
    .Fields("CoName") = CompanyName
    .Fields("CustName") = CustomerName
    .Fields("Phone") = Telephone
    .Fields("Email") = EmailAdd
 
    rst.Update
End With
    rst.Requery
rst.Close: Set rst = Nothing
 
‘************************************************************
‘ add records to Sessions table
lngSessionID = DLast("[ID]", "CustDetails")
 
With rstEvents
 
rstEvents.AddNew
rstEvents.Fields("ID") = lngEventID
rstEvents.Fields("SessionName") = SessionName01
rstEvents.Fields("SessionDate") = SessionDate01
rstEvents.Fields("SessionTime") = SessionTime01
rstEvents.Update
 
‘ repeating above statements for all remaining records from 02 through 10
With rstEvents
rstEvents.AddNew
rstEvents.Fields("ID") = lngEventID
rstEvents.Fields("SessionName") = SessionName02
rstEvents.Fields("SessionDate") = SessionDate02
rstEvents.Fields("SessionTime") = SessionTime02
rstEvents.Update
 
With rstEvents
rstEvents.AddNew
rstEvents.Fields("ID") = lngEventID
rstEvents.Fields("SessionName") = SessionName03
rstEvents.Fields("SessionDate") = SessionDate03
rstEvents.Fields("SessionTime") = SessionTime03
rstEvents.Update
 
With rstEvents
rstEvents.AddNew
rstEvents.Fields("ID") = lngEventID
rstEvents.Fields("SessionName") = SessionName04
rstEvents.Fields("SessionDate") = SessionDate04
rstEvents.Fields("SessionTime") = SessionTime04
rstEvents.Update
 
With rstEvents
rstEvents.AddNew
rstEvents.Fields("ID") = lngEventID
rstEvents.Fields("SessionName") = SessionName05
rstEvents.Fields("SessionDate") = SessionDate05
rstEvents.Fields("SessionTime") = SessionTime05
rstEvents.Update
 
‘ I will repeat the above another 5 to 10 times
 
End With
 
    rstEvents.Close: Set rstEvents = Nothing
 
doc.Close: Set doc = Nothing
cnn.Close: Set cnn = Nothing
appWord.Quit: Set appWord = Nothing

I wonder if there’s a simpler way to add new records to the Sessions table since the fields in the Word Form are all named as such that it ends with a 2-digit number with an increment of 1 (for example, SessionName01, SessionName02, 03..etc).


Also, is there a way to terminate the add records process once an empty field is detected (for example, the applicant has only applied for 3 sessions, so the 4th row onward are empty fields. The add records process should terminate immediately and end the data transfer.

Any help will be much appreciated.

Thank you.

[zmbd]

clarencelai The MS Word Form I used to collect data contained a table of records where the

See if this insight article will point you in the right direction.
home > topics > microsoft access / vba > insights > importing ms word tables into ms access tables using vba

Stevan even has a link to youtube showing this article in "applied theory" ;)

> Instead of a For..Next Loop, look at a Do_While or Do_Until loop

> If you are comfortable with "Late Binding" you would not need to set the references as given in the document/video. HOWEVER, I usually set the object library(s) during the development phase of the code as this allows me to use the inteli-sense and defined constants (i.e. vbYes actually has the value of 7) and the go back and declare the used constants and setup the late binding.

> Even it's close, but not just right, it will help us to help you zero in on the best solution.

[clarencelai]

Hi

I happened to bump into the aforementioned article shortly after I posted my questions. Took me a while to try the solution. Yes, it has helped to resolve the issue I have. Thanks!

Regards,
Clarence