recordset positioning problem (1 Viewer)

[John Sh]

I have two tables that are used in multiple sub's, for different purposes, in the same module.
I have tried using different "Dim rsx as", "rsx.close and "rsx = nothing" with "x" being different in each sub.
Trouble is the ".move" in one instance is changing the position in all instances of the recordsets.
They are acting as if there is only one instance of each recordset regardless of how they are instantiated.
How do I return each instance of the recordset to it's previous position?

I have found a point of recursion and corrected that but my question stands.

 

[MajP]

You will have to show the code. But this will not happen unless somehow you set the recordset variables to be equal. To prove it.

Private Sub moveRS()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("employees", dbOpenDynaset)
Set rs2 = CurrentDb.OpenRecordset("employees", dbOpenDynaset)
rs1.MoveLast
Debug.Print rs1.AbsolutePosition & " " & rs2.AbsolutePosition
rs2.MoveNext
Debug.Print rs1.AbsolutePosition & " " & rs2.AbsolutePosition

End Sub

9 0
9 1

 

[John Sh]

You will have to show the code. But this will not happen unless somehow you set the recordset variables to be equal. To prove it.

Thank you.
The code is a bit complicated to be able to send it. I'll work on it and get back to you.
John

 

[John Sh]

I gutted the software but the basic problem remains.
If you click on any thing with "Family" or "Genus" you will see that the record count in the message box is 42. This indicates that the "nomatch" sub has been run six times.
Click on any of the other options and the result is 6, which is the correct result.
I can move the call to "Nomatch" to correct the issue but that creates a separate problem.
If you put a break point in "NoMatch: you will see that the flow is from "NoMatch" to "Checkall" and back to "NoMatch" at the "Do" loop.

 

[tvanstiphout]

I get 24 records as below. How is that wrong?


TableName,Quantity,ButtonName,ButtonCaption,short,Accession,
Collier Collection,,btnBC,Barry Collier Collection,,,
Herbarium Collection,,btnMain,Herbarium Collection,,,
National Parks Collection,,btnNatPark,National Parks Collection,,,
Ourimbah Collection,,btnOur,Ourimbah Collection,,,
Steve Bell Collection,,btnSB,Stephen Bell Collection,,,
Herbarium Collection,0,btnMain,Herbarium Collection,,,
Collier Collection,,btnBC,Barry Collier Collection,,,
Herbarium Collection,,btnMain,Herbarium Collection,,,
National Parks Collection,,btnNatPark,National Parks Collection,,,
Ourimbah Collection,,btnOur,Ourimbah Collection,,,
Steve Bell Collection,,btnSB,Stephen Bell Collection,,,
National Parks Collection,0,btnNatPark,National Parks Collection,,,
Collier Collection,,btnBC,Barry Collier Collection,,,
Herbarium Collection,,btnMain,Herbarium Collection,,,
National Parks Collection,,btnNatPark,National Parks Collection,,,
Ourimbah Collection,,btnOur,Ourimbah Collection,,,
Steve Bell Collection,,btnSB,Stephen Bell Collection,,,
Ourimbah Collection,0,btnOur,Ourimbah Collection,,,
Collier Collection,,btnBC,Barry Collier Collection,,,
Herbarium Collection,,btnMain,Herbarium Collection,,,
National Parks Collection,,btnNatPark,National Parks Collection,,,
Ourimbah Collection,,btnOur,Ourimbah Collection,,,
Steve Bell Collection,,btnSB,Stephen Bell Collection,,,
Steve Bell Collection,0,btnSB,Stephen Bell Collection,,,

 

[tvanstiphout]

A few points about the current database design:
1. Every table must have a primary key that uniquely defines each row. Yours don't have that. That must be fixed right away.
2. I don't know your subject matter very well, but it appears you're not using the field's Required property nearly enough. For example:
Main

AccessionNumber	Family	Genus	BoxNo	BayNo	ShelfNo	Collector	CollectorNo
7089​	Scrophulariaceae	Veronica	1​	13​	2​	Williams. P.	10768PW
110​	Myrtaceae	Thryptomene	77​	8​	1​	McNair. D.	429DM
221​	Proteaceae	Hakea	1​	11​	1​	McNair. D.	61968DM
240​	Proteaceae	Grevillea	36​	11​	2​	McNair. D.	1114DM
751​	Myoporaceae	Eremophila	5​	7​	1​	McNair. D.	1100DM
868​	Rutaceae	Zieria	6​	12​	1​	Burke. B.	9004BB
1​			​	​	​
1​			​	​	​
1​			​	​	​
​	a		​	​	​
​	a		​	​	​

