Custom record navigation snafu

[flora8]

I have a database with a switchboard form and a data entry form. On the data entry form, I disabled the record navigation buttons and subtituted my own because I needed some extra code behind them.

There are two ways to get to the data entry form from the switchboard - a button for "add new entry", and a combo for "go to specific record". The button for add new entry works just fine, and when I get to the data entry form, the navigation buttons work just fine.

However, when I select a specific record, it takes me to to the record, which is always then somehow record number 1 out of N, and when I try to use my navigation buttons, like to go to the next record, it opens a new record.

It also will not allow me to navigate to any other records. There's no error message, it just doesn't respond. I've tried the same thing with Access's record navigation buttons enabled, just to see what happens, it displays "(Filtered)" behind it. If I use Access's navigations buttons first, before my own, all the record navigation works fine.

What is wrong?? And more importantly, how can I fix this?

My code is below:

Go to specific record:

The Row Source for Combo24 is: SELECT Main.StudyID FROM Main;

Private Sub Combo24_CLick()
DoCmd.OpenForm "Main", , , "[StudyID] =" & "'" & Me![Combo24] & "'"
End Sub


And for the Next Record Button, it's:

Private Sub Command120_Click()
Dim Command120_Click As Integer
If IsNull(StudyID) Or IsNull(FName) Or IsNull(LName) Or IsNull(DateBaseline) Or IsNull(TimeBaseline) Then
Command120_Click = MsgBox("A required field is missing. The patient will be deleted if you don't fill it in. Is that what you want?", _
vbYesNo, "Required Field Missing!")
If Command120_Click = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.GoToRecord , , acNext
End If
If Command120_Click = vbNo Then
StudyID.SetFocus
End If
Else
DoCmd.Save
DoCmd.GoToRecord , , acNext

End If
Exit_Command120_Click:
Exit Sub
Err_Command120_Click:
MsgBox Err.Description
Resume Exit_Command120_Click
End Sub


Thanks in advance,
Jocelyn

 

[James Deckert]

>>DoCmd.OpenForm "Main", , , "[StudyID] =" & "'" & Me![Combo24] & "'"

You're specifying a filter, which if a primary key will only return one corresponding record. Navigation buttons will only navigate within records which are returned from the filter.

Here is the wizard code which Access generates for a lookup combo box. Maybe it'll help you.

Private Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Store ID] = " & Str(Nz(Me![Combo48], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

James

 

[flora8]

That makes sense. Now If I could only put the code together to work.

I pasted that code in, changing the names accordingly and now I get Run-time error 91 - Object variable or With block variable not set. and it highlights the Set rs = Me.Recordset.Clone line of code. How could I resolve this?

I do need some really explicit help with this, as I'm still pretty new to VBA.
Do I need to keep the previous DoCmd.OpenForm "Main", , , "[StudyID] =" & "'" & Me![Combo24] & "'" line somewhere? I'm guessing that I need some sort of DoCmd.OpenForm line somewhere in the code, I just don't know where.

Thanks,
Jocelyn