Optimal Batch field update of manually selected records in form

M

M.

I have a table with bank account records (M_Mutation). I import them
periodically from my bank.

I would like to manually assign a category to each account record for my
personal bookkeeping, e.g. mortgage, salary, electricity costs, groceries,
etc.

Since it takes a lot of time to select the category for each record, I
decided to design a form that displays only those records without a category.
In this form I'd like to select multiple records with the same category and
update them batchwise with their respective category field value.

What I did now was to display those records in a listbox lbxMutations
(multiselect on), select them and use the first colum which contains the
unique ID field value for each record to update the records in the table with
the selected Category value in the combobox cbxCategory:

Dim ctl As Control
Dim myItem As Variant
Dim strSQLbase As String
Dim strSQL As String

Set ctl = Me.lbxMutations
strSQLbase = "UPDATE M_Mutation " & _
"SET M_Mutation.Category = """ & Me.cbxCategory.Value & """ " & _
" WHERE M_Mutation.Mutation_ID = "

With Application
.SetOption "Confirm Action Queries", False

For Each myItem In ctl.ItemsSelected
strSQL = strSQLbase & CStr(ctl.ItemData(myItem)) & ";"
DoCmd.RunSQL strSQL
Next myItem

.SetOption "Confirm Action Queries", True
End With
Set ctl = Nothing
Me.lbxMutaties.Requery

Question1: Is this the best way to solve this problem or do you have better
suggestions?

Question2: Is there a batchwise SQL statement possible that can contain all
Mutation_ID values in the WHERE statement? This would mean that up to 100
long integer (autonumber) values would have to be included. Is it allowed
that the SQL statement string will end up so long?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top