Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Batch" Editing?

Status
Not open for further replies.
Woodman650

Woodman650

Technical User
Jan 20, 2005
92
0
0
US
Hey guys,
I was just wondering... I've got about 300 records that I need to update one field to the same value. I was wondering if there was a way to edit all of them at once?

Ideally, I've got all the records displaying in a multi-select listbox on a form... you can select 35 records or so you want to edit a certain field for, and I can just type the value into a textbox, hit "append" and have all the records updated with that value. is this doable? it would be a lifesaver. thanks guys!
 
Sort by date Sort by votes
Have a look at the ItemsSelected collection of the ListBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
yeah... an update query... probably. just not sure how to structure the code or where to begin. PHV, let me take a look at what I can find about ItemsSelected collection... thanks!
 
Upvote 0 Downvote
so, I'm not great at code... maybe someone could help me out here... =D

so on event...(button click)
[Form].lstname.ItemsSelected (something)
value selected in combobox, append to record
go to next selected object in listbox...
append combobox selection to record...etc, loop

hmmm...
 
Upvote 0 Downvote
Is this just a one time update? If so, never mind using the form. Just create a query, run it, and you're done. Use the wizard to help you create an update query.


Randy
 
Upvote 0 Downvote
these textboxes will update hoe many records? how does each box relate to a record? by what criteria?
 
Upvote 0 Downvote
Woodman650,
Here is a combination of your concept (recomended by [navy]PHV[/navy]) in conjunction with [navy]addy's[/navy] input.
Code: 
Private Sub Command2_Click()
Dim itmSelected
Dim strInClause As String
Dim sqlUpdate As String

'Loop through the list box and build a string we will use
'to filter the Update recordset (IN clause)
For Each itmSelected In Me.lstName.ItemsSelected
  If Len(strInClause) <> 0 Then
    'Add comma's between the values
    strInClause = strInClause & ","
  End If
  'My list is numbers (long), if yours is different
  'you may need to wrap the values i.e. text values
  'will need to be wraped in double quotes
  'strInClause = strInClause & chr(34) & itmSelected & chr(34)
  strInClause = strInClause & itmSelected
Next itmSelected

'Now build the SQL string the represents the update query,
'make sure the text in italics are changed to match your
'actual data
sqlUpdate = "UPDATE [i]YourTable[/i] " & _
            "SET [i]YourField[/i] = '[i]NewValue[/i]' " & _
            "WHERE [i]FilterField[/i] In (" & strInClause & ");"

'* For testing, you can paste the result in a SQL pane to see if 
'* it does what you want
Debug.Print sqlUpdate
'* For real, if the above string works you can remove the comments
'* and run the query
'DoCmd.SetWarnings = False
'DoCmd.RunSQL sqlUpdate
'DoCmd.SetWarnings = True
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
you can use a sql update like:

update table_x set field_x = some_box where table_x.field_xyz = "some_value";

but you have to have criteria.
 
Upvote 0 Downvote
Wow, thanks. for 'NewValue' and 'FilterField' I'm a little confused. I've got a combobox with Row Source Type = Value List and Row Source = ""Option1";"Option2";"etc

How can I incorporate this? thanks so much for taking the time to code that out. =D

Barny... the listbox contains the field "title" from the table "Bigtable". Within the table "Bigtable", there is another field called "genre"... I want to update all selected records via "title" to a set genre in a combobox. thanks guys
 
Upvote 0 Downvote
also, CMP... you make the note "you may need to wrap the values i.e. text values, will need to be wraped in double quotes"... my values are indeed text values. what do I wrap in quotes? thanks!
 
Upvote 0 Downvote
Woodman650,
I think I have all the pieces and parts figured out from the various posts. Here is an updated routine.
Code: 
Private Sub Command2_Click()
Dim itmSelected
Dim strInClause As String
Dim sqlUpdate As String

'Loop through the list box and build a string we will use
'to filter the Update recordset (IN clause)
For Each itmSelected In Me.lstName.ItemsSelected
  If Len(strInClause) <> 0 Then
    'Add comma's between the values
    strInClause = strInClause & ","
  End If
  'wrapped in double quotes (chr(34)) becuase it's text
  strInClause = strInClause & [b]chr(34)[/b] & itmSelected & [b]chr(34)[/b]
Next itmSelected

'Now build the SQL string the represents the update query,
sqlUpdate = "UPDATE Bigtable " & _
            "SET [genre]= '" & [b]Me.combobox.Value[/b] & "' " & _
            "WHERE [title] In (" & strInClause & ");"

