Eliminate duplication 1

[fatz09]

I am trying to write a macro that will search a table for a duplicate value. I would really appreciate the help.

Ryan

 

[jrbarnett]

A way of doing it with a query is as follows:

Select Fieldname, Count (*)
From Table
Group By Fieldname
Having Count (*) > 1

Replace fieldname with the name of a field, and table with the name of your table.

John

 

[fatz09]

Thanks alot. I think that will work.
I keep getting a warning stating the object is not available for automated operations.
Do you know how to make a property(the field I'm counting) available for automated operations.

Fatz09

 

[jrbarnett]

Fatz09

If the fieldname to check for duplicates is "a property", the query should read as follows:

Select [A property], Count (*)
From Table
Group By [A property]
Having Count (*) > 1

You must replace "table" with the name of the table you mean.

In what circumstances are you trying to use the query?

John

 

[fatz09]

I am trying to eliminate a duplicate field when the field loses focus.

Fatz09

 

[jrbarnett]

Easiest way is to set the "Unique values" property on the field on the underlying table.
You could use the BeforeUpdate event of the control to do a DLookup to check that no value exists and cancel the event if it does:

eg

Private Sub txtTextbox_BeforeUpdate (Cancel As Integer)

If Not IsNull (DLookup ("Field", "Table", "Field='" & txtTextbox & "'&quot) Then
' data exists
Msgbox "Cannot use this. Data duplicated", vbInformation+vbOkOnly
Cancel = True
End If
End Sub

John

 

[fatz09]

What is the code to display a message box so someone can choose "yes" or "no"?

Fatz

 

[jrbarnett]

If Msgbox ("Your Message", vbYesNo+vbQuestion) = vbYes Then
' Do what you want if they clicked yes
Else
' they must have clicked No
End If

John

 

[fatz09]

Thanks,

JrBarnett you have been very helpful and I appreciate it!

Fatz

 

[fatz09]

I keep getting an error that states "Function call on left-hand side of assignment must return an Object or Variant. What is wrong?

 

[fatz09]

This is the code I have so far:

Private Sub Form_s_Hyperlink_LostFocus()
Dim x As Object
Dim rs2 As Field
Dim vbYes As Object

'Check field for empty string

If rs2 = Empty Then
MsgBox("Is the Exemtion Certificate on File?", vbYesNo + vbQuestion) = vbYes
If vbQuestion = vbYes Then
x = "Certificate on File"
Else
Type_of_Certificate.SetFocus
End If

 

[jrbarnett]

That's because you haven't set any variable for your rs2 variable. Access doesn't know which field it relates to.

If you want it to point to something on that form, change it to:

Me!txtTextbox (where txtTextbox is the name of the field), and you don't need to declare a variable of type field.

John

 

[fatz09]

jrbarnett,

This is the code i have so far. For some reason the code skips the first if statement as just puts "Certificate on File" after the last end if.

Private Sub Form_Hyperlink_LostFocus()
Dim Form_Hyperlink As String


'Check field for empty string

If Me.Form_Hyperlink = Empty Then

If MsgBox("Is the Exemption Certificate on File?", vbYesNo + vbQuestion) = vbYes Then
Me.Form_Hyperlink = "Certificate on File"
Else
Me.Form_Hyperlink = "No Certificate on File"
Type_of_Certificate.SetFocus
End If
End If
Me.Form_Hyperlink = "Check for Exemption Certificate!"

End Sub

Do you know why this is happening?

Fatz09

 

[jrbarnett]

Try changing your first line to:

If Len (Me.Form_Hyperlink & "&quot = 0 Then

John

 

[fatz09]

Thanks alot,

It works beautifully. I can finally be done with this thing. Thanks again.

Ryan

 

[fatz09]

jrbarnett,

I cant get my if...Then statements correct. It I choose "yes" or "no" on my button it makes me click it twice. Will you take a look at it please.

Thanks.

If Len(Me.Form_Hyperlink & "&quot = 0 Then

If MsgBox("Is the Exemption Certificate on File?", vbYesNoCancel + vbQuestion) = vbCancel Then
Type_of_Certificate.SetFocus

ElseIf MsgBox("Is the Exemption Certificate on File?", vbYesNoCancel + vbQuestion) = vbNo Then
Me.Form_Hyperlink = "No Certificate on File"
Else
Me.Form_Hyperlink = "Certificate on File"
End If
End If

 

[jrbarnett]

Try this:

Dim intResult As Integer

If Len(Me.Form_Hyperlink & "") = 0 Then

  intResult = MsgBox("Is the Exemption Certificate on File?", vbYesNoCancel + vbQuestion)

  if intResult = vbCancel Then
    Type_of_Certificate.SetFocus
  ElseIf intResult = vbNo Then
    Me.Form_Hyperlink = "No Certificate on File"
  Else ' must be yes
    Me.Form_Hyperlink = "Certificate on File"
  End If
End If

This works by storing the result of the first question and referring to it later on.

John