Requery form

[KateB]

I have a form that when opened asks for a patient's NHS number as criteria
for the underlying query. The main form then gives the 'personal'
information, and a subform displays all entries for that person. However, I
can't work out how to requery the original form/query to call up another
patient without closing and reopening so that it asks me for the criteria
again.

Is this possible? Is it something simple that I'm missing?!

Many thanks,
Kate

 

[Daryl S]

Kate -

I assume you have a control that the user will use to go to another record
(like a button on the form).

First, find out where the code currently is - try the Form's Open event.
You can call that event. For example, if the code runs on the Form_Open
event, you can add this to the code begind the button:
Call Form_Open(0)

If there are other things the current code does that you don't want to
happen when the user clicks on the button, then you may have to split out the
code that asks for the criteria into a separate public function or
subroutine, and then call this code from both the original code and from the
new code.

 

[KateB]

Hi Daryl,

Thanks for your help - when I sat and thought about it I realised what I
thought I could do probably isn't possible. Instead, I added an unbound text
box and used the AfterUpdate event so the number can be entered there
instead. (I'd used it on another form but had forgotten all about it). It
seems to be working OK.

Just in case anyone else might find it useful, the VBA is as follows (where
NHSno is the unbound text box, and NHSnumber is the search field. This was
cribbed from another post on this forum a long while ago!):

Private Sub NHSno_AfterUpdate()

Dim strWhere As String
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
If Not IsNull(Me.NHSno) Then
strWhere = "[NHSnumber] = " & Me.NHSno
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Check the number is correct and try
again."
Else
Me.Bookmark = .Bookmark
End If
Me.NHSno = Null
End With
End If
End Sub

I do appreciate your assistance though - thank you for your time.


Kate

 

[Daryl S]

Kate -

Looks like a great solution - glad it is working for you, and thanks for
sharing it.
--
Daryl S

Hi Daryl,

Thanks for your help - when I sat and thought about it I realised what I
thought I could do probably isn't possible. Instead, I added an unbound text
box and used the AfterUpdate event so the number can be entered there
instead. (I'd used it on another form but had forgotten all about it). It
seems to be working OK.

Just in case anyone else might find it useful, the VBA is as follows (where
NHSno is the unbound text box, and NHSnumber is the search field. This was
cribbed from another post on this forum a long while ago!):

Private Sub NHSno_AfterUpdate()

Dim strWhere As String
If Me.Dirty Then 'Save before move.
Me.Dirty = False
End If
If Not IsNull(Me.NHSno) Then
strWhere = "[NHSnumber] = " & Me.NHSno
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Check the number is correct and try
again."
Else
Me.Bookmark = .Bookmark
End If
Me.NHSno = Null
End With
End If
End Sub

I do appreciate your assistance though - thank you for your time.


Kate

Kate -

I assume you have a control that the user will use to go to another record
(like a button on the form).

First, find out where the code currently is - try the Form's Open event.
You can call that event. For example, if the code runs on the Form_Open
event, you can add this to the code begind the button:
Call Form_Open(0)

If there are other things the current code does that you don't want to
happen when the user clicks on the button, then you may have to split out the
code that asks for the criteria into a separate public function or
subroutine, and then call this code from both the original code and from the
new code.