Duplicate field allowed only with checkbox = true

rlangford

Registered User.
Local time
Today, 18:35
Joined
Feb 5, 2014
Messages
17
Hello, my question is in regards to a access 2007 database. I am trying to figure out the code to try to do 2 things in a form.

1. Prompt a error message when a duplicate number field comes up, (this is not a primary key but rather a stored number ID, number field) saying something like this is a duplicate ID band please select the checkbox duplicate first to add it.

2. Figure out the code to allow duplicates only when the checkbox is selected true.

Thanks guys I appreciate any starter code and hints.
 
Hello,

A code like this at the AfterUpDate event of the textbox ?:
Code:
With Me
    .RecordsetClone.FindFirst "[IdNumber]=" & Me.NewIdNumber
    If Not NoMatch Then
        'Verify if ChkBox is False
        If Not Me.ChkBox Then
            MsgBox "Please select the checkbox duplicate first to add it !"
            Me.NewIdNumber = Null
        Else
            'allow the record
        End If
    End If
End With
 
Hi madefemere and thanks for taking the time to help me with the code to do this. I am taking a class next week on VBA code so hopefully I will be learning alot. My numeric field is called BandNum and my checkbox is called Recapture. Its throwing me a error stating a Compile error: Method or data member not found. Here is the code I used modified from yours. Do you see anything wrong with it? Thanks in advance!

Private Sub BandNum_AfterUpdate()
With Me
.RecordsetClone.FindFirst "[BandNum]=" & Me.NewBandNum
If Not NoMatch Then
'Verify if Recapture is False
If Not Me.Recapture Then
MsgBox "Please select the recapture checkbox first to add it!"
Me.NewBandNum = Null
Else
'allow the record
End If
End If
End With
End Sub
 
Hello,
Sorry a little mistake before NoMatch, I forgot a "."

Code:
Private Sub BandNum_AfterUpdate()
With Me
    .RecordsetClone.FindFirst "[BandNum]=" & Me.NewBandNum
    If Not .NoMatch Then
        'Verify if Recapture is False
        If Not Me.Recapture Then
            MsgBox "Please select the recapture checkbox first to add it!"
            Me.NewBandNum = Null
        Else
            'allow the record
        End If
    End If
End With
End Sub
 
Hello there and thanks again for helping. Here is what I have now (I added a period). When I type a number in every time (if its a new number or duplicate number) it highlights the first private line and still says compile error: Method or data member not found. Could it be something to do with "Me.NewBandNum" from the 2nd line of code? Access seems to always highlight that line too. Any help would be great!

Private Sub BandNum_AfterUpdate()
With Me
.RecordsetClone.FindFirst "[BandNum]=" & Me.NewBandNum
If Not .NoMatch Then
'Verify if Recapture is False
If Not Me.Recapture Then
MsgBox "Please select the recapture checkbox first to add it!"
Me.NewBandNum = Null
Else
'allow the record
End If
End If
End With
End Sub
 
I just wanted to update this and get some more opinions on why I would still be getting an error. Here is the code I'm currently using:

Private Sub BandNum_AfterUpdate()
With Me
.RecordsetClone.FindFirst "[BandNum]= '" & Me.BandNum & "'"
If Not Me.NoMatch Then
'Verify if Recapture is False
If Not Me.Recapture Then
MsgBox "Please select the recapture checkbox first to add it!"
Me.BandNum = Null
Else
'allow the record
End If
End If
End With
End Sub

It keeps giving me a error on the Me.NoMatch. It says its a compile error and the method or data is not found. I'm wondering if I input something wrong in the code. The boxes are called "BandNum" and the Yes/No check box is called "Recapture". Any help again would be appreciated! Thanks
 
It keeps giving me a error on the Me.NoMatch.

I know this is an old thread but I am working through what is beginning to look like a plethora of errors posted by madefemere.

The error is because NoMatch is not a property of the form (Me) but a property of the recordset.

Typically the code starts more like this:
Code:
Dim rsClone as DAO.Recordset

     Set rsClone = Me.RecordsetClone
     With rsClone
          .FindFirst "[BandNum]=" & Me.NewBandNum
          If Not .NoMatch Then
        
etc
 

Users who are viewing this thread

Back
Top Bottom