Data Entry Form and Subform problems

  • Thread starter Thread starter Freeflyer
  • Start date Start date
F

Freeflyer

Hi,

I have a form and subform that I am trying to use for data entry. However,
it's not working out as planned.

I have two tables:
tblAccount
[AccountID] (PK)
[DefaultAddressID]
....more fields
tblAddress
[AccountID] (Combined PK)
[AddressID] (Combined PK)
.... more fields

tblAccount holds Account records including the default address to be used in
all transactions, although an account can have more than one address.
tblAddress holds all the addresses for all accounts with a combined primary
key of AccountID and AddressID.
tblAccount.AccountID is an autonumber field.
tblDefaultAddressID is a text field.

My main form has controls to allow the user to enter all the relevent
information for a new account. My subform has controls for a new address.
What I would like to happen is for the user to enter the new account, enter
an AddressID into tblAccount.defaultAddressID and then enter the address
details in the subform. However, whenever I try to type in the subform I
receive an error message saying "The LinkMasterFields property setting has
produced this error: ‘Object or class does not support the set of events’"
The Account record is created ok, the address record is not.

Link Master Fields is AccountID, DefaultAddressID
Link Child Fields is AccountID, AddressID

I think I need to capture the DefaultAddressID when it is entered and use
this to create the address record and populate the primary key fields, but
I'm not sure how.

Any ideas would be appreciated.
 
Hi - i am unsure as to why you have set your tables up this way - i have a
database that works in a similar way but they are linked purely by companyID
being present in both tables, i then open up a form to enter an additional
address, close it and refresh the subform that has a list of multiple
addresses in row order that are directly linked to the main account. Is this
what you are looking to do, if so then the fault lies in your table design
 
Hi,

Thnaks for your help, I think I've confused the issue here. The tables are
linked by the AccountID as you suggested they should be. Each Address record
contains the AccountID that it relates to and a text AddressID to identify
the address. These two fields combined form the Primary key for the address
table.

Therefore, I've now changed the links to:
Link Master Fields is AccountID
Link Child Fields is AccountID

The user needs to enter Account information on the main form and then enter
the AddressID and details in the subform. (The DefaultAddressID was a red
herring, I just need to copy the subform AddressID before saving the records
so that the account has a default address to use for any transactions)
However, when I try to enter address information I still receive the error
message "The LinkMasterFields property setting has produced this error:
‘Object or class does not support the set of events’". If I ignore this and
continue it will create an address record but the AccountID is 0.

So, essentially it looks like the AccountID which is created when the main
form is dirtied is not being carried over to the subform. How can I force
this?

Rhys Davies said:
Hi - i am unsure as to why you have set your tables up this way - i have a
database that works in a similar way but they are linked purely by companyID
being present in both tables, i then open up a form to enter an additional
address, close it and refresh the subform that has a list of multiple
addresses in row order that are directly linked to the main account. Is this
what you are looking to do, if so then the fault lies in your table design

Freeflyer said:
Hi,

I have a form and subform that I am trying to use for data entry. However,
it's not working out as planned.

I have two tables:
tblAccount
[AccountID] (PK)
[DefaultAddressID]
...more fields
tblAddress
[AccountID] (Combined PK)
[AddressID] (Combined PK)
... more fields

tblAccount holds Account records including the default address to be used in
all transactions, although an account can have more than one address.
tblAddress holds all the addresses for all accounts with a combined primary
key of AccountID and AddressID.
tblAccount.AccountID is an autonumber field.
tblDefaultAddressID is a text field.

My main form has controls to allow the user to enter all the relevent
information for a new account. My subform has controls for a new address.
What I would like to happen is for the user to enter the new account, enter
an AddressID into tblAccount.defaultAddressID and then enter the address
details in the subform. However, whenever I try to type in the subform I
receive an error message saying "The LinkMasterFields property setting has
produced this error: ‘Object or class does not support the set of events’"
The Account record is created ok, the address record is not.

Link Master Fields is AccountID, DefaultAddressID
Link Child Fields is AccountID, AddressID

I think I need to capture the DefaultAddressID when it is entered and use
this to create the address record and populate the primary key fields, but
I'm not sure how.

Any ideas would be appreciated.
Click to expand...
Click to expand...
 
If you want the accountID on the subform to match up to the accountID already
created on your main form then set the default value of the accountID field
on the subform to reference the accountID on the main form. To do this,
right click on the accountID field on the subform, go to properties, go to
the data tab and in the default value enter:
forms!yourmainformnamegoeshere!accountID - this will populate the accountID
with the required value that should ensure that the master and child fields
link properly,

HTH,

Rhys.

Freeflyer said:
Hi,

