A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Optimal Batch field update of manually selected records in form



 
 
Thread Tools Display Modes
  #1  
Old January 6th, 2008, 08:16 PM posted to microsoft.public.access.forms
M.
external usenet poster
 
Posts: 18
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:28 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.