Subform not allowing further data entry

[leahb747]

I have a pre-existing access form that I am trying to get working again.

It's fairly basic - the user selects the Contract No from the drop down menu and all of the resources and their allocated rates are displayed for that Contract in a subform. My issue is that the subform will not display an additional row to allow further data entry. Any ideas would be greatly appreciated

TIA.

Leah

 

[leahb747]

I have found my issue - the query that the subform is based on is non-updateable. This query is made up of two tables and another query (qryCurrent Resources).

It's this query (qryCurrent Resources) that is also non-updateable and I'm assuming causing all the problems. The SQL code is as follows

SELECT tblResources.ResourceID, tblResources.ResourceDesc, tblRates.Code, tblRates.CodeDesc, tblRates.Unit, tblContracts.Contract
FROM tblRates INNER JOIN (tblResources INNER JOIN (tblContracts INNER JOIN tblResourceAllocations ON tblContracts.Contract=tblResourceAllocations.Contract) ON tblResources.ResourceID=tblResourceAllocations.ResourceID) ON (tblRates.Contract=tblResourceAllocations.Contract) AND (tblRates.Code=tblResourceAllocations.Code)
ORDER BY tblResources.ResourceID;


Anyone? Please?

 

[AccessOfEvil]

Well you sort of answered your own question...the underlying query is the problem.

It is hard to say exactly what the problem is without knowing the overall structure of the database, but a good thing to keep in mind is that subform should really just represent a one-to-many relationship (with the main form being the one and the subform being the many).

If you are joining 4 tables (Rates, Resources, Contracts, and ResourceAllocations) it is an indication (not a hard-and-fast rule, just something to consider) that you might be trying to work with too much data at once so to speak. You might think of what you really need to be editing in the subform vs maybe just displaying (which you could do with a listbox for example) and simplify.

Also, you implied that the form used to work. You could retrace your steps as far as what you added in and try to figure out where the complexity got to the point of not allowing updates.

Sorry to be vague, but hope that gives a couple ideas.

 

[leahb747]

I sort of get where you're going with that but I'm unsure of how to change it. This database is 10 years old and may have worked at some point but I'm just going through and fixing it currently.

In the form, the user needs to select the applicable contract.

The subform will have a combo box that selects the resourceID, resource description. These are entered into a new resources form so they can be selected. This is stored in tblResources

The next bit over is the unit of measurement, standby, breakdown and operating costs, start and finish dates all of which are hand entered and are stored in tblRates.

How do I minimise the number of tables I require?

 

[leahb747]

Never mind. I went back and changed my subform record source to be a table not a query. Works fine, just makes it a bit more awkward