Message box on Form if record not found

Y

yvette

I have a form that pulls up a record that matches a control. I used the form
wizard to do this. Below is the VBA code. How do I modify the VBA code so
that a message box will appear if someone tries to pull up information for a
record that doesn't exist?
 
J

Jeff Boyce

Yvette

The code you included seems a little ... sparse ...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Y

yvette

Thanks Jeff. Sorry for the spastic moment.

I want to insert

MsgBox "Sorry, that patient is not a current patient.", , "No record found"


Below is the existing VBA code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
 
M

Mr. B

yvette,

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Sorry, that patient is not a current patient.", , "No
record found"
End If
End Sub

HTH
Mr. B
askdoctoraccess dot com


yvette said:
Thanks Jeff. Sorry for the spastic moment.

I want to insert

MsgBox "Sorry, that patient is not a current patient.", , "No record found"


Below is the existing VBA code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Jeff Boyce said:
Yvette

The code you included seems a little ... sparse ...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Y

yvette

Thanks for the suggestion but it doesn't seem to work. The message doesn't
pop up when the record is not found.


Mr. B said:
yvette,

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Sorry, that patient is not a current patient.", , "No
record found"
End If
End Sub

HTH
Mr. B
askdoctoraccess dot com


yvette said:
Thanks Jeff. Sorry for the spastic moment.

I want to insert

MsgBox "Sorry, that patient is not a current patient.", , "No record found"


Below is the existing VBA code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Jeff Boyce said:
Yvette

The code you included seems a little ... sparse ...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a form that pulls up a record that matches a control. I used the
form
wizard to do this. Below is the VBA code. How do I modify the VBA code
so
that a message box will appear if someone tries to pull up information for
a
record that doesn't exist?
Click to expand...
Click to expand...
Click to expand...
 
M

Mr B

yvette,

Sorry, I did not pay enough attention.

Let's take a little different approach.

Try this code:

Dim strSelectedVal As String
Dim varRecCnt
strSelectedVal = Me.SelectMRN
varRecCnt = DCount("NameOfYourId", "NameOfYourTable", _
"MRN= '" & strSelectedVal & "'")
If varRecCnt = 0 Then
MsgBox "Sorry, that patient is not a current patient.", , "No record
found"
Else
Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & strSelectedVal & "'"
Me.Bookmark = rs.Bookmark
End If

This is just a different way of determining if there is a record already in
the table and providing feedback to the user or taking action.

--
HTH

Mr B
askdoctoraccess dot com


yvette said:
Thanks for the suggestion but it doesn't seem to work. The message doesn't
pop up when the record is not found.


Mr. B said:
yvette,

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Sorry, that patient is not a current patient.", , "No
record found"
End If
End Sub

HTH
Mr. B
askdoctoraccess dot com


yvette said:
Thanks Jeff. Sorry for the spastic moment.

I want to insert

MsgBox "Sorry, that patient is not a current patient.", , "No record found"


Below is the existing VBA code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

:

Yvette

The code you included seems a little ... sparse ...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a form that pulls up a record that matches a control. I used the
form
wizard to do this. Below is the VBA code. How do I modify the VBA code
so
that a message box will appear if someone tries to pull up information for
a
record that doesn't exist?
Click to expand...
Click to expand...
Click to expand...
 
Y

yvette

That works beautifully -- thank you, Mr. B!!! You rock!!


Mr B said:
yvette,

Sorry, I did not pay enough attention.

Let's take a little different approach.

Try this code:

Dim strSelectedVal As String
Dim varRecCnt
strSelectedVal = Me.SelectMRN
varRecCnt = DCount("NameOfYourId", "NameOfYourTable", _
"MRN= '" & strSelectedVal & "'")
If varRecCnt = 0 Then
MsgBox "Sorry, that patient is not a current patient.", , "No record
found"
Else
Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & strSelectedVal & "'"
Me.Bookmark = rs.Bookmark
End If

This is just a different way of determining if there is a record already in
the table and providing feedback to the user or taking action.

--
HTH

Mr B
askdoctoraccess dot com


yvette said:
Thanks for the suggestion but it doesn't seem to work. The message doesn't
pop up when the record is not found.


Mr. B said:
yvette,

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Sorry, that patient is not a current patient.", , "No
record found"
End If
End Sub

HTH
Mr. B
askdoctoraccess dot com


:

Thanks Jeff. Sorry for the spastic moment.

I want to insert

MsgBox "Sorry, that patient is not a current patient.", , "No record found"


Below is the existing VBA code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

:

Yvette

The code you included seems a little ... sparse ...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a form that pulls up a record that matches a control. I used the
form
wizard to do this. Below is the VBA code. How do I modify the VBA code
so
that a message box will appear if someone tries to pull up information for
a
record that doesn't exist?
Click to expand...
Click to expand...
Click to expand...
 
M

Mr B

yvette,

You are quite welcome. Glad to help.

Good luck with your project.
--
Mr B
askdoctoraccess dot com


yvette said:
That works beautifully -- thank you, Mr. B!!! You rock!!


Mr B said:
yvette,

Sorry, I did not pay enough attention.

Let's take a little different approach.

Try this code:

Dim strSelectedVal As String
Dim varRecCnt
strSelectedVal = Me.SelectMRN
varRecCnt = DCount("NameOfYourId", "NameOfYourTable", _
"MRN= '" & strSelectedVal & "'")
If varRecCnt = 0 Then
MsgBox "Sorry, that patient is not a current patient.", , "No record
found"
Else
Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & strSelectedVal & "'"
Me.Bookmark = rs.Bookmark
End If

This is just a different way of determining if there is a record already in
the table and providing feedback to the user or taking action.

--
HTH

Mr B
askdoctoraccess dot com


yvette said:
Thanks for the suggestion but it doesn't seem to work. The message doesn't
pop up when the record is not found.


:

yvette,

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Sorry, that patient is not a current patient.", , "No
record found"
End If
End Sub

HTH
Mr. B
askdoctoraccess dot com


:

Thanks Jeff. Sorry for the spastic moment.

I want to insert

MsgBox "Sorry, that patient is not a current patient.", , "No record found"


Below is the existing VBA code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[MRN] = '" & Me![SelectMRN] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

:

Yvette

The code you included seems a little ... sparse ...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a form that pulls up a record that matches a control. I used the
form
wizard to do this. Below is the VBA code. How do I modify the VBA code
so
that a message box will appear if someone tries to pull up information for
a
record that doesn't exist?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combobox 1
Adding new record in form/not pulling from combo 6
Data entry to table from a form 1
Dlookup in Form want pull data correctly from Table 4
How To Move To Last Record in Form 2
Search box in Form not linked to table? 3
In SubForm Find the record that matches the control 0
Data entry VBA 1

Top