473,323 Members | 1,537 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Filtered Subform - Update specific Column value

6
Hi there,

I am really struggling with one particular problem regarding data manipulation of filtered subforms and I hope you can help me out.

The form includes a subform that contains filtered content.

Example for this table is attached.

Where [Column2] is a checkbox (with Boolean "True").
Now what I want to do is to change the filtered content of [column2] to "false" via vba code.
Please note that the filtered ("non-visible") cells of [Column2] in the form should stay untouched.

The Columns [ID] and [Column4] both have unique content, thus it would be possible to use one of them as reference to address [Column2].

I am getting crazy with this, so I'd appreciate any hint! Thanks. :)

Cheers

cPmod
Attached Images
File Type: jpg Screenshot-Table.jpg (19.3 KB, 126 views)
Aug 29 '15 #1

✓ answered by cPmod

Okay, it took me ages, but I finally solved it.
For all those who are interested, follows the complete code:
Expand|Select|Wrap|Line Numbers
  1.     Dim dbTintendatenbank As Object
  2.     Dim rstTintendatenbank As Object
  3.     Dim fldEnumerator As Object
  4.     Dim fldColumns As Object
  5.     Dim strBatch As String
  6.     Dim intCount As Integer
  7.     Dim strMaxtabcount As Integer
  8.     Dim strCounter As Integer
  9.  
  10.     Set dbTintendatenbank = CurrentDb
  11.     Set rstTintendatenbank = dbTintendatenbank.OpenRecordset("Tabelle-Tintendatenbank")
  12.     Set fldColumns = rstTintendatenbank.Fields
  13.  
  14. Me.[Tinten-DatenbankSubform].SetFocus
  15. DoCmd.RunCommand acCmdSelectAllRecords
  16. DoCmd.RunCommand acCmdCopy
  17. DoCmd.RunCommand acCmdNewObjectTable
  18. DoCmd.OpenTable "Tabelle1", acNormal, acEdit
  19. DoCmd.RunCommand acCmdPaste
  20.  
  21. strMaxtabcount = DMax("[ID]", "Tabelle1")
  22. For strCounter = 1 To strMaxtabcount
  23.  
  24. If Not IsNull(DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)) Then
  25.         strBatch = DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)
  26. End If
  27.  
  28.     rstTintendatenbank.MoveFirst
  29.  
  30.     While Not rstTintendatenbank.EOF ' Scan the records
  31.         For Each fldEnumerator In rstTintendatenbank.Fields ' Check the columns
  32.             If fldEnumerator.Name = "Batch" Then ' Check for "Batch" with unique records
  33.                 If fldEnumerator.Value = strBatch Then ' Check for the specific record
  34.                     rstTintendatenbank.Edit
  35.                         rstTintendatenbank("Entsorgt").Value = "True"  ' Change the value
  36.                     rstTintendatenbank.Update
  37.                 End If
  38.             End If
  39.         Next
  40.         rstTintendatenbank.MoveNext
  41.     Wend
  42.  
  43. Next strCounter
  44.  
  45. rstTintendatenbank.Close
  46. DoCmd.Close acTable, "Tabelle1", acSaveNo
  47.  

One point is still open for discussion:
This Code is quite slow (2000entries in 6min, that is probably because of the recordset...). Does anyone know how to speed this up a little bit.

6 1463
cPmod
6
Alright, I got a codesnippet that might help solving this problem:

Expand|Select|Wrap|Line Numbers
  1.     Dim dbTintendatenbank As Object
  2.     Dim rstTintendatenbank As Object
  3.     Dim fldEnumerator As Object
  4.     Dim fldColumns As Object
  5.     Dim strMatch As String
  6.     Set dbTintendatenbank = CurrentDb
  7.     Set rstTintendatenbank = dbTintendatenbank.OpenRecordset("Table1")
  8.     Set fldColumns = rstTintendatenbank.Fields
  9.  
  10. strMatch = "JA0067" ' Just for testing purposes - Whole Stuff can be embedded in a loop
  11.  
  12.     While Not rstTintendatenbank.EOF ' Scan the records
  13.  
  14.         For Each fldEnumerator In rstTintendatenbank.Fields ' Check the columns
  15.  
  16.             If fldEnumerator.Name = "Column4" Then ' Check for "Column4" with unique records
  17.  
  18.                 If fldEnumerator.Value = strMatch Then ' Check for the specific record
  19.                     rstTintendatenbank.Edit
  20.  
  21.                     'Change value of Column2
  22.                     rstTintendatenbank("Column2").Value = "False"
  23.                     rstTintendatenbank.Update
  24.                 End If
  25.             End If
  26.         Next
  27.         ' Move to the next record and continue the same approach
  28.         rstTintendatenbank.MoveNext
  29.     Wend
  30.  
Found this on http://www.functionx.com/vbaccess200...editrecord.htm

However, this code only edits one single entry!
Two steps to go:
  1. How to select a single column of a filtered subform in vba code
  2. How to copy this filtered content in an array

