Problem cycling through records on a form

[buzz25]

Hi,

I have a simple table and a form associated with it for data entry. I wanted to allow users to be able to open a specific record on the form for data entry. I therefore created a separate form with a control for choosing record ID's. After choosing a record ID users are taken directly to that record on the main form.

This is the problem: Once users are at their chosen record on the main form, I want them to be able to cycle through records on either side of the record ID they have chosen. I am unable to do this. I tried to use the wizard to create the record navigation buttons for "next record" and "previous record" but that dosen't work. Is there code I can write for custom buttons to cycle through records based on the record ID?

Any help will be appreciated!

- A

[puppydogbuddy]

Hi,

I have a simple table and a form associated with it for data entry. I wanted to allow users to be able to open a specific record on the form for data entry. I therefore created a separate form with a control for choosing record ID's. After choosing a record ID users are taken directly to that record on the main form.

This is the problem: Once users are at their chosen record on the main form, I want them to be able to cycle through records on either side of the record ID they have chosen. I am unable to do this. I tried to use the wizard to create the record navigation buttons for "next record" and "previous record" but that dosen't work. Is there code I can write for custom buttons to cycle through records based on the record ID?

Any help will be appreciated!

- A

Before doing anything else, place your main form in design view, invoke the property sheet, and set the following 2 properties:

Navigation= Yes
Cycle = All records

[buzz25]

Before doing anything else, place your main form in design view, invoke the property sheet, and set the following 2 properties:

Navigation= Yes
Cycle = All records

I tried that. Navigation buttons are enabled and Cycle = All records. That works when I open the main form directly. However when I use my second form to choose a record ID and goto the main form that way; it shows only one record and I can't cycle through the records.

- A

[puppydogbuddy]

I tried that. Navigation buttons are enabled and Cycle = All records. That works when I open the main form directly. However when I use my second form to choose a record ID and goto the main form that way; it shows only one record and I can't cycle through the records.

- A

Ok, it sounds like you apply a filter when displaying a record on the main form. If that is the case, either the filter needs to be turned off, or you need to use a method that does not use a filter to display the record in your main form.

It would be helpful if you would post the code you use to display the selected record in the main form, and also the record source (if any) for your main form.

[buzz25]

Ok, it sounds like you apply a filter when displaying a record on the main form. If that is the case, either the filter needs to be turned off, or you need to use a method that does not use a filter to display the record in your main form.

It would be helpful if you would post the code you use to display the selected record in the main form, and also the record source (if any) for your main form.

Hi Thanks for the reply. I have a filter on the main form. My second form is a "welcome" page where I have a combox where users select records by "name" or "record ID". The "on update" property of this combo box has the following code in the event procedure. In the code below, "Master" is the name of my table linked to the main form and "Subid" refers to the "record ID"

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
If IsNull(Me.Combo2) Then GoTo ExitHere
DoCmd.OpenForm "Master", , , "[Subid]=" & Me.Combo2
ExitHere:
Exit Sub
HandleError:
MsgBox ("Error: " & Err.Number & " " & Err.Description)
Resume ExitHere
End Sub

The main form has the following properties
Record Source = "Master"
Filter = [Subid]

Thanks so much for your time and help on this.

-A

[puppydogbuddy]

Hi Thanks for the reply. I have a filter on the main form. My second form is a "welcome" page where I have a combox where users select records by "name" or "record ID". The "on update" property of this combo box has the following code in the event procedure. In the code below, "Master" is the name of my table linked to the main form and "Subid" refers to the "record ID"

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
If IsNull(Me.Combo2) Then GoTo ExitHere
DoCmd.OpenForm "Master", , , "[Subid]=" & Me.Combo2
ExitHere:
Exit Sub
HandleError:
MsgBox ("Error: " & Err.Number & " " & Err.Description)
Resume ExitHere
End Sub

The main form has the following properties
Record Source = "Master"
Filter = [Subid]

Thanks so much for your time and help on this.

-A

Yup, you are setting a filter with your openForm statement.....and the filter is limiting the records to the filter. An easy way to get around this is to not use the filter or where arguments of the OpenForm. Instead, Find the record in the RecordsetClone of the form after you open it as shown below:

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
 
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[Subid]=" & Me.Combo2
 
If IsNull(Me.Combo2) Then
    GoTo ExitHere
End If
DoCmd.OpenForm "Master"
With Forms!Master
      Set rs = .RecordsetClone
       rs.FindFirst strWhere
       If rs.NoMatch Then
             MsgBox "Not found"
       Else
