First record changed when filtering subform problem

[lizymmm]

Hello,

I have a form in access 2007 that filters two subforms within it by a date. The idea is that when you change the date on the main form you can start data entry into the subforms.

When you initially open the form a specific date and it's filtered records (already in the database) are loaded into the form/subforms; I haven't chosen this date and I don't know where to look to see how Access chose to use this date. (Is it possible to view the code behind forms, which haven't been written by the user?) This wouldn't be an issue if you could then change the date to what you needed and use the subforms.

When a user changes the date on the main form to what they require, then the date of the first record in one of the subforms is changed to this date. Obviously this form isn't useable as it changes the data. Does anyone know why it is doing this and how to prevent it from occuring?

I would like the date to filter records, but not to change existing ones. I would still like to be able to edit in the forms also.

Any help would be much appreciated.

Lizy

 

[CBrighton]

The date control which you want to use to filter the records should not be a bound field. Because it's bound it's saving the change each time a user enters a date into there.

You may even want to change the form / subforms data entry property to true if you want the form to be for inputting new records only. That will ensure existing records are not displayed when the form is opened.

 

[lizymmm]

Thanks for the response,

I have tried now to filter the form using an unbound combo box, which will filter the subform, no changes to the records. However, now I am finding that I cannot add new dates to the combo box, which is something else which will not be useful for data entry. I need the user to be able to chose a date, have this populate and filter the date in the subform, and then simply add to records on this date within the subform. Does this sound feasible?

Lizy

 

[CBrighton]

One way to do that would be to have a calendar table.

Create a list of dates in Excel (after typing one or two you can select them and click & drag the bottom right of the bottom cell) then copy them into an access table.

You can then use this, or a query based on this if you want to limit the dates available, as the row source of the combobox.

 

[lizymmm]

If I did this, then would it only solve the problem for the period of time that you had created dates for. At some point in the future a user who doesn't know how access works would come across the issue that the dates they will require aren't in the database. I think it would be better if you could enter a date and use it to search in the same form?

Thanks for helping,

lizy

 

[CBrighton]

In that case abandon the combobox and use a textbox. Or just turn off limit to list on the combobox.

Eitgher of these options will allow the user the input any date they wish.