What to do?

T

Tom

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table
GradeFrm

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
 
A

Arvin Meyer [MVP]

Use a recordset, or DCount to check if there is an existing record. Use a
unique compound index on the 2 fields to ensure there will be no duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = " &
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
A

Arvin Meyer [MVP]

Use a recordset, or DCount to check if there is an existing record. Use a
unique compound index on the 2 fields to ensure there will be no duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = " &
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
T

Tom

Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Arvin Meyer said:
Use a recordset, or DCount to check if there is an existing record. Use a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = " &
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table
GradeFrm

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
 
T

Tom

Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Arvin Meyer said:
Use a recordset, or DCount to check if there is an existing record. Use a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = " &
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table
GradeFrm

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
 
T

Tom

Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Tom said:
Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Arvin Meyer said:
Use a recordset, or DCount to check if there is an existing record. Use a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = " &
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table
GradeFrm

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Tom said:
Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Arvin Meyer said:
Use a recordset, or DCount to check if there is an existing record. Use a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = " &
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table
GradeFrm

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Tom said:
Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Tom said:
Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Arvin Meyer said:
Use a recordset, or DCount to check if there is an existing record. Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = "
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with
YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Tom said:
Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Tom said:
Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Arvin Meyer said:
Use a recordset, or DCount to check if there is an existing record. Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = "
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with
YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
A

Arvin Meyer [MVP]

OK Tom, I'll do the code with your control names. First, put the first sub
(CheckForDupe) in the form's code window without regard to a control. It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are really
lookups for ProductID and VendorID and the bound field is the long integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] = " &
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Tom said:
Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Tom said:
Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing record. Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = "
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?" with
YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Thanks Arvin for your time and patience.
I followed your directions strictly, but I got this:

run time error 3070:
The Microsoft jet database engine does not recognize 'ProductID' as a valid
field name or expression.

Please help, thanks
Tom




Arvin Meyer said:
OK Tom, I'll do the code with your control names. First, put the first sub
(CheckForDupe) in the form's code window without regard to a control. It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are
really
lookups for ProductID and VendorID and the bound field is the long
integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] = " &
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before
updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he
want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be
able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Tom said:
Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing record. Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2] = "
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?"
with
YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
A

Arvin Meyer [MVP]

Are you using lookup fields? By that I mean do you have combo boxes in the
tables themselves?

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
Thanks Arvin for your time and patience.
I followed your directions strictly, but I got this:

run time error 3070:
The Microsoft jet database engine does not recognize 'ProductID' as a valid
field name or expression.

Please help, thanks
Tom




Arvin Meyer said:
OK Tom, I'll do the code with your control names. First, put the first sub
(CheckForDupe) in the form's code window without regard to a control. It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are
really
lookups for ProductID and VendorID and the bound field is the long
integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] = " &
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before
updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he
want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be
able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Can I do something like that:

If there is a duplicate and the user choose to modify the record then the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub
Click to expand...
in
the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing record. Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And [KeyField2]
Click to expand...
=
"
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?"
with
YesNo
option.

I need when the user choose yes that on the form that combination form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination for each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Hi Arvin,
The fields "Product" and "Vendor" in tblGrades are lookup fields.(I saw the
article and I`m not sure it`s good).
for both, data type is Number and field size is Long Integer. the value that
actually you see in the table is text.

Thanks,
Tom
Arvin Meyer said:
Are you using lookup fields? By that I mean do you have combo boxes in the
tables themselves?

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
Thanks Arvin for your time and patience.
I followed your directions strictly, but I got this:

run time error 3070:
The Microsoft jet database engine does not recognize 'ProductID' as a valid
field name or expression.

Please help, thanks
Tom




Arvin Meyer said:
OK Tom, I'll do the code with your control names. First, put the first sub
(CheckForDupe) in the form's code window without regard to a control.
It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are
really
lookups for ProductID and VendorID and the bound field is the long
integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] = " &
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of
vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before
updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he
want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be
able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Can I do something like that:

If there is a duplicate and the user choose to modify the record
then
the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open the
form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in
the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing
record.
Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And
[KeyField2] =
"
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of
vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?"
with
YesNo
option.

I need when the user choose yes that on the form that combination
form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination
for
each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

I tried to change the name of fields in tblGrades:
Product changed to ProducyID and Vendor was changed to VendorID.
I ended up with another error message:
Rum time error 3070:
Syntax error (missing operator) in expression.

Thanks,
Tom
Tom said:
Hi Arvin,
The fields "Product" and "Vendor" in tblGrades are lookup fields.(I saw
the article and I`m not sure it`s good).
for both, data type is Number and field size is Long Integer. the value
that actually you see in the table is text.

Thanks,
Tom
Arvin Meyer said:
Are you using lookup fields? By that I mean do you have combo boxes in
the
tables themselves?

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
Thanks Arvin for your time and patience.
I followed your directions strictly, but I got this:

run time error 3070:
The Microsoft jet database engine does not recognize 'ProductID' as a valid
field name or expression.

Please help, thanks
Tom




OK Tom, I'll do the code with your control names. First, put the first sub
(CheckForDupe) in the form's code window without regard to a control.
It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are
really
lookups for ProductID and VendorID and the bound field is the long
integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] =
" &
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In that
order).

the goal is: in the table each record is unique, one combination of
vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before
updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if he
want
to change grade for that combination.
If he choose yes, that specific record will show and the user will be
able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Can I do something like that:

If there is a duplicate and the user choose to modify the record
then
the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open
the
form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that sub in
the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing
record.
Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And
[KeyField2] =
"
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination of
vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify Grade?"
with
YesNo
option.

I need when the user choose yes that on the form that
combination
form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination
for
each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

ProductID not ProducyID
Tom said:
I tried to change the name of fields in tblGrades:
Product changed to ProducyID and Vendor was changed to VendorID.
I ended up with another error message:
Rum time error 3070:
Syntax error (missing operator) in expression.

Thanks,
Tom
Tom said:
Hi Arvin,
The fields "Product" and "Vendor" in tblGrades are lookup fields.(I saw
the article and I`m not sure it`s good).
for both, data type is Number and field size is Long Integer. the value
that actually you see in the table is text.

Thanks,
Tom
Arvin Meyer said:
Are you using lookup fields? By that I mean do you have combo boxes in
the
tables themselves?

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Thanks Arvin for your time and patience.
I followed your directions strictly, but I got this:

run time error 3070:
The Microsoft jet database engine does not recognize 'ProductID' as a
valid
field name or expression.

Please help, thanks
Tom




OK Tom, I'll do the code with your control names. First, put the
first
sub
(CheckForDupe) in the form's code window without regard to a control.
It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are
really
lookups for ProductID and VendorID and the bound field is the long
integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] =
"
&
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In
that
order).

the goal is: in the table each record is unique, one combination of
vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before
updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if
he
want
to change grade for that combination.
If he choose yes, that specific record will show and the user will
be
able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Can I do something like that:

If there is a duplicate and the user choose to modify the record
then
the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open
the
form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that
sub
in
the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing
record.
Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And
[KeyField2]
=
"
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination
of
vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify
Grade?"
with
YesNo
option.

I need when the user choose yes that on the form that
combination
form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination
for
each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 
A

Arvin Meyer [MVP]

Take your database, or better still a piece of it with the problem. Compact
it, and zip it up. Go to my website (listed in my sig below) and find my
email address. Send me your compacted zipped database via a clearly marked
email subject: "TOM'S DATABASE in caps. I will post the answer for all to
see at http://www.accessmvp.com

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
ProductID not ProducyID
Tom said:
I tried to change the name of fields in tblGrades:
Product changed to ProducyID and Vendor was changed to VendorID.
I ended up with another error message:
Rum time error 3070:
Syntax error (missing operator) in expression.