.            Bookmark = rs.Bookmark
       End If
End With
Set rs= Nothing
ExitHere:
    Exit Sub
HandleError:
    MsgBox ("Error: " & Err.Number & " " & Err.Description)
    Resume ExitHere
End Sub
 

[buzz25]

Yup, you are setting a filter with your openForm statement.....and the filter is limiting the records to the filter. An easy way to get around this is to not use the filter or where arguments of the OpenForm. Instead, Find the record in the RecordsetClone of the form after you open it as shown below:

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
 
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[Subid]=" & Me.Combo2
 
If IsNull(Me.Combo2) Then
    GoTo ExitHere
End If
DoCmd.OpenForm "Master"
With Forms!Master
      Set rs = .RecordsetClone
       rs.FindFirst strWhere
       If rs.NoMatch Then
             MsgBox "Not found"
       Else
.            Bookmark = rs.Bookmark
       End If
End With
Set rs= Nothing
ExitHere:
    Exit Sub
HandleError:
    MsgBox ("Error: " & Err.Number & " " & Err.Description)
    Resume ExitHere
End Sub
 

Thanks for the tip. I changed the "AfterUpdate" property of my combobox to the code you suggested. Now when I pick a record now from the combobox I get the following run time error: "error 2445: you have entered an expression that has an invalid reference to the property bookmark"

any clues?

[puppydogbuddy]

Thanks for the tip. I changed the "AfterUpdate" property of my combobox to the code you suggested. Now when I pick a record now from the combobox I get the following run time error: "error 2445: you have entered an expression that has an invalid reference to the property bookmark"

any clues?

Yes, combo2 is not on the main form, so the following reference using me is incorrect.

Change this:
strWhere = "[Subid]=" & Me.Combo2

To this:
strWhere = "[Subid]=" & Forms!YourForm2!Combo2

Replace yourForm2 with the actual form name in your app

[buzz25]

Yes, combo2 is not on the main form, so the following reference using me is incorrect.

Change this:
strWhere = "[Subid]=" & Me.Combo2

To this:
strWhere = "[Subid]=" & Forms!YourForm2!Combo2

Replace yourForm2 with the actual form name in your app

