Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 4th, 2006, 11:34 AM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default Do Loop

Hi,
  I'm trying to use a Do Loop to look at 2 seperate tables. It needs to check the value in one field on the first table. If it finds a "T" it should look to a field in the other table and count down 3 rows. Then it needs to put the values from those 3 rows into a field beside the field that contains the "T". LOOP
Then it goes to the next row "below the "T"". If its another "T" then it goes to the other table and counts down 3 rows from where it left off last time. Then puts those 3 values below the last ones.

T = 3
D = 2
S = 1

There are actually 2 fields that get copied from the second table.
What property could I use for this? I thought maybe the Count property, but I don't think it actually get the value.

Thanks for any help.
-Dave

Reply With Quote
  #2 (permalink)  
Old October 5th, 2006, 06:33 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The first thing you would want to do is open a recordset on each table. Then start into the first table, if T, then enter a subroutine (function) to count down three rows and concatenate the values... you do want to concatenate the three rows, is that correct?

It also sounds like a design problem. Why would you be doing this? Do you have table structure to share? Are you doing this:

Table1
Field1

Table2
Field2

If table1.field1 = "T" Then sVariable = table2.field2.record1 & table2.field2.record2 & table2.field2.record3
Add sVariable to table1?

Some of the more experienced people here might know how to do this in a query or two, but I can show you the code if you are interested.



mmcdonal
Reply With Quote
  #3 (permalink)  
Old October 5th, 2006, 09:22 AM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was a little off with the first explaination. I realized (like you said mmcdonal) that it wasn't making much sense doing what I thought I was supposed to be doing, so I checked w/ my Boss man again. Turns out when there is say, a "T" I need to get first row from the other table and then skip 2 rows. I would then get the 4th row on the next loop. I've found that I can use the Move method to do this. I'm trying to work out a few kinks now, but I think I'm on the right track. The problem I'm having now is that the 2nd table "rsLoc", is ending b/f the rsRev table.

Option Compare Database
Option Explicit


Function setlocations()

DoCmd.SetWarnings False
'declare necessary variables
Dim db As Database
Dim rsRev As Recordset
Dim rsLoc As Recordset
Dim sLoc As String
Dim iLevel As Integer
Dim sBreak As String

'point to database and tables
Set db = CurrentDb()
Set rsLoc = db.OpenRecordset("select *" & "from tbl_loc")
Set rsRev = db.OpenRecordset("select *" & "from tbl_os_reverse")

'Loop through records
rsRev.MoveFirst
'Do Until rsRev.Fields("[BREAKPT]").Value = " "
Do While rsRev.EOF = False
sBreak = rsRev.Fields("[BREAKPT]").Value
sLoc = rsLoc.Fields("[LOCATION]").Value
iLevel = rsLoc.Fields("[LEVELS]").Value

rsRev.Edit
rsRev.Fields("[LOC SEQ]").Value = iLevel
rsRev![LOCATION] = sLoc
rsRev.Update
rsRev.MoveNext
If sBreak = "T" Then
    rsLoc.Move 3
ElseIf sBreak = "D" Then
    rsLoc.Move 2
Else
    rsLoc.MoveNext
End If
Loop

'reclaim memory that recordset was using
Set rsLoc = Nothing
Set rsRev = Nothing

rsRev.Close
rsLoc.Close

End Function

Reply With Quote
  #4 (permalink)  
Old October 5th, 2006, 10:37 AM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

New problem:
Do While rsRev.EOF = False
This is giving me an "object variable not set" error.

What can I do to correct this?

Reply With Quote
  #5 (permalink)  
Old October 5th, 2006, 11:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

First of all, I think there are some fundamental design issues here that require this kind of work around. But that is opinion, eh?

I am not sure why you are getting that error. I looks like the code will do what you want bu that .EOF is throwing this off. Try retyping the line, and seeing what autocomplete is showing you after you hit the "." EOF should be in the list.


mmcdonal
Reply With Quote
  #6 (permalink)  
Old October 5th, 2006, 01:22 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I can't argue about the fundamental design issues. This is the first I've worked on this program though.
I changed up a few things and it seems to be working now. I changed the Do...Loop and the EOF is working now.
Now my only prob. is that the rsLoc table is running out of records before the rsRev table is completely looped through. There's not much I can do about that from my end though.

Thanks for the input mmcdonal

Set db = CurrentDb()
Set rsLoc = db.OpenRecordset("tbl_loc", DB_OPEN_TABLE)
Set rsRev = db.OpenRecordset("tbl_os_reverse", DB_OPEN_TABLE)

'Loop through records
rsRev.MoveFirst
While Not rsRev.EOF

    sBreak = rsRev.Fields("[BREAKPT]").Value
    sLoc = rsLoc.Fields("[LOCATION]").Value
    iLevel = rsLoc.Fields("[LEVELS]").Value

    rsRev.Edit
    rsRev.Fields("[LOC SEQ]").Value = iLevel
    rsRev![LOCATION] = sLoc
    rsRev.Update
    rsRev.MoveNext
    If sBreak = "T" Then
        rsLoc.Move 3
    ElseIf sBreak = "D" Then
        rsLoc.Move 2
    Else
        rsLoc.MoveNext
    End If

Wend

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with for-each loop athanatos XSLT 0 April 10th, 2006 07:20 PM
Do Until loop with IF crmpicco Classic ASP Databases 2 June 15th, 2005 05:35 PM
For....Loop kliu9 Excel VBA 5 February 10th, 2005 06:43 AM
Do Loop junemo Beginning PHP 8 July 28th, 2004 02:58 AM
nested while loop doesn't loop hosefo81 PHP Databases 5 November 12th, 2003 08:46 AM



All times are GMT -4. The time now is 02:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.