DCount and empty recordset issue

  • Thread starter Thread starter Angi
  • Start date Start date
A

Angi

I might as well move in here!

The code pretty much says it all, but if there aren't any records to
DCount, then I get the "else". How do I say "If the recordset is
empty"? I tried If reccount... Or Recordset Is Empty and it says Object
required. Changed it to Me.Recordset, says object required again.

Private Sub cmdAddShipTo_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim RecCount As Integer

RecCount = DCount("[CoID]", "[ShipToMain]", "[shiptomain.CoID] =
[coid]")

If RecCount < 3 Then
stDocName = "ShipToMainForm"
DoCmd.OpenForm stDocName, , , , acFormAdd, , Me.CoID
Else
MsgBox ("There are already 3 ship to addresses for this
company!")
End If

End Sub
 
A DCount function will return a value of zero if there are no records that
match the criterion expression. Thus, if you have no records, your code
should go through the If branch, not the Else branch.

What is coid in the DCount's criterion expression? Is that a control on your
form? If yes, then concatenate its value into the criterion expression
instead of putting it inside the text string:

RecCount = DCount("[CoID]", "[ShipToMain]", "[shiptomain.CoID] =" &
[coid])
 
I might as well move in here!

The code pretty much says it all, but if there aren't any records to
DCount, then I get the "else". How do I say "If the recordset is
empty"? I tried If reccount... Or Recordset Is Empty and it says Object
required. Changed it to Me.Recordset, says object required again.

Private Sub cmdAddShipTo_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim RecCount As Integer

RecCount = DCount("[CoID]", "[ShipToMain]", "[shiptomain.CoID] =
[coid]")

If RecCount < 3 Then
stDocName = "ShipToMainForm"
DoCmd.OpenForm stDocName, , , , acFormAdd, , Me.CoID
Else
MsgBox ("There are already 3 ship to addresses for this
company!")
End If

End Sub
Click to expand...

What recordset are you trying to count? And where is [coid] coming
from? The syntax of your query criteria appears to be incorrect; if
there is a control named coid on your form, try

DCount("[CoID]", "[ShipToMain]", "[shiptomain].[CoID] = " & Me![coid])

If you want to see if there are zero records for that coID, just check
to see if RecCount is equal to zero.

John W. Vinson[MVP]
 
John,
Thanks for the reply. I tried the reccount = 0, but I get an error
before I even get to the if.

Run time error '2427':
You entered an expression that has no value.

it highlights this row:
RecCount = DCount("[CoID]", "[ShipToMain]", "[shiptomain].[CoID] =
" & Me![CoID])

It has no value because there aren't any records yet...right?? That's
why I'm trying to see if the recordset is empty. Furthermore, wouldn't
the "< 3" cover 0, too or is 0 considered different?

TIA!
 
No. If there are no records then the dcount will return 0. What happens if
you try hardcoding the ID... something like...

RecCount = DCount("[CoID]", "[ShipToMain]", "[shiptomain].[CoID] = 1")

....either using an ID that will return some records, or one that will return
none...

....or if you try something like...

msgbox(me.coid)

....somewhere above the line that goes wrong. (Note: You could do the
msgbox test by setting a breakpoint and using the immediate window... the
msgbox option is just easier to explain.)

Is the control linked to CoID actually called CoID?
 
Rob,

Thanks for that! I actually answered my own question while answering
you. I was referring to the wrong CoID. I've been looking at this so
much, I'm missing the obvious things!

Thanks for all your help everyone!
Angi
 
Back
Top