Results 1 to 6 of 6
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Converting to Subforms (2000)

    I recently made dramatic improvements to order entry and order history forms by changing over to a subform. This was accomplished almost entirely due to input from Hans V. (see <post#=287150>post 287150</post#> ). What I neglected to inform Hans of, and just discovered myself, is that there is a continuous form (showing multiple orders) that relied on the old multiple cell entry format. Those fields are no longer available because they have been "compressed" into a single field with a SeqNo control in the SQL (Union Query).

    Load Board (the continuous form in question - old format attached) is based on tblMaster, while the needed fields are now in tblShCon. These two tables are joined by ProNo (see attachement). I believe I could create a text box on the Load Board ([tblShCon]![ShConCity1]![?]) but don't get how I could retrieve the SeqNo. The same would hold true for ShConDate, as to the SeqNo.

    The Load Board is a critical function for dispatch. Any help greatly appreciated.
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Converting to Subforms (2000)

    You can create a query, or rather a series of queries that reverses the effect of "compressing" the data. The following is not a complete recipe, you will have to adapt it to your situation instead of copying it literally.

    1. Create a query qryShCon1 based on tblShCon that returns ProNo, ShConCity1 and ShConDate and perhaps other fields for SeqNo=1:

    SELECT ProNo, ShConcity1, ShConDate FROM tblShCon WHERE SeqNo = 1

    2. Create a query qryShCon2 based on tblShCon that returns ProNo, ShConCity1 and ShConDate and perhaps other fields for SeqNo=2:

    SELECT ProNo, ShConcity1, ShConDate FROM tblShCon WHERE SeqNo = 2

    3. Create a query based on tblMaster, qryShcon1 and qryShCon2.
    Create a join from tblMaster to qryShCon1 on ProNo.
    Double click the join and make it an outer join that returns ALL records from tblMaster.
    Repeat this for tblMaster and qryShCon2.
    Add the fields you need from each of the three to the query grid, and provide aliases to distinguish the city and date fields. For instance, prefix the field ShConCity1 from qryShCon2 with ShConCity2:

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Converting to Subforms (2000)

    Hans:

    Finally! Took me a while to work through it, but I did manage to get what I want - and actually understand it all. Thanks for not handing it all to me on a silver platter. I had actually worked out something similar to this, but couldn't figure out how to join it all together. Great learning experiance, thanks again!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Converting to Subforms (2000)

    Just wanted to add one last comment for anyone watching this thread (and especially if you were on the thread referenced). While the Search function is down I have been saving threads to CD so I can locate those of interest (some are quite lengthy). Just go to the top right corner of the thread and selecte Save/Print Thread, you'll get a new browser window. Go to File | Save As and select the destination (I burn to CD). Sure saves a lot of paper and you'll have all the thread ref# handy when the search function returns.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Converting to Subforms (2000)

    Good tip!

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Converting to Subforms (2000)

    Can't be taking these words of wizdom lightly ya know <img src=/S/yep.gif border=0 alt=yep width=15 height=15>.
    Treasures to be saved.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

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