passing data from a Form to a nested Form at deep 3

  • Thread starter Thread starter Tran
  • Start date Start date
T

Tran

Hello all,





We encountered a problem in the Forms. We have a Main Form with a subform
which itself has a subform (three nested forms). Our problem arises when we
open the main form (with it subforms) for the first time, when tables are
still empty, we get an error. We pass a value from the main form all the
way down to the third nested form. The third form's Record Source is set to
a query which has a value from the main form, but the query evaluates to
Null when we first try to open this third nested form, raising an error.
The precise error is tblGenericParty.HostFK is null.



The above may be sufficient for the extremely savvy reader to identify the
problem, but I give below my full table design and structure of the forms to
enable pinpointing exactly where I went wrong.



Basically, we have hosts who through various types of parties (birthday
parties, weddings) etc. So we have a host table, a generic party table
which has one-to-one relationships with specific parties.





TABLE DESIGN

tblHost

ID [PK]

HostAttributes



tblGenericParty

ID [PK, autonumber]

Host.FK [first composite natural key - required]

Date [second composit natural key - required]

AttributesCommonToAllParties



tblBirthdayParty

ID [PK, one-to-one with GenericParty.ID]

AttributesOfBirthdayParty



tblWedding

ID [PK, one-to-one with GenericParty.ID]

AttributesOfWedding



MAIN FORM

We have three levels of forms. The objective is that the user selects the
host (eg Tom Smit) and party type (eg Birthday) from two comboxes and then
within a datagrid all the birthday parties hosted by tom will appear, with
all the relevant values (date, AttributesCommonToAllParties, and
AttributesOfBirthdayParties). We therefore have to pass a value from the
combo boxes on the main form all the way down to the (3rd nested) subform.



We (try to) achieve this as follows:



Controllers of the Main Form:

cmbHost

cmbPartyType

subFormParty



Switching of the form of the subform:

subFormParty.Source Object = cmbPartyType.Value

This ensures that frmBirthday will be displayed in the subform.

The expression cmbPartyType.Value evaluates to Wedding or
Birthday etc.



Attributes of subFormParty:

subFormParty.LinkMasterField = cmbHost.Value

subFormParty.LinkChildField = tblGenericParty.Host.FK





Form Birthday (or Form Wedding) Controllers

Label

subFormBirthdayContainer



Attributes of subFormBirthdayContainer:

subFormBirthdayContainer.LinkMasterField =
tblGenericParty.Host.FK

subFormBirthdayContainer.LinkChildField =
tblGenericParty.Host.FK

subFormBirthdayContainer.Source Object = subFormBirthday





SUBFORM BIRTHDAY (or subfromWedding etc)



Attributes of subformBirthday:

RecordSource = qryBirthday



The query being an inner join of tblGenericParty and tblBirthday
(tblGenericParty.ID = tblBirthday.ID)





Controllers (fields) of subformBirthday

Date

AttributesCommonToAllParties

AttributesOfBirthdayParty



Within the qryBirthday we have the HostFK to identify the host. However,
when we first run the form for the first time, the value of HostFK is null
so someone (probably the query) throws and error that a required value is
null.





We are using Access 2000 on Windows XP.

Any help would be appreciated.

Rony Boter
 
Hello all,
Click to expand...
We encountered a problem in the Forms. We have a Main Form with a subform
which itself has a subform (three nested forms). Our problem arises when we
open the main form (with it subforms) for the first time, when tables are
still empty, we get an error. We pass a value from the main form all the
way down to the third nested form. The third form's Record Source is set to
a query which has a value from the main form, but the query evaluates to
Null when we first try to open this third nested form, raising an error.
The precise error is tblGenericParty.HostFK is null.
Click to expand...

I see two posibilities:
add On error resume next above the error line

or check bevor the line: if tblGenericParty.HostFK for nullValue

I use most of the time unbound forms and fill the recordsources on the
onOpen event of the forms to avoid such problems
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Hello ,
Yes I would like to hear more details about it .


Could you please be more specific


Rony Boter
 
Back
Top