Product already selected in Current Record

[Andy]

Hi;

Need code in "Before Update" that would warn a user that a product has
already been selected in the current record.

Have read other post replies concerning this issue, and although they seem
to work for others the answers I have found are not working in this
database.

Have Tables:
tblCustomer MainTblID (AutoNumber)
tblProductBought ProductID (AutoNumber) & MainTblID (Number) & ProductName
(Text)
Relation is one to many MainTblID / MainTblID

Forms:
frmCustomer (it contains a bound control to MainTblID from tblCustomer.)
sfrmProductBought (it also contains a bound control to MainTblID from
tblProductBought.)
Note: Added MainTblID to both for testing purposes.

Tried modifying Microsoft's example:
' SAMPLE FOR NUMERIC LOOKUP
' =DLookUp("[LastName]", "Employees", _
"[EmployeeID] = Form![EmployeeID]")

Private Sub cbxProductName_BeforeUpdate(Cancel As Integer)
Dim X As Variant

X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then
Beep
Msg = MsgBox("That ''Product'' has already been selected.",
vbOKOnly, "Our Company Name")
Cancel = True
Response = acDataErrDisplay
End If

The above code does detect that the product was already added to the current
record but it ALSO shows the MsgBox if the same product was selected in a
different Record. Meaning Record 1 bought a hat, when a user selects a hat
for Record 2 the MsgBox is displayed. Record 2 does not contain a hat as
yet.
I know that means the code is returning the selection from a different
Record.

Have tried this modification to refer to the MainTblId on the frmCustomer
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTableID] = Forms![frmCustomer].Form![MainTblID]")
' and tried "[MainTableID] = Forms![frmCustomer]!Form![MainTblID]")

And this modification to refer to the control on the sfrmProductBought.
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID] = Forms![frmCustomer]![sfrmProductBought].Form![MainTblID]")
' also tried !

In all cases it shows that the product was selected in the Current Record if
that product had been selected in another Record.

Any suggestions.

Thank You for taking the time to read this post.

Andy

 

[Guest]

Hi Andy,

This is how I do it for a subform in datasheet view, but it doesn't involve
the BeforeUpdate event procedure. Instead, I create a unique index (no
duplicates) in table design view for the field in question. Then, I trap for
error 3022 in the Form_Error event procedure. The example shown below also
ensures that the parent record has been written first, so that you are not
attempting to enter items into the subform without first having a record in
the parent form. The code shown below is in the subform.


Private Sub Form_Error(DataErr As Integer, Response As Integer)
' Errors can occur here if the user has not already entered a member for
' the record on the main form. If there is an error and pkMemberIDTo is Null
' inform the user and cancel the entry made in the subform.
If DataErr > 0 Then
If IsNull(Me.Parent!pkMemberID) Then
MsgBox "Enter member's information before entering items
ordered.", _
vbOKOnly + vbInformation, "Team Transplant"
Me.Undo
Me.Parent!cboSalutation.SetFocus
Response = acDataErrContinue
Else
If DataErr = 3022 Then
MsgBox "This item has already been added." & vbCrLf & _
"Please choose another item.", _
vbOKOnly + vbInformation, "Item Already Added..."
Me.Undo
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End If
End If

End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi;

Need code in "Before Update" that would warn a user that a product has
already been selected in the current record.

Have read other post replies concerning this issue, and although they seem
to work for others the answers I have found are not working in this
database.

Have Tables:
tblCustomer MainTblID (AutoNumber)
tblProductBought ProductID (AutoNumber) & MainTblID (Number) & ProductName
(Text)
Relation is one to many MainTblID / MainTblID

Forms:
frmCustomer (it contains a bound control to MainTblID from tblCustomer.)
sfrmProductBought (it also contains a bound control to MainTblID from
tblProductBought.)
Note: Added MainTblID to both for testing purposes.

Tried modifying Microsoft's example:
' SAMPLE FOR NUMERIC LOOKUP
' =DLookUp("[LastName]", "Employees", _
"[EmployeeID] = Form![EmployeeID]")

Private Sub cbxProductName_BeforeUpdate(Cancel As Integer)
Dim X As Variant

X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then
Beep
Msg = MsgBox("That ''Product'' has already been selected.",
vbOKOnly, "Our Company Name")
Cancel = True
Response = acDataErrDisplay
End If

The above code does detect that the product was already added to the current
record but it ALSO shows the MsgBox if the same product was selected in a
different Record. Meaning Record 1 bought a hat, when a user selects a hat
for Record 2 the MsgBox is displayed. Record 2 does not contain a hat as
yet.
I know that means the code is returning the selection from a different
Record.

Have tried this modification to refer to the MainTblId on the frmCustomer
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTableID] = Forms![frmCustomer].Form![MainTblID]")
' and tried "[MainTableID] = Forms![frmCustomer]!Form![MainTblID]")

