Create an Error Message

  • Thread starter Thread starter Haggr1
  • Start date Start date
Do you mean when your query returns no records?

You might do this a couple of ways:

1. Use DCOUNT( ) domain function to count the recods returned by your query
before you set it as a rowsource or whatever you are using it for.

2. If it is the data source for a report, I think the report has a NoData
event that would allow you to put the message box in that event, then cancel
the report.

3. If you are using this query for a form, you could get a count of the
records in the forms recordset clone and display a message if that value is
zero.

If me.recordsetclone.RecordCount = 0 then msgbox "yada yada"

HTH
Dale
 
Here is the query I can make run when the user leaves the text box.

SELECT Table1.[Job Number], Table1.[Date To]
FROM Table1
WHERE (((Table1.[Job Number])=[Forms]![billing]![Job Number]) AND ((Table1.
[Date To])>Date()-60));

And when that Query runs and return null I would like to see a message box
"Not a Valid Job Number"
 
instead of running a query use the dcount function

IF DCount("*","Table1","[Job Number]=""" & [Forms]![billing]![Job Number] &
""" AND [[Date To]>Date()-60" ) = 0 Then
MsbBox "Not a valid job number"
end if


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Haggr1 said:
Here is the query I can make run when the user leaves the text box.

SELECT Table1.[Job Number], Table1.[Date To]
FROM Table1
WHERE (((Table1.[Job Number])=[Forms]![billing]![Job Number]) AND
((Table1.
[Date To])>Date()-60));

And when that Query runs and return null I would like to see a message box
"Not a Valid Job Number"

Dale said:
Do you mean when your query returns no records?

You might do this a couple of ways:

1. Use DCOUNT( ) domain function to count the recods returned by your
query
before you set it as a rowsource or whatever you are using it for.

2. If it is the data source for a report, I think the report has a NoData
event that would allow you to put the message box in that event, then
cancel
the report.

3. If you are using this query for a form, you could get a count of the
records in the forms recordset clone and display a message if that value
is
zero.

If me.recordsetclone.RecordCount = 0 then msgbox "yada yada"

HTH
Dale
Click to expand...
Click to expand...
 
If what you are doing is checking the job numbers in Table1 that are no
older than 60 days and matching to the input of the text box Job Number on
the Billing Form to see if there is a match and if not the Message abox
appears. That is just what I need.

However when I add your code to on enter event for either job number text box
or the amount text box on the billing form the font color in visual basic
is red except for MsbBox "Not a valid job number"
End If

where else might I put this

Thanks

John said:
instead of running a query use the dcount function

IF DCount("*","Table1","[Job Number]=""" & [Forms]![billing]![Job Number] &
""" AND [[Date To]>Date()-60" ) = 0 Then
MsbBox "Not a valid job number"
end if
Here is the query I can make run when the user leaves the text box.
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 
First there is a typo.

MsgBox not MsbBox

Second I would not do this in the Enter Event, I would use the Lost Focus
event and check for Null values as well.

Assumption is that Job Number is a text field, if it is a number field then
change both instances of """ to " (3 quotes to 1 quote).

Private Sub Job_Number_LostFocus()

If isNull(me.Job_Number) = true then
'do what ever you want here
ElseIf DCount("*","Table1","[Job Number]=""" & me.Job_number & _
""" AND [[Date To]>Date()-60" ) = 0 Then
MsgBox "Not a valid job number"
end if

End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Haggr1 said:
If what you are doing is checking the job numbers in Table1 that are no
older than 60 days and matching to the input of the text box Job Number
on
the Billing Form to see if there is a match and if not the Message abox
appears. That is just what I need.

However when I add your code to on enter event for either job number text
box
or the amount text box on the billing form the font color in visual basic
is red except for MsbBox "Not a valid job number"
End If

where else might I put this

Thanks

John said:
instead of running a query use the dcount function

IF DCount("*","Table1","[Job Number]=""" & [Forms]![billing]![Job Number]
&
""" AND [[Date To]>Date()-60" ) = 0 Then
MsbBox "Not a valid job number"
end if
Here is the query I can make run when the user leaves the text box.
Click to expand...
[quoted text clipped - 31 lines]
Would like a Error Message to appear when a query returns null
Click to expand...
Click to expand...
Click to expand...
 
Thanks John, That did it and that was a number field

Also what code can I use for the message box to display "Ok" which would the
set focus back to the Job Number text Box and "Ignore" which would allow the
program to proceed as usually

John said:
First there is a typo.

MsgBox not MsbBox

Second I would not do this in the Enter Event, I would use the Lost Focus
event and check for Null values as well.

Assumption is that Job Number is a text field, if it is a number field then
change both instances of """ to " (3 quotes to 1 quote).

Private Sub Job_Number_LostFocus()

If isNull(me.Job_Number) = true then
'do what ever you want here
ElseIf DCount("*","Table1","[Job Number]=""" & me.Job_number & _
""" AND [[Date To]>Date()-60" ) = 0 Then
MsgBox "Not a valid job number"
end if

End Sub
If what you are doing is checking the job numbers in Table1 that are no
older than 60 days and matching to the input of the text box Job Number
Click to expand...
[quoted text clipped - 25 lines]
Click to expand...
 
Move the code to the exit event of the control and set Cancel = true if
the message box gets triggered.

Let the user exit the control if it is blank (null). If you don't let
them get out of the control somehow, they will control-alt-delete their
way out and you will risk data corruption if they do that often enough.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks John, That did it and that was a number field

Also what code can I use for the message box to display "Ok" which would the
set focus back to the Job Number text Box and "Ignore" which would allow the
program to proceed as usually

John said:
First there is a typo.

MsgBox not MsbBox

Second I would not do this in the Enter Event, I would use the Lost Focus
event and check for Null values as well.

Assumption is that Job Number is a text field, if it is a number field then
change both instances of """ to " (3 quotes to 1 quote).

Private Sub Job_Number_LostFocus()

If isNull(me.Job_Number) = true then
'do what ever you want here
ElseIf DCount("*","Table1","[Job Number]=""" & me.Job_number & _
""" AND [[Date To]>Date()-60" ) = 0 Then
MsgBox "Not a valid job number"
end if

End Sub
If what you are doing is checking the job numbers in Table1 that are no
older than 60 days and matching to the input of the text box Job Number
Click to expand...
[quoted text clipped - 25 lines]
Would like a Error Message to appear when a query returns null
Click to expand...
Click to expand...
Click to expand...
 
Back
Top