I think my head IsNull

[Navyguy]

What I have is a form that I use as a filter to select a record for updating. It is called Frm-Resident Select Incident and the field I enter the criteria is called Incident Report Number.

I then click on a button the opens another form called Frm-Resident Incident Data based on a query that is filtered on the form Frm-Resident Select Incident.

If I enter a number of a record that already exists there is not problem, but if I enter anything else I always get a blank record. What I would like is a MsgBox to come up and say something to the effect that “there is no such record, try again”.

Here is the code that I have tried, but just getting an error saying that the field “|” cannot be found. Please tell me if I am using the right code or even if I am using it in the right place.

If IsNull([Frm-Resident Incident Data]![Incident Report Number]) Or ([Frm-Resident Incident Data]![Incident Report Number]) = "" Then
MsgBox "There Is No Incident Recorded By That Number", vbOKOnly, "Invalid Search Criterion!"
[Frm-Resident Select Incident]![Incident Report Number].SetFocus

As always, thanks for your help

Navyguy

 

[dcx693]

Why not use a DCount formula to check for the existence of the data in the data table before even bringing up the other form?

 

[Navyguy]

Thanks dcx693, that was quick...

Maybe you will have to point me in the right direction, but I don't want to count the records as it is either there or not. There will not be multiple instances of the criteria of Incident Report Number as this is a (PK).

If the user selects the correct Incident Report Number then the record is displayed in a new form for editing...so I am not sure where the DCount comes into play.

Maybe I just don't understand the use of the Dcount function (which is not really all that surprizing!!!)

Navyguy

 

[WayneRyan]

Navyguy,

If Nz(DCount("[Incident Report Number]", _
             "tblResidentIncident", _
             "[Incident Report Number] = " & Me.[Incident Report Number]), 0) = 0 Then
   MsgBox "There Is No Incident Recorded By That Number", vbOKOnly, "Invalid Search Criterion!"
   [Frm-Resident Select Incident]![Incident Report Number].SetFocus
End If

The DCount will count the number of records that have the
appropriate [Incident Report Number]. The Nz function
returns 0 if the DCount returns null.

If the value is 0, then do your message box, setfocus and
don't open the form.

Wayne

 

[dcx693]

What Wayne said....

 

[Navyguy]

All right Wayne

Ok that makes sense...I guess. Just a different way to think about it. Count the records and if you get 1 carry-on and if you don't get any "All Stop".

So I took your sample of code and pasted into where I think it should go and I get a new error stating that "The object doesn't contain the Automation object 'Test001'.

Test001 being my Incident report number of course. Does this error mean that I placed it in the wrong place on the VBA window?

BTW good guess on my table name!!! You get to move to the bonus round!!!

 

[WayneRyan]

Navyguy,

Can you post the code involved and show where you put it and
on what event?

Or post the db?

Wayne

 

[Navyguy]

As requested Wayne

The "View Incident Data Form" is the button that is linked to the form that displays the record for editing. So I pasted your code inside that function as I would think it is associated with that action.

I know that my naming conventions need work, and I was searching the forum regarding the error and only found replies that related to poor naming conventions...of course!!! This is all part of the vertical learning curve I am climbing.

Thanks for all your help, heres the code:

Private Sub View_Incident_Data_Form_Click()
On Error GoTo Err_View_Incident_Data_Form_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Frm-Resident Incident Data"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If Nz(DCount("[Incident Report Number]", _
"Tbl-Incident Details Resident", _
"[Incident Report Number] = " & Me.[Incident Report Number]), 0) = 0 Then
MsgBox "There Is No Incident Recorded By That Number", vbOKOnly, "Invalid Search Criterion!"
[Frm-Resident Select Incident]![Incident Report Number].SetFocus
End If

Exit_View_Incident_Data_Form_Click:
Exit Sub

Err_View_Incident_Data_Form_Click:
MsgBox Err.Description
Resume Exit_View_Incident_Data_Form_Click
End Sub

Navyguy (not part of the code he he he)

 

[WayneRyan]

Navyguy,

Don't know about the automation error.
If you have embedded spaces (tablename) then you have
to use the brackets. Best to just forget about having spaces
and special chars in your names.

Private Sub View_Incident_Data_Form_Click()
On Error GoTo Err_View_Incident_Data_Form_Click

Dim stDocName As String
Dim stLinkCriteria As String
'
' If no related records, msgbox, then exit sub
'
If Nz(DCount("[Incident Report Number]", _
             "[Tbl-Incident Details Resident]", _
             "[Incident Report Number] = " & Me.[Incident Report Number]), 0) = 0 Then
   MsgBox "There Is No Incident Recorded By That Number", vbOKOnly, "Invalid Search Criterion!"
   [Frm-Resident Select Incident]![Incident Report Number].SetFocus
   Exit Sub
End If

stDocName = "Frm-Resident Incident Data"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_View_Incident_Data_Form_Click:
   Exit Sub

Err_View_Incident_Data_Form_Click:
   MsgBox Err.Description
   Resume Exit_View_Incident_Data_Form_Click
End Sub

Wayne

 

[Navyguy]

Thanks again Wayne

I pasted your code and still get the error as before.

I was wondering, since I don't have a control source for the field [Incident Report Number], because the user would input the number, would this cause a problem? I have checked all the spelling and /grinding teeth/ spaces between words /grinding teeth/ and it all appears to be OK.

Thanks for your efforts

Navyguy

 

[Navyguy]

Update or lack there of...

I tried changing some things several items in the code. Even went back and changed the names and pasted it into an new DB but no luck. The error did change a couple of times back to the one that was first posted. As I reset everything I am back to the automation error,

Navyguy

 

[WayneRyan]

Navyguy,

Can you post your db?

Compact/Repair ... zip ... attach

Wayne

 

[WayneRyan]

Navyguy,

ZIP says its not a "valid file".

Can you try again?

Wayne

 

[Navyguy]

Hopefully this will work. I had to get rid of alot of stuff to get below the posting limit...reminds me of flying!!! I think there is enough to look at for this particular problem...

Thanks for your help and patience

Navyguy

 

[WayneRyan]

Navyguy,

You're gonna hate yourself ... but:

Needed single-quotes around Me.[Incident Report Number]
and the .SetFocus just needs a "Me." or you could have
had: Forms![form]![field].Setfocus. "Me." is easier.

If Nz(DCount("[Incident Report Number]", _
             "[Tbl-Incident Details Resident]", _
             "[Incident Report Number] = '" & Me.[Incident Report Number] & "'"), 0) = 0 Then
             MsgBox "There Is No Incident Recorded By That Number", vbOKOnly, "Invalid Search Criterion!"
   Me.[Incident Report Number].SetFocus
   Exit Sub
End If

Wayne

 

[Navyguy]

/Navyguy Jumping up and down/ He He He/Dancing in Circles singing out loud/ It Works!!! It Works!!!

With many many thanks. There is a reason you have over 2600 posts and I have 22!!!

Such a simple little feature makes all the difference, yet I think it has taken the most time for me to do...Oh the price of education

/Navyguy leaving the room for a Dark Rum and Coke to celebrate Wayne's success/

Thanks alot Wayne

 

[WayneRyan]

Navyguy,

You're welcome. You'll get used to the syntax. Enjoy the
rum & coke.

Wayne