Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Advice on storing info, please (WinXP/Word2003)

    I have a project that needs to collect info and use it as follows:

    This template creates a document containing several copies of a form, each copy of which needs to be filled in with a premises address, but we don't know how many addresses there will be and there will be often more than one form for any one address. In other words, the document this template creates could be 2 pages or 200 (the latter being highly unlikely, though!).

    I've got the basic page in an autotext entry for repeated pasting. A userform at the start asks how many addresses there are, then a second userform will (hopefully) appear as many times as required by the first one, collecting an address and a figure for number of times that address is to be used, each time.

    My question is, could someone point me in the right direction on how to make the form appear in this way and what would be the best way to save the information, given that we have no idea at this point how many pairs (address and no) will be needed on each occasion?

    I'm pretty certain we'll be dealing in loops and arrays, but they're probably my two weakest points in VBA, so any help would be greatly appreciated!
    Beryl M


  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Another way to do it would be to redisplay the "Add a Premises" userform until the user clicks an "All Done!" button, setting a flag you can check in your "main" loop.

    Sub whatever
    Do
    frmMyForm.Show
    'if (flag = quit out) Then Exit Do
    'do stuff with data from form
    Loop

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003)

    In a similar situation I followed Jefferson's approach. But then we ran into users who couldn't get it right (complete) the first time so they wanted to add an extra set of information afterwards. For that reason I discarded that approach and instead use a button on a commandbar which shows the form and inserts the info at the correct place in the document. And of course you can combine these two approaches.

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Thanks, Jefferson, that's certainly helpful, but the main thing I'm having trouble with at the moment is how to save the data from each repetition of the form.

    For instance, I'm going to need an infinite number of variables (presumably) to store each pair of items (a number and a longish string of text, ie an address, which will include spaces and punctuation), since I don't know how many there will be - how would I set these up?

    Then, I assume I will need some sort of loop that will increment the no for each repetition of the form, eg add1, no1; add2, no2; add3, no3; etc ...?

    At this moment I'm completely blank as to how to go about this!
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Could you let the user fill a table in a Word document or Excel spreadsheet with two columns and as many rows as needed, and use this table as data source for a mail merge? That way, Word would take care of the looping.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Only if this could be implemented as part of a Word template? I have to admit the idea doesn't appeal, though ...

    I'd really appreciate it if someone would tell me how would I go about creating a dynamic array of individually-named variables, where a new one is added each time it's needed without having to be 'dim'med first ...?

    Pretty please? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    Beryl M


  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Try the attached document. Run the macro in the basCode module.
    Attached Files Attached Files

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Thanks, Hans, I can't even see how you can fit all that into such a tiny piece of code!

    Couple of questions, though -

    1 - the number concerned (as I need to use it) is the number of times that address needs to be used, not the number in the sequence (at least, that seems to be how your code uses it, since it didn't show an entry with the same number as an earlier one), and

    2 - that still doesn't tell me how to give each set of entries (number/address) individual variable names so they can be used separately in the document as necessary, without knowing how many there are going to be before I start. That is, I need to end up with variables called something along the lines of add1, no1, add2, no2, add3, no3 etc. Perhaps a variable number that increments by one each time the form is opened, but then how do I add that to a variable name to get a unique variable (again, without having to dim it first since I don't know how many I'll need), ie newVarName = str("add" + totalNo) or whatever (not that that would work, would it?!)?

    I is confuggulated! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Beryl M


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    You cannot dynamically define variables add1, add2, etc., that's why I used an array. Arrays *can* be declared dynamically using the ReDim statement. Click in the word ReDim (in the Visual Basic Editor) and press F1 to get help about it.
    The numbers are arr(1, 1), arr(1, 2) , arr(1, 3) etc. (corresponding to your no1, no2, no3 etc) and the addresses are arr(2, 1), arr(2, 2), arr(2, 3) etc. (corresponding to your add1, add2, add3 etc.)

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Hi Hans, thanks for that, I see where it goes now.

    However, no matter how I enter content to the textboxes on the test form, the msgbox will only display 2 entries - the first two!

    What am I doing wrong?!
    Beryl M


  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Sorry, my bad. I forgot to change something at the last moment. Change the line

    For i = 1 To UBound(arr, 1)

    to

    For i = 1 To UBound(arr, 2)

    UBound(arr, 1) is always 2, UBound(arr, 2) corresponds to the number of entries.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Ah, I see - glad to know it wasn't me!

    Just one quick (totally unrelated) question - some code already set in this project has the option base set to 0, which I believe means all counting starts at 0 instead of 1, yes? Do I need to change all instances of 1 in this code to 0 to get it to work properly?
    Beryl M


  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Option Base 0 means that array indexes start counting at 0 unless otherwise specified. I explicitly specified that the indexes start at 1:

    ReDim Preserve arr(1 To 2, 1 To lngCount)

    so there is no need to change anything, unless you prefer to do so.

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Nooo-o-o-o-o!! <img src=/S/nope.gif border=0 alt=nope width=15 height=15>

    I'm not changing something you've done unless I have to, to use it in my specific situation!

    Thanks ever so, as usual - now I'll just see how far I can get with this project before I have to resort to coming back for help again!!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Advice on storing info, please (WinXP/Word2003

    Well, I've got further than I expected (on doing this project before I have to ask for help again)!

    The template is up and running exactly as I wanted, now I'd just like to add the ability for the user to check the addresses they've entered before I put them in and replicate any errors several times over!

    I've got a form with a list box in, and I'd like to populate that form with the results of the dynamic array you showed me how to use; you may remember each entry in the array consists of an address and the number of times it is to be used, so I'd like the listbox to look as follows:

    2 x "1 The High Street, Anytown"
    1 x "The Old House, Minivillage"
    3 x "Someplace, Somewhere"

    and have the user able to click on an item to reopen the address entry form populated with that entry and its number so they can edit it if it's wrong.

    Is this possible or am I pushing too hard this time?! If it's not possible I'll survive, it just seemd a good idea to get them to check it if I could, before replicating them - since each adddress is used five times anyway, and twice more for each time it's requested more than once (ie, the above would produce 7 copies of the first entry, 5 of the second and 9 of the third!

    Many thanks
    Beryl M


Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •