openning & reading two recordsets at once 1

[linctus]

Hi all,
I am relatively new to Access I am a perl and old DBase dabbler but find this 'stuff' fun also.
Anyway...
I am trying to open, read & right data between two recordsets simultaneously but am not sure how to call them both. I get an error telling me that there is no current record in the record set.
I have highlighted the trouble line below with "********"
Basically I have created one table with street names in one collum and numbers in another (one street name & one number per row) and am trying to loop through the first recordset and transpose the information into the next recordset running across (one street name lots of numbers per row)

Is it because I am declaring 2 x MyDb.OpenRecordsets ????

Any guidance will be greatly appreciated
Regards
Linctus

Private Sub Option0_Click()
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDb.OpenRecordset("street down", DB_OPEN_TABLE)
Set MyNextTab = MyDb.OpenRecordset("street across", DB_OPEN_TABLE)
Dim Stname As String
Dim ColNo As Integer
MyTable.MoveFirst
Stname = MyTable![street]
Stname2 = MyTable![street]

*****MyNextTab.MoveFirst***** ' tells me no current records

Do While Not EOF("street down&quot
ColNo = 1
If Stname = Stname2 Then
MyNextTable.AddNew
MyNextTable![Street Name] = MyTable![street]
MyNextTable.Value("ColNo&quot = MyTable![Number]
MyTable.MoveNext
ColNo = ColNo + 1
MyTable.Update
Else
Stname = MyTable![street]
Stname2 = MyTable![street]
ColNo = 1
End If
Loop
MyTable.Close
MyNextTab.Close
End Sub

 

[JunglesMcBeef]

First of all I have never seen anyone open a recordset with DB_OPEN_TABLE as the argument value. dbOpenTable is what I use, but if it works it works I guess.

Next, as you are only adding records to MyNextTab you don't need to .MoveFirst. You can leave the line out altogether.

Finally, you are trying to add records to a recordset that has hasn't been defined (MyNextTable). Maybe either name the recordset MyNextTable OR MyNextTab, but you can't chop and change between the two.

Also, since you are new to access, why not start coding in ADODB instead of DAO? DAO is in maintenance mode, I have only recently learnt ADODB and it isn't hard to get into (I actually like it better than DAO now).

Good Luck!

 

[linctus]

Sory Guys / Gals I worked it out...
The table was empty Der!

But now I have a different one.
how do I reference a collum by it's collum number not the name it may have?

eg. the first collum in the table is collum 1 but is called 'street' the second collum is collum 2 but is called 'First number' the third collum is collum three but is called 'second number' etc.

I want to +1 to the variable Nextcol and on each pass of the loop increment the var NextCol by 1 and move to the next collum across the table.

 

[JunglesMcBeef]

Here is some code that will iterate through every field in a table, maybe you can modify this to work to your goals:

'******************************************
Dim fldField

For Each fldField in theTable
<the field manipulation code goes here>
Next fldField
'******************************************

If you just want to reference a field by it column value you just use MyTable.Fields(<col number>) eg your &quot;street&quot; field would be MyTable.Fields(0). The columns start at 0.

 

[linctus]

Thanks for you help JungleMcBeef
I hadn't debugged the other bugs in the script before I posted sorry about that, I was too busy stalling on the first prob.

Your comment about dbOpenTable makes perfect sense but for some reason doesn't work for me I stumbled with that one awhile back and the DB_OPEN_TABLE was the only way I could get any of my codes to work, weird I know, (I'm using 2000 for this stuff).

The other, regarding referencing the collum by it's number worked a treat a big thanks for that. I was trying to use '.value' and alike as one might in excel.

Thanks again