Message In Statement

[gator9]

Hey guy! The code below works great but when none of the criteria meets it shows the form, but shows it blank. What I would like is for it to prompt with a message and a button to close the message box without opening the form if the criteria doesn’t meet, any help?

SELECT DISTINCTROW MDraft.*
FROM MDraft
WHERE (((MDraft.DraftStartDate)<Date()+1) AND ((MDraft.MonthlyDraftYes)=Yes));


Sincerely,

Charles

http://grahamenterprises.net

 

[TheAceMan1]

How are ya gator9 . . . . .

Try this:

[blue]   Dim Criteria As String, Msg As String
   Dim Style As Integer, Title As String
   
   Criteria = "WHERE ([DraftStartDate] < Date()+1) AND " & _
                    "([MonthlyDraftYes] = Yes"
   
   If IsNull(DLookup("[DraftStartDate]", "MDraft", Criteria)) Then
      Msg = "No Records Match Criteria!"
      Style = vbInformation + vbOKOnly
      Title = "No Matching Records Notice . . ."
      MsgBox Msg, Style, Title
   Else
      DoCmd.OpenForm "[purple][b]YourFormName[/b][/purple]"
   End If[/blue]

See Ya! . . . . . .

 

[gator9]

Good thought but this code is located in the record source. If there is another way please let me know.

Sincerely,

Charles

http://grahamenterprises.net

 

[TheAceMan1]

Sure . . . with an added line, add the following to the [blue]On Open Event[/blue] of the form:

[blue]   Dim Criteria As String, Msg As String
   Dim Style As Integer, Title As String
 
   Criteria = "WHERE ([DraftStartDate] < Date()+1) AND " & _
                    "([MonthlyDraftYes] = Yes"
   
   If IsNull(DLookup("[DraftStartDate]", "MDraft", Criteria)) Then
      Msg = "No Records Match Criteria!"
      Style = vbInformation + vbOKOnly
      Title = "No Matching Records Notice . . ."
      MsgBox Msg, Style, Title
      [b]Cancel = True[/b]
   Else
      DoCmd.OpenForm "[purple][b]YourFormName[/b][/purple]"
   End If[/blue]

See Ya! . . . . . .

 

[gator9]

Still getting an error but I do see what your shooting for.

If IsNull(DLookup("[DraftStartDate]", "MDraft", Criteria)) <--- Error in above line

Sincerely,

Charles

http://grahamenterprises.net

 

[TheAceMan1]

Hmmmmm . . . missing a closing parenthese:

[blue]   Criteria = "WHERE ([DraftStartDate] < Date()+1) AND " & _
                    "([MonthlyDraftYes] = Yes[purple][b])[/b][/purple]"[/blue]

See Ya! . . . . . .

 

[gator9]

Here is the exact error:

Run-time error '3075':


Missing ), ], or Item in query expression 'WHERE ([DraftStartDate]< Date()+1) AND ([MonthlyDraftYes] = Yes'.

Sincerely,

Charles

http://grahamenterprises.net

 

[gator9]

Ok Got it, but if you don't bound the record source as it is below it will open all the records once the criteria meets. Oh and I took out the where because I kept getting an error and it worked great.

Record Source =
----
SELECT DISTINCTROW MDraft.*
FROM MDraft
WHERE (((MDraft.DraftStartDate)<Date()+1) AND ((MDraft.MonthlyDraftYes)=Yes));



On Open (On The Form) =
----
Dim Criteria As String, Msg As String
Dim Style As Integer, Title As String

Criteria = "([DraftStartDate] < Date()+1) AND " & _
"([MonthlyDraftYes] = Yes)"

If IsNull(DLookup("[DraftStartDate]", "MDraft", Criteria)) Then
Msg = "No Records Match Criteria!"
Style = vbInformation + vbOKOnly
Title = "No Matching Records Notice . . ."
MsgBox Msg, Style, Title
Cancel = True
Else
DoCmd.OpenForm "ReDraft"
End If

Sincerely,

Charles

http://grahamenterprises.net

 

[TheAceMan1]

gator9 . . . .

Yes your right. DLookup uses criteria without the where! . . . . I must be tired.

[blue]Cheers![/blue]

See Ya! . . . . . .

 

[gator9]

Below is another way that seems to work pretty good and is easer not to make a mistake in having both the record source and the on open the same: Thanks for your help.

Record Source =
----
SELECT DISTINCTROW MDraft.*
FROM MDraft
WHERE (((MDraft.DraftStartDate)<Date()+1) AND ((MDraft.MonthlyDraftYes)=Yes));



On Open (On The Form) =
----
If (IsNull(Me.EstStartDate = Null)) Then
Msg = "No Records Match Criteria!"
Style = vbInformation + vbOKOnly
Title = "No Matching Records Notice . . ."
MsgBox Msg, Style, Title
Cancel = True
Else
DoCmd.OpenForm "RePlan"
End If




Sincerely,

Charles

http://grahamenterprises.net

 

[gator9]

Sratch my last post it does not work!

Sincerely,

Charles

http://grahamenterprises.net

 

[TheAceMan1]

gator9 . . . . .

[blue]If (IsNull([purple][b]Me.EstStartDate = Null[/b][/purple])) Then[/blue]

Your assigning a [blue]null[/blue] to [blue]Me.EstStartDate[/blue], which always makes [blue]IsNull()[/blue] true! This is pointless. You'll get the message everytime you open the form . . . . no matter what.

It also does not take into account the [blue]specific criteria[/blue] you desired to ping against.

See Ya! . . . . . .

 

[gator9]

Yea thats why I say sratch it, I found that out when playing with it.

Sincerely,

Charles

http://grahamenterprises.net

 

[PHV]

Why not simply playing with Me.Recorset.RecordCount ?
I surely have missed something.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya PHV . . . . an honor to have you stop by!

From the post origination:

[blue]What I would like is for it to prompt with a message and a button to close the message box [purple]without opening the form if the criteria doesn’t meet[/purple],[/blue]

See Ya! . . . . . .