Solved

VBA code openrecordset method on form's open event property

Posted on 2009-05-19
12
1,004 Views
Last Modified: 2013-11-27
My code is working for the most part.  The problem is that when the query "z nmdf iitdata review mr and adjust" outputs no results, my form "z supplier qty adjustment" still opens, which is not what I want.  I want the result to show a message box stating "No records found".  Could someone please review the code below to tell me where I am going wrong.  Thanks.
Private Sub Form_Open(Cancel As Integer)
 

Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim qdf As DAO.QueryDef

Dim prm As DAO.Parameter

Dim vnotfound As Boolean

 

Set db = CurrentDb

Set qdf = db.QueryDefs("z nmdf iitdata review mr and adjust")
 

For Each prm In qdf.Parameters

prm.Value = Eval(prm.Name)

Next
 

Set rst = qdf.OpenRecordset
 

If Not (rst.BOF And rst.EOF) Then

    vnotfound = True

Else

    vnotfound = False

End If

 

If vnotfound = True Then

    rst.Close

    DoCmd.CancelEvent

    DoCmd.Close acForm, "z supplier qty adjustment"

    MsgBox "No records found."

ElseIf vnotfound = False Then

    DoCmd.OpenForm "z supplier qty adjustment"

    DoCmd.Maximize

    DoCmd.ApplyFilter "z supplier qty adjustment filter"

    Me.FilterOn = True

End If
 

rst.Close

Set rst = Nothing

Set qdf = Nothing

Set db = Nothing

Set prm = Nothing

 

End Sub

Open in new window

0
Comment
Question by:sxxgupta
  • 5
  • 4
  • 3
12 Comments
 
LVL 20

Expert Comment

by:clarkscott
ID: 24425951
Else vnotfound = False Then
    DoCmd.OpenForm "z supplier qty adjustment"
    DoCmd.Maximize
    DoCmd.ApplyFilter "z supplier qty adjustment filter"
    Me.FilterOn = True
End If
 
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing
if vnotfound = true then
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 24425959
sorry -  I hit the wrong key.....
Let's try this again....
Else vnotfound = False Then
    DoCmd.OpenForm "z supplier qty adjustment"
    DoCmd.Maximize
    DoCmd.ApplyFilter "z supplier qty adjustment filter"
    Me.FilterOn = True
End If
 

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing
if vnotfound = true then
     docmd.close
end if

Scott C
0
 

Author Comment

by:sxxgupta
ID: 24426097
Hi Scott:
I think I have your suggestion in.......but still having problems.  I slightly modded the code below.  Please take a look.  My query shows not records but the form still opens and the filter is applied, which is incorrect.

Private Sub Form_Open(Cancel As Integer)
 

Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim qdf As DAO.QueryDef

Dim prm As DAO.Parameter

Dim vnotfound As Boolean

 

Set db = CurrentDb

Set qdf = db.QueryDefs("z nmdf iitdata review mr and adjust")
 

For Each prm In qdf.Parameters

prm.Value = Eval(prm.Name)

Next
 

Set rst = qdf.OpenRecordset
 

If Not (rst.BOF And rst.EOF) Then

    vnotfound = True

Else

    vnotfound = False

End If

 

If vnotfound = True Then

    DoCmd.Close

    MsgBox "No records found."

End If
 

If vnotfound = False Then

    DoCmd.OpenForm "z supplier qty adjustment"

    DoCmd.Maximize

    DoCmd.ApplyFilter "z supplier qty adjustment filter"

    Me.FilterOn = True

End If
 

rst.Close
 

Set rst = Nothing

Set qdf = Nothing

Set db = Nothing

Set prm = Nothing

 

End Sub

Open in new window

0
 

Author Comment

by:sxxgupta
ID: 24426163
Hi Scott:
I am going home for the day.  4:15pm EST here.  I will follow up on the post tomorrow to see if any suggestions you have that will help my code.  Thank you.  Maybe the sequence of my coding is incorrect..................
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 24426276
Please note.... the CLOSE should be the last thing to run (if vfound = false).  You cannot close a form and expect the 'remaining' code to run as you expect.

