Cycling thru Records in Access 1

[TaintedFlames]

Hi,
I trying cycle thru all records on a subform, in VBA. The subform is getting it's records from a Query. All I can't figure out right now is, what variables to use to cycle thru all records. I found me.CurrentRecord... but there's no AllRecord. Well that I could find.

Any help will be very much appriciated.



Jay

 

[TonyJollans]

Hi Jay,

Take a look at Me.Recordsetclone, but can you give a bit more detail of what you're trying to achieve because that doesn't sound like the best way of doing anything.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TaintedFlames]

Hey Tony,
Thanks for replying so fast.

Well I'm working on a Tracking System of sorts. And it's all pluged into an SQL server. For the Layout ,I have a form, that has a bunch of options and in the middle I have a subform, that queries a Table and only shows the records belonging to the users loging. That subform is in data Sheet view. What I want to do, is to cycle to all the records I get from the table and look at a certain field.

Jay

 

[TonyJollans]

Hi Jay,

It just seems odd to me that you're displaying one record and processing them all in some way which you can't do more easily with a separate query and/or some aggregate function. I'm not entirely sure how Access interacts with SQL Server so you may not find RecordSetClone useful - I'm not sure, sorry.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TaintedFlames]

Hey Tony,
that's alright.
It's pretty easy on my part. I have a table on the SQL Server, and then in access I have a table that links to it. Kinda like a pointer . So then it goes thru a Query and displays it in from in Data Sheet view. Kinda like a spread sheet, but you can add events. I hope this is enough info .

Jay

 

[fiep]

Jay,

To walk through all records in the form you could do the following.

dim rstRec as recordset
set rstRec = me.recordsetclone
rstRec.movefirst
while not restrec.EOF do
  ' do something with the record.
   rstRec.movenext
wend
if not rstRec is nothing then set rstRec = nothing

W.

 

[fiep]

Jay,

To walk through all records in the form you could do the following.

dim rstRec as recordset
set rstRec = me.recordsetclone
rstRec.movefirst
while not restrec.EOF do
  ' do something with the record.
wend

W.

 

[TaintedFlames]

Hey W.,
Awesome, trying it now!!!

Jay

 

[TaintedFlames]

Hey W,
It doesn't like that set rstRec = ...
Any ideas?

Jay

 

[TonyJollans]

Hi Jay,

As I said I don't know much about SQL Server - but it looks like the recordsetclone isn't working for you.

You say you want to cycle through the records looking at a certain field. What do you want to do with that field when it meets your criteria?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[RoyVidar]

Type mismatch, or method or data member not found?

1 - ensure you have a reference to Microsoft DAO 3.# Object Library (in VBE - Tools | References)
2 - do explicit declaration:

[tt]dim rs as dao.recordset
set rs = me.recordsetclone
rs.movefirst
do while not restrec.EOF
' something important
rs.movenext
loop[/tt]

(3 - trhough a forum search, top of the page, you may find there are seven dedicated Access fora, try them for Access questions - and not to forget - search - the first faq in TonyJollans signature is also worth a read;-))

Roy-Vidar

 

[TaintedFlames]

Hey,
to anwser to Tony, when I have a record with "severe", I will set it's background to red. The only problem is that I don't know how to that for one so far. But I haven't really looked at it yet.

Roy, Thanks! I will do that now, and read up about it!


Jay

 

[RoyVidar]

Can't do that with recordset approach I'm afraid (conditional formatting). Check out conditional formatting on the format menu (versions 2000+)

Roy-Vidar

 

[TaintedFlames]

Hi Guys and Girls,
I have another question. I got the clone to work, but now I want to access a field in that record. How do I do it? Is there a method that lets me specify?

Thank you!



Jay

 

[TaintedFlames]

Hey Roy,
What I was thinking is that I would have my records in a tabular form. I dunno if that would work or not yet. :S.

Keep ya infromed

Thanks again.

Jay

 

[TaintedFlames]

Hey,
I got the it working!!! Thank you all!! All I have to do now is to change the color . Which might be tricky !

Jay