Sql question

[Tom]

Hi all.

I have a table (tblStoreInv) with these fields: Index, Store, Product,
Quantity.
A form (SetInventory) that is connected to that table has these fields:
StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[Douglas J. Steele]

You need to put the references to the form controls outside of the quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need to put
quotes around the value that's being passed. For example, if Product is Text
(and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

 

[Tom]

Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom

You need to put the references to the form controls outside of the quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need to
put quotes around the value that's being passed. For example, if Product
is Text (and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all.

I have a table (tblStoreInv) with these fields: Index, Store, Product,
Quantity.
A form (SetInventory) that is connected to that table has these fields:
StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[Douglas J. Steele]

I made a typo. There's an unnecessary "=Quantity" in what I posted. It
should be:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom

You need to put the references to the form controls outside of the
quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need to
put quotes around the value that's being passed. For example, if Product
is Text (and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all.

I have a table (tblStoreInv) with these fields: Index, Store, Product,
Quantity.
A form (SetInventory) that is connected to that table has these fields:
StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[Tom]

Thanks Doug,
I thought it was and I deleted it.
It still didnt work.


TIA,

Tom

I made a typo. There's an unnecessary "=Quantity" in what I posted. It
should be:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom

You need to put the references to the form controls outside of the
quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need to
put quotes around the value that's being passed. For example, if Product
is Text (and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all.

I have a table (tblStoreInv) with these fields: Index, Store, Product,
Quantity.
A form (SetInventory) that is connected to that table has these
fields: StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[Douglas J. Steele]

"Didn't work" doesn't tell me much.

Do you get an error message? If so, what's the error? If there's no error,
what are the symptoms you're experiencing?

Let's see the exact VBA code you're using.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,
I thought it was and I deleted it.
It still didnt work.


TIA,

Tom

I made a typo. There's an unnecessary "=Quantity" in what I posted. It
should be:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom
You need to put the references to the form controls outside of the
quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need
to put quotes around the value that's being passed. For example, if
Product is Text (and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all.

I have a table (tblStoreInv) with these fields: Index, Store,
Product, Quantity.
A form (SetInventory) that is connected to that table has these
fields: StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[Tom]

Thanks Doug for your help. Sorry for not being clear.
The code suppose to find a record in the table and prevent from the user to
create a duplicate.
I dont receive my MsgBox and I can create a duplicate
There is no error massage



Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & Forms![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
Forms![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & Forms![SetInventory]![Squantity]

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then
Cancel = True
MsgBox "Duplicate"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End Sub
Private Sub Exit_Click()
DoCmd.Close
End Sub

Thanks again,

Tom

"Didn't work" doesn't tell me much.

Do you get an error message? If so, what's the error? If there's no error,
what are the symptoms you're experiencing?

Let's see the exact VBA code you're using.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,
I thought it was and I deleted it.
It still didnt work.


TIA,

Tom

I made a typo. There's an unnecessary "=Quantity" in what I posted. It
should be:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom
message You need to put the references to the form controls outside of the
quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need
to put quotes around the value that's being passed. For example, if
Product is Text (and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all.

I have a table (tblStoreInv) with these fields: Index, Store,
Product, Quantity.
A form (SetInventory) that is connected to that table has these
fields: StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[Douglas J. Steele]

Since the RecordCount property is known to be unreliable unless you move to
the end of the recordset, try replacing

If rst.RecordCount > 0 Then

with

If rst.EOF = True Then

(either that, or put rst.MoveLast before you check the Count property)

If that still doesn't work, put a Debug.Print strSQL in your routine after
you've set its value. Go to the Debug window (Ctrl-G) and see whether the
SQL looks okay. Copy it into a new query and run it. What result do you get?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug for your help. Sorry for not being clear.
The code suppose to find a record in the table and prevent from the user
to create a duplicate.
I dont receive my MsgBox and I can create a duplicate
There is no error massage



Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & Forms![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
Forms![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & Forms![SetInventory]![Squantity]

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then
Cancel = True
MsgBox "Duplicate"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
End Sub
Private Sub Exit_Click()
DoCmd.Close
End Sub

Thanks again,

Tom

"Didn't work" doesn't tell me much.

Do you get an error message? If so, what's the error? If there's no
error, what are the symptoms you're experiencing?

Let's see the exact VBA code you're using.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug,
I thought it was and I deleted it.
It still didnt work.


TIA,

Tom
I made a typo. There's an unnecessary "=Quantity" in what I posted. It
should be:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug,
Thanks for the prompt response.
Store and Quantity are numeric and Product is Text.
I tried to implement the second option, but it did not work.

TIA,
Tom
message You need to put the references to the form controls outside of the
quotes.

If all three fields are numeric, try:

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & FORMS![SetInventory]![Product] & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

That assumes that all 3 fields are numeric. If they're text, you need
to put quotes around the value that's being passed. For example, if
Product is Text (and the other two numeric), you'd use:L

strSQL = "SELECT [Quantity],[Store],[Product] " & _
"FROM tblStoreInv " & _
"WHERE Store = " & FORMS![SetInventory]![StoreID] & _
" AND Product = " & Chr$(34) & _
FORMS![SetInventory]![Product] & Chr$(34) & _
" AND Quantity = " & FORMS![SetInventory]![Squantity]=Quantity

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all.

I have a table (tblStoreInv) with these fields: Index, Store,
Product, Quantity.
A form (SetInventory) that is connected to that table has these
fields: StoreID, Product, Squantity.

Only one record is allowed per store, product, Squantity.

So here is what I wrote:

Private Sub Squantity_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [Quantity],[Store],[Product] FROM tblStoreInv WHERE
FORMS![SetInventory]![StoreID]=Store AND
FORMS![SetInventory]![Product]=Product AND
FORMS![SetInventory]![Squantity]=Quantity;"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "Dupicate!"
Me.Undo
End If
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Private Sub Exit_Click()
DoCmd.Close
End Sub

It does not work.

Please help

TIA,

Tom

 

[David W. Fenton]

Since the RecordCount property is known to be unreliable unless
you move to the end of the recordset, try replacing

If rst.RecordCount > 0 Then

Not true. In DAO if the recordcount is not 0 then you know there are
1 or more records in your resultset. You need only check for it not
being zero, and there's no need to test for .EOF and .BOF (you have
to check both for an empty recordset) or to do a .MoveLast.

 

[Tom Wickerath]

Hi Tom,

Perhaps you can try a different approach. Create a combined field index
(unique / no duplicates) using the three fields: StoreID, Product and
Squantity. Then add the following code to the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
MsgBox "This item has already been added." & vbCrLf & _
"You cannot create duplicates.", _
vbOKOnly + vbInformation, "Item Already Added..."
Me.Undo
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select

End Sub


Also, your previous message showed this:

Private Sub Exit_Click()
DoCmd.Close
End Sub

I recommend that you change it to read as follows, assuming this is a bound
form:

Private Sub Exit_Click()

If Me.Dirty = True Then
Me.Dirty = False
End If

DoCmd.Close acForm, Me.Name

End Sub


Here is the reason that I make this recommendation to set Dirty = False:

Losing data when you close a form
http://allenbrowne.com/bug-01.html

Specifying "acForm, Me.Name" will help prevent any possibility of the wrong
object getting closed. While this is usually a rare occurance, it is easy
enough to add this little bit of insurance.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom]

Thanks Tom.

I have index field in the table which is unique autonumber. I put that field
on the form and I put that code you posted.
It did not prevent from creating dups. I may did it wrong or missed
something.
Can you explain more about this?

TIA,

Tom

 

[Tom Wickerath]

You need to have a multifield unique index, created using the three fields in
question: StoreID, Product and Squantity.

To create a multifield unique index, open the table in design view. Click on
the lightening bolt toolbar icon, or View > Indexes. Add an Index Name and
pick the first field, say StoreID. Leave Primary set to No, but choose Yes
for Unique. Then add the next two field names directly underneath StoreID,
without assigning a new index name. Save the table. Test it out in normal
preview mode for the table. You should not be able to enter a duplicate set
of data for these three fields, if you created a correct multi-field index.
After you are satisfied that it is working at the table level, give the code
in the form another try.

Don't forget to make the change to Private Sub Exit_Click() as well.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom]

I tried your suggestions, here is what happen:
when I enter duplicate and try to exit fro the form:
1. there is an error and that part "If Me.Dirty = True Then" is yellow.
2. The sub Form_error do not kick in. I do get error 3022 that basiclly
prevent me to create a duplicate, but not in elegant way and without the
custom massage.

Tom

 

[Tom]

I tried something, Please express your opinions:

I created a query (Look4Q) which include all the elements: StoreID,
DeliveryDay, Squantity and Product.
In order to check dups I have only to count how many records apply to these
criteria.

Private Sub Exit_Click()
Dim Q As Variant
Q = DCount("*", "Look4Q")
If Q = 1 Then
MsgBox "This item has already been added." & vbCrLf & _
"You cannot create duplicates." & vbCrLf & _
"Your entry will not be save.", vbInformation, "Item Already Added..."
Me.Undo
End If
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name
End Sub

Any suggestions? opinions?

Thanks,

Tom

 

[Tom Wickerath]

Hi Tom,

Okay, the Error 3022 is an encouraging sign.

Did you put this procedure:
Private Sub Form_Error(DataErr As Integer, Response As Integer)

in the code module associated with your main form, or in a module associated
with your subform? It needs to be in the subform's module.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

I do not use this method myself, so I have no comments regarding it. See the
posting I just made, here:

http://www.microsoft.com/office/com...cess&mid=e9516fa4-1925-4829-9003-e0a7020c465f


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom]

Hi Tom,

There is no subform, only one form.
and I put it on the form's on error event.

Thanks,

Tom

 

[Tom Wickerath]

Okay, I will try to work up a sample tonight to see what I can determine. It
will be several hours before I have a chance to get started on this.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________