Still getting the same error :(

this is the code for "AfterUpdate" property of combobox "Combo2" on form "WelcomePage"
The main form is called "Master"

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2
If IsNull(Forms!WelcomePage!Combo2) Then
GoTo ExitHere
End If
DoCmd.OpenForm "Master"
With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing
ExitHere:
Exit Sub
HandleError:
MsgBox ("Error: " & Err.Number & " " & Err.Description)
Resume ExitHere
End Sub


Do i need to do anything with the "OnOpen" property of the main form?

[puppydogbuddy]

Still getting the same error :(

this is the code for "AfterUpdate" property of combobox "Combo2" on form "WelcomePage"
The main form is called "Master"

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2
If IsNull(Forms!WelcomePage!Combo2) Then
GoTo ExitHere
End If
DoCmd.OpenForm "Master"
With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing
ExitHere:
Exit Sub
HandleError:
MsgBox ("Error: " & Err.Number & " " & Err.Description)
Resume ExitHere
End Sub


Do i need to do anything with the "OnOpen" property of the main form?

Not that I can think of off the top of my head...except, maybe: the WelcomePage does have to remain open (can be invisible) until the Find is done. If you are closing the Welcome page in the open event of main, then make invisible instead, or put the code to close at the end of the afterupdate code. Tell me what is happening.

Well, try changing this:
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2

To:
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2.Value

[buzz25]

Not that I can think of off the top of my head...except, maybe: the WelcomePage does have to remain open (can be invisible) until the Find is done. If you are closing the Welcome page in the open event of main, then make invisible instead, or put the code to close at the end of the afterupdate code. Tell me what is happening.

Well, try changing this:
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2

To:
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2.Value

The form "WelcomePage" stays open throughout. I removed the filter from the main form "Master". All the event properties on the main from are blank except the "OnCurrent" property where I had written some code to show/hide certain fields. None of these are dependent to the primary key "Subid" though.
Adding ".Value" still gives the same error. Help!

[puppydogbuddy]

Ok Buzz,
I am going to need you to debug by following these steps:

Put the word STOP in at the top of (the line before) your AfterUpdate procedure. Then start your application as you normally do. When the code executes, it will literally open the VBA window up and highlight the line that "Stop" is on. From there, you can "step" through the code one line at a time, using the <F8> key. As you pause on each line, move your cursor and hover over your variables (like strWhere) and values, Access will pop-up values in yellow baloon boxes. You can check your code in this way to make sure that everything is working as it should. Also, while you are stepping thru the code, note if the code is executing properly. Then, tell me what happens.

[puppydogbuddy]

Also, just to make sure. The syntax you are using for the find is to find a numeric string:

strWhere = "[Subid]=" & Forms!WelcomePage!Combo2.Value

If [Subid] is a text field, the syntax should be as follows:
strWhere = "[Subid]= '" & Forms!WelcomePage!Combo2.Value & "'"

[buzz25]

Ok Buzz,
I am going to need you to debug by following these steps:

Put the word STOP in at the top of (the line before) your AfterUpdate procedure. Then start your application as you normally do. When the code executes, it will literally open the VBA window up and highlight the line that "Stop" is on. From there, you can "step" through the code one line at a time, using the <F8> key. As you pause on each line, move your cursor and hover over your variables (like strWhere) and values, Access will pop-up values in yellow baloon boxes. You can check your code in this way to make sure that everything is working as it should. Also, while you are stepping thru the code, note if the code is executing properly. Then, tell me what happens.

Hi, thanks for the advice. I did as you asked. I am pasting the steps of the code as they were executed one line at a time.

Steps executed in debug mode:

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2.Value
If IsNull(Forms!WelcomePage!Combo2.Value) Then
End If
DoCmd.OpenForm "Master"

(Here it executes code I wrote for a subform in the main form to limit users from entering only 3 enteries)

Private Sub Form_Current()
If Me.NewRecord Then
If Me.RecordsetClone.RecordCount > 2 Then
MsgBox "Only three entries allowed."
Me.Recordset.MoveFirst
End If
End If

End Sub

(It executes the above code again)

Private Sub Form_Current()
If Me.NewRecord Then
If Me.RecordsetClone.RecordCount > 2 Then
MsgBox "Only three entries allowed."
Me.Recordset.MoveFirst
End If
End If
End Sub

(Here it exectues code for showing/hiding certain feilds in the main form)

Private Sub Form_Current()
On Error GoTo HandleError


If Me.scrbpcheck = False Then
Me.scrdatebp1.Visible = True

Me.scrdatebp1.Enabled = True

Else
Me.scrdatebp1.Visible = False

Me.scrdatebp1.Enabled = False

End If
If Me.screlig = False Then
Me.screligtxt.Visible = True

Me.screligtxt.Enabled = True

Else
Me.screligtxt.Visible = False

Me.screligtxt.Enabled = False

End If


ExitHere:
Exit Sub
HandleError:
MsgBox ("Error: " & Err.Number & " " & Err.Description)
Resume ExitHere
End Sub

(Now it goes back to the code for the combobox)

With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
Else
Bookmark = rs.Bookmark
MsgBox ("Error: " & Err.Number & " " & Err.Description)

(At this point I see the error message)

[buzz25]

Hi, I just wanted to clarify that the code for the "Current" properties of the main form and subform that I pasted above is the entire code, not just the steps that were executed.

For the combobox code I only pasted the steps that were executed.

Sorry if thats confusing

[puppydogbuddy]

Buzz,
One step you forgot to do was put your mouse cursor over the variables and obtain the values they are showing:

With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere <<<<<<<put your cursor on strWhere and tell me what value strWhere is showing and how does it relate to the selection you made from combo2.......

[buzz25]

Buzz,
One step you forgot to do was put your mouse cursor over the variables and obtain the values they are showing:

With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere <<<<<<<put your cursor on strWhere and tell me what value strWhere is showing and how does it relate to the selection you made from combo2.......

sorry about that.

strWhere = "[Subid] =1005"
rs.NoMatch = False
Bookmark = <You entered an expression that has an invalid reference to the property bookmark>

[puppydogbuddy]

Ok Buzz,
I think there is a problem with not being on the right form when the Find is done, perhaps because of the Current event code. We should be able to fix it by making sure focus is set on a control in the main form.

let's make a change to the following code, and try it:
Forms!Master!SubID.SetFocus ',,,,to make sure we are on the right form
With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere


Hopefully, that will do it. If not, let me know and I will get back to you later after I return from a client visit.

[buzz25]

Ok Buzz,
I think there is a problem with not being on the right form when the Find is done, perhaps because of the Current event code. We should be able to fix it by making sure focus is set on a control in the main form.

let's make a change to the following code, and try it:
Forms!Master!SubID.SetFocus ',,,,to make sure we are on the right form
With Forms!Master
Set rs = .RecordsetClone
rs.FindFirst strWhere


Hopefully, that will do it. If not, let me know and I will get back to you later after I return from a client visit.

nope still gives same error at the same place. sorry this is being such a pain.

[puppydogbuddy]

Buzz,
From your prior debugging, you got the following results:

strWhere = "[Subid] =1005"
rs.NoMatch = False
Bookmark = <You entered an expression that has an invalid reference to the property bookmark>


This tells us that strWhere had the correct numeric string and that rs.NoMatch was false, indicating that a match was found. The only problem is that the bookmark we are trying to set is incorrect......that means that at the time created the clone, the main form's record source was not updated so that it would be in synch with combo2. Consquently, I believe we need to requery the main form's recordset so that it in insynch with Combo2 as shown.

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
 
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2.Value
 
If IsNull(Forms!WelcomePage!Combo2) Then
      GoTo ExitHere
End If
 
DoCmd.OpenForm "Master"
Me.Requery           '<<<<<<<< synch Master form to value in combo2
With Forms!Master
      Set rs = .RecordsetClone         '<<<<clone the form's recordset
      rs.FindFirst strWhere
      If rs.NoMatch Then
           MsgBox "Not found"
     Else
          Bookmark = rs.Bookmark        
     End If
End With
Set rs = Nothing
 
ExitHere:
      Exit Sub
 
HandleError:
     MsgBox ("Error: " & Err.Number & " " & Err.Description)
     Resume ExitHere
End Sub
 

Let me know what happens now.

[buzz25]

Buzz,
From your prior debugging, you got the following results:

strWhere = "[Subid] =1005"
rs.NoMatch = False
Bookmark = <You entered an expression that has an invalid reference to the property bookmark>


This tells us that strWhere had the correct numeric string and that rs.NoMatch was false, indicating that a match was found. The only problem is that the bookmark we are trying to set is incorrect......that means that at the time created the clone, the main form's record source was not updated so that it would be in synch with combo2. Consquently, I believe we need to requery the main form's recordset so that it in insynch with Combo2 as shown.

Private Sub Combo2_AfterUpdate()
On Error GoTo HandleError
 
Dim strWhere As String
Dim rs As DAO.Recordset
strWhere = "[Subid]=" & Forms!WelcomePage!Combo2.Value
 
If IsNull(Forms!WelcomePage!Combo2) Then
      GoTo ExitHere
End If
 
DoCmd.OpenForm "Master"
Me.Requery           '<<<<<<<< synch Master form to value in combo2
With Forms!Master
      Set rs = .RecordsetClone         '<<<<clone the form's recordset
      rs.FindFirst strWhere
      If rs.NoMatch Then
           MsgBox "Not found"
     Else
          Bookmark = rs.Bookmark        
     End If
End With
Set rs = Nothing
 
ExitHere:
      Exit Sub
 
HandleError:
     MsgBox ("Error: " & Err.Number & " " & Err.Description)
     Resume ExitHere
End Sub
 

Let me know what happens now.

Hi,
Sorry for the delay in replying. I'm still getting the same error :(( at the same place. Also when I go back to the WecolmePage and pick another record I get a the MsgBox "Not Founnd" error.

[puppydogbuddy]

Hi,
Sorry for the delay in replying. I'm still getting the same error :(( at the same place. Also when I go back to the WecolmePage and pick another record I get a the MsgBox "Not Founnd" error.

Hmm....Now I am curious. Could you email me a sanitized version of your application (all sensitive info removed) so that I can look at it? My email address is with the VCard in my profile. Just download my vCard. Thanks.

[buzz25]

Hmm....Now I am curious. Could you email me a sanitized version of your application (all sensitive info removed) so that I can look at it? My email address is with the VCard in my profile. Just download my vCard. Thanks.

Hi,

So I figured out the problem! It was a missing "." before "Bookmark". Thank god. I was really at my wits end! The app works fine now with the very first code you had sent and allows me to cycle through records in the main form. Thanks so much for your help! Sorry that a single "." caused so much trouble!

Much appreciate your time! :)

[puppydogbuddy]

Hi,

So I figured out the problem! It was a missing "." before "Bookmark". Thank god. I was really at my wits end! The app works fine now with the very first code you had sent and allows me to cycle through records in the main form. Thanks so much for your help! Sorry that a single "." caused so much trouble!

Much appreciate your time! :)

Buzz,
Glad you got it resolved. Thanks for the feedback. So, for the record the code posted on July 2nd, 2007 05:52 PM #6 is the correct solution, except that Bookmark had to be changed to .Bookmark on line #18.