Setvalue action - 2 seperate macros using same column?

[mix]

Hello.
I hope someone can help. I have 2 macros which open 2 different forms but
they reference the same column. The first macro opens the form and auto adds
my ClientID number to the new form (spouse details) using the SetValue
action, which is what I want and it works fine.
The second form (alternate address), opens but I get an error saying "You
tried to run a VB procedure that improperly references and property or method
of an object".
The macro's item value has been changed to reference the correct form name
(alternate address) and the expression is the same as for the spouse details
form. Is it failing because you cant use the same column (ClientID) in two
seperate macros??

Any help will be much appreciated.

 

[Steve Schapel]

Mix,

Well, it's difficult to say, without knowing more details of what you
are doing. For example what does the second macro do, and what are the
record sources of the two forms? But in general it is not a good idea
to have the same data being accessed by two forms at the same time. And
the need to do so is very unusual, so it would be good to know why you
are doing it like this.

 

[mix]

Hello Steve

Thanks for the reply. I was using 2 macros, 1 to update the Spouse details
form with the ClientID (other spouse's ID number in the Clients Table) and
the other macro was to do the same thing in the Alternate Address table. As
the client might have an alternate address and also might have a spouse, I
created the 2 additional tables to avoid too many nulls in the clients table.

I think there was something wrong with my relationships set-up. I had the
ClientID field in the Client Details table pointing to the Erf details table
to set up a 1-many relationship (for if a client bought more than 1
property). The erf details table had a foreign key pointing to the Spouse
table, allowing for the spouse details to be entered twice if the couple
bought more than one property (this macro works).

Then, the additional address table needed to be connected to the ClientID
field in the clients table to allow for the optional detail. This was done
with a 1 to 1 relationship (the client can only have one alternate address).
I think this is where I went wrong? So, I eventually added the alternate
address details to the Clients table to get around the problem and to
simplify the relationships.
I now add the additional address detail by calling up a query.

I hope you can make sense of my explanation. Any advice as to how to have
done this in a better way, would be much appreciated.

Thanks for taking the time out to help.

 

[Steve Schapel]

Mix,

Generally, I would not give "avoid too many nulls" a very high priority
in considering table design. I would either leave the spouse and
alternate address in the Clients table. Or, if there is a good reason
to separate them out, it would be *both* spouses, and *both* addresses.
In other words, your Clients table defines the Client as an entity,
and there is a one-to-many relationship with the Persons and the
Addresses. In the Persons table, you put each of the people involved in
that client, so if it is a couple, you have a Person record for each
spouse. And then for the data entry interface, you use a subform for
the person(s), and then the ClientID is automatically entered for each
person. Same with addresses. If you are going to separate any
aggresses out into a second table, you separate them all out. Don't
have an address in the Clients table at all. And the Addresses table
has all the addresses for any given client, and if necessary in this
table you have a field to identify each address as either principal or
alternate. Make sense?

 

[mix]

Hello

Yes, that makes sense, but this is where I cannot figure out how to set up
the relationships.... If I have the ClientID field pointing to the Erf
Details (property which is being sold/bought) table, what would i use as the
reference for the Address table? THe common thing is the ClientID. At the
moment I have the Spouse ID as PK in the Spouse Table pointing to foreign key
SpouseID in Client table. If I were to add an address table, how would I
connect it to the main table? The first line of an address is not unique
enough to use as PK in the Address table and ClientID is already being used?

Any suggestions would be greatly appreciated.

 

[Steve Schapel]

Mix,

ClientID is the primary key field in the Client table. If you have an
Addresses table, you would have a ClientID field in this table, which is
a foreign key field, and there is a one-to-many relationship between
Client and Addresses. Same with Spouses... you have a ClientID foreign
key field, and a one-to-many relationship between Client and Spouses.

 

[mix]

Hi

yes, I realise how the relationships work. What I dont know, is if you can
use Client Id field twice to reference to 2 different tables i.e. to spouse
table and to address table.

 

[Steve Schapel]

Mix,

Yes. No problem. This is very common design, where a "core" table will
be related to more than one of the other tables. In the database I am
working on at the moment, one of the tables is related via its primary
key field to 11 other tables.

 

[mix]

Hi Steve

Wow! That is great news. Your database must be so involved. I take my hat
off to you guys who have so much knowledge on this and share that knowledge
with newbies like me. I will try it!

Have a great day.