Form / subform / query question

[Bianca]

Hello all!

Maybe what I am trying to do cant be done - but I will ask anyhow. I have a
form with customer information on it, the subform has the accounting
information for this customer (we sell membership cards), each year we update
the membership card we enter the amount etc in the subform. One of the
fields in the subform has the customers card number - which we also enter -
what I want is that card number to show up on the other form automatically.
Basically I dont know how to do this at all - I tried to find a similar
question but I couldnt find one. Do I have to make a query? or is there
something else I have to do? Or is it that you cannot move information from a
subform to a form?
Any help would be appreciated.

 

[Rick Brandt]

Hello all!

Maybe what I am trying to do cant be done - but I will ask anyhow. I
have a form with customer information on it, the subform has the
accounting information for this customer (we sell membership cards),
each year we update the membership card we enter the amount etc in
the subform. One of the fields in the subform has the customers card
number - which we also enter - what I want is that card number to
show up on the other form automatically. Basically I dont know how to
do this at all - I tried to find a similar question but I couldnt
find one. Do I have to make a query? or is there something else I
have to do? Or is it that you cannot move information from a subform
to a form?
Any help would be appreciated.

If the card number appears as a field in the main form, why do you want to see
it again in the subform? Are you storing it in both tables? You should not be.
If the card number is data that pertains to the Customer, then the Customer
Information table is the ONLY place it should be stored. Only the linking key
field should be redundantly stored in the child table.

Now, if the card number IS the field that the two tables share then you need to
enter that into the MasterLink and ChildLink properties of the subform control.
Those properties are what cause the subform to only display data related to the
record in the parent form and they also cause those values in the parent form to
automatically be populated in any new child record created.

Since you appear to already have a relationship in those properties that is
working, then my original point stands. The card number does not belong in the
subform nor in the table that the subform is bound to. In reports or other
forms where you want to *see* the card number yes, you would make a query that
joined the two tables and then you can display data from both tables. That is
why redundantly storing data in both tables is neither necessary nor desired.

 

[Bianca]

I probably didnt explain it all - my fault. In the subform - that is where
we figure out the card number - for example if it is free we give it a
certain two digit code, the nest three digits is the sales reps number, and
the the next four numbers are the continuous number (from the last card
number) each one of these has it own field - then there is another field
where we write down the whole number.
This db is not mine - someone asked me to make this change - so that we can
have a complete picture of the customer in the original form.

 

[Rick Brandt]

I probably didnt explain it all - my fault. In the subform - that is
where we figure out the card number - for example if it is free we
give it a certain two digit code, the nest three digits is the sales
reps number, and the the next four numbers are the continuous number
(from the last card number) each one of these has it own field - then
there is another field where we write down the whole number.
This db is not mine - someone asked me to make this change - so that
we can have a complete picture of the customer in the original form.

Well that is a flawed design for sure. You should never have a field that is
made up of the data from other fields. You could just use an expression to
combine the other fields into one control and that would be automatic. It just
wouldn't be saved to the table (but it shouldn't be anyway).