Find Duplicate Before Update

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi;

Found: 209479 ACC2000: How to Use Visual Basic for Applications to Check for
Duplicate Values in a Field example.

Trying to convert it to work in my DBase.

Snippet example:
Private Sub CustomerID_BeforeUpdate(Cancel As Integer)
Dim x As Variant

x = DLookup("[CustomerID]", "Customers", "[CustomerID]= '" _
& Forms!newcustomers!CustomerID & "'")

The Obstacle is this line:
Private Sub txtProdNumber_BeforeUpdate(Cancel As Integer)
Dim x As Variant

x = DLookup("[txtProdNumber]", "sfrmProducts", "[txtProdNumber]= '" _
&
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms![txtProdNumber] &
"'")

Tried Brackets on all "Form Names". Returns "This Object doesn't support
this property or method".

Tried
x = DLookup("[txtProdNumber]",
"frmProdCatagory!sfrmProdSubCat!sfrmProducts", "[txtProdNumber]= '" _
&
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms![txtProdNumber] &
"'")

Same Error.

A few more combinations led to "Can't find txtProdNumber".

I know it has to do with referring to the control on the "Sub-SubForm".
Checked MVPs web-site and followed it correctly. No Go. Checked
Microsoft's support and followed correctly. Still No Go.

What is the correct way to refer to the control in this instance?

Thank You.

Andy
 
1. Should use:

... .Form!txtProdNumber

NOT "Forms".

2. There still problems with your DLoopUp usage. The DLookUp is a Domain
Aggregate Function which is used on a Domain (the 2nd argument), usually a
Table or a Query and the 1st argument is usually a Field name as per
Microsoft example. Unless you use a rather strange naming convention, the
1st and 2nd arguments of your DLookUp do not look remotely what is needed.

Check Access Help on DLookUp().
 
Van;

Did read Access Help re: DLookUp. Very similar to example given in 209479
ACC2000: How to Use Visual Basic for Applications to Check for Duplicate
Values in a Field example.

Changed Forms to Form and using Your reply changed:
DLookup("[txtProdNumber]", "sfrmProducts", "[txtProdNumber]=
To
DLookup("[txtProdNumber]", "tblProducts", "[txtProdNumber]=

Tried the following. The results are listed after each:
Private Sub CustomerID_BeforeUpdate(Cancel As Integer)
Dim x As Variant

' x = DLookup("[txtProdNumber]", "tblProducts", "[txtProdNumber]= '" _
' & Form!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Form!txtProdNumber&
"'")
' Can't find field "frmProdCatagory"

' x = DLookup("[txtProdNumber]", "tblProducts", "[txtProdNumber]= '" _
' &
Form![frmProdCatagory]![sfrmProdSubCat]![sfrmProducts].Form!txtProdNumber&
"'")
' Can't find field "frmProdCatagory"

' x = DLookup("[txtProdNumber]", "tblProducts", "[txtProdNumber]= '" _
' &
Form![frmProdCatagory]![sfrmProdSubCat]![sfrmProducts].Form![txtProdNumber]
& "'")
' Can't find field "frmProdCatagory"

This one came the closest to the solution:
' x = DLookup("[txtProdNumber]", "tblProducts", "[txtProdNumber]= '" _
' &
Forms![frmProdCatagory]![sfrmProdSubCat]![sfrmProducts].Form![txtProdNumber]
& "'")
' ALTHOUGH NOT A DUPLICATE STILL GETS MSGBOX AND CAN'T MOVE OUT OF CONTROL.

' x = DLookup("[txtProdNumber]",
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts, "[txtProdNumber]= '" _
' &
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms!txtProdNumber& "'")
' OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD.

Need a little more help.

Andy

Van T. Dinh said:
1. Should use:

... .Form!txtProdNumber

NOT "Forms".

2. There still problems with your DLoopUp usage. The DLookUp is a Domain
Aggregate Function which is used on a Domain (the 2nd argument), usually a
Table or a Query and the 1st argument is usually a Field name as per
Microsoft example. Unless you use a rather strange naming convention, the
1st and 2nd arguments of your DLookUp do not look remotely what is needed.

Check Access Help on DLookUp().

--
HTH
Van T. Dinh
MVP (Access)


Andy said:
Hi;

Found: 209479 ACC2000: How to Use Visual Basic for Applications to Check for
Duplicate Values in a Field example.

Trying to convert it to work in my DBase.

Snippet example:
Private Sub CustomerID_BeforeUpdate(Cancel As Integer)
Dim x As Variant

x = DLookup("[CustomerID]", "Customers", "[CustomerID]= '" _
& Forms!newcustomers!CustomerID & "'")