Scott C
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 24427489
sxxgupta ...

Is the form in question BOUND to the query you using to check for records? ... If not ... is it true that the query your form is bound to will be empty if the query used to test for a record count is empty? ... Next question; Does the form you are opening allow the user to enter records? ...

If you want to pursue, what I consider an easier way, then let me know ...

Otherwise ... I have these questions for you ...

Is the code you posted behind the form named "z supplier qty adjustment" ... if it is (which is what I assume), then there is no need to try to open it again in your code ... for that matter there is no need to close the form either, setting Cancel = True will do that for you.

What is your code to open this form? I ask because if the Cancel is set to true, then the initial call to open the form {DoCmd.OpenForm "z supplier qty adjustment"} will raise an error, so you need to supress the error before that call is made.

Additionally, I recommend setting the Filter in the OnLoad() event, then there is no need to manage that code block since it will not event get executed if the OnOpen() event is cancelled ...

So ... here is my thought ... dump your whole though process, and with some assumptions (the main one being the recordsource of the form is in sync with the query you are testing), I would procede by simply using the OnLoad() event exclusively ...

Private Sub Form_Load()
   
    Dim blShouldClose As Boolean
   
    'test to see if the form should close
    If Me.Recordset Is Nothing Then
        blShouldClose = True
    Else
        If Me.CurrentRecord = 0 Or Me.NewRecord Then
            blShouldClose = True
        End If
    End If
   
    'React accordingly
    If blShouldClose = True Then
        MsgBox "No Records to View, Form will Close"
        DoCmd.Close acForm, Me.Name, acSaveNo
    Else
        DoCmd.Maximize
        DoCmd.ApplyFilter "z supplier qty adjustment filter"
        Me.FilterOn = True
    End If
   
End Sub

------

If you don't like this method, then I would go with something like this ....

In the caller to open the form do something like this {note: you can easily create better Error Handling, I just want to make you aware the you will need to handle the error upon setting the Cancel to True}:

On Error Resume Next
DoCmd.OpenForm "z supplier qty adjustment"
On Error GoTo 0

Private Sub Form_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("z nmdf iitdata review mr and adjust")

    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next

    Set rst = qdf.OpenRecordset
   
    If (rst.BOF And rst.EOF) = True Then
        Cancel = True
        MsgBox "No records found."
    Else
        DoCmd.Maximize
        DoCmd.ApplyFilter "z supplier qty adjustment filter"
        Me.FilterOn = True
    End If
   
    rst.Close
   
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Set prm = Nothing

End Sub

-------------------

Hope all this info helps out!!! ... Let us know!
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24427502
... Just a note ... in the first option (using the Form_Load() exclusively) ... here are the assumptions I made ..

Is the form in question BOUND to the query you using to check for records?: yes

is it true that the query your form is bound to will be empty if the query used to test for a record count is empty? Yes (if applicable)

Does the form you are opening allow the user to enter records?: NO .. (But I check the new record property just in case)
0
 

Author Comment

by:sxxgupta
ID: 24430229
So answering your questions:
1.  The form in question is not bound to the query I am using to check for records.  There is another subform within this form that is bound to the query.
2.  The form that I am opening should allow the user to enter records and add additional data into fields after the filter filters out the relevant records to write to.
0
 

Author Closing Comment

by:sxxgupta
ID: 31583193
Thank you very much.  I guess I was making the code more difficult than needed.  The second portion of your code did the trick.  I have another question taking this one step further.  Would you be interested?
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24433427
Hello sxxgupta,

I am glad the information I provided helped you progress with your project! ... Excellent!!

Also ... with respect to:
>>  have another question taking this one step further. Would you be interested? <<

You bet I am interested! ...
0
 

Author Comment

by:sxxgupta
ID: 24433951
Hi datAdrenaline:
I was able to figure out the solution to my next proposed question.  But again, thank you and thank you.  As I progress on my project, I will post questions if I hit any road blocks.  Stay tuned......
Sanjay
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24437695
Excellent news! ... Good luck on your project!

Brent
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now