DCount criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PadmaBhaskar@gmail.com - 26 Jun 2006 17:03 GMT

I am trying to use DCount criteria to verify if the record with some
criteria already exists in a table. Here's the code I used:

Dim conKey As String
Dim strExist As String
conKey = txtBox1 & txtBox2 & txtBox3

strExist = DCount("[Concatenated_Key]", "MASTER_KEY_TABLE",
"[Concatenated_Key] = conKey")

If (strExist > 0) Then
MsgBox "Already exists"
End If

This doesn't seem to verify the criteria. I don't know if my DCount
syntax is wrong. Please help!

Thanks!

Reply to this Message

Ofer Cohen - 26 Jun 2006 17:29 GMT

Try this

' Change to number
Dim conKey As Long
Dim strExist As String

conKey = txtBox1 & txtBox2 & txtBox3

' Take the conKey outside the string
strExist = DCount("*", "MASTER_KEY_TABLE","[Concatenated_Key] = '" & conKey
& "'")

If strExist > 0 Then
MsgBox "Already exists"
End If

Signature

Good Luck
BS"D

> I am trying to use DCount criteria to verify if the record with some
> criteria already exists in a table. Here's the code I used:
[quoted text clipped - 14 lines]
>
> Thanks!

Reply to this Message

PadmaBhaskar@gmail.com - 26 Jun 2006 18:15 GMT

Thanks for your reply, but it gives me a Type Mismatch error. The
conKey holds a string data.

I also tried by changing the conKey to string, with the same dcount
statement. It doesn't seem to verify the criteria..

> Try this
>
[quoted text clipped - 34 lines]
> >
> > Thanks!

Reply to this Message

PadmaBhaskar@gmail.com - 26 Jun 2006 19:06 GMT

Ofer Cohen, it worked now. I think I had messed up with the quotes in
the criteria. Thank you very much!

> Try this
>
[quoted text clipped - 34 lines]
> >
> > Thanks!

Reply to this Message

Ofer Cohen - 26 Jun 2006 19:37 GMT

Actually it was my mistake, I messed up with the delaration, it should have
been the oposite.
But I'm happy you solved my error

Signature

Good Luck
BS"D

> Ofer Cohen, it worked now. I think I had messed up with the quotes in
> the criteria. Thank you very much!
[quoted text clipped - 37 lines]
> > >
> > > Thanks!

Reply to this Message

RoyVidar - 26 Jun 2006 17:46 GMT

PadmaBhaskar@gmail.com wrote in message
<1151337790.187117.148550@m73g2000cwd.googlegroups.com> :

> I am trying to use DCount criteria to verify if the record with some
> criteria already exists in a table. Here's the code I used:
[quoted text clipped - 14 lines]
>
> Thanks!

A couple of thoughts

1 - as Ofer Cohen has suggested, you need to concatenate the value of
the variable into the criterion, not the variable name, as well as the
text delimiters (single quotes for text criterion)
2 - a DCount should return a number, but you assign it to a variable of
datatype string, you might consider using a long.
3 - the concatenation of the text controls, and the mentioning of a
"Concatenated_Key" makes me shiver.

In my humble opinion etc ...
There is no (or rather, should not be) such thing as a concatenated
key.

A key, regardless of whether it is a candidate key, foreign key or
primary key, is not concatenated. That is just creating a monster which
will keep you back in your office while your collueges go home ...

A key is either one single field, or a composite key consisting of
more than one field - with emphasis on the key containing, or
consisting
of more than one field. It is never one concatenated thingie.

I'll repeat once more - do not concatenate fields into a monster field.
Keep them as separate fields, and create a composite/compound primary
key or index on them in stead.

Signature

Roy-Vidar

Reply to this Message

PadmaBhaskar@gmail.com - 26 Jun 2006 18:43 GMT

Roy, thanks for your suggestion,

1. When I concatenate, I do get the values in the Textboxes. I also
verified it by using a MsgBox to see the concatenation.

2. I agree, DCount returns a number, I changed strExist from String to
Integer.

Now, the actual problem is not with the concatenation (it works fine),
but with the DCount criteria which does not work.

3. I understand what you are saying about the Concatenated key.

The table Master_Code should have a composite Primary Key of three
fields. (say, Field1, Field2, Field3). The other fields in the table
give the details for the combination of Fields 1, 2 and 3.

Now, all these three fields should appear in the same ListBox as
multiple columns in the Form1. I now have a subform Form2 which should
link to Master Form using the composite key as the link. But it did not
work.
Please tell me if there a way to link the subform Form2 to Form1, while
having the primary keys in a single List Box in Form1?

Thanks in advance

> PadmaBhaskar@gmail.com wrote in message
> <1151337790.187117.148550@m73g2000cwd.googlegroups.com> :
[quoted text clipped - 43 lines]
> Keep them as separate fields, and create a composite/compound primary
> key or index on them in stead.

Reply to this Message

RoyVidar - 26 Jun 2006 19:09 GMT

PadmaBhaskar@gmail.com wrote in message
<1151343791.078391.5350@p79g2000cwp.googlegroups.com> :

> Roy, thanks for your suggestion,
>
[quoted text clipped - 21 lines]
>
> Thanks in advance

As far as I know, no - you can't directly link a subform to a listbox
control, when the primary key is composite. You will probably need
some programming.

I have not experimented with this, but I think I'd try to set the
recordsource of the subform programatically from the main form, after
you've selected something in the listbox.

Some air code

strSql = "select field1, field2..., fieldN " & _
"from yourtable " & _
"where field1 = " & me!lstMyList.value & _
" and field2 = " & me!lstMyList.column(1) & _
" and field3 = " & me!lstMyList.column(2)
with me!sfrmMySubformControl.Form
.recordsource = vbNullString
.recordsource = strSql
end with

I don't know if that helps you further, though.

Signature

Roy-Vidar

Reply to this Message