And this modification to refer to the control on the sfrmProductBought.
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID] = Forms![frmCustomer]![sfrmProductBought].Form![MainTblID]")
' also tried !

In all cases it shows that the product was selected in the Current Record if
that product had been selected in another Record.

Any suggestions.

Thank You for taking the time to read this post.

Andy

 

[Andy]

Tom;

Thank You for Your reply.

The DataErr = 3022 won't work in this situation.
3022 = "The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit duplicate entries
and try again."

The "ProductName" is not an "index, primary key, or relationship" it is Text
and the MainTblID field is a Number in tblProductBought.

Need code that would inform the user that the same product was already
selected before the record was updated.

Know that the answer is somewhere in:
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then

One more thing, spent over an hour searching Microsoft.com and other search
engines for a list of err.number messages. Finally found the answer at:
http://support.microsoft.com/kb/268721/en-us
How to Generate a list of Access Error Messages.

Added the link above because I discovered that a lot of other people were
searching for the same thing.

Thank You again.

Andy

Hi Andy,

This is how I do it for a subform in datasheet view, but it doesn't
involve
the BeforeUpdate event procedure. Instead, I create a unique index (no
duplicates) in table design view for the field in question. Then, I trap
for
error 3022 in the Form_Error event procedure. The example shown below also
ensures that the parent record has been written first, so that you are not
attempting to enter items into the subform without first having a record
in
the parent form. The code shown below is in the subform.


Private Sub Form_Error(DataErr As Integer, Response As Integer)
' Errors can occur here if the user has not already entered a member for
' the record on the main form. If there is an error and pkMemberIDTo is
Null
' inform the user and cancel the entry made in the subform.
If DataErr > 0 Then
If IsNull(Me.Parent!pkMemberID) Then
MsgBox "Enter member's information before entering items
ordered.", _
vbOKOnly + vbInformation, "Team Transplant"
Me.Undo
Me.Parent!cboSalutation.SetFocus
Response = acDataErrContinue
Else
If DataErr = 3022 Then
MsgBox "This item has already been added." & vbCrLf & _
"Please choose another item.", _
vbOKOnly + vbInformation, "Item Already Added..."
Me.Undo
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End If
End If

End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi;

Need code in "Before Update" that would warn a user that a product has
already been selected in the current record.

Have read other post replies concerning this issue, and although they
seem
to work for others the answers I have found are not working in this
database.

Have Tables:
tblCustomer MainTblID (AutoNumber)
tblProductBought ProductID (AutoNumber) & MainTblID (Number) &
ProductName
(Text)
Relation is one to many MainTblID / MainTblID

Forms:
frmCustomer (it contains a bound control to MainTblID from tblCustomer.)
sfrmProductBought (it also contains a bound control to MainTblID from
tblProductBought.)
Note: Added MainTblID to both for testing purposes.

Tried modifying Microsoft's example:
' SAMPLE FOR NUMERIC LOOKUP
' =DLookUp("[LastName]", "Employees", _
"[EmployeeID] = Form![EmployeeID]")

Private Sub cbxProductName_BeforeUpdate(Cancel As Integer)
Dim X As Variant

X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then
Beep
Msg = MsgBox("That ''Product'' has already been selected.",
vbOKOnly, "Our Company Name")
Cancel = True
Response = acDataErrDisplay
End If

The above code does detect that the product was already added to the
current
record but it ALSO shows the MsgBox if the same product was selected in a
different Record. Meaning Record 1 bought a hat, when a user selects a
hat
for Record 2 the MsgBox is displayed. Record 2 does not contain a hat as
yet.
I know that means the code is returning the selection from a different
Record.

Have tried this modification to refer to the MainTblId on the frmCustomer
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTableID] = Forms![frmCustomer].Form![MainTblID]")
' and tried "[MainTableID] = Forms![frmCustomer]!Form![MainTblID]")

And this modification to refer to the control on the sfrmProductBought.
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID] =
Forms![frmCustomer]![sfrmProductBought].Form![MainTblID]")
' also tried !

In all cases it shows that the product was selected in the Current Record
if
that product had been selected in another Record.

Any suggestions.

Thank You for taking the time to read this post.

Andy

 

[Guest]

Hi Andy,

The "ProductName" is not an "index, primary key, or relationship" it is Text
and the MainTblID field is a Number in tblProductBought.

Is there a reason that you do not wish to create a multi-field index, set as
unique (no duplicates)? You could include the ProductName and MainTblID
fields from the tblProductBought table in a multi-field index. Are you
familiar with the process of creating a multi-field index? If you do this,
the code I gave you should work.

Why are you storing ProductName in the tblProductBought table in the first
place? I would think that you should store a ProductID from the Products
table instead.