Thnaks for your help, I think I've confused the issue here. The tables are
linked by the AccountID as you suggested they should be. Each Address record
contains the AccountID that it relates to and a text AddressID to identify
the address. These two fields combined form the Primary key for the address
table.

Therefore, I've now changed the links to:
Link Master Fields is AccountID
Link Child Fields is AccountID

The user needs to enter Account information on the main form and then enter
the AddressID and details in the subform. (The DefaultAddressID was a red
herring, I just need to copy the subform AddressID before saving the records
so that the account has a default address to use for any transactions)
However, when I try to enter address information I still receive the error
message "The LinkMasterFields property setting has produced this error:
‘Object or class does not support the set of events’". If I ignore this and
continue it will create an address record but the AccountID is 0.

So, essentially it looks like the AccountID which is created when the main
form is dirtied is not being carried over to the subform. How can I force
this?

Rhys Davies said:
Hi - i am unsure as to why you have set your tables up this way - i have a
database that works in a similar way but they are linked purely by companyID
being present in both tables, i then open up a form to enter an additional
address, close it and refresh the subform that has a list of multiple
addresses in row order that are directly linked to the main account. Is this
what you are looking to do, if so then the fault lies in your table design

Freeflyer said:
Hi,

I have a form and subform that I am trying to use for data entry. However,
it's not working out as planned.

I have two tables:
tblAccount
[AccountID] (PK)
[DefaultAddressID]
...more fields
tblAddress
[AccountID] (Combined PK)
[AddressID] (Combined PK)
... more fields

tblAccount holds Account records including the default address to be used in
all transactions, although an account can have more than one address.
tblAddress holds all the addresses for all accounts with a combined primary
key of AccountID and AddressID.
tblAccount.AccountID is an autonumber field.
tblDefaultAddressID is a text field.

My main form has controls to allow the user to enter all the relevent
information for a new account. My subform has controls for a new address.
What I would like to happen is for the user to enter the new account, enter
an AddressID into tblAccount.defaultAddressID and then enter the address
details in the subform. However, whenever I try to type in the subform I
receive an error message saying "The LinkMasterFields property setting has
produced this error: ‘Object or class does not support the set of events’"
The Account record is created ok, the address record is not.

Link Master Fields is AccountID, DefaultAddressID
Link Child Fields is AccountID, AddressID

I think I need to capture the DefaultAddressID when it is entered and use
this to create the address record and populate the primary key fields, but
I'm not sure how.

Any ideas would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Thanks again for your help, but I'm still not quite there. I do need the
accountID on the subform to match that on the main form. However, when the
form is first opened there is no accountID on the main form as it is an
autonumber field. I presume I need to copy the AccountID across once it is
generated (OnDirty?) but for some reason I can't seem to get this to work.
It's driving me mad as I'm sure this should be really simple functionality to
implement.

Rhys Davies said:
If you want the accountID on the subform to match up to the accountID already
created on your main form then set the default value of the accountID field
on the subform to reference the accountID on the main form. To do this,
right click on the accountID field on the subform, go to properties, go to
the data tab and in the default value enter:
forms!yourmainformnamegoeshere!accountID - this will populate the accountID
with the required value that should ensure that the master and child fields
link properly,

HTH,

Rhys.

Freeflyer said:
Hi,

Thnaks for your help, I think I've confused the issue here. The tables are
linked by the AccountID as you suggested they should be. Each Address record
contains the AccountID that it relates to and a text AddressID to identify
the address. These two fields combined form the Primary key for the address
table.

Therefore, I've now changed the links to:
Link Master Fields is AccountID
Link Child Fields is AccountID

The user needs to enter Account information on the main form and then enter
the AddressID and details in the subform. (The DefaultAddressID was a red
herring, I just need to copy the subform AddressID before saving the records
so that the account has a default address to use for any transactions)
However, when I try to enter address information I still receive the error
message "The LinkMasterFields property setting has produced this error:
‘Object or class does not support the set of events’". If I ignore this and
continue it will create an address record but the AccountID is 0.

So, essentially it looks like the AccountID which is created when the main
form is dirtied is not being carried over to the subform. How can I force
this?

Rhys Davies said:
Hi - i am unsure as to why you have set your tables up this way - i have a
database that works in a similar way but they are linked purely by companyID
being present in both tables, i then open up a form to enter an additional
address, close it and refresh the subform that has a list of multiple
addresses in row order that are directly linked to the main account. Is this
what you are looking to do, if so then the fault lies in your table design

:

Hi,

I have a form and subform that I am trying to use for data entry. However,
it's not working out as planned.

I have two tables:
tblAccount
[AccountID] (PK)
[DefaultAddressID]
...more fields
tblAddress
[AccountID] (Combined PK)
[AddressID] (Combined PK)
... more fields

