Easiest way for user information and user entered values to populate table?

[JAGster]

I have started creating an Access 2007 database for users to audit part number setup information entered in separate ERP system, but need advice on best design to allow for auto-populating some of the values and user-entry for other values. Need to record part info audited, auditor name, audit date, pass or fail, add'l concerns or comments.

Scope of Auditing
Performed Monthly and Quarterly
Multiple clients with unique criteria for some values
Hundreds to thousands of parts audited monthly
Tens of thousands of parts audited quarterly
Multiple useres performing audits
Criteria for two of the values audited can change
weekly (or in a some cases more often)

Manual auditing due to frequency of changes to criteria on the two values, but considering ways to automate the auditing in the future.

Working on Quarterly Auditing functionality first because that will provide most benefit.

Database has a table "Quarterly Audit" with fields populated from query that links to tables in ERP system and has criteria to pull the correct parts and part info. It also has following fields to record auditing info - "Performed By", "Date", "Pass or Fail", and "Addl Concern(s) or Comments".

I have a login form in place and am setting a global variable "gbl_User" = username. Value set during login to the database.

I have a form "frm_QuarterlyAudit" with a subform "frm_QuarterlyAudit_sub" bound to the "Quaterly Audit" table and displayed in datasheet view. My thought was that the user performing the audit could use the datasheet filters to filter by client and then by other fields to identify all parts that pass the audit, enter concerns or comments in the "Addl Concern(s) or Comments" field, select "Pass" by marking radio button in grouped controls (one radio button for Pass and one for Fail and only one can be marked at any time), and then save the form.

When the form is saved need the following fields auto-populated and saved to Quarterly Audit table for filtered records (just visible records no changes made to other records):
1) "Performed By" - username from global
variable "gbl_user", variable value set during log in
2) "Date" - system date when form saved
3) "Pass or Fail" - value saved to "Quaterly Audit"
based on radio button selected
4) "Addl Concern(s) or Comments" - text entered by user

Will this design even work? Can I save to the table fields of just the records showing after filter in datasheet view? Do you have suggestion(s) for a better approach?

My thanks in advance for your help.

[jimatqsi]

Seems to me you've got a good idea about what needs done and mostly how to do it. But I'm not sure you're aware of a few VBA features that will make this simple.

First, I don't use form filter very much, so I'm not 100% sure about whether a Recordstclone pays attention to filters or not. Instead of filtering a form I change the the Recordsource for the form when the user selects filter options. It could be that my method is slower for large database recordsets/tables, but I never see a problem with it. Let's assume for a moment that Recordsetclone pays attention to filters.

You could place an update button on your main form with a click event that would contain this code:

dim rs as dao.recordset
set rs = me.recordsetclone  ' copy current recordset
rs.movefirst     ' start at the beginning
do while not rs.EOF  ' continue to last row
   rs.edit
      rs!field1name=x   ' plug in each field values as you need
      rs!field2name=y
      rs!field3name=z  ' etc etc
   rs.update
rs.movenext
loop
rs.close
me.requery  ' make sure you're looking at current data
 

And add appropriate error handling

Is that helpful? That should be enough to get you started, then you can come back with more specific questions.

Jim

[NeoPa]

@Jim
I'm pretty sure a RecordsetClone would mirror the Recordset exactly.

As for using Filters with Forms, it's true that most Record Sources can be updated to include filtering, but life is far, far simpler when using the Filter and Filter On properties (both set automatically by the WhereCondition parameter of DoCmd.OpenForm()). A wrapper of :

SELECT *
FROM (%R)
WHERE {NewFilter}

should always work, where %R is the original Record Source of the Form, but otherwise SQL parsing is required, and that only works if the original was a SQL string.

If anything, the Filtering option might even be a little slower (but unnoticeably so), but the only time where I think changing the Record Source would have any benefit over the alternative would be where you wanted to allow user filtering but disallow them from un-applying your own filter (Piggyback Filtering).

[JAGster]

@Jim I know next to nothing about VBA. The majority of the code I am using in the database I have found in other posts or has been provided in answers to post as you have done here. For which I am very grateful.

When I click the button to run the code I get the following error "Compile error: User-defined type not defined" with line 1 highlighted.

@NeoPa I am opening the form using the OpenForm action in a macro that is set to the click event of a button on a sub switchboard. I am not doing any initial filtering, the user will be doing the filtering. Can I implement your suggestion in the macro or do I need to change from using the macro to using VBA code using the DoCmd.OpenForm()?

[jimatqsi]

I suspect line 1 is a Dim instruction, probably with some misspelling; or more likely you have to set a reference to the DAO library. In the VBA Editor, under Tools/References, select Microsoft DAO 3.6 Object library.

