Russ
Sun Jul 22 04:09:30 CDT 2007
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.
> 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
>
> On Jun 29, 1:43 am, "Doug Robbins - Word MVP"
> <d...@REMOVECAPSmvps.org> wrote:
>> 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
>>
>> <shadows...@gmail.com> wrote in message
>>
>> news:1183044378.070867.199540@o11g2000prd.googlegroups.com...
>>
>>> 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
>>
>>> On Jun 27, 7:59 pm, "Doug Robbins - Word MVP"
>>> <d...@REMOVECAPSmvps.org> wrote:
>>>> 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
>>
>>>> <shadows...@gmail.com> wrote in message
>>
>>>> news:1182981451.263794.83140@x35g2000prf.googlegroups.com...
>>
>>>>> 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)
>
>
--
Russ
drsmN0SPAMikleAThotmailD0Tcom.INVALID