DCount not functioning correctly

[matthewnsarah07]

I have got the following DCount code set up in a command button that is supposed to only allow the frmppe to open if the [Staffname] on MainForm is not present in qryppe.

The code runs fine but no matter whether a name entered on the form is in the query or not the frmppe still opens regardless, What is missing from this code to make it work properly??

Thanks

Private Sub Command6_Click()
If DCount("[StaffName]", "qryppe", "[StaffName]=' " & Forms("MainForm").Staffname & "'") > 0 Then Exit Sub
DoCmd.OpenForm "frmppe", acNormal
End Sub

 

[KenHigg]

Put a break point on the 'if' line, execute the code and hoover the mouse over the 'Forms("MainForm").Staffname' part and see what the value is. That's the only thing I can see that would cause a problem...

 

[matthewnsarah07]

The text box [Staffname] is an unbound text box, could this cause a problem as the value has not been saved or stored?

 

[KenHigg]

It should be ok. Have you tried the hover?

 

[matthewnsarah07]

Yes, it is showing the contents of the text box correctly

Any thoughts?

 

[missinglinq]

DCount() takes some time to go thru the recordset, depending on its size, and so opening the form may occur before DCount() is finished. Access doesn't wait for one command to be completed before starting the next. If that's the case maybe this would work:

Private Sub Command6_Click()
If DCount("[StaffName]", "qryppe", "[StaffName]=' " & Forms("MainForm").Staffname & "'") <> 0 Then 
 Exit Sub
Else
 DoCmd.OpenForm "frmppe", acNormal
End If
End Sub

How does StaffName get populated if the textbox isn't bound?

 

[matthewnsarah07]

Added your suggestion but still does the same

The text box value is just typed in at the moment as the I am still building and testing it

 

[matthewnsarah07]

I've attached the sample Dbase I am using to try and test this feature before using it anywhere else.

You need to open mainform and enter the dates as From 01/10/2008 and To 31/12/2008. The qryppe takes dates from these.

Maybe you can find what I am missing here, the command 6 button should not open frmppe if either of the names in the current tblppe are entered

Hope you can help?

 

[missinglinq]

I don't have time to really play around with this right now, but from Access Help on DCount()

Domain: A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter. I believe qryppe is a parameter query, is it not?

 

[matthewnsarah07]

I tried running the query with no parameters and that makes no difference

 

[missinglinq]

This thing has driven me crazy, but I finally got it to work.

First, as stated, you can't use a Parameter Query as the Domain in the DCount(), you'll either have to make the query without a parameter, or use DCount() against the underlying table instead of the query. Either will work.

Secondly, and this is what took so long to spot, in your line

If DCount("[StaffName]", "qryppe", "[StaffName]=' " & Me.txtStaffname & "'") > 0 Then

you have a space between the Single Quote and the Double Quote immediately following

"[StaffName]=

You need to remove that space, so that it reads

If DCount("[StaffName]", "qryppe", "[StaffName]= '" & Me.txtStaffname & "'") > 0 Then

 

[matthewnsarah07]

Thank you so much for solving this one, I don't think I would have noticed that space for a long time!

Interestingly I got the DCount to work immediately by removing the space and then I added a Between line into the crieria of qryppe again, and it is now working perfectly with criteria rather than a parameter.

Thanks again

 

[missinglinq]

Glad you got it working!