Is your cbxProductName combo box a control on the sfrmProductBought subform?
If so, what is it's recordsource (SQL statement please), and which column is
the bound column for this control?

From your initial message:

X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

The above code does detect that the product was already added to the current
record but it ALSO shows the MsgBox if the same product was selected in a
different Record.

The reason is that the criteria needs to be more specific. If you still want
to go with the domain aggregrate function DLookup (instead of setting a
multi-field index), then try the following (untested):

X = DLookup("[ProductName]", "tblProductBought", _
"[MainTblID] = [MainTblID] AND [ProductName] = '" & [ProductName] & "'")


Also from your first message:

Forms:
frmCustomer (it contains a bound control to MainTblID from tblCustomer.)

If you have not already done so, I recommend setting the Visible property
for this bound control to No. There is no reason that a user should have a
need to see an autonumber. Displaying autonumbers is a good way to confuse a
user.

Actually, the more I look at your design, I'm thinking that you should have
a table in-between your tblCustomers and tblProductBought tables that
represents Order header information (ie. OrderDate, CustomerID, etc.) similar
to the Orders table in the Northwind sample database. Your tblProductBought
table seems to be more like the Order Details table in Northwind.

One more suggestion: Add Option Explicit as the second line of all code
modules. Your "msg" and "Response" variables appear to be an undeclared
variables.

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom;

Thank You for Your reply.

The DataErr = 3022 won't work in this situation.
3022 = "The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit duplicate entries
and try again."

The "ProductName" is not an "index, primary key, or relationship" it is Text
and the MainTblID field is a Number in tblProductBought.

Need code that would inform the user that the same product was already
selected before the record was updated.

Know that the answer is somewhere in:
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then

One more thing, spent over an hour searching Microsoft.com and other search
engines for a list of err.number messages. Finally found the answer at:
http://support.microsoft.com/kb/268721/en-us
How to Generate a list of Access Error Messages.

Added the link above because I discovered that a lot of other people were
searching for the same thing.

Thank You again.

Andy

 

[Andy]

Tom;

The sfrm has 3 cbxs.
Category
Sub-Category
Product.

Each one drills down to the one below.

Category: Appliances, Clothing, Dry Goods, ETC.
Sub-Category: Hats, Shoes, Ties, ETC.
Product: Derby, Straw Hat, Top Hat.

The Product Name is the primary index.
So are the Category and Sub-Category as per Microsoft's instructions for
using "Drill Down" w/cbxs.

The Option Explicit is at the top of the srmsModule there are a few more
codes in that module..

With a Multi-Field index, (already tried), Got No Where Fast.

The Categories, Sub-Catgories, and Products are seperate tables. The user
enters that information in a different frm and sfrms.
(frmCategory/sfrmSub-Category/sfrmProducts.)

Then a salesperson selects from the lists in the cbxs in a seperate frm.

Will try the "AND" statement. Haven't tried that yet.

As for the undeclared variables left them out of the post to save space.
They are there.

Thank You for Your help.

Andy

Hi Andy,

The "ProductName" is not an "index, primary key, or relationship" it is
Text
and the MainTblID field is a Number in tblProductBought.

Is there a reason that you do not wish to create a multi-field index, set
as
unique (no duplicates)? You could include the ProductName and MainTblID
fields from the tblProductBought table in a multi-field index. Are you
familiar with the process of creating a multi-field index? If you do this,
the code I gave you should work.

Why are you storing ProductName in the tblProductBought table in the first
place? I would think that you should store a ProductID from the Products
table instead.

Is your cbxProductName combo box a control on the sfrmProductBought
subform?
If so, what is it's recordsource (SQL statement please), and which column
is
the bound column for this control?

From your initial message:

X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

The above code does detect that the product was already added to the
current
record but it ALSO shows the MsgBox if the same product was selected in a
different Record.

The reason is that the criteria needs to be more specific. If you still
want
to go with the domain aggregrate function DLookup (instead of setting a
multi-field index), then try the following (untested):

X = DLookup("[ProductName]", "tblProductBought", _
"[MainTblID] = [MainTblID] AND [ProductName] = '" & [ProductName] & "'")


Also from your first message:

Forms:
frmCustomer (it contains a bound control to MainTblID from tblCustomer.)

If you have not already done so, I recommend setting the Visible property
for this bound control to No. There is no reason that a user should have a
need to see an autonumber. Displaying autonumbers is a good way to confuse
a
user.

Actually, the more I look at your design, I'm thinking that you should
have
a table in-between your tblCustomers and tblProductBought tables that
represents Order header information (ie. OrderDate, CustomerID, etc.)
similar
to the Orders table in the Northwind sample database. Your
tblProductBought
table seems to be more like the Order Details table in Northwind.

One more suggestion: Add Option Explicit as the second line of all code
modules. Your "msg" and "Response" variables appear to be an undeclared
variables.

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom;

