Queries with no records - John Viescas??

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

John Viescas helped me with some code behind a command button that opened a
form but if there were no records I get a message. The code works fine apart
from the fact that I wanted the form hidden The code John gave me for this
was
Docmd.OpenForm stDocName, WindowMode:=acHidden
Iv'e also tried
DoCmd.OpenForm stDocName, , , , , acHidden

but neither work my form is still there
Anyone any ideas?
Sorry to post this as a new thread John if you read this but I wasn't too
sure how long to check threads
TIA
Tony Williams
 
Is the form already open when you run this OpenForm? Access should still
hide it. What is the error message you're getting? Please post the rest of
the code.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks John. As far as I can see the form isn't open. Anyway here is my code
as it stands
Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click

Dim stDocName As String
stDocName = "frmDocumentbydate"
If IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date", vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date", vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
Else
DoCmd.Minimize
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria", vbExclamation,
"No records found"
DoCmd.Close acForm, stDocName
If IsLoaded("frmFindDocumentbyDate") Then
DoCmd.OpenForm "frmFindDocumentbyDate", acNormal

End If
Else
Forms(stDocName).Visible = True
Forms!frmDocumentbyDate!parastartdatetxt = Me.StartDatetxt
Forms!frmDocumentbyDate!paraenddatetxt = Me.EndDatetxt
End If
End If

Exit_cmdFindDocs_Click:
Exit Sub

Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click
End Sub

Tony
 
Well, for starters, setting Cancel = True does nothing in a Click event.
Try the code below, but read my inserted comments. BTW, you should get in
the habit of indenting your If / Else / End If statements so that you can
see where each one closes off and what executes when the If or the Else is
true.

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click

Dim stDocName As String
stDocName = "frmDocumentbydate"
If IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date", vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date", vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Exit Sub
Else
DoCmd.Minimize
' WHERE is the filter for the OpenForm?
' Is the form using a parameter query that points to this form?
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria", _
vbExclamation, "No records found"
DoCmd.Close acForm, stDocName
' What is the name of the form where this code is running?
' Is it frmFindDocumentbyDate? If so, just do a Maximize
If IsLoaded("frmFindDocumentbyDate") Then
DoCmd.OpenForm "frmFindDocumentbyDate", acNormal
End If
Else
Forms(stDocName).Visible = True
Forms!frmDocumentbyDate!parastartdatetxt = Me.StartDatetxt
Forms!frmDocumentbyDate!paraenddatetxt = Me.EndDatetxt
End If
End If

Exit_cmdFindDocs_Click:
Exit Sub

Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click
End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks John tried your code still get the form visible. The command button
is on the form "frmFind DocumentbyDate" and when clicked runs a parameter
query that opens the form "frmDocumentbydate" showing records within the
date range. What I've tried to do is when the form "frmDocumentbyDate" runs
if there are records then the form "frmFindDocumentbyDate" is minimised and
when the user closes the form "FrmDocumentbyDate" then the find form is
restored. I don't want to maximise it because it seems to maximise every
form once you've done that to one so it's restored to its original size by
the Close button on the "frmDocumentbyDate" Hope that makes sense.
Thanks for your tip on the If Then statements that makes good sense.
Any further ideas why this isn't working?
Tony
 
Yup. Shoulda said Restore, not Maximize. I just tried out a similar
scenario in NorthWind - created a copy of the Customers form with a
parameter query pointing to Forms!CustSearch!cmbCustomer. Created the
CustSearch form and used a Row Source in the combo box that had one bad
customer. It works like a champ. Here's the code:

Private Sub Command2_Click()
On Error GoTo Err_cmdFindDocs_Click
Dim stDocName As String
stDocName = "CustomersParm"
If IsNull(Me.cmbCustomer) Then
MsgBox "Please enter Customer", vbExclamation
Me.cmbCustomer.SetFocus
Exit Sub
Else
DoCmd.Minimize
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria", _
vbExclamation, "No records found"
Me.SetFocus
DoCmd.Restore
DoCmd.Close acForm, stDocName
Else
Forms(stDocName).Visible = True
End If
End If
Exit_cmdFindDocs_Click:
Exit Sub
Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click
End Sub

When I pick the "bad customer" in the list, I get the error message, never
see the CustomersParm form, and this search form restores just fine. If I
pick a good customer, the form appears correctly with the customer I picked.

Are you doing something in the Open, Load, or Current events of your
parameter form that might make it visible? Even so, it should close
immediately after the MsgBox displays.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
John, here is all the code for the parameter form
Option Compare Database

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click

Dim stDocName As String
stDocName = "frmDocumentbydate"
If IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date", vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date", vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Exit Sub
Else
DoCmd.Minimize
' WHERE is the filter for the OpenForm?
' Is the form using a parameter query that points to this form?
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria", _
vbExclamation, "No records found"
DoCmd.Close acForm, stDocName
' What is the name of the form where this code is running?
' Is it frmFindDocumentbyDate? If so, just do a Maximize
If IsLoaded("frmFindDocumentbyDate") Then
DoCmd.OpenForm "frmFindDocumentbyDate", acNormal
End If
Else
Forms(stDocName).Visible = True
Forms!frmDocumentbyDate!parastartdatetxt = Me.StartDatetxt
Forms!frmDocumentbyDate!paraenddatetxt = Me.EndDatetxt
End If
End If

Exit_cmdFindDocs_Click:
Exit Sub

Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click

End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Private Sub cmdViewReport_Click()
'On Error GoTo Err_cmbViewReport_Click
On Error GoTo HandleErr
Me.Visible = False
Dim stDocName As String

