Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link master fields (a2003)

    I have a parent form (frmConsumer) calling a popup form (frmOrder). The popup form has a tabbed control with a subform(fsubAddrHmOrder).

    I've wound up changing the subform's linking field (lngConsumerID) to a field not native to its host form (frmOrder). Is there a way to keep the subform relationship?

    --Can I place an unbound text box on frmOrder and have it pull the value of the field needed from frmConsumer, it's parent form?

    --Or perhaps change the Link Master Fields to reference the field needed, something like [frmConsumer].Form![lngConsumerID]

    --Or am I SOL and need to establish the relationship between the two forms another way?

    Eli

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

    Re: Link master fields (a2003)

    If lngConsumerID is not part of the record source of frmOrder, what it the purpose of a) popping up frmOrder from frmConsumer and [img]/forums/images/smilies/cool.gif[/img] having a subform on frmOrder that does include lngConsumerID?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Images Attached Images

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    organizational address
    Attached Images Attached Images

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    I think I'm twisting things up in knots and making this much harder than it is. I'd like the tabbed form attached to display the appropriate addresses, if they are in the database. And, if they are not, I want to be able to click a "cmdSame as work" or "cmdSame as home" on the pg and have the other addresses fill in. I've attached the relationships for both the organizational and work addresses.

    Can anyone suggest a strategy?

    E
    Attached Images Attached Images

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

    Re: Link master fields (a2003)

    According to the relationships in your screenshots, lngConsumer is a field in tblOrder. I assume that tblOrder is the record source of the popup form frmOrder. So you should be able to link the address subform(s) to frmOrder by lngConsumer. Or am I completely off?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    No, I'm the one that was completely off. I was getting completely twisted up in setting up the subforms because they were not displaying data that I knew was in the database.

    The problem was the subforms loading before there was a value in the linking criteria. I was sure there was something wrong with the record source or the linking field and in trying to "fix" it I was creating problems where none existed and becoming extraordinarily stupid in the process! Now that I'm requerying the subforms after the linking criteria is there, I can feel my IQ making a slow turn around.

    What I'm working on now is getting the "Bill to" and "Ship to" subforms load the copy the values from either the Work or Home subforms depending on whether I click a "Same as home address" cmdBtn or a "Same as work address" cmdBtn. Can you point me to the most efficient was to set it up? I fear making a complete hash of it...again.

    Chagrined and Abashed

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

    Re: Link master fields (a2003)

    The answer depends on whether you already have a blank record for Bill To and Ship To, or you need to add a new record. And we need to know where the table(s) containing these addresses fit into the overall structure (I can make a guess, but it's better to know for sure.)

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    I don't believe I already have a blank record. Clicking on the tab shows you a blank subform but nothing has been entered, so no record created, right?

    I have one table for home addresses that uses the consumer ID as the FK and another table for organizational addresses that uses the organization ID as the FK. All the forms/subforms use one of these two as the record source.

    You'll see the relationships in the graphics above. I couldn't figure out a way to create a single address table to handle both situations. If there's a better way, I'd love to learn it.

    E

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

    Re: Link master fields (a2003)

    But in which table(s) are the Bill To and Ship To addresses going to end up, and how are they related to the rest?

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    If Ship to or Bill to fills with Home address info it will end up in tblAddrHm. If Ship to or Bill to fills with Work address info it will end up in tblAddrWk. Both table have a field that allows the select ion of the type of address; ship to and bill to are both options in both tables.

    Currently there is no PK/FK linkage tblAddrHm and tblAddrHm

    E

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

    Re: Link master fields (a2003)

    I don't understand this setup. Why would you let the Bill To address end up in a different table depending on whether it is a home or work address?

    What if the same address is to be both Bill To and Ship To address?

  13. #13
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    Maybe I'm building a house on a bad foundation. Let me back way up...I built two address tables because the work address linked to the organization linked to the consumer (see the organizational graphic above) while the home address linked directly to the consumer without reference to the organization (diagram above the organizational). Did I go wrong here? Is there a way to create a single table with these relationships? Is there a better way to define the relationships?
    E

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

    Re: Link master fields (a2003)

    I think there is some confusion about what goes where.

    Your relationships pictures show a table tlkpAddrHm linked to tblConsumer, with home addresses. This would imply that a consumer can have multiple home addresses. These addresses are properties of the consumer; that is fine.

    Your relationships pictures also show a table tlkpAddr linked to tblOrg, with work addresses. So an organization can have multiple addresses. These addresses are properties of the organization; fine again.

    The Ship To and Bill To addresses should be properties of the order. If there can be multiple Ship To addresses for a single order, you should have a table with addresses linked to tblOrder. I don't see why you would have separate "home ship to" and "work ship to" tables, as far as I can see that would complicate things unnecessarily, but perhaps you have a special reason for it.

    Similar for the Bill To addresses.

    If an order can have only one Ship To address and one Bill To address, I would either put these addresses in tblOrder itself, or create a table linked to tblOrder with two records per order: one for Ship To, one for Bill To.

  15. #15
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link master fields (a2003)

    I just experienced a cognitive shift...Duh, of course the ship to and bill to addresses should link to the order. That helps immensely.

    I'll hold the "same as home address" "same as work addresses" command buttons piece of the discussion until I get your thoughts on the home and work address tables, see below.

    Circling back to the home and work addresses: I don't really like having two address tables. The reason for creating a separate address table in the first place was because consumers could have multiple addresses. However, once I've split the address table into two tables, hm and wk, I've pretty much got two one to one relationship. Do you see a way to consolidate my two address tables into one? Or, would you recommend folding the home address table into the consumer table and the work address table into the organization table?

    E

    PS - Is there a good way to split an single order between ship to and bill to addresses or is the key two separate orders. I've run into this kind of situation several times in different circumstances, including payments, when I'd like to split a single payment between several different orders. (Should I make this a new thread rather than complicating this one?)

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
  •