Link to home
Start Free TrialLog in
Avatar of Bernard Thouin
Bernard ThouinFlag for Switzerland

asked on

Problem with DB update in Access tabbed form

Hi

I have a very simple and very irritating problem: I have a data capture form with a number of tabs, each tab corresponds to one table in my database. One tab has only 3 fields on it, and all of these can be set either by a fixed default value or a default value which I fetch by VBA code from another table in the DB when the user clicks on that tab and that field is empty.

The problem with that tab is that, if the user does not change anything because the 3 fields contents are correct, Access does not store the field data in the database, unless one at least physically clicks in one of the fields. I've tried everything, using docmd.runcommand accmdsaverecord, me.dirty, even faking a click in one field by VBA code to force a save, but to no avail. Even though I set one of the fields with a value through VBA code, Access just doesn't insert a new row for that data. Tab change also doesn't help. I still need a real user interaction to get the data saved. However, I want the data to be saved even without user interaction. How do I do that ?

Thanks for help
Bernard
Avatar of danishani
danishani
Flag of United States of America image

Hi Bernard,

On the Before Update event of your Form, you check if these particular fields are Null using the IsNull() function. If so, you either fill them up with a Value, or you restrict the user to fill these, to setfocus on these fields.

Another approach is set these fields Required = yes in Table design, so user needs to enter these fields.

HTH,
Daniel
Avatar of Bernard Thouin

ASKER

Hi Daniel

I fill the fields actually directly on the tabctl.change event, checking if they are set or not. But the problem is not there. The problem is that, if the user does not touch these fields because their values as set by my code are correct, the data does not get stored in the DB.
Avatar of Richard Daneke
Could a OnDirty event trigger a short DoCmd.Save for this tabbed control?
Yes, I understand, so thats why you need to force the user by going to this TabControl or set as Required.

Another possible solution to store the data with code; check IsNull() Then store ...

HTH,
Daniel
@DoDahD: The subform on the tab in question does NOT get dirty, as it shows 2 DefaultValues and a value set by VBA code, so there is no OnDirty event triggered, and a docmd.runcommand acCmdSaveRecord doesn't do anything either because the form is not dirty. I could not find a DoCmd.Save attribute which fitted my situation (acTable, maybe ?) so I didn't try that one.

@danishi: I hoped I could do without code to store...
ASKER CERTIFIED SOLUTION
Avatar of Richard Daneke
Richard Daneke
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi

I actually, after a long time not working on the app, came back to the problem, and did ... exactly what DoDahD suggested (without looking at the trail of answers), and it worked of course fine. I don't know why I ddin't think of it before, I'm doing the same on at least 2 other tabs... Silly me. Anyway, thanks for the help of all of you guys.

Bernard