I'm sorry but I'm still confused. There is only one place where these subforms reside, and it's on what you call the "main form" which is also the one with the two buttons on it to "create a new customer" and "open existing customer"?
The "New Customer" form has no subforms? but some how you return to the "main form" after you add a new customer and then the "main form" has the new customer information displayed in its subforms?
Sorry I'm having a hard time wrapping my arms around this--it's not a typical set up so I'm trying to understand.
In any case, you'd make duplicate tables of your table structures (don't include the data) for any tables which are used in subforms. These temp tables would become the recordsources of your forms/subforms. Make some sample forms/tables for this to try it out first.
So say someone is entering a new record. You'd make an unbound form, which essentially is the same as your current "New Customer Form" but take out the recordsource and remove the control sources of all the controls. Put a button that says SAVE on it on the form. In the OnClick code, you'd put something like:
Code:
'Confirm user wants to add data
if msgbox("Are you sure you wish to save this data?",vbYesNo,"Confirm Addition") = vbNo then exit sub
'Add record to table
Dim rs as dao.recordset
set rs = currentdb.openrecordset ("Select * from tCustomer")
rs.addnew
rs!CustomerName = me.txtCustomerName
rs!CustomerAddress = me.txtCustomerAddress
rs!CustomerPhone = me.txtCustomerPhone
rs.update
set rs = nothing
msgbox "Done adding " & me.txtCustomerName & "!"
'Clear all boxes by opening the form in ADD mode
Docmd.OpenForm "frmMain",,,,acFormAdd
Then say you are on your form where you are looking at an existing record and the related subforms. The subforms have recordsources of the temp tables. The Main form though is unbound (no recordsource) and the fields get filled up in the OnOpen event of the main form. In the button on the Open Customer form, don't put any where clause. Just say to open this form. The first bit of code goes to the tCustomer and fills the form with data for the customer you've chosen.
Code:
dim intCustomerID as integer
intCustomerID = Forms![Open Customer]![CustomerID]
dim rs, rsAsset, rsAssetTemp, rsOther, rsOtherTemp as dao.recordset
set rs = currentdb.openrecordset ("Select * from tCustomer where CustomerID = " & intCustomerID
me.txtCustomerName = rs!CustomerName
me.txtCustomerAddress = rs!CustomerAddress
me.txtCustomerPhone = rs!CustomerPhone
set rs = nothing
'Fill temp tables with related data; first delete any related data that might be left in them
currentdb.execute ("Delete * from tAssetTemp where CustomerID = " & intCustomerID)
currentdb.execute ("Delete * from tOtherTemp where CustomerID = " & intCustomerID)
'-----------------
set rsAsset = currentdb.openrecordset ("Select * from tAsset where CustomerID = " & intCustomerID
set rsAssetTemp = currentdb.openrecordset ("Select * from tAssetTemp")
rsAssetTemp!CustomerID = intCustomerID
rsAssetTemp!Asset = rsAsset!Asset
rsAssetTemp!Field2 = rsAsset!Field2
rsAssetTemp!Field3 = rsAsset!Field3
set rsAsset = nothing
set rsAssetTemp = nothing
'-----------------
set rsOther = currentdb.openrecordset ("Select * from tOther where CustomerID = " & intCustomerID
set rsOtherTemp = currentdb.openrecordset ("Select * from tOtherTemp")
rsOtherTemp!CustomerID = intCustomerID
rsOtherTemp!Field1 = rsOther!Field1
rsOtherTemp!Field2 = rsOther!Field2
rsOtherTemp!Field3 = rsOther!Field3
set rsOther = nothing
set rsOtherTemp = nothing
In the VBA code window, from the menu you have to pick TOOLS+REFERENCES and make sure that Microsoft DAO is picked. Choose the highest version (probabaly 3.6). I didn't test the code above, but take a look and see if you can decipher it.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
