Message Box if there are No Records in a Form

[pretty gal]

Hi
I am trying to get a Message Box if there are No Records in a Form, here is the code.
thank you in advance
Pretty

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim intHolder As Integer
intHolder = DCount("UnitAreaName", "qryMainDue")
If intHolder > 0 Then
stDocName = "frmDue"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "There are no records!"
End If
Exit_Command40_Click:
Exit Sub
Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub

[RobDog888]

You are using Access VBA?You could write code in the Forms Load event and check for records in there.

[Static]

?? when I try similar code.. it works fine

Dim intHolder As Integer
    intHolder = DCount("ID", "Query1")
    If intHolder > 0 Then
        DoCmd.OpenForm "Form1"
 
    Else
        MsgBox "No Records"
    End If

[pretty gal]

Yes i am using Access VBA.. I tried in FORM LOAD event but I am getting error msg saying "You entered an invalid argument in a domain aggregate function".

Thanks

Pretty

[RobDog888]

Well you can open the forms recordset initially like static posted and check the records and then only open the form itf records exist.

[Static]

well thats what she is doing...

my guess is though.. the Dcount is returning > 0 thats why its opening the form..
check to make sure your looking at the SAME data as the form is... and check to see if its really empty

[RobDog888]

Well I think the error is more related to the passing of a nullstring as the criteria and giving different recordset results. stLinkCriteria is never populated.

[pretty gal]

No I have no records in RecordSource (qryMainDue) ... still form is opening... i am confused here :-)
By the way i just started working with VB Access, i am not an expert
Static i tried ur code as well,, didnt work
I have my Main switchboard named frmMainMenu and there i have my button "frmDue" (my Sub Form)...

[RobDog888]

Can you describe the design of your form as I see now that there is a sub form involved. What recordset is tied to what form?

[pretty gal]

Main Form Called frmMainMenu (just switchboard) I have several buttons thats it, no record set.. one of the buttons called command40(no name:-). When user click on that i need my sub form (frmDue) to be open. but i need a Message Box if there are No Records in a Form.
frmDue from recordsource qryMainDue.

thanks for your time

[Static]

alos.. put a breakpoint on this line:

If intHolder > 0 Then

to add the breakpoint click in the gray column next to the line... it should add a dot in the column and highlight the line...

now go back to the form and trigger the code.. (click the button)
it should stop on the breakpoint...
put your mouse over intHolder and it should give u a tooltip with the value of it...
whats the value?

[RobDog888]

It should be qryMainDue then and is this an actual nested subform or just a secondary form?

[pretty gal]

i am getting a error msg saying "u cancelled the previous operation" :S i added the breakpoint its not even start trigger the code..

[pretty gal]

secondary form

[RobDog888]

Can you zip and attach your db in a post?

[pretty gal]

its a separate form(frmDue) .. main swichboard doesnt have any forms only buttons

[pretty gal]

nooo i have 1000 records ... its confidencial

[RobDog888]

how about removing all the records and confidential data?

[pretty gal]

still i have other coding for my other sub forms.... all i did using VB coding... i work for a bank i cant do that u know...

[pretty gal]

its ok i will find an other way to fix the problem... i am going to have empty form for now until i fix....

[RobDog888]

Ok, its just hard to debug Access forms without seeing them.

[pretty gal]

hmm ok thanks

[Static]

Citi?

anyways.. u added the breakpoint and now it wont even work? u mustve changed something....

When u open "qryMainDue" (from the database window) does it return results?
is it using any params from open forms etc?

[pretty gal]

no ididnt change anything i have same code as i have above.. if i just open qryMainDue i have no problem.. qryMainDue has criteria ..first it asks year from them user then the month.. then it gives them issues due on that specific month of the year...

[Static]

so when u do the Dcount() does it ask for the criteria?

Where is it getting the criteria from when u open the form?
is the form bound to that query??

as was said before.. hard to pinpoint without a db to play with.

I used to work for Citigroup so I know allll about need for confidentiality...
copy the database.. open up the copy and clear the tables of all confidential data.
then zip and attach it or if u want.. PM it to me, that way it will not be "public"

[pretty gal]

Did i say i work for Citi? i just said bank...

