Function Start and End

[Nylex]

When running code I dlookup record 1 in a table which is a control date
If the control is greater than date() it just opens the switchboard
If date() is less than control it starts another function
By using dlookup I want to read in the 1st record but don’t know how – I
usually know the RecordID and do it that way but in this instance I do not
know what is in the table
I want to read in 1st record – do certain calculations and then make an
entry ( I already have the system to do this)
Then I want to read in the 2nd record and so on to the last and how do I
know when there are no more records
Any help would be appreciated

 

[Arvin Meyer [MVP]]

Instead of DLookup use a recordset. You don't need to count them, although
you can if you like. Here's one to delete them 1 by 1:

Public Function DeleteOneByOne(strTbl As String)
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset(strTbl)

With rst
.MoveFirst
Do
.Delete
.MoveNext
Loop
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
Resume Exit_Here

End Function

 

[Brendan Reynolds]

When running code I dlookup record 1 in a table which is a control date
If the control is greater than date() it just opens the switchboard
If date() is less than control it starts another function
By using dlookup I want to read in the 1st record but don’t know how – I
usually know the RecordID and do it that way but in this instance I do not
know what is in the table
I want to read in 1st record – do certain calculations and then make an
entry ( I already have the system to do this)
Then I want to read in the 2nd record and so on to the last and how do I
know when there are no more records
Any help would be appreciated

To loop through records in a table (or query) use the OpenRecordset method.
Here's an example, and you can find more details in the help files by
searching for "OpenRecordset". One potential source of confusion to watch
out for is that Access and VBA support two alternative data access
technologies, DAO and ADO. Both technologies have an OpenRecordset method,
so when reading about them in the documentation or elsewhere always check
which data technology is being discussed. The example below is a DAO
example. Finally, it can be much more efficient, when possible, to perform
bulk updates using an update query rather than looping through a recordset.

Public Sub LoopThroughRecords()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Assets ORDER BY ID")
Do Until rst.EOF
Debug.Print rst.Fields("Description").Value
rst.MoveNext
Loop
rst.Close
Debug.Print "Finished"

End Sub

 

[Stefan Hoffmann]

hi Arvin,

you've got an infinite loop at the end...

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
Resume Exit_Here

End Function

mfG
--> stefan <--

 

[Arvin Meyer [MVP]]

Oops forgot the Exit Sub after Set db = Nothing. That's what I get for
typing code in a post instead of a code window.

 

[Stefan Hoffmann]

hi Arvin,

Oops forgot the Exit Sub after Set db = Nothing. That's what I get for
typing code in a post instead of a code window.

We need a compiler plug-in for our mail clients, maybe we should as cc


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

We need a compiler plug-in for our mail clients, maybe we should as cc

Meant 'ask'... my keyboard is just not that sensitive


mfG
--> stefan <--

 

[John W. Vinson]

hi,


Meant 'ask'... my keyboard is just not that sensitive

It's a law of Usenet that any post criticising a typo must contain a typo.

 

[Stefan Hoffmann]

hi John,

It's a law of Usenet that any post criticising a typo must contain a typo.

<g>


mfG
--> stefan <--