stDocName = "rptDocbyDate"
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date",
vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date",
vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(finddocnametxt) Then
DoCmd.Minimize
DoCmd.OpenReport stDocName, acPreview

End If
Exit_cmbViewReport_Click:
Exit Sub

'Err_cmbViewReport_Click:
' MsgBox Err.Description
HandleErr:
Select Case Err.Number
Case 2501
'The OpenReport action was cancelled.
'There no rows. So do nothing.
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume Exit_cmbViewReport_Click

End Sub

Private Sub Form_Close()
DoCmd.Restore

End Sub

Private Sub Form_Load()
If IsLoaded("frmFindDocument") Then
DoCmd.Close acForm, "frmFindDocument"
End If
End Sub

Can you see anything wrong here?
Thanks for your patience I'm a 60 year old newbie, so the brain works a bit
slow!!!
Tony
 
Well, I'm only a couple of years younger than you! <s>

Part of the problem is your Load event code. It isn't checking to see if
there are no records - it's just closing the search form regardless! That
should not make it "appear" on the screen, but it ain't right. Change it to
this:

Private Sub Form_Load()
If Me.RecordsetClone.RecordCount <> 0 Then
If IsLoaded("frmFindDocument") Then
DoCmd.Close acForm, "frmFindDocument"
End If
End If
End Sub

And your code in frmFindDocumentByDate should look like:

Private Sub cmdFindDocs_Click()
On Error GoTo Err_cmdFindDocs_Click

Dim stDocName As String, varStart As Variant, varEnd As Variant

stDocName = "frmDocumentbydate"
If IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date", vbExclamation, "Enter Start Date"
Me.StartDatetxt.SetFocus
Exit Sub
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date", vbExclamation, "Enter End Date"
Me.EndDatetxt.SetFocus
Exit Sub
Else
DoCmd.Minimize
' Save my two values for later
varStart = Me.StartDatetxt
varEnd = Me.EndDatetxt
DoCmd.OpenForm stDocName, , , , , acHidden
If Forms(stDocName).RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry there no records that match this criteria", _
vbExclamation, "No records found"
DoCmd.Close acForm, stDocName
Me.SetFocus
DoCmd.Restore
Else
Forms(stDocName).Visible = True
' I changed the following two statements because this form will
' already be closed by frmDocumentByDate when they run
' Using saved variables instead...
Forms!frmDocumentbyDate!parastartdatetxt = varStart
Forms!frmDocumentbyDate!paraenddatetxt = varEnd
End If
End If

Exit_cmdFindDocs_Click:
Exit Sub

Err_cmdFindDocs_Click:
MsgBox Err.Description
Resume Exit_cmdFindDocs_Click
End Sub


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks John. When I put the first part in the Form Load property I get an
error 7951 "You entered an expression that has an invalid reference to the
RecordsetClone property" ?
Sorry this is taking so long
Tony
 
The Load code goes in frmDocumentbyDate. The Click code goes in
frmFindDocument. If you try to put the Load code in frmFindDocument, you'll
get this error because the form is unbound (has no recordset).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Sorry John, got rid of the message BUT form still appears. Do I give up and
let the users see the empty form with my message I ask myself??????
Without wanting to try your patience where else do I go from here???
Tony
 
Let's take this offline.

Can you email the database to me? Compact it first and then put it in a zip
file. Email to johnv-at-viescas.com (replace -at- with @ - trying to avoid
spammers!)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thank you John I would be extremely grateful for that. Give me a day to tidy
up the database first and I'll then send you a zipped file.
Tony
 
Well, the answer is the Open event of frmDocumentByDate contains this code:


Private Sub Form_Open(Cancel As Integer)
DoCmd.RunCommand acCmdDocRestore
End Sub

I asked you 'way back if there was any code in the Open or Load events of
the form you're trying to open. The Restore what is making the form
visible. When I remove that code, the form stays hidden.

BTW, you have a strange VB library referenced in your project - Outlook Sync
Type Library. I had to remove that to compile your project. Open any
module in the VB Editor and choose References from the Tools menu. Are you
also using ADODB and ADOX? If not, you can also remove ActiveX Data Objects
2.1 library and ADO Ext. for DDL and Security.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I don't know how to apologise enough, my excuse? I haven't one. Lesson
learnt? Take more care when posting questions so as not to waste other
people's time. Thank you for all your help and patience.

As regards the references. The Outlook Sync Type Library was one I had to
install to make the code work on the form (frmdocumentrecord) which opened
Outlook to send an email it's on the 3rd tab control. as for the others I
don't know what they are and unfortunately I don't know what ADOB and ADOX
are so I don't know whether I can remove them. Under what circumstances
would I use them? If they don't fit into my database I'll remove them.
Thanks again
Tony
 
No need for apologies. Would you believe that sometimes I have my wife come
and read code over my shoulder (she's not a programmer) while I explain it?
Works a high percentage of the time to find the bug!

ADODB = ActiveX Data Objects for manipulating ADO recordsets in code. You
usually don't need this in an MDB

ADOX = ActiveX Data Objects for working with the Catalog object - unless
you're modifying tables and queries on the fly with ADO, you don't need this
either.

One quick way to find out if you really need a library is to uncheck it and
then do a compile. If you get no errors, you didn't need it. I was able to
remove Outlook Sync Type with no problems.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks again John. Funny I do the same thing. My wife has an incredibly
logical mind so I draw pictures for her of what I'm trying to do and she
spots the faults much quicker than me!!!
Tony
 
Back
Top