Need reminder... 1

[straybullet]

Will Indexed Yes (No Duplicates) allow blanks to exist? or, will they all be detected as duplicates and freak out Access? lol

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

Let me apologize, I just realized I had posted this in the wrong forum.

Let them hate - so long as they fear... Lucius Accius

 

[straybullet]

Private Sub FlexNum_AfterUpdate()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   
   If Not (Me.FlexNum.Value = "" Or IsNull(Me.FlexNum.Value)) Then
      strSQL = "SELECT FlexNum FROM tblCustomers WHERE FlexNum = " & Me.FlexNum

      Set db = CurrentDb
      Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
   
      If Len(Me.FlexNum.Value) <> 4 Then
         MsgBox "The Flex Card Number must be blank or contain four (4) digits"
         
         Me.FlexNum.Value = ""
      Else
         If Not (rs.EOF And rs.BOF) Then
            rs.MoveFirst
      
            If Not (rs.Fields(0) = "" Or IsNull(rs.Fields(0))) Then
               MsgBox "This Flex Card Number already exists in the system.  Please enter a new Flex Card Number"
         
               Me.FlexNum.Value = ""
            End If
         End If
      End If
   End If
End Sub

What Im trying to do is allow no duplicate values in the FlexNum field while still allowing blanks to exist.

The form this field sits in is based on a query:

SELECT tblCustomers.*, tblFlags.BroBuddyFlag, tblFlags.BroFlag, tblFlags.WgwFlag, tblFlags.RentsOwns, tblFlags.LandFlag, tblFlags.Location, tblFlags.PrimaryFlag, tblFlags.DeadFlag, tblFlags.Other, wqrySource.sourceAll, tblFlags.CreditCheckPending
FROM (tblCustomers INNER JOIN wqrySource ON tblCustomers.SourceID = wqrySource.SourceID) INNER JOIN tblFlags ON tblCustomers.CustID = tblFlags.CustID
ORDER BY tblCustomers.CustID;

I am receiving the following error:

Compile error: Variable not defined

on

strSQL =

The code was originally written for a form based directly on a table. Is there something I need to change in order to use it in my form?

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

Try to add this instruction:
Dim strSQL As String

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[straybullet]

TY hun - you're the best! More coffee for me!

Let them hate - so long as they fear... Lucius Accius