type error

[sunil@]

I have a table called transactions. i have created a form for data entry in which a field called "mon" where I set a [Event Procedure] before update for preventing duplicate entry in the table. What i am looking for... when I put a number in the "mon" field, it will check in the table that customerid (text field) and mon (numeric field) should not be pre existed. When i run the below code it appeared as "Type Mismatch",,,,, please anybody help me to settle the problem. Thanks

Private Sub MONTH_BeforeUpdate(Cancel As Integer)
Dim strMsg As String, strTitle As String
Dim intStyle As Integer


If DLookup("[customerid]", "[Transactions]", "[customerid]= '" & Me![CUSTOMERID] & "'" & " And " & "[mon] = " & Me![mon]) Then

strMsg = "Entry will not be accepted, as the similar data is already existed in the table"
strTitle = "DUPLICATE ENTRY"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle


Cancel = True
End If
End Sub

[Micron]

for DLookup, you have to put that as part of a logical test or assign to a variable. So like
IF DLookup(...) = something (or use comparison operations such as >, < and so on).

Or
myVariable = DLookup( ... ) , then do a comparison or logical test against myVariable.

Put another way, this
DLookup("[customerid]", "[Transactions]", "[customerid]= '" & Me![CUSTOMERID] & "'" & " And " & "[mon] = " & Me![mon])

returns something (let's assume it returns "ACME"). Your line now reads as

If ACME Then
which makes no sense, yes?

[Welshgasman]

Why not look up the syntax for the commands you want to use. Generally they have examples of use as well?

[moke123]

Is customerid numeric or string?

Private Sub MONTH_BeforeUpdate(Cancel As Integer)

Do you have a field "Month"? Month is a reserved word.

[NoellaG]

If you want to avoid doubles on he combination of 2 fields, the simplest and most sure way to do so is putting an unique index on the combination of those fields

[Micron]

Agree. Using a compound index means no code required. Table field validation can have custom message too.

[sunil@]

No, it is a text name only, my field name is mon (numeric)

[sunil@]

for DLookup, you have to put that as part of a logical test or assign to a variable. So like
IF DLookup(...) = something (or use comparison operations such as >, < and so on).

Or
myVariable = DLookup( ... ) , then do a comparison or logical test against myVariable.

Put another way, this
DLookup("[customerid]", "[Transactions]", "[customerid]= '" & Me![CUSTOMERID] & "'" & " And " & "[mon] = " & Me![mon])

returns something (let's assume it returns "ACME"). Your line now reads as

If ACME Then
which makes no sense, yes?

Sir, thank u for the reply ...... well sir can u please send the code that apply to your logic.... thanks

[sunil@]

If you want to avoid doubles on he combination of 2 fields, the simplest and most sure way to do so is putting an unique index on the combination of those fields

sir, there may be a same value in cutomerid field similarly in mon field also the values may be same, but my conditions is that there should not be commination of similar value in a row of a table. Exp. if your table is have row which contains coustomerid = "BB123" and mon = 3 then no new entry should allow for customerid = "BB123" & mon = 3.

[moke123]

you probably want dcount, not dlookup.

If DCount("*", "[Transactions]", "[customerid]= '" & Me.[CUSTOMERID]  & "' And [mon] = " & Me.[mon]) > 0 then . . .

[Micron]

Sir, thank u for the reply ...... well sir can u please send the code that apply to your logic.... thanks

If DLookup("[customerid]", "[Transactions]", "[customerid]= '" & Me![CUSTOMERID] & "'" & " And " & "[mon] = " & Me![mon]) > 0 Then

In post 9 you seem to be saying that what you want is exactly what a compound index provides so I don't understand your comments. The data in a compound index behaves like this:

A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3
B	1	<-- would not be allowed again

[sunil@]

If DLookup("[customerid]", "[Transactions]", "[customerid]= '" & Me![CUSTOMERID] & "'" & " And " & "[mon] = " & Me![mon]) > 0 Then

In post 9 you seem to be saying that what you want is exactly what a compound index provides so I don't understand your comments. The data in a compound index behaves like this:

A	1
A	2
A	3
B	1
B	2
B	3
C	1
C	2
C	3
B	1	<-- would not be allowed again

Thank u sir, now it is working...........a big thanks and god bless you sir

[Micron]

You're welcome. Glad you have a solution, although I'm not sure what that is.