If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Optimal Batch field update of manually selected records in form
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? |
Thread Tools | |
Display Modes | |
|
|