Using Count(*) HELP!!!!!!!

[proe]

I am using a query to get the Count(*) from a table. On the Query I used Expr1:Count(*)

I am having problems when trying to used:

If Expr1 = 0 then.... The result of the query is 0 but when I used that statement in a Subrouting using VB and I trying to luse the If statment it seems like the value its blank....!

I know I am not using this correctly what I am doing wrong any advice?

Thanks
proe

 

[Rich]

Either use DCount on the query or RecordsetClone.RecordCount
HTH

 

[proe]

Thanks, I try to change the name in my query to DCount and I got this in my error compile: "Argument not optional".

I am not sure what to do with your second sugestion.
let me put my code here:

Private Sub Move_Click()

With CodeContextObject


DoCmd.Echo True, "running"
DoCmd.OpenQuery "Look dbl rec", acNormal, acEdit



If (Forms!DIE_FORM!dif1 < 0) Then
Beep
MsgBox "Can not move", vbOKOnly, "BAD MOVE"

If (Forms!DIE_FORM!dif1 = 0) Then
If (DCount = 0) Then
' MsgBox Format(Forms!DIE_FORM!dif1), 0, "Debug Move Button"
' MsgBox "I am here1", 0, "status"
DoCmd.OpenQuery "Update loc", acNormal, acEdit
MsgBox "sub=0 count=0", vbOKOnly, "status"
Else
DoCmd.OpenQuery "Update Rec", acNormal, acEdit
DoCmd.OpenQuery "DELREC", acNormal, acEdit
MsgBox "sub=0 count>0", vbOKOnly, "status"
'MsgBox Format(Forms!DIE_FORM!dif1), 0, "Debug Move Button"
'MsgBox Format(Expr1), 0, "Debug Move Button"

End If
Else
If ([Look dbl rec]![DCount] > 0) Then
DoCmd.OpenQuery "Update Rec", acNormal, acEdit
DoCmd.OpenQuery "DELREC", acNormal, acEdit
MsgBox "sub=0 count>0", vbOKOnly, "status"
'MsgBox Format(Forms!DIE_FORM!dif1), 0, "Debug Move Button"
'MsgBox Format(Expr1), 0, "Debug Move Button"

Else
If ([Look dbl rec]![DCount] = 0) Then
DoCmd.OpenQuery "Update loc", acNormal, acEdit
DoCmd.RunSQL "INSERT INTO DIE_TABLE (DIENAME,COLORS,LENGTH,QUANTITY,LOCATION) VALUES ([DIE_TABLE sub1].Form![DIENAME],[DIE_TABLE sub1].Form![COLORS],[DIE_TABLE sub1].Form![LENGTH],[DIE_TABLE sub1].[qty_in],[DIE_TABLE sub1].Form![loc_in])", -1
MsgBox "sub>0 count=0", vbOKOnly, "status"

Else
'MsgBox "here doing updates", vbOKOnly, "status"
DoCmd.OpenQuery "Update Rec", acNormal, acEdit
DoCmd.OpenQuery "Update Quantity", acNormal, acEdit
MsgBox "sub>0 count>0", vbOKOnly, "status"

End If
End If

DoCmd.OpenForm "DIE_FORM", acNormal, "", "", , acNormal
MsgBox "done", vbOKOnly, "status"
End With



move1_Exit:
Exit Sub


move1_Err:
MsgBox Error$
Resume move1_Exit
End Sub

 

[proe]

OKay,

I have just learned how to use the DCount..
How can I see what is the value on DCount?

Dim CNT As Integer
DoCmd.Echo True, "running"
CNT = DCount("LOCATION", "DIE_TABLE", "[DIENAME]='" & Forms!DIE_FORM![die_name_in] & "'AND[COLORS]='" & Forms!DIE_FORM![colors_in] & "'AND [LENGTH]='" & Forms!DIE_FORM![length_in] & "'and[LOCATION]='" & Forms!DIE_FORM![loc_in] & "'")
MsgBox Format(CNT), 0, "Debug Move Button"

I can get the CNT when the result is 0 but when I expect to be > 0 then I get and error
Run_time error "3464"
Data Type mismatch criteria expression.