• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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
0
bthouin
Asked:
bthouin
  • 3
  • 2
  • 2
1 Solution
 
danishaniCommented:
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
0
 
bthouinAuthor Commented:
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.
0
 
Richard DanekeAdjunct FacultyCommented:
Could a OnDirty event trigger a short DoCmd.Save for this tabbed control?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
danishaniCommented:
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
0
 
bthouinAuthor Commented:
@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...
0
 
Richard DanekeAdjunct FacultyCommented:
Once you set the value by VBA, could you not save the record?
This would not require the user to view the tab, if the user viewed the tab, the record would display.  You don't mention if the values could be updated, but that consideration would be available.   No extra process is needed since you have just set the field values.  
Do you need to consider the impact of recording a record not viewed by the operator?
0
 
bthouinAuthor Commented:
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now