Check if record exists based on 1 field.

[Archer]

Hi Folks.



I want to have a field on my Access Form check to see if the record i'm about to add already exists based on the text value of the Field I am about to enter data into so essentially, something on the 'After Update' event that checks if that value already exists, if so takes me to that record, if not, proceeds with the data entry. Tried working my way through this with Macros with no joy so hoping someone has a vba answer?

thanks in advance.

[ranman256]

in an unbound form (fLookup) , with an text box, (txtBox)

sub txtBox_afterupdate()
dim lCt as long

lCt = Dcount("*","table","[field]='" & me.txtBox & "'")
if lCt = 0 then
     docmd.openform "fDataEntry",,,,acFormAdd
     forms!fDataEntry!txtBox = forms!fLookup!txtBox
else
     msgbox lCt & " exist"
endif
end sub

[June7]

I don't use macros, only VBA.

Can use domain aggregate function (DLookup, DCount, etc) to search.

I suppose by 'field' you mean an UNBOUND control - a textbox? If you enter into a BOUND control then a new record is initiated. Can cancel the record depending on result of search.

[Archer]

Sorry my bad. Should have stated it's a Form bound to a table.

[June7]

I wonder why user would be entering data that may have already been entered.

Can set field in table to not allow duplicates then Access can nag the user when they try to enter duplicate. Otherwise, perhaps code in textbox BeforeUpdate event.

[Archer]

I wonder why user would be entering data that may have already been entered.

Can set field in table to not allow duplicates then Access can nag the user when they try to enter duplicate. Otherwise, perhaps code in textbox BeforeUpdate event.

User does not know if record already exists. Setting not to allow duplicates will only notify user after the end of the data entry process. There are 60 fields on this form. I want to notify the user before they proceed any further and then take them to the record that already exists.

[June7]

But why? What about your business process would allow the same data to go through data entry process again?

Have you attempted code in the textbox BeforeUpdate as suggested?

[Archer]

But why? What about your business process would allow the same data to go through data entry process again?

Have you attempted code in the textbox BeforeUpdate as suggested?

I'm attempting to put code on the 'After Update' event of the field in question but a novice at VBA so was looking for an example.

[June7]

I am suggesting you use BeforeUpdate. Something like:

Private Sub tbxFieldname_BeforeUpdate(Cancel As Integer)
Dim strVal As String
strVal = Me.tbxFieldname
If DCount("*", "tablename", "fieldname='" & strVal & "'") > 0 Then
    Me.Undo
    Me.Filter = "fieldname='" & strVal & "'"
    Me.FilterOn = True
End If
End Sub

[Archer]

I am suggesting you use BeforeUpdate. Something like:

Private Sub tbxFieldname_BeforeUpdate(Cancel As Integer)
Dim strVal As String
strVal = Me.tbxFieldname
If DCount("*", "tablename", "fieldname='" & strVal & "'") > 0 Then
    Me.Undo
    Me.Filter = "fieldname='" & strVal & "'"
    Me.FilterOn = True
End If
End Sub

Works perfectly. Thank you sir. Much appreciated. Clearly I would have taken the wrong approach.