Data corruption: Selecting a record in subform overwrites value in parent form (1 Viewer)

[GregP]

Hi guys,

I have a form with a combo box, and when you select an item from this combo box it displays all the records that match that item in a subform. All OK so far. Trouble is, once you've selected an item and the matching records are displayed, when you then click in the subform the first item in the list in the combo box is overwritten with the item name whose records you're presently viewing.

DB is in the attachment. Here's the method to reproduce the fault:
1) open frmInspectionsByItem
2) Click the combo box and note the first item is Other: Have you reviewed your previous workplace inspection and CAR?
3) Select from the list (for example) Lighting: Light fittings OK. The matching records are shown as intended
4) Click a record in the subform
5) Click the combo box again and note that the first item has changed from Other: Have you reviewed... to Lighting: Light fittings OK. Hey presto!

The same behaviour happens with frmInspectionsByRoom where the first room number is overwritten.

Do any of you smart people out there have an idea as to why this is so? The list in the combo box shouldn't be able to be overwritten by the subform. I've spent a reasonable amount of time playing with various configurations but nothing's made any difference. Could be something in the relationships which are pretty loose at the moment.

Any help greatly appreciated!
Greg

 

[georgedwilkinson]

You are modifying the first record in the query since the form is "bound" to the underlying query and the DDL is bound to that field. Nothing strange going on here.

This is not how you filter a result set. You need to use unbound controls to filter and write just a small amount of VBA code in the unbound combo change event. Either that or just use the built in Access filters.

 

[GregP]

Thanks George. I originally tried to make this form with the form wizard but it didn't give me the option of creating a subform during the wizard process. So I had to end up designing it from scratch and manually adding the subform, which seemed to work until I hit this.

Must admit my greenness to the deep ins and outs of Access - how might I go about implementing what you've suggested? Perhaps the built in filters is the easiest way to go about it but I could do with a few tips.

Thanks!
Greg

 

[georgedwilkinson]

All you're trying to do is filter by Item and Date (sic), right?

First, rename the column "Date" and all the related controls to something more descriptive that is not an Access reserved keyword.

Create a new form based on the query:

SELECT qryInspectionResults.Item, qryInspectionResults.Room, qryInspectionResults.Notes, qryInspectionResults.Date FROM qryInspectionResults;

Do everything else you did with the combo-boxes, etc. on your subform (i.e. get it the way you want it).

Tell the main form to display in Datasheet view (see the Default View property) and also Allow Datasheet View.

Make sure you see ALL of the data from the query. Right click on the field you want to filter on and type in your criteria. So, to only see "Lighting: Light fittings OK" just type that into the filter box. You can shorten it with wildcards, so "Lighting*OK" would give you the same filtered set. You can use the same approach on the other fields you want to filter on. If you want to re-filter on any of the fields that are already in the filter, you need to clear the filter first.

I applaud your efforts so far. I'd just like you to know that your design is not very conducive to easy form manipulation (like using filters). But that is a discussion for another time. To see my view on this (and the views of some smart people, too), see this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=146332