Searching for common field names in Nav Menu (1 Viewer)

[sbrown106]

Hi everyone, I have been trying to pull out common fields in a textbox above a navigation menu so as I browse and search for something if I click on a different menu (say hospital in the example attached) the patient name would still be visible. Ive been reading past threads and I remember reading one from Pat that said its not possible to retrieve fields from a form thats not active in the menu (hope I understood that correctly). I have a working example of something that works to do this by copying a form and putting it above the navigation menu, so when I click on a PatientID - that stays in place if I click on another tab so I can still reference the original information. DBguy helped me a few weeks ago with the browse to search on nav forms - is it possible to still show all the records when the user has browsed to the form he is looking for? the browse takes me straight to the form but then i lose the other records - there may be a better way of doing this than my example - any help would be great, thanks -I'm still picking this up slowly!

 

[June7]

If this were a Tab control with subforms, I would use code that goes to a record instead of applying filter to form. I did some testing. This seems to work:

Private Sub Command13_Click()
  Dim strCriteria As String
  Dim rst As DAO.Recordset
  Set rst = Me.RecordsetClone
  strCriteria = "[fldPatientID] = " & Me![txtSearchPatientID]
  rst.FindFirst strCriteria
  Me.Bookmark = rst.Bookmark
  Me.NavigationSubform.SetFocus
  GoToPatient
End Sub

Private Sub NavigationButton7_Click()
GoToPatient
End Sub

Private Sub NavigationButton9_Click()
GoToPatient
End Sub

Private Sub NavigationButton11_Click()
GoToPatient
End Sub

Private Sub GoToPatient()
With Forms!frmNavigation.NavigationSubform.Form.RecordsetClone
    .FindFirst "fldPatientID=" & Forms!frmNavigation.txtSearchPatientID
    Forms!frmNavigation.NavigationSubform.Form.Bookmark = .Bookmark
End With
End Sub

 

[arnelgp]

see also this demo.
same thing i suggest on you previous thread but you never listen.

 

[sbrown106]

see also this demo.
same thing i suggest on you previous thread but you never listen.

thanks for that, I'm still trying to learn VBA with access.Thanks

 

[sbrown106]

see also this demo.
same thing i suggest on you previous thread but you never listen.

If this were a Tab control with subforms, I would use code that goes to a record instead of applying filter to form. I did some testing. This seems to work:

Private Sub Command13_Click()
  Dim strCriteria As String
  Dim rst As DAO.Recordset
  Set rst = Me.RecordsetClone
  strCriteria = "[fldPatientID] = " & Me![txtSearchPatientID]
  rst.FindFirst strCriteria
  Me.Bookmark = rst.Bookmark
  Me.NavigationSubform.SetFocus
  GoToPatient
End Sub

Private Sub NavigationButton7_Click()
GoToPatient
End Sub

Private Sub NavigationButton9_Click()
GoToPatient
End Sub

Private Sub NavigationButton11_Click()
GoToPatient
End Sub

Private Sub GoToPatient()
With Forms!frmNavigation.NavigationSubform.Form.RecordsetClone
    .FindFirst "fldPatientID=" & Forms!frmNavigation.txtSearchPatientID
    Forms!frmNavigation.NavigationSubform.Form.Bookmark = .Bookmark
End With
End Sub

Hi June7,
Thanks for that, both yours and Arnelgp work well. I'm trying to learn VBA so its been really helpful from both of you. There is one thing that both answers do that I'm trying to fix, which is if I enter a value for a PatientID, say 2 and search, then the search works correctly.If I delete the value and then search then I get an error (3077 Syntax Operator missing expression) - which you would expect because there is nothing to search for and the debug points to the line 'rst.FindFirst strCriteria'. So I am trying to catch this user error.
Ive tried to put a MsgBox in forcing the user to enter a value, but it doesnt seem to work

strCriteria = "[fldPatientID] = " & Me![txtSearchPatientID]
Debug.Print strCriteria
' If strCriteria = Null Then
' MsgBox "enter val"
' End If
rst.FindFirst strCriteria
Me.Bookmark = rst.Bookmark

Is there a way to force the user to enter a value if the box is empty? I may be misunderstanding the problem . I tried a search for the strCriteria to see how it worked but couldnt find much - so I'm not sure how the 'rst.FindFirst strCriteria' line works

Any help/advice would be great - thanks

 

[June7]

To force user, you have the idea.

Your code assumes fldPatientID is a number type.

Can't use = Null, nothing is ever equal to Null, not even Null, because there is nothing there to test against. Use IsNull() or Is Null. Also, a variable declared as string or number type cannot hold Null.

strCriteria is just a variable set to the filter criteria string. Code can be written without.

If IsNull(Me.txtSearchPatientID) Then
    MsgBox "enter val"
Else
    rst.FindFirst "[fldPatientID] = " & Me![txtSearchPatientID]
    Me.Bookmark = rst.Bookmark
End If

 

[sbrown106]

To force user, you have the idea.

Your code assumes fldPatientID is a number type.

Can't use = Null, nothing is ever equal to Null, not even Null, because there is nothing there to test against. Use IsNull() or Is Null. Also, a variable declared as string or number type cannot hold Null.

strCriteria is just a variable set to the filter criteria string. Code can be written without.

If IsNull(Me.txtSearchPatientID) Then
    MsgBox "enter val"
Else
    rst.FindFirst "[fldPatientID] = " & Me![txtSearchPatientID]
    Me.Bookmark = rst.Bookmark
End If

Thanks for that, that works well now- I just needed to move the 'goto' inside the loop because it kept on tripping with the same error , inside the function I'd forgotten to do that.
Just one final question - Ive noticed with recordsets do i need a rst.Close with Recordsetclone?
Ive noticed that sometimes the database size increases when I close so I use the compact and repair tool. Ive read its sometimes because recordsets arent closed - is this necessary with recordsetclone?
thanks again

 

[June7]

Access automatically closes objects and clears variables when procedure ends. But never hurts to explicitly close and set object variables to nothing.

It is possible to use RecordsetClone without even setting a recordset variable.

Also, I should have suggested:

If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark

 

[Pat Hartman]

I don't use the Access navigation form because of its limitations and quirks but let me start with - it is intended to be a menu/switchboard so all forms that show on the nav form are "top" level forms. They are not technically subforms although you need to address them as if they are. I don't know if that is how you are using the form. I can't guarantee it but I think you can't nest a navigation form on another navigation form so someone pipe in if I'm wrong.

If I have a cluster of data that needs multiple forms/subforms to display due to the volume of information, I use a main form with a tab control. So I can have a Customer form and and have tabs for contacts, orders, order history summary, shipping locations, etc. I.e. everything you ever wanted to know about a customer all in one place.