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
Okay, it took me ages, but I finally solved it.
For all those who are interested, follows the complete code: - Dim dbTintendatenbank As Object
-
Dim rstTintendatenbank As Object
-
Dim fldEnumerator As Object
-
Dim fldColumns As Object
-
Dim strBatch As String
-
Dim intCount As Integer
-
Dim strMaxtabcount As Integer
-
Dim strCounter As Integer
-
-
Set dbTintendatenbank = CurrentDb
-
Set rstTintendatenbank = dbTintendatenbank.OpenRecordset("Tabelle-Tintendatenbank")
-
Set fldColumns = rstTintendatenbank.Fields
-
-
Me.[Tinten-DatenbankSubform].SetFocus
-
DoCmd.RunCommand acCmdSelectAllRecords
-
DoCmd.RunCommand acCmdCopy
-
DoCmd.RunCommand acCmdNewObjectTable
-
DoCmd.OpenTable "Tabelle1", acNormal, acEdit
-
DoCmd.RunCommand acCmdPaste
-
-
strMaxtabcount = DMax("[ID]", "Tabelle1")
-
For strCounter = 1 To strMaxtabcount
-
-
If Not IsNull(DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)) Then
-
strBatch = DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)
-
End If
-
-
rstTintendatenbank.MoveFirst
-
-
While Not rstTintendatenbank.EOF ' Scan the records
-
For Each fldEnumerator In rstTintendatenbank.Fields ' Check the columns
-
If fldEnumerator.Name = "Batch" Then ' Check for "Batch" with unique records
-
If fldEnumerator.Value = strBatch Then ' Check for the specific record
-
rstTintendatenbank.Edit
-
rstTintendatenbank("Entsorgt").Value = "True" ' Change the value
-
rstTintendatenbank.Update
-
End If
-
End If
-
Next
-
rstTintendatenbank.MoveNext
-
Wend
-
-
Next strCounter
-
-
rstTintendatenbank.Close
-
DoCmd.Close acTable, "Tabelle1", acSaveNo
-
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
Alright, I got a codesnippet that might help solving this problem: -
Dim dbTintendatenbank As Object
-
Dim rstTintendatenbank As Object
-
Dim fldEnumerator As Object
-
Dim fldColumns As Object
-
Dim strMatch As String
-
Set dbTintendatenbank = CurrentDb
-
Set rstTintendatenbank = dbTintendatenbank.OpenRecordset("Table1")
-
Set fldColumns = rstTintendatenbank.Fields
-
-
strMatch = "JA0067" ' Just for testing purposes - Whole Stuff can be embedded in a loop
-
-
While Not rstTintendatenbank.EOF ' Scan the records
-
-
For Each fldEnumerator In rstTintendatenbank.Fields ' Check the columns
-
-
If fldEnumerator.Name = "Column4" Then ' Check for "Column4" with unique records
-
-
If fldEnumerator.Value = strMatch Then ' Check for the specific record
-
rstTintendatenbank.Edit
-
-
'Change value of Column2
-
rstTintendatenbank("Column2").Value = "False"
-
rstTintendatenbank.Update
-
End If
-
End If
-
Next
-
' Move to the next record and continue the same approach
-
rstTintendatenbank.MoveNext
-
Wend
-
Found this on http://www.functionx.com/vbaccess200...editrecord.htm
However, this code only edits one single entry!
Two steps to go: - How to select a single column of a filtered subform in vba code
- How to copy this filtered content in an array
Any ideas how to solve these jobs?
Okay, it took me ages, but I finally solved it.
For all those who are interested, follows the complete code: - Dim dbTintendatenbank As Object
-
Dim rstTintendatenbank As Object
-
Dim fldEnumerator As Object
-
Dim fldColumns As Object
-
Dim strBatch As String
-
Dim intCount As Integer
-
Dim strMaxtabcount As Integer
-
Dim strCounter As Integer
-
-
Set dbTintendatenbank = CurrentDb
-
Set rstTintendatenbank = dbTintendatenbank.OpenRecordset("Tabelle-Tintendatenbank")
-
Set fldColumns = rstTintendatenbank.Fields
-
-
Me.[Tinten-DatenbankSubform].SetFocus
-
DoCmd.RunCommand acCmdSelectAllRecords
-
DoCmd.RunCommand acCmdCopy
-
DoCmd.RunCommand acCmdNewObjectTable
-
DoCmd.OpenTable "Tabelle1", acNormal, acEdit
-
DoCmd.RunCommand acCmdPaste
-
-
strMaxtabcount = DMax("[ID]", "Tabelle1")
-
For strCounter = 1 To strMaxtabcount
-
-
If Not IsNull(DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)) Then
-
strBatch = DLookup("[Batch]", "Tabelle1", "[ID] = " & strCounter)
-
End If
-
-
rstTintendatenbank.MoveFirst
-
-
While Not rstTintendatenbank.EOF ' Scan the records
-
For Each fldEnumerator In rstTintendatenbank.Fields ' Check the columns
-
If fldEnumerator.Name = "Batch" Then ' Check for "Batch" with unique records
-
If fldEnumerator.Value = strBatch Then ' Check for the specific record
-
rstTintendatenbank.Edit
-
rstTintendatenbank("Entsorgt").Value = "True" ' Change the value
-
rstTintendatenbank.Update
-
End If
-
End If
-
Next
-
rstTintendatenbank.MoveNext
-
Wend
-
-
Next strCounter
-
-
rstTintendatenbank.Close
-
DoCmd.Close acTable, "Tabelle1", acSaveNo
-
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.
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.
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.
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: - Dim sSQL As String
-
sSQL = sSQL & "UPDATE [Tabelle-Tintendatenbank] "
-
sSQL = sSQL & "INNER JOIN [Tabelle1] "
-
sSQL = sSQL & "ON [Tabelle-Tintendatenbank].[Batch]=[Tabelle1].[Batch] "
-
sSQL = sSQL & "SET [Tabelle-Tintendatenbank].[Entsorgt]=True "
-
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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 -...
|
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...
|
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...
|
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. ...
|
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:
...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |