Struggling with VBA in access, please help! (1 Viewer)

[uniboy]

I'm currently designing a database for a "personal training company". I've got code working for things like closing forms, passwords and error messages. Now in my forms, for example the Client Form, I'm trying to use code for the "Next" and "Previous" record button instead of using the wizard. I've had help from teachers and peers but it's not working and I really need some help. Here is what I have got so far with their help:

Option Compare Database

Option Explicit

Dim rstClientDetails As DAO.Recordset

Private Sub Form_Load()
'create a connection with the current database
Call setDatabaseConnection
'create a set of film detail records
Set rstClientDetails = dbase.OpenRecordset("tblClient", dbOpenDynaset)
End Sub

Private Sub Form_Activate()
'everytime this form is active, refresh the current record details
Call displayCurrentRecord
End Sub

Private Function validRecord() As Boolean
'check if record pointer is at a valid record
'-1 is a flag value used to indicate an invalid
'position in a record set
'MsgBox rstMemberDetails.AbsolutePosition

If rstClientDetails.AbsolutePosition = -1 Then
validRecord = False
Else
validRecord = True
End If
End Function

Private Sub displayCurrentRecord()
'assign form text boxes to values from a record set
'need to check record pointer is pointing to a valid record in the set ...
If validRecord Then
txtClientID.Value = rstClientDetails("ClientID")
txtTitle.Value = rstClientDetails("Title")
txtFirstName.Value = rstClientDetails("FirstName")
txtSurname.Value = rstClientDetails("Surname")
txtDOB.Value = rstClientDetails("DOB")
txtAddress1.Value = rstClientDetails("Address1")
txtAddress2.Value = rstClientDetails("Address2")
txtCity.Value = rstClientDetails("City")
txtPostCode.Value = rstClientDetails("Postcode")
txtTel_No.Value = rstClientDetails("Tel_No")
End If
End Sub



Private Sub cmdNext_Click()
If Not rstClientDetails.EOF Then
rstClientDetails.MoveNext
End If

If Not rstClientDetails.EOF Then
Call displayCurrentRecord
Else
MsgBox "End of Record Set"
rstClientDetails.MoveLast
End If

End Sub

Private Sub cmdPrevious_Click()
'write your code here
If Not rstClientDetails.BOF Then
rstClientDetails.MovePrevious
End If

If Not rstClientDetails.BOF Then
Call displayCurrentRecord
Else
MsgBox "Start of record set - no more records "
rstClientDetails.MoveFirst
End If
End Sub



'This will close the Client Form
Private Sub cmdClose_Click()
DoCmd.Close
End Sub




I would really appreciate the help guys, thanks

 

[Steve R.]

I assume that this a school assignment? Have you searched this forum for code samples? Do you have a reference book? My apologies, but what you have seems unduly complex, incorrect, and confusing which makes responding difficult.

 

[uniboy]

yeah steve that was quite complicated lol. if found this peice of code which now works:

DoCmd.RunCommand acCmdRecordsGoToNext

BUT when it gets to the end of the record set, it shows as a code error. what code can I add for my error message to appear when the end of records is reached

 

[boblarson]

yeah steve that was quite complicated lol. if found this peice of code which now works:

DoCmd.RunCommand acCmdRecordsGoToNext

BUT when it gets to the end of the record set, it shows as a code error. what code can I add for my error message to appear when the end of records is reached

in the error handler just use

If Err.Number <> 2501 Then
   Msgbox Err.Description, vbExclamation, "Error #: " & Err.Number
End If

And then it will just sit there if the user tries to go beyond. Your alternative is to let them know with a message box (using the Else part of the code I gave) to tell them that they can't move further.

 

[uniboy]

oh thank you so much mate. which "else" code are you refereing to tho? code you post it here please. Thank you so much!

 

[Steve R.]

yeah steve that was quite complicated lol. if found this peice of code which now works:

DoCmd.RunCommand acCmdRecordsGoToNext

BUT when it gets to the end of the record set, it shows as a code error. what code can I add for my error message to appear when the end of records is reached

Good for you. There are many different ways to a solution.

 

[boblarson]

oh thank you so much mate. which "else" code are you refereing to tho? code you post it here please. Thank you so much!

In the IF THEN ELSE statement:

If Err.Number <> 2501 Then 
   Msgbox Err.Description, vbExclamation, "Error #: " & Err.Number
End If

So it would be:

If Err.Number <> 2501 Then
   Msgbox Err.Description, vbExclamation, "Error #: " & Err.Number
Else
   Msgbox "You cannot move further", vbExclamation
End If

 

[uniboy]

Cheers mate