Loop locking up comp....

[snayjay]

Ok here is what I have. I am using a table to store values for one value on another table. What I mean. My main table has customer information. One of the values of the main table is Rank (Military Rank). The table I'm working off is the Rank Table. This table looks like this.

Rank | Position (rank is "RT" position is "POS" in code.)

Mr. 1
Mrs. 2
AB 3
Amn. 4
A1C 5
Sra. 6
SSgt. 7
TSgt. 8
MSgt. 9
SMSgt. 10
CMSgt. 11

I made this table to be able to totally change the rank value. Say someone in the Navy needed a program like this....well those are Air Force Ranks/Titles. They'll need to change the values. (In case you are wondering...I use this table as a source for a listbox and/or drop down lists on different forms)

I've got the code done to add new value and delete a selected value. What I can't do is the move up or move down code. In order to change the position. What I basically have is a listbox with the values in it. In order based on position number. But the position number can't be seen or modified. So this is what I've written.
Just so you know "NRT" is for the selected value in the listbox.

Private Sub MD_Click() 'move down button
Do Until Recordset.EOF 'starts the loop
Recordset.MoveFirst 'goes to first recordset
If RT = NRT Then 'checks to see if the listbox value is in the recordset
POS = POS + 1 'if so it increases the pos number by 1
Do Until Recordset.EOF 'starts second loop
Recordset.MoveNext 'goes to next recordset
POS = POS + 1 'increases next value by 1
Loop
End If
Loop
RTLB.Requery 'requery the listbox that holds the values
End Sub

Everytime I run the code....it locks up the computer. I thought since the do loop was till EOF, it wouldn't cycle forever. I guess I'm wrong. Anyway, any help in the matter would be greatly appreciated.

 

[snayjay]

Ok Ok...I've already noticed a problem in my code.
I only wanted to add one to the next record so here is what I did.

Private Sub MD_Click()
Do Until Recordset.EOF
Recordset.MoveFirst
If RT = NRT Then 'finds the right value
POS = POS + 1 'adds 1 to the position number
Recordset.MoveNext 'moves to the next record
POS = POS - 1 'subtracts one position number
End If
Loop
RTLB.Requery
End Sub

But unfortunately still locks up.

 

[snayjay]

Ok I was running in VB mode....going thru line by line. And it's showing the "RT" as (Null) but the "NRT" shows the correct value as selected on the form.

Private Sub MD_Click()
Do Until Recordset.EOF
Recordset.MoveFirst
If RT = NRT Then 'finds the right value
POS = POS + 1 'adds 1 to the position number
Recordset.MoveNext 'moves to the next record
POS = POS - 1 'subtracts one position number
End If
Loop
RTLB.Requery
End Sub

So obviously I know that it's not looking in the right recordset. Well...I don't know how to get it to read the right recordset. I have the form.source as the right table and the value names in code match that of the recordsource and form. So I dont' get it. But atleast I know what isn't working. Any input?

 

[scriverb]

Within your Subroutine you must make some declarations and identify the database and recordset.

Dim db as database
dim rs as recordset
Set db = currentdb()
Set rs = db.openrecordset("Rank". dbOpenDynaset)

Now you can use the recordset identifier rs. to negotiate the recordset and change values. You must use rs.edit and rs.update to intiate the edit process and update the current record. To add to the recordset rs.AddNew with a corresponding rs.Update is necessary. To assign values to the rs fields you use rs("POS&quot = POS + 1 as an example.

This should get you started to figuring out your problem.

Bob Scriver

 

[scriverb]

Comma instead of period. Sorry.

Set rs = db.openrecordset("Rank", dbOpenDynaset)

Bob Scriver

 

[snayjay]

Thanks....but here's what happened.

I tried the

dim db as database

and that part of the code errors with
User-Defined type not defined. After typing "as " I get the options (that start with D)
dataaccesspage
dataaccesspages
datatypeenum
date
defaultweboptions
dispparams
docmd
double

but not database.

What's up with that.

 

[scriverb]

You are probably using ACCESS 2000 or greater. DAO code must be declared:

Dim db as DAO.database
dim rs as DAO.recordset

Give that a try and let me know.

Bob Scriver

 

[snayjay]

Well Bob...I appreciate the help. I tried everything you said and still get the same error on the same line. I'm using windows XP at home with office XP, and windows 98 with office 2000 at work. Doesn't work on either. I did happen to figure a ghetto way of doing it. I used a couple unbound box's and some queries...assigning this to that....and running queries off assigned values. All in all I got it to work....Although...I would have loved to get this code working.....cause it's much shorter. Thanks again.

 

[scriverb]

As long as you got a solution.

Bob Scriver

 

[krsherm]

Snayjay,

I'm a Marine, so of course I chuckled when I saw TSgt! Anyway, without getting too deep into your code, I found the reason that you are locking up your system... you've made it impossible to exit the loop! You need to do your Recordset.MoveFirst PRIOR to entering the loop. Your .movefirst is inside the Loop, so each time you loop you are moving to the first record.

'Snippet:

set rst = db.openrecordset("USMCisBEST&quot

rst.MoveFirst

Do Until rst.EOF
'Beat up Airmen for fun
'Execute code here
rst.MoveNext
Loop

' End snippet here...

You, my wild-blue-yonder friend are running the functional equivalent of:

dim i as integer

do until i = 2
i = 1
loop

You'll never leave a loop (or reach its end) if you keep the recordset on the first record.

I hope this helps, and please know that I hold the Air Force (chair Force?) in high regard. This is, as you must know, benign inter-service rivalry.

Semper Fi, Aim High, and ROCK ON!

Kevin

[NOTE: It looks to me like you've got more problems than just not being able to exit your loop. Post a more detailed explantion of your set-up (or email me your app krsherm@hotmail.com) and I'd be happy to work through it with you]