Jim

[JAGster]

@Jim
That was it. Now I am getting Run-time error 7951: You entered an expression that has an invalid reference to the RecordsetClone property. Highlighting line 2 of the code you posted.

[jimatqsi]

That indicates that you don't have your form bound to a recordset, therefore no clone of the recordset can be made. It could be you've only got the subform bound to a recordset.

Jim

[JAGster]

@Jim
Right again. I bound the form to the recordset and now the code you provided is populating data, but it is populating the fields in all records not just the records showing based on filter. Any suggestions?

[NeoPa]

JAGster:
Can I implement your suggestion in the macro or do I need to change from using the macro to using VBA code using the DoCmd.OpenForm()?

I do very little with macros I'm afraid (Think of a common expression using the word bargepole and you'll see where I'm coming from), so I'm not the best person to answer this, but I suspect there is a way to handle it using a macro action. I suggest you explore the options of the OpenForm action (I had a quick look in the Help and it does indeed appear to support it).

NB. I'm assuming you understand what I was suggesting - IE to use the WhereCondition parameter rather than to fiddle with the Control Source of the form.

[NeoPa]

JAGster:
I bound the form to the recordset and now the code you provided is populating data, but it is populating the fields in all records not just the records showing based on filter. Any suggestions?

Strange. I looked at Jim's code and saw nothing there that would cause that situation. Although the Help system is unclear on the matter, testing shows that the RecordsetClone member of the form does, indeed, reflect the filtering applied.

[jimatqsi]

The problem, I suspect, is that the subform is filtered and the undisplayed recordset of the main form is not filtered. The button and the click event code is on the main form. Both main form and subform have the same recordset; one is filtered, one is not.

It's getting a bit klugey, but you could do something like this:

me.filter = Me!Subform1.Form.filter

Replace "Subform1" with the name of the Subform - not the name of the form on the subform, but the subform object.

Or maybe the code needs to change

set rs = me.recordsetclone 

should be changed to

set rs = Me!Subform1.Form.recordsouceclone

Or put the button on the subform. There, 3 different ways to deal with it. One of them ought to work. :)

Jim

[JAGster]

@Jim,
Suggested Fix #1

me.filter = Me!Subform1.Form.filter
 

I entered this line of code (replacing "Subform1" with "frm_QuarterlyAudit_sub", the name of the subform) in the Update Button's On Click event (before the first line of code you supplied earlier).

I opened the form, made changes and clicked on the Update Button and got the following error: Run-time error '3197': The Microsoft Access Database Engine stopped the process because you and another user are attempting to change the same data at the same time.

I know that no one else is in the database and no other forms, queries, etc are open. I click on the debug button and the following line of code is highlighted rs.edit.

If I hit the end button in the error message popup and then click the Update Button again, it does not give me the error. But the auditor's username and audit date are entered inot all the records not just those that were showing after I filtered.

Should I be placing the new code somewhere else?

Suggested Fix #2
I took out the line of code in the first suggestion and made the change in the code in the second suggestion:

set rs = me.recordsetclone  
 

Changed to:

set rs = Me!frm_QuarterlyAudit_sub.Form.recordsourceclone
 

I opened the form, made changes and clicked on the Update Button and got the following error: Run-time error '2465': Microsoft Access can't find the field 'frm_QuarterlyAudit_sub' referred to in your expression.

I click on the debug button and the following line of code is highlighted Set rs = Me!frm_QuarterlyAudit_sub.Form.recordsourceclone
.

If I hit the end button in the popup and click the Update Button again I get the same error message.

Suggested Fix #3 - this worked
I deleted the subform and changed the form to a split form and it is now working.


Thank you,
JAGster

[JAGster]

NeoPa,
I see in the macro OpenForm macro where I can enter the code you provided in the WhereCondition parameter to automatically set the Fiter and Filter On properties.

Unfortunately, I do not understand what this does as compared to fiddling with the Control Source of the form.

Will you please expound on that?

[NeoPa]

JAGster:
Unfortunately, I do not understand what this does as compared to fiddling with the Control Source of the form.

A WHERE clause supplies filtering to a recordset. A filter applied to a form selects from those records that the form's recordset includes and filters out further. It's a bit like a second level of filtering.

The main difference is that the recordset itself can be considered as atomic. If it's a QueryDef reference for instance, you cannot get at the WHERE clause. It is considered a single item. The facility provided by the Filter property, even though it is fundamentally similar to a WHERE clause within that recordset specification, is that it enables filtering to be applied, reset, re-applied, etc to the underlying record source of the form without changing the spec of that recordset. It is also something that a user can choose generally, but the point is mainly that it's separate from the specification of the recordset itself.