'* For testing, you can paste the result in a SQL pane to see if
'* it does what you want
Debug.Print sqlUpdate
'* For real, if the above string works you can remove the comments
'* and run the query
'DoCmd.SetWarnings = False
'DoCmd.RunSQL sqlUpdate
'DoCmd.SetWarnings = True
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
thanks CMP... for some reason, only the first value in the listbox is being updated... nothing else. and it's not even selected.

I'm using:
Code: 
Private Sub AppendButt_Click()
Dim itmSelected
Dim strInClause As String
Dim sqlUpdate As String

'Loop through the list box and build a string we will use
'to filter the Update recordset (IN clause)
For Each itmSelected In Me.BatchList.ItemsSelected
  If Len(strInClause) <> 0 Then
    'Add comma's between the values
    strInClause = strInClause & ","
  End If
  'wrapped in double quotes (chr(34)) becuase it's text
  strInClause = strInClause & Chr(34) & itmSelected & Chr(34)
Next itmSelected

'Now build the SQL string the represents the update query,
sqlUpdate = "UPDATE BigTable " & _
            "SET [genre]= '" & Me.cmbGenre.Value & "' " & _
            "WHERE [title] In (" & strInClause & ");"

'* For testing, you can paste the result in a SQL pane to see if
'* it does what you want
Debug.Print sqlUpdate
'* For real, if the above string works you can remove the comments
'* and run the query
'DoCmd.SetWarnings = False
'DoCmd.RunSQL sqlUpdate
'DoCmd.SetWarnings = True

End Sub
 
Upvote 0 Downvote
Woodman650,
If the following line is still commented out [tt][green]'DoCmd.RunSQL sqlUpdate[/green][/tt] then nothing should be updated.

Maybe I need to clarify.
Woodman650 said:
so on event...(button click)
[Form].lstname.ItemsSelected (something)
value selected in combobox, append to record
go to next selected object in listbox...
append combobox selection to record...etc, loop
Click to expand...
This routine will check the listbox ([tt]BatchList[/tt]) for selected items, if there are any, they are added to a list ([tt]strInClause[/tt]) that will be used as a filter for a batch update of [tt]Bigtable[/tt]. This will cause all selected records to update at once, not one at a time. There is no value selected in combobox, append to record, go to next selected object in listbox... progression as you originally detailed.

...and it's not even selected I always forget to validate user input before I start testing so you may want to add this line to the routine, that way if nothing is selected, nothing gets updated.

Code: 
...
Dim sqlUpdate As String

[b][green]'Make sure something is selected before continuing[/green]
If Me.BatchList.ItemsSelected.Count = 0 Then
  Exit Sub
End If[/b]

'Loop through the list box and build a string we will use
...

CMP

(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
Woodman650,
Your trying to make a batch update form? If so you don't need the form bound to a reocrdset ([tt]qryClientData[/tt]), just leave it unbound. When you do this you the combobox ([tt]cmbGenre[/tt]) should also be unbound and keep the rest of settings the same.

Woodman650 said:
so, I'm not great at code...
Click to expand...
I think I need to cover something about the code. If you see [green][tt]'[/tt][/green] in the code (and the color of the following text is [green]green[/green], it's a comment so it doesn't get run by Access.
With that said the routine won't do anything with the records until the following line is un-commented (remove the single quote):
[tt] DoCmd.RunSQL sqlUpdate[/tt]

The [tt]DoCmd.SetWarnings = True/False[/tt] above and below this line just stop Access from giving you the "You are about to run an Append Query..." message. It's up to you whether you want to see the message or not, if you don't un-comment those lines as well.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)
 
Upvote 0 Downvote
oh ok. still doesn't work... haha. I unbound both the form and the combobox. =(
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

arpeggione
  • Locked
  • Question
&quot;Updates Available&quot; banner - disable Updates not working - Office 2016
Replies
1
Views
46
Darrylles
Darrylles
lameid
  • Locked
  • Question
&quot;System Resources Exceeded&quot; Compact and Repair
Replies
2
Views
42
dhookom
dhookom
lameid
  • Locked
  • Question
&quot;Intel is in serious trouble. ARM is the Future.&quot; - Is this the beginning of the end?
Replies
0
Views
34
lameid
lameid
bigdavidt
  • Locked
  • Question
&quot;The recordset is not updatable.&quot;
Replies
2
Views
74
bigdavidt
bigdavidt
tunsarod
  • Locked
  • Question
Run a Dos .bat batch file from Access
Replies
8
Views
80
tunsarod
tunsarod

Part and Inventory Search

Sponsor

Back
Top