HELP!! Problem with Linking subform to Mainform

[lcalcagni]

I have several subforms by using tabs. I have fields on the mainform that they Link to: Employee name. When I go to open or view from design view it asks me to enter an employee name. That will be difficult, if someone is adding a new name. what have I done wrong. Thanks Lisa

 

[Anauz]

I had a similar problem.....here's a suggestion. I opened my form up from a btton on a MenuForm. when i loaded the form to view details i filled the dropdown box with the first name in the table ( alpabetically )
when i wanted to add new details i opend the form with a different button stating i wanted to open the form in "add mode"
DoCmd.OpenForm stDocName, , , , acFormAdd this blanks all the fields

[This message has been edited by Anauz (edited 03-20-2001).]

 

[Rich@ITTC]

Hi Icalcagni

I do not understand why you are having a problem. I have a number of tabbed forms in use, some with 7 -10 subforms (on different tabs) and there is no problem. Perhaps you have overlooked one of the key points with forms/subforms.

Your form and subform should be based on different queries/tables. The main form should be based on the main table (e.g. tblCustomer) and the subforms will look after related areas like address, invoices, etc.
The queries/tables behind the subform(s) should have their own Primary Key (PK) or ID number but also, as a foreign key, the PK from the main customers' table (e.g. CustomerID). With things like invoices you would have a one-to-many relationship between the two tables storing the data (tblCustomer and tblInvoice), so one Customer could have many invoices (so a CustomerID could appear many times in the foreign field CustomerID within tblInvoice).

The subform needs to be linked to the main form via the properties for the whole subform of Link Child Fields and Link Master Fields - both should be set to CustomerID (as this field/value appears in both tables).
Nothing else needs to be set in terms of a default value or a validation rule for the CustomerID within the subform (or table tblInvoice).

It would aslo be wise to make sure your main record is saved before entering data in the subform, so it would be better to put in the code

DoCmd.Save

in some event (perhaps in a required field like LastName - txtLastName_AfterUpdate) If there are other required fields these will need to be completed first (so this coding should go into the After_Update event, or a similar event, of the last required field).

When you enter a new record in the main form all the key fields should be completed and when the user leaves the last required field and moves onto the subform (on the same tab or on another tab) the record is saved. Once they go into the subform the linked field automatically takes up the value (so CustomerID is transferred across) and that subform can be completed and so on. The linking field (CustomerID) does not even need to appear on the subform (so long as it is there in the table or query behind that subform).

HTH


Rich Gorvin





[This message has been edited by Rich@ITTC (edited 03-20-2001).]