Problems with DCount Function

[slenish]

Hello everyone,

I am using a Dcount expression to let a user know if they have already entered in a record on a certain date it will tell them and then cancel the action. My problem is that my Dcount expression only works part of the time. I have it checking through several columns at once which could be the problem but that is what i need it to do.

Also there are 9 text boxes on my form to enter in the information so i have this same code running for each text box so once you put in the information it checks to make sure it is not duplicating anything. Also I have tried running the code in both before and afterupdate.

Any help on this would be greatly appreciated :D

Thanks

If DCount("*", _
          "[MainTable]", _
          "(([Date]=#" & Format(Me.Text3, "mm/dd/YYYY") & "#) AND (" & _
          "[Box1]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box2]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box3]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box4]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box5]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box6]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box7]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box8]=" & Chr(34) & Me.Text7 & Chr(34) & ") or (" & _
          "[Box9]=" & Chr(34) & Me.Text7 & Chr(34) & "))") > 0 Then
  
   LResponse = MsgBox("A Record Of This Box On This Date Already Exists.", vbNo + vbQuestion, "Overwrite")
    LResponse = vbNo
       Cancel = True
End If

[NeoPa]

Slenish:
My problem is that my Dcount expression only works part of the time.

What does that mean??

When it fails, what happens?

If there's an error message, what is it?

With nearly 200 posts now, I would expect a better question than this.


Anyway, if you create your filter string before passing it to the DCount() call and post it here that would be something we could work with more easily. It is also a good technique to use in future whenever you have similar problems. It's always much easier for you to see what's wrong if you're looking directly at the relevant code, which in this case is the SQL string used for the filter. You may be interested in what I include below, because I'm pretty sure you're quite experienced in Access and this could take you a step further.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[NeoPa]

You could also simplify the filter string as :

Dim strSQL As String

strSQL = "(([Date]=#" & Format(Me.Text3, "mm/dd/YYYY") & "#) AND " & _
         "('" & Me.Text7 & "' In([Box1],[Box2],[Box3],[Box4]," & _
         "[Box5],[Box6],[Box7],[Box8],[Box9])))"
If DCount("*", "[MainTable]", strSQL) > 0 Then
...

You may also find Quotes (') and Double-Quotes (") - Where and When to use them of some use.

[slenish]

Hey hey Neopa,

How are u my friend? I was wondering if you were still coming here. I'm sorry I did not explain the "works sometime part" I didn't figure out why till later. What would happen is it would work on the first box1 and date but the rest are not working. I think its because its skipping the rest of my expression.

Thanks a lot for help I will try out your suggestions and let you know how it goes.

Also i appreciate you calling me an experienced programer but I still feel there is much to learn before I can hold that title :)

[NeoPa]

Hey Slenish, we all build up our experience as we go along. I'm sure you have grown in experience in the time you've been coming here. More to come surely, but building all the time :-)

The tip about viewing the SQL in the string separately from the VBA code that creates it though, will certainly help you progress.

I look forward to your next update.

[slenish]

Hi NeoPa,

I really appreciate you helping me out with this, and all the help in the past. My experience is growing but i feel like with every step I grow there is three more a head.

I like the tip about the SQL string. After looking at it, it seems cleaner and in some ways easier to reference back to for editing. I definatly will incorporate this feature moving forward.

I wish i could send you some of the projects I have been working would love to get your feed back on them. maybe i can send you pieces so you could take a look at them. I built one feature i found useful so you could see active users when they are in the program and not when they exit. :D

Anyway im rambling to much. I took the SQL example you gave me and pluged it in but now im getting a syntax error. It says Syntax error (missing operator) in query expression '(([Date]=#11/14/2010#) AND ('10' In([Box1],..[Box9])'

Any ideas why that might be happening? I tried moving things around taking away quotes adding quotes, I added Chr(34) to Me.Text7 and still no luck.

Let me know any ideas you might have.

Talk to you soon :)

[NeoPa]

Sure. I left off a couple of closing parentheses from the end (blush).

Look again at the post as I've updated it to include them. Sorry for the slip-up.

[slenish]

Amazing how much of a differnce a couple of parentheses make... :D hehe works great now!

No need to appologize!

Thank you again

is it ok if I add you to my friends list?

[NeoPa]

Slenish:
is it ok if I add you to my friends list?

Sure. That would be fine :-)