Problem with subform and main form

[dromorkid]

I have a table tblJobInfo with a field in it called InsuranceID. This is not the KeyField, but it is related to another table tblInsuranceInfo who's keyfield is InsuranceID. The two other fields are related elsewhere and it is a kind of spiders web, but once I have the answer to this, the rest will unfold. So lets say the other fields where named CertificateHolder and AdditionallyInsured.

On my main form, which is based of tblJobInfo I have a section where I need to enter the Insurance Information. This information can be associated with more than one job, hence the need for a seperate table, tblInsuranceInfo.

My main problem is creating a subform to do this. I'm sure I will be, and should be directed to a guide to creating subforms, but if someone could directly answer my question that would be great.

I get to the field InsuranceID (from tblJobInfo which has datatype of Number). This field is empty. Here lies my problem I know. So I click into the subform I have created which includes the fields InsuranceID, CertificateHolder and AdditionallyInsured, all from tblInsuranceInfo. InsuranceID is an autonumber. When I tab out of there try to type the Certificate Holders name, I get the infamous 'You can't assign a value to this object' message.

I know I need to create the InsuranceID in tblJobInfo first but I thought it would be created when I created a new a record in tblInsuranceInfo.

If you can make head or tail of that, I would appreciate your assistance.
Thanks

 

[dkinley]

Hey.

Let us review .... your tables are set up like ....

tblJobInfo
----------
JobInfoID
InsuranceID
JobName


tblInsuranceInfo
---------------
InsuranceID
CertificateHolder
AdditionallyInsured

And in your form bound to tblJobInfo you want to be able to click on the appropriate InsuranceID (from tblInsuranceInfo).

Now, if there is one and only one InsuranceID for each JobInfoID, then you place a combo box on the form. You can use the wizard and it should get the mechanics of it correctly. Have it look up values in a table (tblInsuranceInfo) and get all 3 fields. Have it store the key column (InsuranceID), a.k.a. 'bound column', in your InsuranceID of JobInfoID.
And have it hide the key column.

The end result is your users will see the combo box, click it and be presented with the CertificateHolder and the AdditionallyInsured columns. You can manually change the cosmetics of these columns in the combo box properties by column widths 0";1";1" (where the 0" column is the InsuranceID field).

If you are having to store more than one InsuranceID for each job then we can look at subforms.

That help?
-dK

EDIT: If you are needing to present more information than described above, we can still look at a subform for this purpose. Let us know.

 

[dromorkid]

Hey.

Let us review .... your tables are set up like ....

tblJobInfo
----------
JobInfoID
InsuranceID
JobName


tblInsuranceInfo
---------------
InsuranceID
CertificateHolder
AdditionallyInsured


All of the above is accurate. But I'm not trying to click on anything. I do understand where you are going with the combo box, and know how to create that and its functions but its not what I am aiming to do.

When I'm creating the 'NewJob' I want to create the InsuranceInfo at the same time by placing a subform in the mainform FormNewJob, calling the subform FormInsuranceInfo. The error occurs when I don't have an InsuranceID already created in tblJobInfo, therefore I can't create the new record for InsuranceInfo.

I'm not making it clear, sorry. I have figured a solution to the problem that will allow me to do this. I realize I have to have a InsuranceID in the table JobINfo before I can create a InsuranceInfo record that is related to the particular JobID I'm creating.

Answer me this. I make InsuranceID in tblJobInfo an autonumber, therefore when I move through FormJobInfo, InsuranceID.tblJobInfo is assigned a value, then having my subform included, the InsuranceID.InsuranceInfo will be created and be related to the JobInfo table.

I'm sorry, I'm not good at explaining. Do you see doing it this way posing any problems?

 

[dromorkid]

It only allows 1 autonumber per table, theres my answer. Ok, so back to the subform method. Any ideas where to go with this?

 

[dkinley]

I think I get it now ...

