Update a table using the table as a recordset

[rbozza]

Good afternoon,

I've been given a project at work that i'm simply not up to the task for. I understand what needs to be done but lack the 'skill' to actually do it. I'm about halfway through it and have come to the part that i'm just not sure about (I'm a dba/sql guy so my VB is very very very rusty at this point). Hopefully this wont' turn into a wall of text.

A quick explanation: There are 2 tables (logons and logoffs) that hold the network login information for employees. I've been tasked with pulling the related information from both tables and putting it all into a very simple to read report for the boss.

Pulling the logon information is simple: its based on 3 fields in teh table (user, logondate, logontime) and pulling that is simple. I'm just pulling the information out and putting it into a temp table.

The tricky part is this: The logoffs don't match the logons neither by time, date, or anything else. To do this previously they were just running a code on the recordset to pull the first possible match and just puts that as the logoff records.

So my temp table currently has: User, Logondate, logontime, logoffdate, logofftime, and Invalid

The first 3 are complete. The last 2 i'm struggling with.


Basically what I think needs to happen is this:
1. I need to treat the temp table as a RS
2. I need to start at the BoF and on a record by record basis, and pull the first possible match from the Logoffs table and update the LogOffDate, and LogOfftime with valid matches, or set the Invalid to 'True' until the EoF. (the sql used for this would just be select logoffdate, logofftime from logoffs where logoffdate >= logondate and logofftime >= logontime and tempUser = logoffs.user)

The problem is I have no idea how to translate that to VBA in access at this point.

Any suggestions or can you point me to a tutorial that would be beneficial in this instance?


*edit*Just to add this, there is no direct 'link' between logons and logoffs or this would be cake to do. It's simply based on a general timeframe in descending order. So if someone logged in on the 29th it would look for the first logoff on the 29th, and the same on the 28th.

 

[rbozza]

OK this is what I've gotten so far. My Questions are below:

Dim DB As Database
Dim rsLogs As ADO.Recordset
'Select String
Dim Sql1 As String
'Update String
Dim Sql2 As String

Set DB = CurrentDb()

Dim User As String
Dim LogOnDate As Date
Dim LogOffDate As Date
'Table already ordered by Date/time descending
Set Sql1 = "Select User, LogOnDate, LogOffDate from tblLogs"

Set rsLogs = DB.OpenRecordset(Sql1)

If rsLogs.EOF = False Then

Else

End If

------------

Is it possible to set the User, LogOnDate, and LogOffDate after each row is read in order to pull the information based on those 3 variables in an if statement?