do... loop basics

[Guest]

I'm trying to load a table with records based on a previously executed
dlookup() statement.

I obviously don't know the first thing about do ... loops in VB or Access
(in ABAP I could do a select.. endselect statement to loop through a table
and do all sorts of things in between).

Can someone please set me straight on this or give me a link that shows
examples?

Here is what I want to do in English...

‘I have a RetUserID from a dlookup() statement further up in the code;

strSQL = "INSERT INTO tblSuperTemp (superid) values ('" & [RetUserID] & "')"

DoCmd.RunSQL strSQL 'this updates the table

strSQL = "" 'this clears the string

'load tblTempHold with employees under supervisor

Do while there are records to read
‘initialize values
SupersEmpID = ""
SupersEmpLName = ""
SupersEmpFName = ""
SupersEmpID = Nz(DLookup("EmployeeID", "TblEmp", "SupervisorID ='" &
[RetUserID] & "'"))
SupersEmpLName = Nz(DLookup("Lname", "TblEmp", "SupervisorID ='" &
[RetUserID] & "'"))
SupersEmpFName = Nz(DLookup("Fname", "TblEmp", "SupervisorID ='" &
[RetUserID] & "'"))
strSQL = "INSERT INTO ..... " etc.
DoCmd.RunSQL strSQL


'keep looping until there are no more records in TblEmp

Loop

I'll turn off the warnings before the do...loop and turn them back on. Am I
even on the right track here?

Help!
tia,

 

[Douglas J. Steele]

Actually, you're NOT explaining what you want to do in English: you've got
some code that may or may not be the best way of doing whatever it is you're
trying to do.

As well, it looks as though you're trying to store data redundantly: you're
looking up First Name and Last Name from one table in order to store it in
another table. The whole point of relational databases is that each piece of
information needs to be stored in one, and only one, place.

Maybe if you stepped back and did explain what you're trying to do we could
help you.

 

[Guest]

I need a means to limit access to reports. In my Annual Leave db, if an
employee logs on, I want them to be able to look at their leave information.
If the employee is a supervisor, I want them to be able to look at their
leave information as well as any leave information for any of their
employees. Supervisors can have supervisors under them. There are 3 levels
of supervisors: 1 = the director, 2 = his immediate supervisors and 3 = all
supervisors under level 2 supervisors.

My Employee table holds EmployeeID, first and last names, IsSupervisor
(yes/no), SupervisorID, among other fields.

I have a Reports Form which has an OnOpen event to capture the NTUserName,
from that I capture their UserID and whether they are a supervisor or just an
employee. If NTUser is a supervisor, they should be able to look at only
the information that pertains to them. If the NTUser is not a supervisor
then they are an employee and should be able to look at only their own leave
information. The Director should be able to look at everyone’s leave
information.

It’s a select cascade for a report problem that I’m facing. I thought that
by creating a temp table to query from, I would be able to limit access to
data.

I’m totally up for any suggestions.

Tiaa,
JMorrell

Actually, you're NOT explaining what you want to do in English: you've got
some code that may or may not be the best way of doing whatever it is you're
trying to do.

As well, it looks as though you're trying to store data redundantly: you're
looking up First Name and Last Name from one table in order to store it in
another table. The whole point of relational databases is that each piece of
information needs to be stored in one, and only one, place.

Maybe if you stepped back and did explain what you're trying to do we could
help you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to load a table with records based on a previously executed
dlookup() statement.

I obviously don't know the first thing about do ... loops in VB or Access
(in ABAP I could do a select.. endselect statement to loop through a table
and do all sorts of things in between).

Can someone please set me straight on this or give me a link that shows
examples?

Here is what I want to do in English...

'I have a RetUserID from a dlookup() statement further up in the code;

strSQL = "INSERT INTO tblSuperTemp (superid) values ('" & [RetUserID] & "')"

DoCmd.RunSQL strSQL 'this updates the table

strSQL = "" 'this clears the string

'load tblTempHold with employees under supervisor

Do while there are records to read
'initialize values
SupersEmpID = ""
SupersEmpLName = ""
SupersEmpFName = ""
SupersEmpID = Nz(DLookup("EmployeeID", "TblEmp", "SupervisorID ='" &
[RetUserID] & "'"))
SupersEmpLName = Nz(DLookup("Lname", "TblEmp", "SupervisorID ='" &
[RetUserID] & "'"))
SupersEmpFName = Nz(DLookup("Fname", "TblEmp", "SupervisorID ='" &
[RetUserID] & "'"))
strSQL = "INSERT INTO ..... " etc.
DoCmd.RunSQL strSQL


'keep looping until there are no more records in TblEmp

Loop

I'll turn off the warnings before the do...loop and turn them back on. Am I
even on the right track here?

Help!
tia,