DCount problem

[DKpitbull]

hi guys!
It sounds easy but still don't know how to do it..Initially, I'm a new Access user and not very good in vba!
I have a form where the users are going to enter new data. This form is looking at a table and I don't want the users to be permitted to enter duplicated records in the table. I can do it with Dlookup function, but now I want to prevent entring data when a combination of criteria is met. As far as I know Dlookup works only for one criteria. More specific..
My form is called 'Companyform', the table 'Company' and the three fields in the table that I want to check are: 'Company Name', 'Street' and 'Town'. the user will be prevented to enter a new record when at least two of them are the same with a past record.
Any helpppppppp?????:banghead:

what I tried but I receive run time error:
code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Street As String
Dim Company_Name As String
Dim Town As String
If DCount("[Street]", "Company", "[Company_Name]='" & Me!Company_Name & "' AND [Town]='" & Me!Town & "'") > 0 Then
msg = "You already have that OrderID and Customer_ID combination" & vbNewLine
msg = msg & "The record will now be undone"
MsgBox msg, vbExclamation, "System Duplication Message"
Me.Undo
Cancel = True
End If
End Sub

 

[pr2-eugin]

So what's happening with this? The code throws an error? Does not count? Wrong result?

As far as I know Dlookup works only for one criteria. More specific..

Not True. DLookup comes under the Domain Function list which can take multiple arguments.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Street As String
Dim Company_Name As String
Dim Town As String
If DCount("[Street]", "Company", "[Company_Name]='" & Me!Company_Name & "' AND [Town]='" & Me!Town & "'") > 0 Then

Please use Code Tags when posting VBA Code

 

[DKpitbull]

Hi pr2-eugin!
Thanks for your reply!
The code seems not to run at all. I have three text boxes in my form: 'txtCompanyName', 'txtStreet', 'txtTown'. My form is called 'Companyform' and my table 'Company'. There are three field in the table: 'Company Name', 'Street' and 'Town'. I want to appear a message to the user when the Street & the Town names in combination, already exist in the table. I don't know how to syntax this?? Could you write some code to help me understand?
Thanks again!

 

[jdraw]

see this for an example

 

[DKpitbull]

Thanks jdraw! But it didn't help a lot..I know how Dlookup works..Read my posts again please. Thanks again!

 

[pr2-eugin]

Thanks jdraw! But it didn't help a lot..I know how Dlookup works..Read my posts again please. Thanks again!

Now now, calm down !

You say the code is not executing at all, what point do you expect the code to execute? The Before_Update is the best place to capture, but the code will not be triggered unless the Form is dirtied, then moved to a different record or a new record.

Any of the three combination So you need 3 DCount's?

If _
  DCount("*", "yourTable", "field1 = 1 AND field2 = 2") OR _
  DCount("*", "yourTable", "field1 = 1 AND field3 = 2") OR _
  DCount("*", "yourTable", "field1 = 2 AND field3 = 2") Then

 

[DKpitbull]

That's my code at the moment:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Street As String
Dim Town As String
If DCount("*", "Company", "Street = 1 AND Town = 2") Then
msg = "You already have that Street and Town combination" & vbNewLine
msg = msg & "The record will now be undone"
MsgBox msg, vbExclamation, "System Duplication Message"
Me.Undo
Cancel = True
End If
End Sub

Let's leave it simple. I don't want the combination of same street and same town at the same time. I don't mind the company's name. The message I get is:
Ru-time error '3464', Data type mismatch in criteria expression

 

[jdraw]

I want to appear a message to the user when the Street & the Town names in combination, already exist in the table. I don't know how to syntax this??

& has special meaning in Access

also

the Street & the Town names in combination

sounds like concatenation
You have a syntax issue.

It would clarify if you gave a sample of your data as input and what you expect as output.

OOops: I see we were typing at the same time.

 

[DKpitbull]

Input1: Street name text box
Input2: Town name text box

statement: If (Street name & Town name) already exist in the table ..Then
appear a message to the user and do not allow the new record to be saved in the table.

example: Lets say my table has: Alstom Ltd , Clifton Road , London

If the user try to enter for example: GE , Clifton Road , London then it will not be accepted by the system because the combination of (Clifton Road & London) already exists in the table, but If the User try :
Alstom Ltd, Clifton Road, Surrey it will be accepted by the system.
I don't mind the name of the company to be the same to a past record, only the combination of street and town not to be the same.

Thanks again guys! Sorry if I wasn't very clear

 

[DKpitbull]

Guys..this one looks to work fine!! I'll test it a bit more to be 100% sure..Thanks for your time!!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Street As String
Dim Town As String
If DCount("*", "Company", "[txtStreet]= '" & Me![Street] & "' AND [txtTown] = '" & Me![Town] & "'") > 1 Then
msg = "You already have that Street and Town combination" & vbNewLine
msg = msg & "The record will now be undone"
MsgBox msg, vbExclamation, "System Duplication Message"
Me.Undo
Cancel = True
End If
End Sub

 

[jdraw]

Research unique composite index
You can use the database system to NOT allow duplicates.