Any ideas how to solve these jobs?
Aug 29 '15 #2
cPmod
6
Okay, it took me ages, but I finally solved it.
For all those who are interested, follows the complete code:
Expand|Select|Wrap|Line Numbers
  1.     Dim dbTintendatenbank As Object
  2.     Dim rstTintendatenbank As Object
  3.     Dim fldEnumerator As Object
  4.     Dim fldColumns As Object
  5.     Dim strBatch As String
  6.     Dim intCount As Integer
  7.     Dim strMaxtabcount As Integer
  8.     Dim strCounter As Integer
  9.  
  10.     Set dbTintendatenbank = CurrentDb
  11.     Set rstTintendatenbank = dbTintendatenbank.OpenRecordset("Tabelle-Tintendatenbank")
  12.     Set fldColumns = rstTintendatenbank.Fields
  13.  
  14. Me.[Tinten-DatenbankSubform].SetFocus
  15. DoCmd.RunCommand acCmdSelectAllRecords
  16. DoCmd.RunCommand acCmdCopy
  17. DoCmd.RunCommand acCmdNewObjectTable
  18. DoCmd.OpenTable "Tabelle1", acNormal, acEdit
  19. DoCmd.RunCommand acCmdPaste
  20.  
  21. strMaxtabcount = DMax("[ID]", "Tabelle1")
  22. For strCounter = 1 To strMaxtabcount
  23.  
  24. If Not IsNull(DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)) Then
  25.         strBatch = DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)
  26. End If
  27.  
  28.     rstTintendatenbank.MoveFirst
  29.  
  30.     While Not rstTintendatenbank.EOF ' Scan the records
  31.         For Each fldEnumerator In rstTintendatenbank.Fields ' Check the columns
  32.             If fldEnumerator.Name = "Batch" Then ' Check for "Batch" with unique records
  33.                 If fldEnumerator.Value = strBatch Then ' Check for the specific record
  34.                     rstTintendatenbank.Edit
  35.                         rstTintendatenbank("Entsorgt").Value = "True"  ' Change the value
  36.                     rstTintendatenbank.Update
  37.                 End If
  38.             End If
  39.         Next
  40.         rstTintendatenbank.MoveNext
  41.     Wend
  42.  
  43. Next strCounter
  44.  
  45. rstTintendatenbank.Close
  46. DoCmd.Close acTable, "Tabelle1", acSaveNo
  47.  

One point is still open for discussion:
This Code is quite slow (2000entries in 6min, that is probably because of the recordset...). Does anyone know how to speed this up a little bit.
Aug 29 '15 #3
jforbes
1,107 Expert 1GB
I'm not sure why you wouldn't just run an update Query against your table. I don't see anything about this that would require you to use VBA to step through the Rows one at a time.
Sep 1 '15 #4
cPmod
6
Well, the table content in the subform changes due to code based filter option, and therefore I wanted a bulletproof solution that only the visible content is changed.

To be honest, I did not fully trust the update query option and it takes more effort, if some things change afterwards (lets say additionally filtered column in the subform, needs to be inserted as well in the query to show the same results...)

But it is a good point (and definitely much faster), eventually I'll change that, especially when the performance ennerves me.

Thank you for the comment.
Sep 1 '15 #5
jforbes
1,107 Expert 1GB
It looks like have your reasons, but I think you could replace a good portion of your code with something like this; It would run a lot faster:
Expand|Select|Wrap|Line Numbers
  1. Dim sSQL As String
  2. sSQL = sSQL & "UPDATE [Tabelle-Tintendatenbank] "
  3. sSQL = sSQL & "INNER JOIN [Tabelle1] "
  4. sSQL = sSQL & "ON [Tabelle-Tintendatenbank].[Batch]=[Tabelle1].[Batch] "
  5. sSQL = sSQL & "SET [Tabelle-Tintendatenbank].[Entsorgt]=True "
  6. CurrentDB.Execute sSQL, dbFailOnError 
I won't be offended if you don't try it, I just thought I would float the idea by you.
Sep 1 '15 #6
cPmod
6
Ah, now I understand your idea a bit better.
Yes, that is definitely much more effective. I will definitively give that a try.
Thank you very much for sharing.
Sep 1 '15 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: D | last post by:
Guys, i had try to update the "Date" value in my access to empty string. The data type for the column value is Date/Time. I use the following simple code: rsForum ("Date") = "" It give me...
2
by: Danny | last post by:
Is there any way to update a column in a clustered index without incurring the cost of reordering. Example: Create table TableX ( Col1 int, Col2 smalldatetime, Col3 varchar(10)) go create...
4
by: =?Utf-8?B?QmFidU1hbg==?= | last post by:
Hi, I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD operations. The DataSource has three columns one of which -...
0
by: doomsday123 | last post by:
I have a gridview that displays a ORDER_ID in the first column and then I add 2 button columns on the end of it to approve or deny the order. If they approve it I want to update the database row's...
1
parshupooja
by: parshupooja | last post by:
Hey All, I have table A which has 20 columns. I want to create trigger on table A which should get triggered whenever someone tries to update any value in Table A than trigger should insert new as...
3
by: hauschild | last post by:
Guys, I am looping thru a dataset and need to update rows' columns based on the ColumnName value. I get this far but I'm unsure of how to update that actual columns value with the new value. ...
2
by: sukatoa | last post by:
I have a table, where it composed of several columns, but i am concerning only on a specific column with datatype varchar under that db table... Now, for example: column: links values: ...
2
by: gsreenathreddy | last post by:
Hi All, I need to update a specific column when ever sysdate changes, is there any possibility to update the specific column using trigger? Thanks, Sreenath
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.