Searching if Record is in Table

F

ftrujill0

Can someone help me put together the vba code that would search an access
table for a record entered into a form field and if the record is not found
enable the data entry fields to create the new record into the table.

Example of problem.
Is record 121212 in table X?
If so, return record 121212 already exists in table X.
If not, allow enable the data entry fields (name, address, etc...)

THANKS IN ADVANCE.
 
O

Ofer

Create a field in the form that is unbound, where you can type the number you
want to search for

On the after upate event of the field run the code

' check if the record exist
If IsNull(DLookup("FieldName", "TableName", "FieldName='" &
Me.FieldnameInTheForm & "'")) Then
' if the record doesn't exist we'll move to a new record, and add the
number to the real field that is bound to the table
DoCmd.GoToRecord , , acNewRec
Me.RealFieldName = Me.FieldName
Else
' if the record exist we'll apply a filter and move to that record
Me.Filter = "FieldName = '" & Me.FieldName & "'"
Me.FilterOn = True
End If

============================================
' In this code sample I assume that the field is text, if the field is a
number type, then use this

' check if the record exist
If IsNull(DLookup("FieldName", "TableName", "FieldName=" &
Me.FieldnameInTheForm)) Then
' if the record doesn't exist we'll move to a new record, and add the
number to the real field that is bound to the table
DoCmd.GoToRecord , , acNewRec
Me.RealFieldName = Me.FieldName
Else
' if the record exist we'll apply a filter and move to that record
Me.Filter = "FieldName = " & Me.FieldName
Me.FilterOn = True
End If
=======================================================
 
F

ftrujill0

OFER - YOU ARE THE MAN!!!! THANK YOU! ;-)

Ofer said:
Create a field in the form that is unbound, where you can type the number you
want to search for

On the after upate event of the field run the code

' check if the record exist
If IsNull(DLookup("FieldName", "TableName", "FieldName='" &
Me.FieldnameInTheForm & "'")) Then
' if the record doesn't exist we'll move to a new record, and add the
number to the real field that is bound to the table
DoCmd.GoToRecord , , acNewRec
Me.RealFieldName = Me.FieldName
Else
' if the record exist we'll apply a filter and move to that record
Me.Filter = "FieldName = '" & Me.FieldName & "'"
Me.FilterOn = True
End If

============================================
' In this code sample I assume that the field is text, if the field is a
number type, then use this

' check if the record exist
If IsNull(DLookup("FieldName", "TableName", "FieldName=" &
Me.FieldnameInTheForm)) Then
' if the record doesn't exist we'll move to a new record, and add the
number to the real field that is bound to the table
DoCmd.GoToRecord , , acNewRec
Me.RealFieldName = Me.FieldName
Else
' if the record exist we'll apply a filter and move to that record
Me.Filter = "FieldName = " & Me.FieldName
Me.FilterOn = True
End If
=======================================================
Click to expand...
 
Top