Is Update Query right solution?

[JennyL]

hi

I have a form called "frm_LevelExportCIN" - in this form, there are six command buttons:

Select All CIN Fees


Delete All CIN Fees
Select Product A fees
Select Product B fees
Export
Close


When I used the VBA to select the fees under each button, it was moving through each record and then check the ones "specified" in the VBA codes. I ended up using the "update query" which means which user click on Select All CIN Fees button, the update query check off all record fields called "export". When user click on "select product a fees", the update query only update the ones specified under the update query criteria. The purpose of this command is so that my Export button will only export the ones checked.

So far it is working fine, but I am just not sure if this is the best way to achieve these types of selections. Any recommendations?

Thank you!

[June7]

It will work fine as long are there are not multiple simultaneous users doing this same process. If there are, they will conflict.

Why would you need a check field if you have criteria that can be used to filter records? The same criteria used to determine which records to check should be valid filter criteria.

[JennyL]

It will work fine as long are there are not multiple simultaneous users doing this same process. If there are, they will conflict.

Why would you need a check field if you have criteria that can be used to filter records? The same criteria used to determine which records to check should be valid filter criteria.

This database will be split into front end and back end so multiple users will be using the database.

frm_LevelExportCIN is a filtered form based on previous form's selection, and it is a continuous form. what's the best way to select multiple items with split database? Do you have any examples that I can look at?

[June7]

Apply filter criteria to field that has value "Product A".

Do the same for the other criteria when selected.

Review: http://allenbrowne.com/ser-62.html

[JennyL]

ummm..I am bit lost on this one. The Allen Browne option is to filter the form, but what i am trying to achieve is to automatically check off the "export" field for records when following command buttons are clicked:

Select All CIN Fees
Delete All CIN Fees
Select Product A fees
Select Product B fees
Export
Close

Are you recommending to have users manually click on each record after it's filtered?

[June7]

No, I am not.

I am suggesting use code to build filter criteria that can be applied to report then export report. If you must export a query object, that is entirely different issue. And then yes, can use an UPDATE action to set the 'export' check of records meeting the criteria. However, as already noted, multiple users doing this export at same time will conflict. Accommodating multiple users with this method customarily requires use of a 'temp' table in frontend - table is permanent but records are temporary. Code would save record IDs into this table then include this table in query with INNER JOIN to restrict selection of data records. The temp table would be purged with DELETE action before each process run. This way users will not conflict when flagging records for selection.

[JennyL]

Unfortunately I have to export the records into excel so that another department can use the excel to import into another system.

Does UPDATE query conflict if multiple users work on different fee schedules? I guess i could let users know that they can't use same fee schedules at the same time, which usually they don't, because most of the times customers are assigned so they only work on their own customers' schedules. But again, there is no "rules" preventing them not to do it.

Do you have an example of how "temp" table has to be designed. It seems to make sense but haven't done this before.

Thanks,

[June7]

Design of temp table depends on situation. For yours, probably just need 1 number field to hold the IDs.

A very simple report can often export nicely to Excel.

[JennyL]

are you referring to a temp table in another database?

is there no other ways in Access to accomplish this?

If I warn users to not update same feescheduleid at the same time, is that going to eliminate the issue?

[June7]

Temp table would be located in the frontend.

Alternatives:
1. dynamic parameterized query
2. export a simple report as already described
In both cases, filter parameters would be supplied via textbox or combobox as shown in the Allen Browne tutorial

Maybe, I don't really know your db structure nor the reliability of users to follow rules.

[JennyL]

Is this considered as parameterized query?

Below is the screenshot of my update query and my VBA code behind the command button to select all fees.

Private Sub cmdSelect_Click()
On Error GoTo Err_cmdselect_Click

With DoCmd
.SetWarnings False
.OpenQuery "Update_SelectAllCIN"
.SetWarnings True
End With
Me.Refresh
Exit_cmdSelect_Click:
Exit Sub

Err_cmdselect_Click:
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Exit_cmdSelect_Click

End Sub

[June7]

Yes. It shows static as well as dynamic parameters.

Criteria under the FeeScheduleID and FeeLevel fields (and any others that are necessary) should adequately restrict record selection. Why complicate with a yes/no field and the issue of user conflict?