VBA detect end of records on form

[Crilen007]

I have a button that performs some options in my database however, i want to beable to cycle through the records till the last and then stop.

Im not sure what functions i can use to do this, ive tried searching for it with no luck, thanks in advance.

Private Sub cmdAutomate_Click()
Dim objAccess
Dim strPathToMDB
Dim strTableName
Dim strFilePath
Dim AmtRecords
Dim CurrentRecord
Const acImportDelim = 0
Const acFormatHTML = "HTML (*.html)"
Const acOutputTable = 3


AmtRecords = RecordCount
CurrentRecord = 1
Me.lblRecord = AmtRecords
Me.lblCurrent = CurrentRecord
    
Set objAccess = CreateObject("Access.Application.10")


  Do While CurrentRecord <= AmtRecords
   strPathToMDB = ContestPath & ContestDBName
   strFilePath = ContestPath & ContestName & ".htm"
   ' Open the desired database
   objAccess.OpenCurrentDatabase (strPathToMDB)

   ' Use the TransferText command to import the file (with Column Heads)
   ' objAccess.DoCmd.OutputTo acOutputTable, strTableName, acFormatHTML, strFilePath, True
   strTableName = "qmtSales"
   objAccess.DoCmd.OpenQuery strTableName, acNormal, acEdit
   strTableName = "qmtInside"
   objAccess.DoCmd.OpenQuery strTableName, acNormal, acEdit
   strTableName = "rptInside"
   objAccess.DoCmd.OutputTo acOutputTable, strTableName, acFormatHTML, strFilePath, False
 
 


   ' Clean up
   objAccess.CloseCurrentDatabase
   objAccess.Quit
   CurrentRecord = CurrentRecord + 1
   DoCmd.GoToRecord , , acNext
   Call MsgBox("Operation Completed", vbInformation Or vbSystemModal Or vbDefaultButton1, "Done")
   
Loop
    

 Set objAccess = Nothing
  
 
End Sub

 

[Crilen007]

Changed some code, still no luck. Just wish i had the function to tell me if im at the end of the records or not.

 

[GumbyD]

If you can get your data into a recordset object than you can use the do until loop as follows:

dim rstYourSQL as recordset

if rstYourSQL.EOF then
msgbox "No records in Loop",vbokonly
else
rstYourSQL.moveFirst
Do until rstYourSQL.EOF

'Do you stuff with the record
rstYourSQL.moveNext
Loop

I'm not sure from your code below if that will assist you or not. Good Luck!

GumbyD

 

[Crilen007]

I tried The following code, but it had an error on the bolded part.

Not really good with the recordset stuff yet.

Private Sub Automate2_Click()
Dim rstYourSQL As Recordset
Dim objAccess
Dim strPathToMDB
Dim strTableName
Dim strFilePath
Const acImportDelim = 0
Const acFormatHTML = "HTML (*.html)"
Const acOutputTable = 3
Set objAccess = CreateObject("Access.Application.10")

[b]If rstYourSQL.EOF Then[/b]
MsgBox "No records in Loop", vbOKOnly
Else
rstYourSQL.MoveFirst
Do Until rstYourSQL.EOF
   strPathToMDB = ContestPath & ContestDBName
   strFilePath = ContestPath & ContestName & ".htm"
   ' Open the desired database
   objAccess.OpenCurrentDatabase (strPathToMDB)

   ' Use the TransferText command to import the file (with Column Heads)
   ' objAccess.DoCmd.OutputTo acOutputTable, strTableName, acFormatHTML, strFilePath, True
   strTableName = "qmtSales"
   objAccess.DoCmd.OpenQuery strTableName, acNormal, acEdit
   strTableName = "qmtInside"
   objAccess.DoCmd.OpenQuery strTableName, acNormal, acEdit
   strTableName = "rptInside"
   objAccess.DoCmd.OutputTo acOutputTable, strTableName, acFormatHTML, strFilePath, False
 
   ' Clean up
   objAccess.CloseCurrentDatabase
   objAccess.Quit
   CurrentRecord = CurrentRecord + 1
   DoCmd.GoToRecord , , acNext
   rstYourSQL.MoveNext
Loop
Call MsgBox("Operation Completed", vbInformation Or vbSystemModal Or vbDefaultButton1, "Done")
Set objAccess = Nothing
End If
End Sub

 

[GumbyD]

You need to set your recordset value equal to the set of records you want to move through. Here would be an example:

Dim rstProducts as recordset

Set rstProducts = currentdb.openrecordset _("qryProducts",dbopendynaset)

if rstProducts.eof then
msgbox "no records in recordset",vbOKonly
Else
rstProducts.MoveFirst
do until rstproducts.EOF
debug.print rstproducts!name,rstproducts!cost
rstproducts.movenext
loop
end if

GumbyD

 

[Mile-O]

Since neither of you have mentioned which version of Access is being used here you've got to remember that a recordset object is used in both DAO and ADODB and it would be best to specify. The User-Defined Type not being recognised/found may be the error but, again, there's a lack of detail there too.

 

[Mile-O]

Dim objAccess
Dim strPathToMDB
Dim strTableName
Dim strFilePath
Dim AmtRecords
Dim CurrentRecord

As an aside you have created all these variables and prefixed the majority of them with either the accepted object prefix or the string prefix - they are neither object variables or string variables; they are Variants.

 

[Crilen007]

Hehe, it works ok tho =D

Here, i attatched the DB