Viewing information in the subform

[Natasha]

Hi

I have a form with customer info in it and a subform with cooler
information. Each customer can have nore than one cooler. How do i view the
cooler info automatically when a customer is selected.

 

[Klatuu]

You should have two tables. A customer table with customer demographics in
it and a cooler table that has cooler specific information in it. The cooler
table should have a field that contains the primary key value of the customer
it belongs to.

Your form should be for viewing and editing data in the customer table. On
your form you need a subform control. Two properties of a subform control are
Link Master Fields and Link Child Fields. These two properties are used to
relate the records in the sub form to the record in the main form. In the
Link Master Fields, you put the name of the field in the table or query you
are using as the Record Source of the main form that uniquely identifies the
customer. In the Link Child Fields, you put the name of the field in the
table or query you are using as the subform's record source that identifies
which customer the cooler belongs to.

Then you need the subform for the coolers. Don't confuse a subform control
with a form. A subform control is a control on a form. The Source Object
property of the subform control identifies the name of the form that will
appear in the control.

So once you have created a form to show the coolers (usually a datasheet or
continuous form), a main form to show the customers, and a subform control on
the main form that identifies your cooler form and set the Link field
properties in the subform control, each time you change which customer you
are looking at, their coolers will display in the subform.

 

[Natasha]

Hi

I already have that done and is working but when i go to add a new cooler
say, i select the company and all the company info comes up in the main form,
but nothing comes up in the subform which has the cooler info. I have to add
the new cooler and then go back to the record before i get all cooler info.
What i want is when adding a new cooler select the customer, the customer
info comes up and i want all the coolers info to come up and then add the new
cooler to that list. Basically on a new record the cooler info does not come
up when i select a customer, it will only come up on an old record.

Natasha

 

[Klatuu]

I don't think I quite understand, let me see if this is correct.

If you move to a customer record in the main form that has existing cooler
records, the existing records do not show up in the subform.

If you move to a customer record in the main form and add a new cooler
record in the subform, then move away from the customer record and go back to
it, then all the previous records and the new record are displayed.

Please correct the above statements if there are errors in my understanding.

Also, what is the Data Entry property of the form and the subform?

 

[Natasha]

Hi

When i open the form it automatically goes to a new record which is blank.
I select Company the company details show up, this information is on the main
form. ( If that company already has coolers i want this information also to
come up at the moment it doesn't.) so i put the cooler information in for the
new cooler and add the record. At the botomof the form on the left there are
nav buttons to scroll through existing records, when i scroll back over
existing records i get company information and the information on all the
coolers that are in that company. My problem is i can't get this information
on a new record and i want to. I hope this explains it properly. Thanks

Natasha

 

[Klatuu]

Can't get information on which new record? the company or the cooler. Check
your form and subform and be sure that neither of them have the Data Entry
property set to Yes.

 

[Natasha]

both have data entry set to no. When the form is opened it is blank. I
select a company name and then the address ect are automatically filled in.
The cooler fields in the subform are blank. I want these to show
automatically too.

 

[Klatuu]

Why is it showing as Blank. If a form has its Data Entry property set to No,
the normal behaviour would be for the first record in the form's record
source to show when the form opens. There are three ways it shows as blank.
The Data Entry property is Yes, there is some code (probably in the Open
event) that is forcing it to a new record, or something in the OpenForm
method that is opening the form is forcing it to a new record.

If the Link Field properties are set in the subform control are correctly
set, then I would suspect that whomever originally created the forms is
manipulating what is being displayed. I would start examining the code where
the form is opened and the code in the form and subform.

 

[Natasha]

Right there was code telling the form to open on a new record, i did that coz
i wanted that to happen, i have taken it away. Now when opening the form
the first record shows up. Say its Customer1 with address1 and phone no1 ect
on the main form and cooler1 info in the subform. Say i want to add a new
cooler to customer7 and customer7 already has 3 coolers. If i select
customer7 from the companyname combo box address7, phone no7 ect go into the
fields automatically fine. Cooler 1 info remains in the subform. I want
cooler7 info to automatically show in the subform at the same time as the
address7 and phone7 info shows and then i want to be able to add the new
cooler info in the subform. I need to be able to see the existing coolers
before i add a new one. I know its a problem between the main form and the
subform but i don't know what it is.

 

[Klatuu]

How are you moving from one customer to another?
This is puzzling, because if you have the Link Fields properties set up
correctly and each cooler record has a field that tells which customer it
belongs to and that is the field you are using the Link Child Field property,
there should be no reason for this to happen.

The reason I ask is that if the form opens on customer 1 with the correct
cooler records showing, but when yo move to customer 7 an his records don't
show, there is something in the way you are navigating records or with how
the customer and coolers are linked.

 

[Natasha]

I want two ways to move between customers the first is to move using the
navigation arrows at the bottom, but this is timeconsuming coz if i want to
go to customer 314 i have to go through 314 records to get to it the, but
this way works. If i use the arrow to to go to customer314 i will get all
the details for that costomer. The other way is to select the customer from
the customer combo box which should also show all the info for customer314
but it doesn't show the cooler314 info , it stays at the cooler1 info. Two
tables tblservicehis and tblinstallsubform both linked by CustomerID. On
the subform control i have CustomerID from tblservicehist in Link master
field and CustomerID from tblinstallsubform in Link child field.

 

[Klatuu]

It all sounds correct. Since the main form was originally set to open to a
new record, is it possible the same is true of the subform?
There is something going on somewhere. Check all your code and properties.
If the form opens with the first record correctly, then the linking and
relations are okay. As soon as you try to move to another record, it starts
failing, so something is going on in your code that is creating the problem.

 

[Natasha]

There is no code for the subform and there is code for just the 4 buttons on
the main form Add, Delete, Close and Search I don't know what else to look
for.

 

[Klatuu]

I have no idea what could be happening. I did some testing, but could not
reproduce your problem. My suggestion would be to create test form and
subform using the same recordsets as what you have now, and see what the
results are.