Tab forms with multiple subforms

[kkpen]

Hi, I have a form that has 3 tabs on it, 2 of the tabs have subforms on them. Using Access 97

tab 1 is general bill to data: bill to number, name, address, phone ext.
tab 2 is a parent listing: it shows for a company all the subcompanys along with amount of sales and commissions.
tab 3 is a ship to tab: basic shipping info, also bill to company can have multi ship to entrys.

Heres the problem: I add a new record to the bill to tab, and then append the same data to tab 3 subform (because the first ship to address is always the bill to companys) this works.

but on tab 2 which contains the parent companys subcompanys representative and sales and commissons. I have another append qry that takes the bill to name and appends it to another table that contains the parent - subcompanys data. After all info is typed into tab1 you then click on another tab and it automacticly runs the qrys and then go's to tab 2, but there is no data displayed? If you look at the table at this point the data is there, If you manualy refresh the screen it will show up after 1 or more refreshes. I have coded a Me.refresh into the afterinsert event and i know it works because the info on tab 3 shows.

Thanks for anyhelp

 

[Rob.Mills]

I'm a little confused by the relationships between the forms...

Have you tried using Me.requery? Or better yet, the parent listing, is this a subform on tab2?

 

[kkpen]

Sorry heres more info,

The form is add / edit customer. tab 1 contains bill to info and that is in a bill to table,
tab 2 is the customers parent info which contains the parent company and then the sale info, such as territory, division, commission, commission rate and customer service rep. this is its own table.

tab 3 contains the ship to info that is in a ship to table.

When you make a new bill to customer I want a new ship to and parent company entrys made. So that every bill to has a shipto and parent company. Then focus goes to the parent tab were you enter in the territorys and commission info. But the parent name will already be there so you just have to make the other selections. and that is were the I have to refresh multi times to see the info. If you close the form and reopen it and goto the record you just added and go to the parent tab the parent name is there.

all the tables are related on the bill to number.

yes the parent tab is in a subform, i have deleted it and made the subform again using the wizard to ensure i have done the parent/child correctly.

here is the code for the after insert event along with the rem'd out lines that i have tryed.

-----------------------------------

Private Sub Form_AfterInsert()
On Error GoTo Err_Form_AfterInsert

DoCmd.OpenQuery "qryAppendtblParent"
DoCmd.OpenQuery "qryAppendtblShipToAddresses"
Me.Refresh
'Forms![frmCustomer]![subfrmCustomerParent].SetFocus
'DoCmd.GoToControl Forms![frmCustomer]![subfrmCustomerParent].Forms![Territory]
'Forms![frmCustomer]![subfrmCustomerParent].Forms![Territory].Dropdown

' DoCmd.GoToControl "subfrmCustomerParent"
'DoCmd.GoToControl "Territory"
'Forms!frmCustomer!subfrmCustomerParent.Requery
'Me.Refresh

'= "Forms!frmCustomer![BTNumber]"
'Forms!frmCustomer!subfrmCustomerParent.Form![ParentNo] = "Forms!frmCustomer![BTNumber]"


Exit_Form_AfterInsert:
Exit Sub

Err_Form_AfterInsert:
MsgBox Error$
Resume Exit_Form_AfterInsert

End Sub

 

[Rob.Mills]

Instead of Me.Refresh try:

Me![Parent Subform].Form.Requery

And also, don't take this the wrong way (just want to be sure) but does your code in the module have apostrophes at the beginning of all those lines? It looks like most of your code is comprised of comments.

 

[kkpen]

no, i just rem'd out the lines that i was trying, those will all go away when i get the correct coding.

 

[kkpen]

tried Me![Parent Subform].Form.Requery
and removed the me.refresh it works now for tab 2 but now tab 3 info does not show only

 

[Rob.Mills]

The subform on tab3; what's it linked to? And does it need to be requeried also?

 

[kkpen]

tab 3 subform is the ship to addresses, yes it needs to be requeryed also so i added another line and used the same command but changed the form name after the Me!
but now only tab 3 is requeryed and tab 2 is blank again.

Also i noticed if i add 2 new customers in a row using the Me![Parent Subform].Form.Requery for tab 2 on the second add it will not show

Thanks for all your help.

 

[Rob.Mills]

No offense, but this is giving me a nice chuckle this morning.

Would you mind emailing me a copy of your db? It just sounds like something else is going on that's not very obvious.

 

[kkpen]

Thats good if i can make someones day a happy one. I am no expert in programing access as you can see, and some of the problem is me trying to communicate the info correctly.

The way its setup would be hard to email to you, The tables are linked to the gui side, and some of the tables data is linked to a unix box for the data. (the tables are 20mb also)

This form is a redesign on an exsiting db and will replace it if it works.

should this be used on a diff event than afterinsert ?

Thank you for all your help though.

 

[Rob.Mills]

Well, I'll try to keep helping you then. First off, what's the recordsource of the main form and it's primary key?

Then what's the sources for each subform and their keys. How are the relationships setup (one to many?)? And what are the master and child links for each subform?

 

[kkpen]

the main form rec souc is tblbillto

tab 2- subfrmparent rec souc is a qry with fields from tblbillto and tblparent with the criteria of Forms!Frmcustomer![BTNumber] on the BTNumber and Parentno fields

tab 3 -subfrmcustomershipto rec souc is a qry with fields from tblshipto


the 3 tables are:
tblshipto - keys btnumber and stnumber
tblbillto - key btnumber
tblparent-keys btnumber and parentnumber

tblbillto has a 1 to many to tblshipto on the btnumber key
tblbillto has a 1 to many to tblparent on the btnumber key

 

[kkpen]

just tried the following code:


DoCmd.OpenQuery "qryAppendtblParent"
DoCmd.OpenQuery "qryAppendtblShipToAddresses"
Me.Refresh
Me![subfrmCustomerParent].Form.Requery

and tab 2 has data and tab 3 is now showing data (because of the me.refresh)

but if i go to add another record it will not work (add 1 and exit ok, add 2 and the 2nd one will not show.)

is it the afterinsert ?

 

[kkpen]

were it works 1 time and not 2 in a row is there a problem with the button that adds the new record?
heres the code:

DoCmd.GoToRecord , , acNewRec
Me![BTNumber].SetFocus


do i need to have the button requery or do something else when clicked again. (if i enter a new record then goto other exsiting records, then try and enter a new record it will not work, if i close the form and then open it, it works, that leads me to think that "is there a reset or something for the button". for when i don't close the form.)

 

[kkpen]

Thank you for all your help rob.

 

[kkpen]

Figured it out, the form was refreshing all the time. The field that i was looking at was a combo box. I redid the subform and before i changed the field to a combo box, i tried it again and it worked several times, then changed it to a combo box and used the following code:
--------------------
DoCmd.OpenQuery "qryAppendtblParent"
DoCmd.OpenQuery "qryAppendtblShipToAddresses"
Me.Refresh
Forms!frmCustomer.Form!subfrmCustomerParent.Form.Refresh
DoCmd.GoToControl "ParentCompany"
-----------
now it works all the time.
Thank you everyone for your help

 

[Rob.Mills]

Good to hear you got it figured out.