For next loop 1

[hargy]

I need to set up a For Next loop to go through the values in a table.

As this table expands, I want the For Next loop to change with it - I dont want to have to manually change the For Next loop.

Any ideas?

 

[Maquis]

Sounds like you need a Do while not rs.eof loop instead.

Something like this:


Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tablename&quot

rs.MoveFirst
Do Until rs.EOF
<do stuff>
rs.MoveNext
Loop Maq B-)
<insert witty signature here>

 

[hargy]

Thats really cool - the only problem I have is in Dimensioning my variables at the begining of the function - what do I set them to. You'll have to excuse my ignorance - its the first code writing I have done in a good number of years since leaving Uni - I was a physicist and wrote a Fortran 77 modelling program - a piece of research for my Uni - you only had 4 or 5 different variable types, now there are thousands

 

[Maquis]

LOL, I know how you feel. I'm a PICK programmer myself and in PICK we don't have to type variables at all. We just use them and let the compiler figure out what they are. Regardless, I am used to having to type &quot;normal&quot; variable types (Number, String, Boolean), but Access does have a lot of variety to learn.

The ones you need right now are:
Dim dbs As Database
Dim rs As Recordset

The best way to learn is by writing the code and looking at good examples that others have written. Since you're already a programmer (not to mention a physicist) you'll pick it up quickly. Maq B-)
<insert witty signature here>

 

[hargy]

I now have the problem that the function I have just created is not defined the code is as follows. Where do I do that? I thought as it was a Public Function, that I was in fact defining it here for the rest of my code to call. Clearly something missing somewhere.

Public Function TableCheck() As String

' This function is to check through the Security table and check for authorisers
Dim rs As Recordset

Set rs = OpenRecordset(&quot;tblSecurity&quot

rs.MoveFirst
Do Until rs.EOF
' Do stuff
rs.MoveNext
Loop
End Function

I feel like a complete jerk asking questions like this, but again this seems an intrisic thing to VB - other languages define differently

 

[Maquis]

Need more info. Where is this function? In a module? Form? someplace else?

Where are you trying to call it from?

A Public function defined in a module is available to the entire database. A public function defined in a form is available to other routines in that form. If you only need to call this function from 1 form, then place it in that form.

You can read about scoping in Access help for more details. Maq B-)
<insert witty signature here>

 

[hargy]

Its in a module. To explain more -

What I am doing is building a Purchasing System for my Company. I was given some code which provides the NT Username and Workstation name of the person using the request system. This is coded into a module called mdlSecurity.

Also inside that module, I wanted to create an authentication function (which you have helped with). This checks an authentication table by moving through the table (currently inside my db, but later to be taken out for other dbs we have to use). This checks through the table for the relevant authorisers listed and checks the NT Username against the table. If these match and they are the correct authoriser for a task then the request is authorised. If not, then they are unable to do anymore. Also all users of the db can be logged, so I can monitor activity.

What I don't understand in vba is the definition of functions. Surely the Public statement is enough to define the function below it. I have 3 subforms that will need to use this function - I had hoped to make a more general function as part of the security module as the scope to expand this database to include Access changes, New Users etc as part of the daily activity here would be useful. As such I want the function to be used a lot and dont want to have to recode each time - as a newbie isn't that the point of object oriented programming?

One thing I've just noticed - when running the code the OpenRecordset command is highlighted - should that be Open.Recordset? Or am i using a differnet version of vba to yourself - I should have said I am using Access 2000. Is there a library I haven't got as part of my installation?

 

[Maquis]

Access 2000? Uh oh

I'm currently using Access 97 and the syntax I gave you works for 97, however between 97 and 2000, Microsoft totally changed the VBA language from DAO to ADO. (Don't ask what those acronyms stand for, I don't know.) You can still use my syntax by setting a reference to the DAO library in your &quot;Tools&quot; --> &quot;References&quot; menu and dimming the variables with a DAO prefix.
Dim dbs as dao.database
dim rs as dao.recordset.

However, the gurus will tell you that you should really be using the more modern ADO syntax. I'm still stuck in the DAO dark ages, so I can't advise you on the Access 200 syntax. I'm sure some of the bright folks lurking here can help.

As far as the problems with calling the function are concerned, I don't know the problem there. It may be another Access97/2000 syntax thing. Sounds like you set it up correctly. Public function in a module. You call it just by typing:
strSomevariable = TableCheck

Hmmm, make sure the module doesn't have the same name as the function. That could cause problems. Maq B-)
<insert witty signature here>

 

[hargy]

Thats really helpful and I have learned a lot - I loaded the correct library and fingers crossed everything will be cool.

I really like this iste - everyone in it is really helpful - the books you get on VB/VBA are helpful, but you really need other peoples experiences to get to the answers.

Thanks for your help again

 

[jebry]

Hi!

Just keep Maq's code with the following changes:

Dim rs As DAO.Recordset

and

Set rs = CurrentDb.OpenRecordset(&quot;tblSecurity&quot;, dbOpenDynaset)

The rest should work fine.

One last thing, you may need to set your reference for the DAO Object library. To do that go to the code window and click Tools - References. Scroll down the list and find the reference to the DAO(Data Access Object) 3.6 Library and make sure the box next to it is checked.

hth
Jeff Bridgham
bridgham@purdue.edu