error msg when form has no records

[mpbarker]

I have a form that open based on what material you enter. I'd like it to display an error msg and not open the form if no records exist with that material.

Can someone help?

Thanks.
MPB.

 

[mph1]

In the design view of the form, go to properties of the report and scroll down to the line that says OnNoData. This is where you should create your error message. If not sure what code to use, click on the line OnNodate and hit F1. That should explain what you need to do.


HTH

Maurie An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[mpbarker]

I am familiar with that function under reports but I'm opening a form for editing. This function isn't available to forms??

 

[mph1]

Sorry about that. I figured you meant report. Actually, I even created more confusion by indicating form and report.

Are you using a form or search to indicate the material? You could use in the On Open of the 2nd form

If [fieldname]<> something, Then
MsgBox &quot; whatever message you want&quot;
Close
End If An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[mpbarker]

I'm using a form.

I think something like that will work except I have
at least 8 materials. Can I use that many ors?

 

[jode]

Using the OnLoad event of the form you need to check whether you have data or not.

A sub routine similar to the following will work: -

Private Sub Form_Load
Dim db as database
Dim rs as recordset

set db = CurrentDB
set rs = db.openrecordset(&quot;Select * from your table&quot;, dbopensnapshot)

if rs.eof then
msgbox &quot;No Data&quot;
docmd.close acform, &quot;Form1&quot;
end if

End Sub

 

[mph1]

Do you have a table that lists all of the possible materaials? jode's example implies if no data exists. I was under the impression that data could exist, but not be accurate, even mispelled. Mispelling is a good reason for having a separate table and then a drop down in the form to choose.

If I am missing the point, I appoligize. An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[mpbarker]

Yes I have a table that lists all of the possible available materials. I want it to act as a check. If the entered material exists then open the form to edit the record, if the material does not exist display an error msg to that effect.

 

[mph1]

I think you could use an If statement with or. Keep in mind, if the number of materails cahnge, then the if statement will have to be changed. Best bet would be to create an if statement on form (a) that checks the items listed in table of materials. If the material exists, open, else msgbox and close form (b). An investment in knowledge always pays the best dividends.

by Benjamin Franklin