1. frmJob opens up as a new job and in it is a section (not concluding it's a subform yet) with Insurance Information which is blank at this time.

2. The user puts in some information about the new job and then puts in some information about the Insurance for that job.

This could go a bazillion ways here ... but assuming ...

3. They put in some info in that section and it automatically creates new insurance information in tblInsuranceInfo.

Off the top of my head worse case I see is ...

Duplicates. For instance, in one job they put in "ACME, Inc." as the certificate holder, but in another job a different user (or original user forgets input scheme) they put in "ACME, Incorporated" or "ACME, Inc". Now you have ACME running around with two, three, upteen different InsuranceIDs.

This could be cured easily by mapping the JobInfoID to insurance but sometimes you want to reuse the InsuranceID on another job - so we aren't fulfilling requirements.

Perhaps you want the user to pick and choose if that certificate holder already exists (to meet reusability requirements). Then the user will utilize the existing information for them in tblInsuranceInfo provided they can do a proper search for the correct Certificate Holder.

For simplicity, I would have another form where the user updates tblInsuranceInfo and possibly use the combo box method on the Job form. Alternatively, have the combo box sitting on the Job form and if a particular Certificate Holder didn't exist in the combo box, a button right underneath that says 'Add New Insurance' will spawn the aforementioned form to add a new Certificate Holder.

For complexity, I can see entering the information into a section (or the subform) of the Job form and having it do some fancy logic to see if that name already exists but then you are back to how the user enters the name (unless you really good at fuzzy logic and name/spatial recognition algorithms) for the searching and going 'Aha! We have a company with a similar name, would you like to use it?'. Although if you hotkey the combo box it will do narrowing down as you type in it.

Apologies, I am back to the combo box method. Not telling you what to do or think - I just keep painting myself back to that methodology.

If this chain of events is correct, perhaps someone else can help out on some (better) creative ideas?

-dK

 

[dkinley]

Well, you could just put in a simple subform with all the Certificate Holders and let them scroll through it and add as necessary.

Apologies, I was thinking something a little more secure - but I am still back to duplicate entries for the Certificate Holder if you have many, many of them in the tblInsuranceInfo. They skip the one because they have spent 10 minutes scrolling or what-have-you and their mind starts to drift.

For starters ... this link: http://www.functionx.com/ is great for tutorials and examples. If you would like you can check out how to add the aforementioned subform and if you are still having problems let us know.

-dK

 

[dromorkid]

I'm with you all the way with the combo box method. Totally works for that part. I guess I have to explain the spiders web part of this to highlight the problem further.....

So I create a combo box to lookup CertificateHolder names in tblInsuranceInfo.CerticateHolder, if they don't exist (Not in List) open form attached to tblCertificateHolder and add a new Certificate holder and all their info. This works great and does what I want it to do. Now I have created the InsuranceID for tblJobInformation, it is linked to the record in tblInsuranceInfo.

Remember, tblnsuranceInfo has three fields;

InsuranceID
CertificateHolderID
AdditionallyInsuredID

We've taken care of CertificateHolderID now and their information by creating the combo box to add the details. But the AdditionallyInsured field is another problem. I can't have this linked to CertificateHolderID because even though the CertificateHolder can be used again, they won't necessarily have the same AdditionallyInsured names attached. So this is a different table again. tblAdditionallyInsured.

The fields for this are
AdditionallyInsuredID
AdditionallyInsuredNameID

The reasoning here is because if you remember from my first post earlier ( http://www.access-programmers.co.uk/forums/showthread.php?p=735188#post735188 ) Each Job has Additionally Insured people on it, and these can change all the time. Also the number of Additonally Insured people on each job can change. So I have a further table to store individual names tblAdditionallyInsuredNames. Its all about populating the KeyFields for these tables. Do I create combo boxes here again? I'm so confused now. I'm pretty sure I need to create a subform for tblAdditionallyInsuredNames, because I will be entering anunknown amount of names. Right? Can you explain a good way of doing this. If your head is as fried as mine, don't worry about it. Thank you for your help thus far!

 

[dkinley]

Naw ... you just have to get the bones of it correct up front so you don't fry your mind trying to get sensible, cohesive information out of later =]

You might have been staring to close and may have been correct but the you only presented part of the problem so the proposed solution might have been incorrect.

Let's get the bones sorted and then see what can be done about it. I've attached what I think you are saying is your table structure. Correct?

Note: I've added tblNames since you specified a NameID and didn't say where that was coming from.

-dK