Results 1 to 4 of 4
  1. #1
    stillearnin
    Guest

    Further re: Post 65676, updating more than one tbl (Access 2000 )

    I followed this post carefully but have a slightly different problem using a similar design. As Charlotte had advised, I created my tblComp, tblAddresses. tblContacts, as well as tblAcctNumber, tblCompName, etc. and made the junction/relation tbls for each to designate the m-m relationships (one comp can have many addresses, many contacts, (contacts many addresses), many acct numbers, many names (it is a rather volatile business <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>!).

    I then created a main frm using tblComp, then created qrys for the join between names, acctnumbers, addresses and created the fsubs. The master/child for all is the compID. If you start out on the main frm and enter the [original date entered into database] (obviously not the real field name!), then you can go to each of the fsubs and enter a name, an acct num, etc. But knowing that the users will NOT follow a specific sequence, I tried to enter a company name without entering info into the main frm. Since the main frm is empty, it won't allow me to do that because there is not a compID (main frm not dirty yet). I'm pulling my hair out trying to figure out if I've got the structure wrong, or if there is some secret code (of which I know minimal) that needs to be used to make the main frm automatically get its pk filled in if an entry is made in the fsub.

    I hope someone understands my question and can give me a clue as to how to make this work. Thanks for reading this long post!!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Further re: Post 65676, updating more than one tbl (Access 2000 )

    If the subform is linked to the parent form on related keys in each recordset, then you can't enter a record in the subform without a parent record existing first. Your structure is OK, it's your logic that's getting confused.

    Don't try to create the new company record from a subform. Put the company name information on the parent form where it will allow you to create a new company record and will provide the new link for the subforms.
    Charlotte

  3. #3
    stillearnin
    Guest

    Re: Further re: Post 65676, updating more than one tbl (Access 2000 )

    Charlotte, Thank you for your reply. My logic is VERY convoluted right now! The fsubCompName, fsubCompAcctNum are in tabular form so that the user can have access to all the names that this company has gone by in the past. Deletes are not allowed, because previous names are tied to an order form that may need to be used again, especially if the company is involved in non-payment or litigation (they were operating as Comp123, Comp123 is now in a legal jam, so they now start opertating again as CompXYZ). When they change their name, the users change the company acctnum as well because that's what is used in their separate accounting program. My befuddled logic is telling me that if they want to enter a new company that has never done business with them, then they may just have to be presented with a form that starts out with them entering only the original date that they are entering this info. Then maybe use a command button to open another layer of fsub for them to enter the compname, acctnum., etc.

    I hope I haven't confused this more, 'cause I've got enough confusion for everyone. I'm going to think on this tonight and see if I see things differently in the am, reposting as I try to make this work. Or perhaps have a separate form for entering the initial comp info, and another for chaning names/accnum/etc. I KNOW I can get this worked out!! Again, thanks for your reply.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Further re: Post 65676, updating more than one tbl (Access 2000 )

    OK, I didn't realize that tblCompName contained a name change history. However, that means that company name belongs only in the tblCompName table, and that means that you should be OK as long as you're adding a new name for the same company. If you want to add a whole new company, you'll need a button on the main form that will trigger the creation of a new parent record and will save that record so that you can go on. A popup form for that is certainly a possibility.
    <hr>fsubCompName, fsubCompAcctNum are in tabular form<hr>
    I'm not sure what you mean by this. Do you mean you're presenting a continuous subform or that you have the subforms on a tab control?
    Charlotte

Posting Permissions

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