Can I "Tick" (Select) records and batch update them?

[Mackbear]

Hello everyone, Good day! Reaching out again for help... Is it possible to create a form where it displays a certain set of records then I can select some and batch update them? Something that looks like this image attached....

 

[arnelgp]

of course you can, using the form's recordset, is that a subform?

 

[Mackbear]

of course you can, using the form's recordset, is that a subform?

Hello, actually not sure how to go about it, is it possible on a split form or datasheet form to display the records?

 

[arnelgp]

add code to the Click event of resolve button:

private sub resolveButton_Click()
with me.recordsetClone
   if not (.bof and .eof) then
       .movefirst
   end if
   while not .eof
      if ![select] then
         'add code here when "select" is ticked
      end if
      .movenext
   wend
end with
end sub

 

[CJ_London]

doesn't have a tickbox but this 'multiselect' form may do what you want

https://www.access-programmers.co.uk/forums/showthread.php?t=289116

 

[Mackbear]

doesn't have a tickbox but this 'multiselect' form may do what you want

https://www.access-programmers.co.uk/forums/showthread.php?t=289116

This is close to what I have in mind... one more thing though... say I want to update the country field for all entries I selected, how would I do that? I'm sorry I'm struggling thinking about how to build the code for it or is an update query not sure?

 

[arnelgp]

if the country field is in your split form:

private sub resolveButton_Click()
with me.recordsetClone
   if not (.bof and .eof) then
       .movefirst
   end if
   while not .eof
      if ![select] then
         .Edit
         ![countryField] = "Zimbabwe"
 
        'untick? for safety.
         ![select] = False
        .Update
      end if
      .movenext
   wend
end with
end sub

 

[CJ_London]

on a button to do your updates, something like

currentdb.execute "Update myTable SET Country='" & me.newcountry & "' WHERE ID IN (" & Replace(SelectedList, "|", ",") & ")"

 

[Pat Hartman]

There are two more ways to do this.
1. Add a "select" field to the table
2. Add a new table to hold the selections.

method 1 will not work in a multi-user environment where two or more people might be using this feature at the same time. With method 2, you can include a UserID along with the selected record ID so you can have separate selections for each person.

Once the selected records are marked or added to the select table, you would use a query returns the selected records and apply the bulk update.

 

[Mackbear]

doesn't have a tickbox but this 'multiselect' form may do what you want

https://www.access-programmers.co.uk/forums/showthread.php?t=289116

How do I change the color of the entry when the record is selected? Also the altbackground does not change color when I select, only the "select" word changes...

 

[CJ_London]

it does for me - have you enabled the code?

 

[Mackbear]

it does for me - have you enabled the code?

This is how it looks like on my end... yes I have enabled the code... for the update, it says I have Syntax error in Update statement:


CurrentDb.Execute "Update tbl_LFMngment SET tbl_LFMngment.[Due Date Extended]= " & Format(Me.DueDateExtended, conJetDate) & ", tbl_LFMngment.[WaiverApproved]='" & Me.waivapp & "', tbl_LFMngment.[RequestMethod]='" & Me.reqmethod & "', tbl_LFMngment.[CreditedAmount]=" & Me.CreditedAmount & ", tbl_LFMngment.[CreditMethod]= '" & Me.credmethod & "' WHERE entry_id IN (" & Replace(SelectedList, "|", ",") & ")"

 

[CJ_London]

This is how it looks like on my end

you've probably not included in your form the altback control - or if you have, you've not applied the conditional formatting

with regards the syntax error - apply the code to a string variable and debug.print it - you should be able to see what the issue is

dim sqlstr as string

sqlstr="Update tbl_LFMngment S...........
debug.print sqlstr
currentdb.execute sqlstr

 

[arnelgp]

if you update the table that is displayed on the subform, using SQL, you will not see the immediate result in your subform (because it is in-memory copy) until you click on it.

use recordset to immediately see the result on your subform.

 

[Mackbear]

you've probably not included in your form the altback control - or if you have, you've not applied the conditional formatting

with regards the syntax error - apply the code to a string variable and debug.print it - you should be able to see what the issue is

dim sqlstr as string

sqlstr="Update tbl_LFMngment S...........
debug.print sqlstr
currentdb.execute sqlstr

Great! this code worked I thank you so much! I do have the altback control, it is just a box or rectangle is what it is called right? I don't know how to apply the conditional formatting though....

 

[Mackbear]

Another question, how do I select all without clicking them all one at a time? like a select all command?

 

[CJ_London]

I don't know how to apply the conditional formatting though....

in the example db, in design view, click on the control then in the ribbon select conditional formatting you will see the code there.

how do I select all without clicking them all one at a time? like a select all command?

you would need a button in the header to update selectedlist value by looping through the recordset. Plenty of examples on this and other forums how you might do this.

However would think it much easier just to have another button to do all your updates without the criteria

 

[Mackbear]

in the example db, in design view, click on the control then in the ribbon select conditional formatting you will see the code there.

you would need a button in the header to update selectedlist value by looping through the recordset. Plenty of examples on this and other forums how you might do this.

However would think it much easier just to have another button to do all your updates without the criteria

Can I have a link of the example and will those apply to your example form?

 

[CJ_London]

Can I have a link of the example and will those apply to your example form?

already provided in post #5 and from your example in post #12 you have already downloaded it - look in your downloads folder

 

[Mackbear]

already provided in post #5 and from your example in post #12 you have already downloaded it - look in your downloads folder

Yes I did, however I am unable to find from your example form the option where I can select all instead of selecting one by one, may I know where I could find it? I apologize