DCount("UnitAreaName", "qryMainDue")... frmDue bounds to qry (qryMainDue).when i click the button on the Main switchboard, the form is not getting the qry "qryMainDue" (recordsource).. thats why its saying "You cancelled the previous operation" it doesnt read the qry criteria i think...just confused

[pretty gal]

if there is no criteria then the form working fine....

[Static]

put a textbox in the main form.. have the user enter the criteria in the textbox then in the query criteria section

=[Forms].[Form2].[Text1]

something like that.. so it reads the criteria from the switchboard
another method...

create a public funtion in a module

Public Function GetCrit() As String
 
GetCrit = 5
 
End Function

something along those lines... that can read public variables that u have set from the button click
then just put this in the criteria field

GetCrit()

[pretty gal]

i knew the first method, but i didnt wnat to have txtbox in my swichboard.. i am going to try doing 2nd method u mentioned.. thank you

Pretty

[Static]

Just to explain it better:

in a module put this:

Public sCrit1 As String
Public sCrit2 As String
Public sCrit3 As String
 
Public Function GetCrit1() As String
    GetCrit = sCrit1
End Function
Public Function GetCrit2() As String
    GetCrit = sCrit2
End Function
Public Function GetCrit3() As String
    GetCrit = sCrit3
End Function

THen in your form set the variables before its called...

Private Sub Command40_Click()
[B]    sCrit1 = "a"
    sCrit2 = "b"
    sCrit3 = "c"[/B]
    On Error GoTo Err_Command40_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intHolder As Integer
    intHolder = DCount("UnitAreaName", "qryMainDue")
    If intHolder > 0 Then
    stDocName = "frmDue"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    MsgBox "There are no records!"
    End If
Exit_Command40_Click:
    Exit Sub
Err_Command40_Click:
    MsgBox Err.Description
    Resume Exit_Command40_Click
End Sub

then u can use

GetCrit1()
GetCrit2()
GetCrit3()
in the criteria fields

[pretty gal]

How do i get the user input ...

InputBox("Please enter year")

[Static]

sCrit1 = Inputbox("Please Enter Year")

[pretty gal]

error msg i am getting now "undefined function GetCrit1 in Expression"
its time to leave now so i am going to try it tmr..
many thanks ....
your help is appreciated.

[Static]

u put the functions in a module? (Made them public?)

just like this? (PS.. I just noticed that I was not setting the function to the value so use this code in the module now)

Public sCrit1 As String
Public sCrit2 As String
Public sCrit3 As String
 
Public Function GetCrit1() As String
    GetCrit1 = sCrit1
End Function
Public Function GetCrit2() As String
    GetCrit2 = sCrit2
End Function
Public Function GetCrit3() As String
    GetCrit3 = sCrit3
End Function

[pretty gal]

hmm thank you so much ... i am done :P Thanks a bunch Static and Robdog............

[Static]

Cool. glad it works now... just a tidbit.. another way to do this that would reduce code:

Public sCrit(9) As String
 
Public Function GetCrit(cNum As Integer) As String
    GetCrit = sCrit(cNum)
End Function

then u could set them line
sCrit(0) = "Whatever"
sCrit(1) = "This"

then in the Query Criteria
GetCrit(0)

etc....

also, if this thread is resolved now, please click thread tools > mark thread resolved so we all know its been answered Thanks!!!

[pretty gal]

opps..one more question...
when i have no records, the form working fine, i am getting the msg "there is not records", but when there are records (>0) then i am getting prompt 2 times, it asking year then month, again year and month?

Dim intHolder As Integer
[B]    intHolder = DCount("UnitAreaName", "qryMainDue")
    If intHolder > 0 Then
    stDocName = "frmDue"
    DoCmd.OpenForm stDocName, , , stLinkCriteria[/B]
    Else
    MsgBox "There are no records!"
    End If

i couldnt figure it out

[Static]

what are u using this for? stLinkCriteria

try just this:

DoCmd.OpenForm stDocName

[pretty gal]

no i am not using it (stlinkcriteria)
???:S

[pretty gal]

Private Sub Command40_Click()
    sCrit1 = "a"
    sCrit2 = "b"
    Dim stDocName As String
    stDocName = "frmDue"
    Dim intHolder As Integer
    intHolder = DCount("UnitAreaName", "qryMainDue")
    If intHolder > 0 Then
    DoCmd.OpenForm stDocName
    Else
    MsgBox "There are no records!"
    End If
End Sub