Thank You for Your reply.

The DataErr = 3022 won't work in this situation.
3022 = "The changes you requested to the table were not successful
because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate
data, remove the index, or redefine the index to permit duplicate entries
and try again."

The "ProductName" is not an "index, primary key, or relationship" it is
Text
and the MainTblID field is a Number in tblProductBought.

Need code that would inform the user that the same product was already
selected before the record was updated.

Know that the answer is somewhere in:
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then

One more thing, spent over an hour searching Microsoft.com and other
search
engines for a list of err.number messages. Finally found the answer at:
http://support.microsoft.com/kb/268721/en-us
How to Generate a list of Access Error Messages.

Added the link above because I discovered that a lot of other people were
searching for the same thing.

Thank You again.

Andy

 

[Andy]

Tom;

Thank You.

The AND did it.

More importantly I now know why it didn't work correctly without the AND.

Thank You again.

Andy

Tom;

The sfrm has 3 cbxs.
Category
Sub-Category
Product.

Each one drills down to the one below.

Category: Appliances, Clothing, Dry Goods, ETC.
Sub-Category: Hats, Shoes, Ties, ETC.
Product: Derby, Straw Hat, Top Hat.

The Product Name is the primary index.
So are the Category and Sub-Category as per Microsoft's instructions for
using "Drill Down" w/cbxs.

The Option Explicit is at the top of the srmsModule there are a few more
codes in that module..

With a Multi-Field index, (already tried), Got No Where Fast.

The Categories, Sub-Catgories, and Products are seperate tables. The user
enters that information in a different frm and sfrms.
(frmCategory/sfrmSub-Category/sfrmProducts.)

Then a salesperson selects from the lists in the cbxs in a seperate frm.

Will try the "AND" statement. Haven't tried that yet.

As for the undeclared variables left them out of the post to save space.
They are there.

Thank You for Your help.

Andy

Hi Andy,

The "ProductName" is not an "index, primary key, or relationship" it is
Text
and the MainTblID field is a Number in tblProductBought.

Is there a reason that you do not wish to create a multi-field index, set
as
unique (no duplicates)? You could include the ProductName and MainTblID
fields from the tblProductBought table in a multi-field index. Are you
familiar with the process of creating a multi-field index? If you do
this,
the code I gave you should work.

Why are you storing ProductName in the tblProductBought table in the
first
place? I would think that you should store a ProductID from the Products
table instead.

Is your cbxProductName combo box a control on the sfrmProductBought
subform?
If so, what is it's recordsource (SQL statement please), and which column
is
the bound column for this control?

From your initial message:

X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

The above code does detect that the product was already added to the
current
record but it ALSO shows the MsgBox if the same product was selected in
a
different Record.

The reason is that the criteria needs to be more specific. If you still
want
to go with the domain aggregrate function DLookup (instead of setting a
multi-field index), then try the following (untested):

X = DLookup("[ProductName]", "tblProductBought", _
"[MainTblID] = [MainTblID] AND [ProductName] = '" & [ProductName] & "'")


Also from your first message:

Forms:
frmCustomer (it contains a bound control to MainTblID from
tblCustomer.)

If you have not already done so, I recommend setting the Visible property
for this bound control to No. There is no reason that a user should have
a
need to see an autonumber. Displaying autonumbers is a good way to
confuse a
user.

Actually, the more I look at your design, I'm thinking that you should
have
a table in-between your tblCustomers and tblProductBought tables that
represents Order header information (ie. OrderDate, CustomerID, etc.)
similar
to the Orders table in the Northwind sample database. Your
tblProductBought
table seems to be more like the Order Details table in Northwind.

One more suggestion: Add Option Explicit as the second line of all code
modules. Your "msg" and "Response" variables appear to be an undeclared
variables.

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom;

Thank You for Your reply.

The DataErr = 3022 won't work in this situation.
3022 = "The changes you requested to the table were not successful
because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate
data, remove the index, or redefine the index to permit duplicate
entries
and try again."

The "ProductName" is not an "index, primary key, or relationship" it is
Text
and the MainTblID field is a Number in tblProductBought.

Need code that would inform the user that the same product was already
selected before the record was updated.

Know that the answer is somewhere in:
X = DLookup("[ProductName]", "tblProductBought ", _
"[MainTblID ]= Form![MainTblID]")

If Not IsNull(X) Then

One more thing, spent over an hour searching Microsoft.com and other
search
engines for a list of err.number messages. Finally found the answer at:
http://support.microsoft.com/kb/268721/en-us
How to Generate a list of Access Error Messages.

Added the link above because I discovered that a lot of other people
were
searching for the same thing.

Thank You again.

Andy

 

[Guest]

Hi Andy,

You're welcome. Glad to hear that you got it working.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________