Form produces error after creating query from 3 tables

[avalon60]

As the title says I have 3 tables:

tblMain
BalanceID PK
GasID FK
ElecID FK

tblGas
GasID FK? not sure
BalanceID

tblElectric
ElecID FK? not sure
BalanceID

I created 1 query from all 3 tables, then created the form from that 1 query.
Now when I enter data into the form and click to save it , it produces this error message:
The Microsoft Database Engine cannot find a record in the tblGas with key matching fields 'tblMain_GasID.

The tblGas does contain a field GasID, but does it mean that the field should be tblMain_GasID

What have I missed.

 

[GinaWhipp]

How are those tables related to each other? Because the error message is saying it can't create a record on the many side because the one is missing. It also indicates that GasID had to be a PK in one of those table, probably tblGas and you are adding the record in tblMain.

 

[avalon60]

How are those tables related to each other? Because the error message is saying it can't create a record on the many side because the one is missing. It also indicates that GasID had to be a PK in one of those table, probably tblGas and you are adding the record in tblMain.

Apologies, I made a bit of an error when I posted my initial message:
This is how I related the 3 tables

tblMain
BalanceID PK
GasID
ElecID

tblGas
GasID PK
BalanceID

tblElec
ElecID PK
BalanceID

tblGas is related to tblMain by GasID
tblElec is related to tblMain by ElecID

I am only guessing that I have something mixed up somewhere.

 

[GinaWhipp]

What I said still applies... you cannot add a record in tblMain (GasID) without having a related record in tblGas where the PK is.

 

[avalon60]

What I said still applies... you cannot add a record in tblMain (GasID) without having a related record in tblGas where the PK is.

Yes I see what you mean now but as all 3 tables are in 1 query so to speak, and the the form is created from the said query, the GasID field is in/on the form.

Anyway, how do I get a related record in tblGas. Do I change the PK in tblMain to GasID?

Thanks

 

[avalon60]

Yes I see what you mean now but as all 3 tables are in 1 query so to speak, and the the form is created from the said query, the GasID field is in/on the form.

Anyway, how do I get a related record in tblGas. Do I change the PK in tblMain to GasID?

Thanks

Ok I think I have now solved this.

After doing some checking on the PK's, and then closing down the db. Then reopen it, and in the main form when a date or any data is added, some data MUST be added to both the Gas and Elec parts of the same form.
I then saved it without any error message, and the same data was there in the relevant fields. At least I hope or think that was the problem.

 

[GinaWhipp]

Yes, but I have to wonder why you have tblGas as part of the query as you already have the FK in tblMain which is all you need.

 

[avalon60]

Ok, I have just created another query using just the tblMain, and although the GasID and ElecID are also in the said query, when the form is loaded up it only shows records from tblMain. There are text boxes for both GasID and ElecID but no others, which is kind of obvious in one way.

So doing this I have not now included either tblGas nor tblElec, but also there are no records of either tables/queries on the form.
Is there a way of getting or seeing those records from just GasID etc, which might be called Child records??
I've just done an 'on click ' event for both GasID and ElecID to bring up a subform.

 

[GinaWhipp]

I'm thinking you can use Combo Boxes to show those records BUT you said child records so NOW I am concerned about your data structure. Please tell me a little about what you are trying to do.

 

[avalon60]

I was only sort of just thinking about Child forms, as many years ago I did use them for one db I built, but moved on a bit since then.
We get out dual fuel bills monthly , where they consist of an overall usage and payment on one page, then on a second page, all the figures are clearly broken down so that we can see how much gas we have used etc etc. There is actually quite a bit of detail on that second page which I want to enter into the db.

So, the tblMain has the overall payments and usage etc, and both tblGas and tblElec will show the broken down payments and usage for each utility.

At the moment, I am just playing around with different options which I can think of.

 

[GinaWhipp]

Oh, then forget Combo Boxes, use Subforms to display the detailed information.

 

[avalon60]

Oh, then forget Combo Boxes, use Subforms to display the detailed information.

Yes it does look like subforms is the only way to go with what I want.

Thanks

 

[avalon60]

Oh, then forget Combo Boxes, use Subforms to display the detailed information.

Thanks, and that is what I have ended up doing. On both the GasID and ElecID fields, I have created an 'onclick' event so that the relevant subform opens up.