CODEWITHSUNDEEP
vbams-access
Deke
Deke

Reputation: 495

Access VBA determining a Unique Value?

I'm trying to write an If statement that determines if my column [Pack_Number] has only one Unique number or if there is more than one.

I'm not exactly sure of the syntax to make this check for an Access Table since it uses records instead of rows like Excel.

This isn't right but it shows what I'm trying to do:

If [Forms]![ReviewButton]![RetailEntry].[Form]![Pack_Number].Value = Distinct Then
MsgBox "It's Unique!"
Else
MsgBox "It's NOT Unique!"
End If

Can anyone point me in the right direction for this check or have an example they can show me? Any help would be greatly appreciated! Thanks!

Upvotes: 0

Views: 289

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider domain aggregate, DCount, to search on specific value. Below assumes form value is a string to encloses single quotes.

If DCount("Pack_Number", "myTable", _ "Pack_Number = '" & _
          [Forms]![ReviewButton]![RetailEntry].[Form]![Pack_Number].Value & "'") = 1 Then
  ...

Per @Erik's comment, no need to concatenate or punctuate:

If DCount("Pack_Number", "myTable", _
          "Pack_Number = [Forms]![ReviewButton]![RetailEntry].[Form]![Pack_Number].Value") = 1 Then
  ...

Upvotes: 1

Related Questions