tblAccount holds Account records including the default address to be used in
all transactions, although an account can have more than one address.
tblAddress holds all the addresses for all accounts with a combined primary
key of AccountID and AddressID.
tblAccount.AccountID is an autonumber field.
tblDefaultAddressID is a text field.

My main form has controls to allow the user to enter all the relevent
information for a new account. My subform has controls for a new address.
What I would like to happen is for the user to enter the new account, enter
an AddressID into tblAccount.defaultAddressID and then enter the address
details in the subform. However, whenever I try to type in the subform I
receive an error message saying "The LinkMasterFields property setting has
produced this error: ‘Object or class does not support the set of events’"
The Account record is created ok, the address record is not.

Link Master Fields is AccountID, DefaultAddressID
Link Child Fields is AccountID, AddressID

I think I need to capture the DefaultAddressID when it is entered and use
this to create the address record and populate the primary key fields, but
I'm not sure how.

Any ideas would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Hi Freeflyer, im confused because you say that accountID on the main form is
not displayed when you open the form. But it is an autonumber field,
therefore that is precisely what it is, and indeed should be doing, when you
open the form to create a new entry it will automatically generate an account
number for you - is this happening?
if not then i would do the following, enter some information on the main
form so that teh auto number account field populates, and then on that field,
enter code on the on exit event to save the form and populate the sub form
with the account number:

e.g. on exit event of a field on main form
DoCmd.RunCommand acCmdSaveRecord 'saves the record, ensures accountID is
filled in
forms!subformname!accountID = me.accountID 'ensures that accountID on the
sub form is equal to the value entered on to the main form

Hi,

Thanks again for your help, but I'm still not quite there. I do need the
accountID on the subform to match that on the main form. However, when the
form is first opened there is no accountID on the main form as it is an
autonumber field. I presume I need to copy the AccountID across once it is
generated (OnDirty?) but for some reason I can't seem to get this to work.
It's driving me mad as I'm sure this should be really simple functionality to
implement.

Rhys Davies said:
If you want the accountID on the subform to match up to the accountID already
created on your main form then set the default value of the accountID field
on the subform to reference the accountID on the main form. To do this,
right click on the accountID field on the subform, go to properties, go to
the data tab and in the default value enter:
forms!yourmainformnamegoeshere!accountID - this will populate the accountID
with the required value that should ensure that the master and child fields
link properly,

HTH,

Rhys.

Freeflyer said:
Hi,

Thnaks for your help, I think I've confused the issue here. The tables are
linked by the AccountID as you suggested they should be. Each Address record
contains the AccountID that it relates to and a text AddressID to identify
the address. These two fields combined form the Primary key for the address
table.

Therefore, I've now changed the links to:
Link Master Fields is AccountID
Link Child Fields is AccountID

The user needs to enter Account information on the main form and then enter
the AddressID and details in the subform. (The DefaultAddressID was a red
herring, I just need to copy the subform AddressID before saving the records
so that the account has a default address to use for any transactions)
However, when I try to enter address information I still receive the error
message "The LinkMasterFields property setting has produced this error:
‘Object or class does not support the set of events’". If I ignore this and
continue it will create an address record but the AccountID is 0.

So, essentially it looks like the AccountID which is created when the main
form is dirtied is not being carried over to the subform. How can I force
this?

:

Hi - i am unsure as to why you have set your tables up this way - i have a
database that works in a similar way but they are linked purely by companyID
being present in both tables, i then open up a form to enter an additional
address, close it and refresh the subform that has a list of multiple
addresses in row order that are directly linked to the main account. Is this
what you are looking to do, if so then the fault lies in your table design

:

Hi,

I have a form and subform that I am trying to use for data entry. However,
it's not working out as planned.

I have two tables:
tblAccount
[AccountID] (PK)
[DefaultAddressID]
...more fields
tblAddress
[AccountID] (Combined PK)
[AddressID] (Combined PK)
... more fields

tblAccount holds Account records including the default address to be used in
all transactions, although an account can have more than one address.
tblAddress holds all the addresses for all accounts with a combined primary
key of AccountID and AddressID.
tblAccount.AccountID is an autonumber field.
tblDefaultAddressID is a text field.

My main form has controls to allow the user to enter all the relevent
information for a new account. My subform has controls for a new address.
What I would like to happen is for the user to enter the new account, enter
an AddressID into tblAccount.defaultAddressID and then enter the address
details in the subform. However, whenever I try to type in the subform I
receive an error message saying "The LinkMasterFields property setting has
produced this error: ‘Object or class does not support the set of events’"
The Account record is created ok, the address record is not.

Link Master Fields is AccountID, DefaultAddressID
Link Child Fields is AccountID, AddressID

I think I need to capture the DefaultAddressID when it is entered and use
this to create the address record and populate the primary key fields, but
I'm not sure how.

Any ideas would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top