Thanks,
Tom
Tom said:
Hi Arvin,
The fields "Product" and "Vendor" in tblGrades are lookup fields.(I saw
the article and I`m not sure it`s good).
for both, data type is Number and field size is Long Integer. the value
that actually you see in the table is text.

Thanks,
Tom
Are you using lookup fields? By that I mean do you have combo boxes in
the
tables themselves?

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Thanks Arvin for your time and patience.
I followed your directions strictly, but I got this:

run time error 3070:
The Microsoft jet database engine does not recognize 'ProductID' as a
valid
field name or expression.

Please help, thanks
Tom




OK Tom, I'll do the code with your control names. First, put the
first
sub
(CheckForDupe) in the form's code window without regard to a control.
It
should then show up as General and the sub's name. Also make sure the
FindFirst code is all on one line. I am assuming that the combos are
really
lookups for ProductID and VendorID and the bound field is the long
integer:

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

' Try to find a matching record
' Next line is all on 1 line
rst.FindFirst "[ProductID] = " & Me![cboProduct] & " And [VendorID] =
"
&
Me![cboVendor]

' Has a record been found?
If Not rst.NoMatch Then
' If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

' AfterUpdate code for Product
Sub cboProduct_AfterUpdate()
CheckForDupe
End Sub

' AfterUpdate code for Vendor
Sub cboVendor_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


This event got me all confused and frustrated.

I would like to restart from the beginning.


I have a table "tblGrades": Index, Product, Vendor, Grade.
I have a form "GradeFrm" with 3 ComboBox: Product, Vendor, Grade (In
that
order).

the goal is: in the table each record is unique, one combination of
vendor,
product and grade.
When a user fill the form, after the vendor was chosen and before
updating
the grade I want the system to check for that combination.
if it exist then a message pop-up saying that, and ask the user if
he
want
to change grade for that combination.
If he choose yes, that specific record will show and the user will
be
able
to choose a different grade.

Thank you for reading this and for the help,
Tom






Can I do something like that:

If there is a duplicate and the user choose to modify the record
then
the
form will be open with that record.

Maybe use to Dlookup: 1 to look for duplicate and second to open
the
form
with the record by Id.

In case this is a good idea, how do I implement it?

Thanks,
Tom


Hi Arvin,
Thank you for helping.
1.I tried to work with that code, it didnt do it (because of me).
I`m not sure where to put the sub and how to integrate that
sub
in
the
AfterUpdate event with the message.
2. I tried to work wth Dcount as well, but I get mistyp error.


Thanks again,
Tom


Use a recordset, or DCount to check if there is an existing
record.
Use
a
unique compound index on the 2 fields to ensure there will be no
duplicates.

Sub CheckForDupe()
Dim rst as Recordset
Set rst = Me.RecordsetClone

'Try to find a matching record
rst.FindFirst "[KeyField1] = " & Me![KeyField1] & " And
[KeyField2]
=
"
&
Me![KeyField2]

'Has one been found?
If Not rst.NoMatch Then
'If yes then
Me.Undo
Me.Bookmark = rst.BookMark
End If
End Sub

Sub KeyField1_AfterUpdate()
CheckForDupe
End Sub

Sub KeyField2_AfterUpdate()
CheckForDupe
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have a table:
tblGrade
ID
Vendor
Product
Grade

I have a form to enter data to that table

cboProduct
cboVendor
cboGrade

now I have on after update event that see if that combination
of
vendor
and
product exist.
if yes, a message popup "blah blah do you want to modify
Grade?"
with
YesNo
option.

I need when the user choose yes that on the form that
combination
form
tblGrade will appear and will be modified.
I dont want a new record to be created, so there 1 combination
for
each
vendor and product.

Thanks for your time and help,

Tom
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Sorting and grouping using multiple tables 0
Multiple Vendors for products - Table relationships 4
Two Vendor fields - relationships etc. 2
tab cntl requery 1
Database Design Question 3
Build search function in to a form 5
three combo boxes 1
2 unique Values 4

Top