Those last 5 records don't make logical sense. Then don't allow it, by using PK (see #1), required fields, and unique indexes to protect your tables against bad data entry.
3. You are coding values in table names, by having several *Collection tables with identical design. That should be 1 table, with an additional CollectionID field, linking back to a Collections table (NOTE: Careful: Collection is a Reserved Word). When you do that, you may find that the need for TableList table goes away.

There is more, but this is a start.

 

[John Sh]

A few points about the current database design:
1. Every table must have a primary key that uniquely defines each row. Yours don't have that. That must be fixed right away.
2. I don't know your subject matter very well, but it appears you're not using the field's Required property nearly enough. For example:
Main

AccessionNumber	Family	Genus	BoxNo	BayNo	ShelfNo	Collector	CollectorNo
7089​	Scrophulariaceae	Veronica	1​	13​	2​	Williams. P.	10768PW
110​	Myrtaceae	Thryptomene	77​	8​	1​	McNair. D.	429DM
221​	Proteaceae	Hakea	1​	11​	1​	McNair. D.	61968DM
240​	Proteaceae	Grevillea	36​	11​	2​	McNair. D.	1114DM
751​	Myoporaceae	Eremophila	5​	7​	1​	McNair. D.	1100DM
868​	Rutaceae	Zieria	6​	12​	1​	Burke. B.	9004BB
1​			​	​	​
1​			​	​	​
1​			​	​	​
​	a		​	​	​
​	a		​	​	​

Those last 5 records don't make logical sense. Then don't allow it, by using PK (see #1), required fields, and unique indexes to protect your tables against bad data entry.
3. You are coding values in table names, by having several *Collection tables with identical design. That should be 1 table, with an additional CollectionID field, linking back to a Collections table (NOTE: Careful: Collection is a Reserved Word). When you do that, you may find that the need for TableList table goes away.

There is more, but this is a start.

First things first..
If you have a look at the list you produced you will see that the list repeats itself. This means that the same information is being collected over and over again. that is how it is wrong!
Second;
As I have stated many times in the past I am under the constraints of scientific method which deems that ALL relevant information MUST be held in a single table. I do not, therefore, have the luxury of a normalised data table. I do not have the requirement of a primary key as each row in each table is complete unto itself. I also have the restriction that ALL data must be displayed on a single form hence my main form consists of some 90 controls plus operational buttons etc.
So get off your high horse about what I MUST do until you know, and understand, the circumstances.
As a for instance, the Excel data sheet from Kew Gardens, the ultimate authority on things botanical, is 154 megabytes without a single cross reference.
The information I provided is, as stated, "Gutted" so it probably doesn't make any sense to you.
The problem is the re-collection of the same data, as demonstrated so well by your table, something you have chosen to ignore in your quest for a bloody key.
John

 

[John Sh]

I still do not understand why the recordsets behaved as they did but I have come up with a simple work around.
Knowing there should only be 7 records in listTables I have simply trapped that number and exited.
The code now only scans the data once and sets the buttons with the results.

I thank those who helped for that help
John

Private Sub getMatch()
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim db As Database
    Set db = CurrentDb
    Dim sCount As String
    Dim sEnd As String
    Dim sName As String
    Set rs1 = db.OpenRecordset("listTables", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("tablelist", dbOpenSnapshot)
    DoCmd.SetWarnings False
    sCount = IIf(InStr(Me.cboSearchFor, "Genus") > 0, "QBadGenusCount", "QBadFamilyCount")
        rs2.MoveFirst
        With rs2
            Do
                If DCount("*", "listtables") = 7 Then GoTo OutaHere  _____________Trap here
                If !TableName = "dtv_records" Then .MoveNext
                Select Case !TableName
                    Case "Barry collier collection": sEnd = "C"
                    Case "Main": sEnd = "H"
                    Case "National Parks Collection": sEnd = "N"
                    Case "Ourimbah Collection": sEnd = "O"
                    Case "Reference Collection": sEnd = "R"
                    Case "SBCollection": sEnd = "S"
                End Select
                sName = sCount & sEnd
                Me.cboHidden.RowSource = sName
                rs1.AddNew
                rs1!TableName = rs2!CommonName
                rs1!Quantity = Me.cboHidden.ItemData(0)
                rs1!ButtonName = rs2!ButtonName
                rs1!ButtonCaption = rs2!ButtonCaption
                rs1.Update
                rs2.MoveNext
            Loop While Not rs2.EOF
OutaHere:                                                                                ___________________________ and gone
        End With
    DoCmd.SetWarnings True
    Set rs1 = Nothing
    Set rs2 = Nothing
End Sub

Private Sub CheckAll(sField As String)
    Dim nQuantity As Integer
    Dim db As Database
    Set db = CurrentDb
    Dim RS3 As Recordset
    Dim RS4 As Recordset
    Set RS3 = db.OpenRecordset("listTables", dbOpenDynaset)
    Set RS4 = db.OpenRecordset("TableList", dbOpenSnapshot)
    RS4.MoveFirst
    If RS4!TableName = "Barry collier collection" Then RS4.MoveNext
    Do
        If InStr(sField, "Box") > 0 Or InStr(sField, "Bay") > 0 Or InStr(sField, "Shelf") > 0 Then
            If RS4!TableName = "Reference_Collection" Or RS4!TableName = "Duplicates" Then
                nQuantity = 0
                GoTo adder
            End If
        Else
                nQuantity = Searcher(RS4!TableName)
        End If
        If DCount("*", "ListTables") = 7 Then Exit Sub       ______________trap here
adder:
        RS3.AddNew
        RS3!TableName = RS4!CommonName
        RS3!Quantity = nQuantity
        RS3!ButtonName = RS4!ButtonName
        RS3!ButtonCaption = RS4!ButtonCaption
        RS3.Update
        RS4.MoveNext
    Loop While Not RS4.EOF
    RS3.Close
    RS4.Close
    Set RS3 = Nothing
    Set RS4 = Nothing
End Sub

 

[gemma-the-husky]

As I have stated many times in the past I am under the constraints of scientific method which deems that ALL relevant information MUST be held in a single table. I do not, therefore, have the luxury of a normalised data table. I do not have the requirement of a primary key as each row in each table is complete unto itself. I also have the restriction that ALL data must be displayed on a single form hence my main form consists of some 90 controls plus operational buttons etc.

What does the comment about scientific method mean? Where does that curious statement come from?

 

[cheekybuddha]

As I have stated many times in the past I am under the constraints of scientific method which deems that ALL relevant information MUST be held in a single table. I do not, therefore, have the luxury of a normalised data table. I do not have the requirement of a primary key as each row in each table is complete unto itself. I also have the restriction that ALL data must be displayed on a single form hence my main form consists of some 90 controls plus operational buttons etc.

Never confuse how you store data with how it is displayed.

You can easily have normalised data and then a query to show the data as if it is a single table.

Your form can then be based off this query as before, or use the base tables but 'appear' to the user as if the data is all in a single table/record.

 

[ebs17]

Never confuse how you store data with how it is displayed.

When it comes to a radio, most people have already understood that there are no little men sitting there talking and singing.

 

[John Sh]

It is exactly the storage of the data the data that dictates the way I have to do things. Each record has to be a single entity therefore it cannot be "normalised". I have used relational databases in the past and know the advantages of normalisation and multiple relationships. Working with botanical specimens comes with a whole new set of rules that don't necessarily fit in with the what some seem to consider the one and only way to do things. Cat's can be skun in many ways!

 

[MajP]

Yes there are many ways to "skun" a cat. And doing it clear and logically is no fun.
What do these people know? Unlike all other data in the world, biological data is different. Who knew?
Good luck with that.

 

[gemma-the-husky]

It is exactly the storage of the data the data that dictates the way I have to do things. Each record has to be a single entity therefore it cannot be "normalised". I have used relational databases in the past and know the advantages of normalisation and multiple relationships. Working with botanical specimens comes with a whole new set of rules that don't necessarily fit in with the what some seem to consider the one and only way to do things. Cat's can be skun in many ways!

You may as well just use Excel then.

Too many industries (and government bodies) mistakenly think Excel has sufficient rigour for such use.

 

[The_Doc_Man]

As I have stated many times in the past I am under the constraints of scientific method which deems that ALL relevant information MUST be held in a single table.

As a PhD chemist, I have some extreme difficulty with this statement. Your record-keeping constraints have nothing to do with computational or display requirements. If your BOSS says "thou shalt keep this crap in a single table" then perhaps you have a valid reason, but don't foist this off to hang on "scientific method" hook.

Normally, when you are going to do this, you keep an "observations" table but query the heck out of it and NEVER do anything but APPEND data to it. But extracting data selectively for particular types of analyses is perfectly within method.

 

[LarryE]

As a PhD chemist, I have some extreme difficulty with this statement. Your record-keeping constraints have nothing to do with computational or display requirements. If your BOSS says "thou shalt keep this crap in a single table" then perhaps you have a valid reason, but don't foist this off to hang on "scientific method" hook.

Normally, when you are going to do this, you keep an "observations" table but query the heck out of it and NEVER do anything but APPEND data to it. But extracting data selectively for particular types of analyses is perfectly within method.

The OP probably means the perodic table of elements.