The Obstacle is this line:
Private Sub txtProdNumber_BeforeUpdate(Cancel As Integer)
Dim x As Variant

x = DLookup("[txtProdNumber]", "sfrmProducts", "[txtProdNumber]= '" _
&
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms![txtProdNumber] &
"'")

Tried Brackets on all "Form Names". Returns "This Object doesn't support
this property or method".

Tried
x = DLookup("[txtProdNumber]",
"frmProdCatagory!sfrmProdSubCat!sfrmProducts", "[txtProdNumber]= '" _
&
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms![txtProdNumber] &
"'")

Same Error.

A few more combinations led to "Can't find txtProdNumber".

I know it has to do with referring to the control on the "Sub-SubForm".
Checked MVPs web-site and followed it correctly. No Go. Checked
Microsoft's support and followed correctly. Still No Go.

What is the correct way to refer to the control in this instance?

Thank You.

Andy
Click to expand...
Click to expand...
 
Copied/Pasted exactly as listed in:
209479 ACC2000: How to Use Visual Basic for Applications to Check for
Duplicate Values in a Field example.

Private Sub CustomerID_BeforeUpdate(Cancel As Integer)

Dim x As Variant

x = DLookup("[CustomerID]", "Customers", "[CustomerID]= '" _
& Forms!newcustomers!CustomerID & "'")

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub

Andy
Van T. Dinh said:
1. Should use:

... .Form!txtProdNumber

NOT "Forms".

2. There still problems with your DLoopUp usage. The DLookUp is a Domain
Aggregate Function which is used on a Domain (the 2nd argument), usually a
Table or a Query and the 1st argument is usually a Field name as per
Microsoft example. Unless you use a rather strange naming convention, the
1st and 2nd arguments of your DLookUp do not look remotely what is needed.

Check Access Help on DLookUp().

--
HTH
Van T. Dinh
MVP (Access)


Andy said:
Hi;

Found: 209479 ACC2000: How to Use Visual Basic for Applications to Check for
Duplicate Values in a Field example.

Trying to convert it to work in my DBase.

Snippet example:
Private Sub CustomerID_BeforeUpdate(Cancel As Integer)
Dim x As Variant

x = DLookup("[CustomerID]", "Customers", "[CustomerID]= '" _
& Forms!newcustomers!CustomerID & "'")

The Obstacle is this line:
Private Sub txtProdNumber_BeforeUpdate(Cancel As Integer)
Dim x As Variant

x = DLookup("[txtProdNumber]", "sfrmProducts", "[txtProdNumber]= '" _
&
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms![txtProdNumber] &
"'")

Tried Brackets on all "Form Names". Returns "This Object doesn't support
this property or method".

Tried
x = DLookup("[txtProdNumber]",
"frmProdCatagory!sfrmProdSubCat!sfrmProducts", "[txtProdNumber]= '" _
&
Forms!frmProdCatagory!sfrmProdSubCat!sfrmProducts.Forms![txtProdNumber] &
"'")

Same Error.

A few more combinations led to "Can't find txtProdNumber".

I know it has to do with referring to the control on the "Sub-SubForm".
Checked MVPs web-site and followed it correctly. No Go. Checked
Microsoft's support and followed correctly. Still No Go.

What is the correct way to refer to the control in this instance?

Thank You.

Andy
Click to expand...
Click to expand...
 
No, I meant replacing the "Forms" just before "txtProdNumber" to "Form"
since you are referring to the SourceObject of the SubformControl (a
Property of the SubformControl).

The "Forms" at the beginning is correct since you are referring to the Forms
Collection.

The full-syntax if you want to refer to a TextBox on a Sub-Subform inside a
Subform in a MainForm is: (all lines in *one* referrence):

Forms!MainFormName!SubformCONTROLName.Form!
SubSubFormCONTROLName.Form!TextBoxName

Note that the SubformCONTROLName and SubSubformCONTROLName can (and probably
are) different from the names of the Forms being used as Subforms (or more
technically correct, being used as the the SourceObjects of the
SubformControl & SubSubformControl).
 
If you look at the Microsoft example, the 1st argument of the DLookUp is a
(Table) Field name and the 2nd argument is the Table name. Remember I wrote
that DLookUp is a Domain Aggregate function which act on a *Domain*, i.e. a
Table or a Query. In fact, the "D" in DLookUp signifies it is a *Domain*
Aggregate function. In D.A.F., the 2nd argument is the domain and the 1st
argument is a Field in that domain.

From your various bits of code, are you telling me that you have a (Table)
Field name "txtProdNumber" in the Table "tblProduct"?
 
Van;

Corrected my code to match Your answer. You were right there isn't a field
named txtProdNumber it's ProdNumber.

