memory only record set 3

[ninash]

Hi all

I have a record set based on a table, this record set only contains 1 record containing many true or false statements.

I need to create another record set from this containing only the names of the fields that are true.

Has anyone got any ideas as to how this can be done??

Thanks in advance
Tony

 

[Rick39]

Maybe something like:


Dim db As Database, rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Select * from yourtable&quot
rs1.Filter = "yourfield = true"
Set rs2 = rs1.OpenRecordset
'rs2 should contain only one record
MsgBox rs2.RecordCount
Set rs2 = Nothing: Set rs1 = Nothing
Set db = Nothing

 

[ninash]

Hi Rick,

Thanks for the speed of your reply

The record set contains about 30 fields all of which are true / false entries. Is there a way I can examine all of them without entering each field.

the second record set could end up with with 30 records all containing the name of the fields that were true... eg...

rs1=
rs1![Fire Alarm] = true, [Fire Doors] = true, [Wet-Dry Riser] = False, [Emergency Lights] = true

rs2 =
rs2!

• = Fire Alarm
  rs2!
  • = Fire Doors
    rs2!
    • = Emergency Lights
      
      Any more thoughts???
      
      Tony

 

[JeremyNYC]

Tony,

This would probably be best done with an array, not a recordset. But I'm not sure you need to do this. What are you trying to do? You could always walk through the fields collection of hte original recordset and only act on fields where the value is true. But if you give us a broader picture of what your goals are, I'm sure we'll be able to help you out.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ninash]

Thanks Jeremy

You are quite correct an array is what I should be using.

I am collecting data from the recordset to be input into a word table.

The resulting table will show areas covered by the report that is being created.

Sorry for getting my question wrong this time.

Tony

 

[JeremyNYC]

Tony,

Well, I don't think you got the question wrong, just that you didn't yet have the answer. Let us know how the array wroks out for you.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ninash]

Hi Again Guys,
Well it has been nothing but bad news
The record set keeps coming up with a mismatch error so the array method just hasn't happened

Just letting ou know I am still high and very dry

Tony

 

[lestatdelioncourt]

try this:

Sub selection()

Dim dbs As Database
Dim rst As Recordset
Dim index As Integer

Dim pos_fields As Variant

' declare array size and initialise with blanks
pos_fields = Array("", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "&quot

index = 0
'set the data base and record set to process
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("parameters&quot

'if there is a row to process then process it

While index <= 30
If rst(index) = True Then
' if the value is positive save the field name in the array
pos_fields(index) = rst(index).Name
End If
index = index + 1
Wend

End Sub


In the above code the table is named Parameters, and contains 30 yes/no fields.

at the end of it you have an array called pos_fields populated with the field names that had a positive value.

This only assumes 1 row of data.

 

[ninash]

Still getting a type mismatch error
brain about to explode

 

[oharab]

Try this:
Sub selection()

Dim dbs As Database
Dim rst As Recordset
Dim index As Integer
Dim pos_fields() As Variant
dim rCount as Integer
' declare array size and initialise with blanks
index = 0
rcount=0
'set the data base and record set to process
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;parameters&quot

'if there is a row to process then process it

While index <= 30
If rst(index) = True Then
' if the value is positive save the field name in the array
redim preserve pos_fields(rCount)
'expand the array to add the record
pos_fields(index) = rst(index)
rcount=rcount+1
End If
index = index + 1
Wend

End Sub


This creates the array for you to whatever size you need.

hth

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[ninash]

Thanks for your help
All is working fine now

Stars for your help