Question Type Mismatch In A Dcount Function

[josephbupe]

Hi,

I am trying to use a DCount Function to prevent users from creating duplicate records, but i am getting a type mismatch error. The function is as follows: -

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[T_Training_Programmes]", "[Programme Name] = '" & Me.[txtProgrammeName] And "[Country] = '" & Me.[cboVenue] & "'") <> 0 Then

Cancel = True
MsgBox "cannot save duplicate", vbExclamation, "Duplicate"
End If
End Sub

Any help will be appreciated.

http://www.easyaccess.up4.net/josephbupe

 

[spikepl]

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere as String
strWhere=  "[Programme Name] = '" & Me.[txtProgrammeName] And "[Country] = '"& Me.[cboVenue] & "'" 
debug.print strWhere 
If DCount("*", "[T_Training_Programmes]", strWhere) <> 0 Then  
   Cancel = True 
   MsgBox "cannot save duplicate", vbExclamation, "Duplicate" 
End If 
End Sub

Check the strWhere in the Immediate Window and compare the data types of the two fields to what you specified in the strWhere

 

[josephbupe]

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere as String
strWhere=  "[Programme Name] = '" & Me.[txtProgrammeName] And "[Country] = '"& Me.[cboVenue] & "'" 
debug.print strWhere 
If DCount("*", "[T_Training_Programmes]", strWhere) <> 0 Then  
   Cancel = True 
   MsgBox "cannot save duplicate", vbExclamation, "Duplicate" 
End If 
End Sub

Check the strWhere in the Immediate Window and compare the data types of the two fields to what you specified in the strWhere

Hi spikepl,

I tried that but I got the same error : "Type mismatch"

Please, have a look at attached copy of my project file if you have time.
The form I am using to add data is F_Training_Programmes_Add. You might open the form F_Training_Programmes_Running To get an already existing record for testing duplicate.

I will appreciate.

Josephbupe

 

[spikepl]

No. It's a very simple thing that you should deal with by yourself, if you follow the suggestion:

Check the strWhere in the Immediate Window and compare the data types of the two fields to what you specified in the strWhere

 

[JANR]

strWhere=  "[Programme Name] = '" & Me.[txtProgrammeName] [COLOR=red]& "' And [Country] =[/COLOR] '" & Me.[cboVenue] & "'"

The formrefrence txtProgrammeName is inside the stringexpression, it needs to be outside.

JR

 

[JANR]

Another away is to include the full form refrence as stringexpression:

strWhere=  "[Programme Name] = Forms![COLOR=red]MyForm[/COLOR]!txtProgrammeName And [Country] = Forms![COLOR=red]MyForm[/COLOR]!cbovenue"

then you don't have to worrie about delimiters as access evaluate the string at runtime, provided that the form is open at execution.

JR

 

[josephbupe]

No. It's a very simple thing that you should deal with by yourself, if you follow the suggestion:

Hi,

Not sure about the immediate window you referred to, but each time i get the error i am taken to the vb window where the D Count Function is highlighted in yellow.

And the other thing am not sure whether the control source of the cboVenue is causing this, since its control source is based on the Autonumber "CountryID"

Kindly advise

josephbupe

 

[JANR]

And the other thing am not sure whether the control source of the cboVenue is causing this, since its control source is based on the Autonumber "CountryID"

Yes that will be your mismatch. If [Country] in table T_Training_Programmes is a string then you might use the column(x) to refer to the text refrence.

strWhere=  "[Programme Name] = '" & Me.[txtProgrammeName] [COLOR=red]& "' And [Country] =[/COLOR] '" & Me.[cboVenue].Column(1) & "'"

I assume that the textvalue is the second column of your combobox.

JR

 

[josephbupe]

Yes that will be your mismatch. If [Country] in table T_Training_Programmes is a string then you might use the column(x) to refer to the text refrence.

strWhere=  "[Programme Name] = '" & Me.[txtProgrammeName] [COLOR=red]& "' And [Country] =[/COLOR] '" & Me.[cboVenue].Column(1) & "'"

I assume that the textvalue is the second column of your combobox.

JR

You are right JANR. The text value is the second column of my cboVenue.

Control source:

CountryID

Row Source:

SELECT [T_Countries].[CountryID], [T_Countries].[Country] FROM T_Countries;

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String

strWhere = "[Programme Name] = '" & Me.[txtProgrammeName] & "' And [Country] = '" & Me.[cboVenue].Column(1) & "'"
If DCount("*", "[T_Training_Programmes]", strWhere) <> 0 Then

   Cancel = True
   MsgBox "cannot save duplicate", vbExclamation, "Duplicate"
End If

End Sub

But unfortunately the records are still being duplicated.

Josephbupe

 

[jonathanchye]

Try this?

If DCount("*", "[T_Training_Programmes]", "[Programme Name] = '" & Me.txtProgrammeName And "[Country] = '" & Me.cboVenue & "'") <> 0

 

[josephbupe]

There was a mismatch error again

 

[jonathanchye]

Hmm, from experience another problem that could throw a Type Mismatch error is that you are feeding a Null value into the query. Double check using the debugger to make sure you are passing valid values into the query.

Also, instead of DCount "*" you can try Dcount the Primary Key for the table instead.

 

[josephbupe]

Thank you jonathan.

 

[Galaxiom]

The bound column of cboVenue is CountryID which is a number so should not have the single quotes.

If DCount("*", "[T_Training_Programmes]", "[Programme Name] = '" & Me.txtProgrammeName And "[Country] = " & Me.cboVenue) <> 0