Problems Linnking a SubForm

[Duncs]

I've got a form with a subform, but I can't get the subform displaying
the data correctly. Here's what I have...

I have two tables.

Table1 - Contains Company IDs
Only field is PPMIP
Table2 - Contains transactions against each company ID
Fields are PPMIP, CRN, TransAmt, TransDate, TransID

So, my main form has two combos on it, one for the PPMIP from Table1
and one for the CRN form Table2. When a PPMIP is selected from teh
first combo, the second combo only displays CRNs for that PPMIP.
There is also a subform on the form, that should then display all the
transaction details for the PPMIP / CRN combination.

When I select the subform, I need to set up the Child & Master field
links. However, when I try to do that, I get the message "Can't build
a link between unbound forms".

The subform has the following properties:

SourceObject - sfrm_TransDetails

The RecordSouorce for the subform is:

SELECT Table2.TransAmt, Table2.TransDate, Table2.MSN, Table2.MSN2,
Table2.TransID FROM Table2;

Can anyone tell me what I'm doing wrong and how to fix this?

TIA

Duncs

 

[Mr. B]

Duncs,

Try making Table1 as the record source for the main form. Then use the Combo
box Wizard to create a combo box to locate a record in that main form.

Then make Table2 to be the record source for your sub form. Place the sub
form in the main form and you will be able to like the two forms by the
"PPMIP" field.

The error indicates that your main form is Unbound, meaning that it does not
have a record source.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Duncs]

Sorry Mr. B, but I still can't get this working!

Table1 only contains the PPMIP field but, I need the second field on
the form--CRN from Table2--to populate only with values based on the
contents of PPMIP. I then need the subform to be filtered on the CRN.

So, the form should have the PPMIP & CRN fields on the main form.
Then, in the subform, there should be all details based on the PPMIP /
CRN combination.

If I set the main forms record source to Table1, I then cannot finid a
way to link in the CRN from Table2, which I need in order to link to
the subform.

Does this make sense?

Rgds

Duncs

 

[Mr. B]

I think you may be closer to getting what what you want than you think you are.

With a record displayed in the main form, you can make a selection from a
combo box that displays the list of CRN values and then apply a filter to the
sub form using the value selected in the CRN combo box.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Duncs]

Oh the hassle that bosses create!!

My boss has now decided on a different approach, adn I want to check
the feasibility of what he would like before I jump in.

I still have the same table:

tblTrans
-fldPPMIP
-fldCRN
-fldSerial1
-fldSerial2
-fldTransDate
-fldTransAmt
-fldCardNum

What my boss would like to do, is as follows:

The first four fields should be combo boxes. That's fine, but here is
where it gets complicated...
If someone selects an entry from the drop-down for PPMIP, which lists
a unique entry for each company, the remaining three controls should
only show only those entries that relate to the company selected.
Similarly, if any value is selected from any of the other combo boxes,
for example Serial1, the other three controls should only infomration
related to the value that has been selected, for example a Serial1
value of 123456 should only show those entries in the other controls
that relate to that serial, and so on.

Then, depending on the value(s) that are selected in the first four
combo fields, the remaining three fileds should appear in a subform,
listing all data that is relevant to the information selected. So, if
a PPMIP of "SWIT" is selected and a CRN of "5268425679542" is
selected, the subform should all transactions that relate to that
PPMIP and the specific CRN.

As regards the data, the following is true about it:

Due to how the file information is collated, all the information comes
in, in the one table
The PPMIP field is never null
The CRN field is sometimes null, and will contain duplicate values
The Serial1 field is sometimes null, and will contain duplicate values
The Serial2 field is sometimes null, and will contain duplicate values
The TransDate field will never be blank, and will contain duplicate
values
The TransAmt field will never be blank, and will contain duplicate
values
The CardNum field is sometimes null, and will contain duplicate values

So, what I need to know is:

1. Is it possible to do?
2. Are there any sample databases out there, that do something
similar, so that I can have a look and get some hints / tips?

Many, many TIA

Duncs