(yet another) Dcount problem

[Raddy]

I am using a form (FrmPupils) to update a Pupils table.
I am checking the table to prevent duplicate entries (ParentGuardianID and ChildID).
I am checking using 2 fields, both are integers
I am using Dcount
I have tried and changed and chopped and looked everywhere and I have nowhere left to go, so, the statement NOW looks like this (and I apologise because it now doesn't make any sense at all after so many iterations)

If DCount("*", "TblPupils", "[ParentGuardianID] =" &Forms!FrmPupils!ParentGuardianID& AND "[Child_ID] =" &Forms!Frm.Pupils!ChildID") > 0 Then

I send a message

help gratefully appreciated

 

[JamesMcS]

You've got it a bit mixed up. Try

[FONT=Courier New]If DCount("*", "TblPupils", "[ParentGuardianID] =" & Forms!FrmPupils!ParentGuardianID & " AND [Child_ID] =" &Forms!Frm.Pupils!ChildID & ")" > 0 Then[/FONT]

I'm assuming that both childID and parentguardianID are numbers. If they're not you'll have to surround them in apostrophes (')

 

[Raddy]

James
That looks a lot clearer - however I'm getting a syntax error

Any ideas?

 

[JamesMcS]

[FONT=Courier New]If DCount("*", "TblPupils", "[ParentGuardianID] =" & Forms!FrmPupils.form!ParentGuardianID & " AND [Child_ID] =" &Forms!FrmPupils.form!ChildID) > 0 Then[/FONT]

Sorry I didn't notice you had a dot in the second frmpupils reference.

 

[Raddy]

James
Still getting a syntax

is there any way to look at the actual string generated by the VB line?

 

[JamesMcS]

Yep when it hangs and you get the yellow line, you can Print the values in the controls in the immediate window of the VBA editor (I think the option to show the immediate window is in View in the VBa editor)

 

[Raddy]

Thanks again James

I'll try that

It currently reads
If DCount("*", "TblPupils", "[Parent/Guardian ID] =" & Forms!FrmPupils!ParentGuardianID & " AND [Child_ID] =" &Forms!Frm.Pupils!ChildID & ")"

<< Compile error
"Expected list seperator or )" >>

 

[JamesMcS]

Yeah I corrected my post - change the & ")" to ). The final bracket is part of the dcount expression and not part of the filter string, my apologies.

 

[Raddy]

Still get the same error

If DCount("*", "TblPupils", "[Parent/Guardian ID] = " & Forms!FrmPupils!ParentGuardianID & " AND [Child_ID] = " &Forms!Frm.Pupils!ChildID & ") > 0 Then

I must say, looking at the brackets and quotations it now makes sense (I am from an iSeries background). This one has me totally flummoxed - and that's a word I don't use lightly

 

[JamesMcS]

No sorry mate - it should just be a bracket after childID, not & ")

I'm an idiot... sorry this could have been fixed yesterday!

 

[Raddy]

Thanks James
Don't be hard on yourself - you got me out of a hole
How many hours have you spent over a problem which has baffled you then when you find it you could kick yourself really hard ( a missing apostrophe, or a line of code commented out that you didn't spot). I spent a lot of time reading up on queries and stuff, so I learned loads.

Anyway, this worked:

If DCount("*", "TblPupils", "[Parent/Guardian ID] = " & Me!ParentGuardianID & " AND [Child_ID] = " & Me!ChildID) Then

i'm going to pin it just above my desk

It's a pity there isn't a list of "Dcount" SQL & "query" examples for programmers to pick up and plug their own stuff into. I tried everywhere and nowhere gave me a decent example of Dcount with variables

Thanks again

Paul

 

[JamesMcS]

Yeah, but it keeps all us Access flunkies in a job!

Glad it worked - I just wasn't reading the code properly (and not drinking enough tea)

There are a couple of links on some of the mod's posts that might come in useful, Allen Browne's website is full of useful bits and bobs for example.

Anyhoo have a fun day!