extract multiple records to access from one word form?

[shadowsong]

I'm setting up a Word form that customers can use to submit product
registrations, and an Access database to store the data they send, one
record per product registered. I found this site -
http://gregmaxey.mvps.org/Extract_Form_Data.htm - which shows me how
to extract the form data to an Access table, but I have a minor
problem.

The code on that site seems to require unique fields, meaning each
instance of the form would be one record. However, my customers want
to send one form per end user - which could be anywhere from one to
100 separate products.

Is it possible to adjust the code found at the link above to treat
multiple form fields with the same name (such as 15 instances of
"serialnumber") as separate records, and copy form fields that only
appear once (such as "customername") into the appropriate field in
each record?

Also, if there are any variables in that code other than the field
names in Access and Word (such as "text1" and "favorite food") that
need to be changed depending on what I name things and where I save
them, please point them out to me. I haven't really learned VBA yet,
so I'm flying blind.

-Joanna

(I apologize if this is a double post... it timed out on me the first
time)

 

[Doug Robbins - Word MVP]

I believe that it is certainly possible to do what you want, but you will
have quite a bit of learning to do, and it will depend upon how the multiple
record type information appears in your form.

Tell us some more about it so that we have more chance of providing relevant
tips.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[shadowsong]

Well, in general terms, the form looks like this:

Several fields of dealer information (dlrname, dlrnum, salesname)
Several fields of customer information (custname, addr1, addr2, city,
state, zip, phone, email)
Several fields of product information (sold, model, upc, sn), repeated
in 15 rows

Currently the bookmark names for each iteration of product information
are unique (ie, model1 through model15 instead of fifteen fields all
named model), but I don't know if that's necessary. Each row of
product information on the form should end up as a new record in
access, with the same customer and dealer information for every
product row on that form. The combination of sn and model should be
unique, but I don't know if that's relevant right now.

I suspect that the solution will involve WHILE statements somewhere in
the section I've excerpted below, but I'm not sure exactly where.
Maybe just after the "With myDoc" bit where it's actually saying what
to export to Access, or maybe before the vRecordSet.AddNew bit so that
it adds a new record each time around the while loop.

'Retrieve the data
vConnection.Execute "DELETE * FROM MyTable"
For i = 1 To UBound(FileArray)
Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), _
Visible:=False)
FiletoKill = oPath & myDoc 'Identify the file to move after
processing
vRecordSet.AddNew
With myDoc
If .FormFields("Text1").Result <> "" Then _
vRecordSet!Name = .FormFields("Text1").Result
If .FormFields("Text2").Result <> "" Then _
vRecordSet("Favorite Food") = .FormFields("Text2").Result
If .FormFields("Text3").Result <> "" Then _
vRecordSet("Favorite Color") = .FormFields("Text3").Result
.SaveAs oPath & "Processed\" & .Name 'Save processed file in
Processed folder
.Close
Kill FiletoKill 'Delete file from the batch folder
End With
Next i

Obviously the table, FormFields(), and RecordSet() names will need to
be changed to match my field names. I'm kind of worried about the
"DELETE * FROM MyTable" bit, but I may be incorrectly assuming that it
means "delete all records from table".

Let me know if I'm still being too vague.

-Joanna

 

[shadowsong]

I think what I need to do is give all the repeated fields the same
bookmark name, and put a line before the vRecordSet.AddNew line saying
For Each ActiveDocument.FormFields("SN") In myDoc

but in a way that actually works, since it tells me it needs a
variable and FormFields isn't one. What I'm trying to say is "for each
field bookmarked as SN, create a new record".

 

[shadowsong]

I got it:

what I needed to do was add an iterating variable, and a variable
concatenating the field name and the iterating variable:

With myDoc
For x = 1 To 15 Step 1
SNX = "SN" & x
If .FormFields(SNX).Result <> "" Then _
vRecordSet.AddNew
If .FormFields("DLRNAME").Result <> "" Then _
vRecordSet!DLRNAME
= .FormFields("DLRNAME").Result
If .FormFields("DLRNUM").Result <> "" Then _
vRecordSet!DLRNUM
= .FormFields("DLRNUM").Result
If .FormFields("CUSTNAME").Result <> "" Then _
vRecordSet!CUSTNAME
= .FormFields("CUSTNAME").Result
If .FormFields(SNX).Result <> "" Then _
vRecordSet!SN
= .FormFields(SNX).Result
Next x
.SaveAs oPath & "Processed\" & .Name 'Save processed file in
Processed folder
.Close
Kill FiletoKill 'Delete file from the batch folder
End With

 

[Doug Robbins - Word MVP]

As you are still setting up your form, take a look at

http://www.mousetrax.com/techpage.html#autoforms


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[shadowsong]

I have one more question about this. I'm receiving many forms where
they've only filled out the first line completely, and left cells
blank in subsequent lines where the value is the same, usually in the
date and model columns. I need to ensure that all of the information
gets into Access.

I can't just say, "If this cell is blank use the value from the
previous cell," because if the date's in row 1 and I'm on row 15, 14
will also be blank. I can't say, "If this cell is blank use the value
from row 1" because they could have a different date in row 2 which is
the one that needs to be copied into subsequent blanks.

I could have the macro set the blank field as the previous value in
Access, but I don't know how to refer to "previous value"....
something involving a MovePrevious cursor command?

The method I tried first was having the macro change the form when it
encountered a blank, but I think I'm referring to the form value
incorrectly. Here's what I have:

If x > 1 And .FormFields(SOLDX).Result = "" Then _
.FormFields(SOLDX) = .FormFields(PREVSOLD).Result

where SOLDX is SOLDX = "SOLD" & x and PREVSOLD is what SOLDX was
defined as the previous time around the loop.

However, .FormFields(SOLDX) is an "invalid use of property". Should I
be using .FormFields(SOLDX).Result instead, or something else
entirely?


Thanks for your help,
Joanna

 

[Russ]

Is this data in a Word Table or aligned formfields, not in a table.

If data is in a table then, through VBA, you can select the cells in a table
column make it a range and test 'While Not Acell.next Is Nothing', whether
it is empty, for example, and fill it with the current cell's value, etc.

For formfields not in a table, hopefully they are named with a pattern that
has a sequence number.