How to "open'' frm hidden if listbox is Null/close if not show frm? 1

[Jrs2nd]

Hi, I have a form(Popup/modal) that I wish to use more like
a MsgBox. It's based on a Query that takes the Value of a
textbox and searches the records input over the last 90 days
for matches. I presently have this set up as a CmdButton
and it opens properly and displays the matches( if any).
what I am trying to achieve is: call this form
( from the On Enter Event of the save button)
hidden in the background(not seen by user) and if the
ListBox contains no matches to close the form and continue
the save operation. But if there are matches to allow the
form to "POPUP" so they can review them. I've set the OK
button on the popup form & it does allows the save
operation to finish, and the CANCEL button does stop the
saving of a new record.

this is the code I'm using to open the form.

Private Sub Save_Record_Enter()
On Error GoTo Save_Record_Enter_err

DoCmd.OpenForm "frm90DayDuplicates", acNormal, , , acFormReadOnly, acDialog
Save_Record_Enter_Exit:
Exit Sub

Save_Record_Enter_err:
GlobalErrorHandler Err.Number, Erl, Err.Description, "Form_frmSprvsr", "Save_Record_Enter", ""
GoTo Save_Record_Enter_Exit
End Sub

Since its Popup and Modal I'm unsure of setting the Visible
property to false in the On Open event.
But:

Private Sub Form_Load()
DoCmd.MoveSize 0, 2000, 11800, 5000

If (IsNull([List35].Value)) Then
DoCmd.RunCommand acCmdCloseWindow
Else
Visible = True
End If

End Sub

Private Sub Form_Open(Cancel As Integer)

Visible = False

End Sub

Of Course, this does not work.... any suggestions???

Thanks in advance,
Jim

 

[JoeMiller]

A question.. Rather than always open the form, why not use the code to check the source for the listbox (without opening the form) and count the records and decide to show the form only if there are records to show? Is there a particular reason you have to have the form come up? Joe Miller
joe.miller@flotech.net

 

[Jrs2nd]

Hey Joe,

I, not being the fastest chip in the computer, didn't know
that was possible.. it would be amore elegant way of
achieving my goal. How would I go about doing that??

If you could steer me in the right direction, I would
appreciated it.

Thanks,
Jim

 

[JoeMiller]

Is your listbox populated by a query or by a long SQL statement beginning with "SELECT ..." If it's got a select statement can you post that here so I can have a look see?

Joe Miller
joe.miller@flotech.net

 

[Jrs2nd]

Joe thanks, for replying,

Here the Query you requested.

SELECT [90DAYDUPLICATES].RecordNumber, [90DAYDUPLICATES].AssetID, [90DAYDUPLICATES].AssetDescription, [90DAYDUPLICATES].[Action Type], [90DAYDUPLICATES].[Input Date], [90DAYDUPLICATES].ActionNumber, [90DAYDUPLICATES].[Ex-Date], [90DAYDUPLICATES].[Expiration Date], [90DAYDUPLICATES].Status, *
FROM 90DAYDUPLICATES
WHERE ((([90DAYDUPLICATES].AssetID)=[Forms]![frmSprvsr]![txtAssetID]) AND (([90DAYDUPLICATES].[Input Date]) Between Date()-90 And Date()))
ORDER BY [90DAYDUPLICATES].[Input Date]
WITH OWNERACCESS OPTION;

thanks for your time in helping me.

Jim

 

[JoeMiller]

Ok, their is a way to do this in code but it will be easier to explain if we save this SELECT statement as a query. Make a new query, and click View-->SQL View. Now Paste that whole long SELECT statement into the window. Click File-->Save and give your query a name, I'm going to call it qry90DayDuplicates. Now check to be sure that this query is pulling the right records by running it.

Now in the form we have to modify the Save_Record event like so (you can copy/past this if you like):

[tt]
Private Sub Save_Record_Enter()
On Error GoTo Save_Record_Enter_err

If DCount("[RecordNumber]","qry90DayDuplicates&quot > 0 Then
DoCmd.OpenForm "frm90DayDuplicates", acNormal, , , _
acFormReadOnly, acDialog
Else
MsgBox "No matching records found!"
End If

Save_Record_Enter_Exit:
Exit Sub

Save_Record_Enter_err:
GlobalErrorHandler Err.Number, Erl, Err.Description, "Form_frmSprvsr", "Save_Record_Enter", ""
GoTo Save_Record_Enter_Exit
End Sub
[/tt]

That should do it, let me know how you do!

Joe Miller
joe.miller@flotech.net

 

[Jrs2nd]

Hey Joe!!!

Thank you very much, it's working perfectly, I also
like the added msgbox for "no matches", will let them know
it it's working....I appreciate the time you used to help
me.

Jim