DCount criteria

[PadmaBhaskar]

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!

 

[Guest]

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

 

[RoyVidar]

(e-mail address removed) wrote in message

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!

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.

 

[PadmaBhaskar]

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

' 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

--
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:

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!

 

[PadmaBhaskar]

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

(e-mail address removed) wrote in message

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!

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.

 

[PadmaBhaskar]

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

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

--
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:

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!

 

[RoyVidar]

(e-mail address removed) wrote in message

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

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.

 

[Guest]

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


--
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!

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

--
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:

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!