Form - Subform Link? Maybe??

[BillH]

I'll do my best to clearly describe this - any help would be GREATLY appreciated!

2 tables: Qualification and ClientInfo.

2 forms: Qualifier (main form - from Qualification) and QualifierSub (subform - from ClientInfo)

Qualifier contains fields to enter general info about a particular client including "ReqNumber" and a "Client Number" drop-down which pulls all of the client numbers from the ClientInfo table.

QualifierSub contains fields such as "Insureds", Inforce premium", etc. which are auto-populated based on the client number selected (all corresponding data is in the ClientInfo table). This is accomplished via a query called ClientDetail - based on the ClientInfo table.

The query contains all of the fields from the ClientInfo table and the criteria for the ClientNumber field is [Forms]![Qualifier]![ClientNumCombo].

HERE'S THE PROBLEM:

The subform works fine (populates the fields based on the Client Number selected from the drop-down) however, I cannot seem to link this subform to the main form.

I tried adding "ReqNumber" to the ClientInfo table (and QualifierSub form) and tried to link but the subform would not work properly (wouldn't auto-populate).

I removed ReqNumber from the QualifierSub and the fields auto-populate but the client detail isn't saved with that particular record.

This seems like some logical/simple functionality that Access would handle (although, every time I say that it turns out to be super difficult).

PLEASE HELP!!!

 

[RichMorrison]

Main froms with subforms are used to manage one-to-many relationships.

What is the relationship between Qualification and Client ?

Does one Client "have" many Qualifications ?
Does one Qualification "have" many Clients ?

Or if there is one Client to a Qualification, then why have a subform ?

You need to figure this out first.

RichM

 

[BillH]

Hi Rich,

There would only be a single client per qualification (one client to a qualification).

I think (although I'll have to go back and check) I built this as a subform because the data that is auto-populated is based off of a query.

Could I accomplish this without using a subform?

Thanks very much for replying,

Bill

 

[RichMorrison]

Bill,

OK, there is one client per qualification.
Is there one qualification per client ?

If the answer is yes, then you seem to have a one-to-one relationship. Then you really don't need a subform.

Anything you need to do to "autopopulate" data can be accomplished with one form.

HTH,
RichM

 

[BillH]

Thanks Rich and Pat.

Rich - The form is basically a data-entry form. Each record (or qualification) would only contain a single qualification for a single client.

So, I guess it is a one-to-one relationship but I am using 2 separate tables. "Qualification" is for the data-entry and "ClientInfo" contains client specific information for all clients and is only used as a lookup to autopopulate those fields on the form (ideally/hopefully).

Can I still autopopulate these fields using a single form?

Sorry if all of this is elementary - just when I think I'm getting it, something throws me for a loop.

Thanks again,

Bill

 

[RichMorrison]

Bill

<<
So, I guess it is a one-to-one relationship but I am using 2 separate tables. "Qualification" is for the data-entry and "ClientInfo" contains client specific information for all clients and is only used as a lookup to autopopulate those fields on the form (ideally/hopefully).
>>

Sorry, that makes no sense to me. "Client specific information for all clients" sounds impossible.

RichM

 

[BillH]

Hi Rich,

The ClientInfo table looks like this:

ClientNumber ClientName Insureds Premium
101 Smithson 235 $48,000
102 IBM 12000 $102,000
103 ATK 190 $22,000

There are approximately 300 total records in this table. When a user opens the form to enter information, they would click the drop-down menu, select the appropriate ClientNumber from the list, and the corresponding info (ClientName, Insureds, Premium) would autopopulate fields on the form.

Does this make sense?

Thanks

 

[RichMorrison]

No Bill, it still does not make sense.

If all the data fields for one client - Client Info and Qualification - were in ***one*** table, would that cause a problem ??

If so, what is the problem ?

RichM

 

[BillH]

Hi Rich,

I finally had some time to get back to this and re-evaluate exactly what I'm trying to do here (from your obvious points - which made complete sense). Not sure why I built it this way initially.

Anyways, I included those fields into the same table and then figured out how to auto-fill the textboxes - no problem!

Just wanted to say thanks for your help.

Bill

 

[RichMorrison]

Bill,

You are welcome and good luck.

RichM