x = DLookup("[ProdNumber]", "tblProducts", "[ProdNumber]= '" _
&
Forms![frmProdCatagory]![sfrmProdSubCat].Form![sfrmProducts].Form![txtProdNu
mber] & "'")

Still couldn't move out of the control.

Discovered that the Required=Yes in the tblProducts was causing the problem.
Had it set that way on purpose. Want the user to enter a value.

Set Required=No and added If IsNull, (w/MsgBox), code to return to
txtProdNumber until the user enters an answer.

The "Find Duplicate Before Update" works!

The code is a little "Bloated" but it works correctly and servers the
purpose.

Thank You for Your time and help.

Andy


Van T. Dinh said:
If you look at the Microsoft example, the 1st argument of the DLookUp is a
(Table) Field name and the 2nd argument is the Table name. Remember I wrote
that DLookUp is a Domain Aggregate function which act on a *Domain*, i.e. a
Table or a Query. In fact, the "D" in DLookUp signifies it is a *Domain*
Aggregate function. In D.A.F., the 2nd argument is the domain and the 1st
argument is a Field in that domain.

From your various bits of code, are you telling me that you have a (Table)
Field name "txtProdNumber" in the Table "tblProduct"?

--
HTH
Van T. Dinh
MVP (Access)




Andy said:
Copied/Pasted exactly as listed in:
209479 ACC2000: How to Use Visual Basic for Applications to Check for
Duplicate Values in a Field example.

Private Sub CustomerID_BeforeUpdate(Cancel As Integer)

Dim x As Variant

x = DLookup("[CustomerID]", "Customers", "[CustomerID]= '" _
& Forms!newcustomers!CustomerID & "'")

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub

Andy
Click to expand...
Click to expand...
 
Van;

After sending the last reply learned that my solution "IF ISNULL"
Goto.Control "txtProdNumber" causes a loop.

If the user accidently moves to a new record row and puts the focus on
txtProdNumber the user can't move out of the record or delete it. They keep
returning to txtProdNumber.

My knowledge of coding is increasing but as yet it is rudimentary.

Without an alternative, going to place a MsgBox stating that the Product
Number must contain an answer or the program won't work correctly.

Not happy with that. It will have to due for now until I learn more.

Thank You again.

Andy

Andy said:
Van;

Corrected my code to match Your answer. You were right there isn't a field
named txtProdNumber it's ProdNumber.

x = DLookup("[ProdNumber]", "tblProducts", "[ProdNumber]= '" _
&
Forms![frmProdCatagory]![sfrmProdSubCat].Form![sfrmProducts].Form![txtProdNu
mber] & "'")

Still couldn't move out of the control.

Discovered that the Required=Yes in the tblProducts was causing the problem.
Had it set that way on purpose. Want the user to enter a value.

Set Required=No and added If IsNull, (w/MsgBox), code to return to
txtProdNumber until the user enters an answer.

The "Find Duplicate Before Update" works!

The code is a little "Bloated" but it works correctly and servers the
purpose.

Thank You for Your time and help.

Andy


Van T. Dinh said:
If you look at the Microsoft example, the 1st argument of the DLookUp is a
(Table) Field name and the 2nd argument is the Table name. Remember I wrote
that DLookUp is a Domain Aggregate function which act on a *Domain*,
Click to expand...
i.e.
a
Table or a Query. In fact, the "D" in DLookUp signifies it is a *Domain*
Aggregate function. In D.A.F., the 2nd argument is the domain and the 1st
argument is a Field in that domain.

From your various bits of code, are you telling me that you have a (Table)
Field name "txtProdNumber" in the Table "tblProduct"?

--
HTH
Van T. Dinh
MVP (Access)




Andy said:
Copied/Pasted exactly as listed in:
209479 ACC2000: How to Use Visual Basic for Applications to Check for
Duplicate Values in a Field example.

Private Sub CustomerID_BeforeUpdate(Cancel As Integer)

Dim x As Variant

x = DLookup("[CustomerID]", "Customers", "[CustomerID]= '" _
& Forms!newcustomers!CustomerID & "'")

On Error GoTo CustID_Err

If Not IsNull(x) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub

Andy
Click to expand...
Click to expand...
Click to expand...
 
Sorry, you need to re-post your code.

OTOH, if the txtProdNumber is Null when it gets the Focus and is still Null
when it loses the Focus, the txtProdNumber_BeforeUpdate Event doesn't fire
(since there is nothing to update) so your code in the
txtProductNumber_BeforeUpdate is not even executed. Hence, it may be some
other problem and not the txtProdNumber_BeforeUpdate Event.
 
Back
Top