Test for existing record

[kbestle]

I have a form where the user makes selections from a group of combo boxes. When finished they click a command button that concatenates these different selections and enters the result in a field on the form. After this result has been entered I want to check to see if it already exists in the underlying table. If it does exist, I want to move to that record. If it does not exist I want to move to a New record. I have tried several different approaches but none that works, obviously.

TIA

 

[MajP]

Is the form bound or unbound? If the form is bound then use the forms before update event. In this event use a dcount to see if any values exist in the table meeting your criteria
Something like

private sub form_beforeUpdate(cancel as integer)
dim fldValue as string
dim strWhere as string
fldValue = nz(yourFieldName,"")
strWhere = "someField = '" & fldValue & "'"
if dcount("someField","sometable",strWhere) > 0 then
msgbox "duplicate"
docmd.cancelevent
end if
end sub

 

[MajP]

forgot the second part

if dcount("someField","sometable",strWhere) > 0 then
msgbox "duplicate"
docmd.cancelevent
else
me.recordset.findfirst strwhere
end if

This is untested, but a general approach.

 

[kbestle]

The form is bound. By putting this in the Before Update event on the form I am not sure how it is triggered. When the command button is clicked then the following code runs

x=[field1]&" "&[field2]&" "&[field3]
Me![field4]= x

I then want to check x against the underlying table for a match.
I would think something like
c=DCount("[myfield]","[mytable]","[myfield]=Me![field4]")
if c > 0 then
go to the matching record
else
go to new record

end if

Something like this.

 

[MajP]

I think something like this

Public Sub addOrMove()
  Dim strFld4 As String
  Dim strWhere As String
  Dim intCount As String
  Call updateFld4
  
  strFld4 = Nz(Me.field4, "")
  strWhere = "field4 = '" & strFld4 & "'"
  intCount = DCount("field4", "tblOne", strWhere)
  If intCount > 0 Then
    MsgBox "Record Found"
    Me.Undo
    Me.Recordset.FindFirst strWhere
  Else
    MsgBox "Record Added"
  End If
End Sub

If your form is bound when you enter the values you are already creating a new record. I have to undo that record. Are you saving field 1-3 also? If not you could make the form unbound. If it finds the record it opens another form to that record, or goes to a new record and inserts the values into a new record.

I would do it the latter. Having the same controls do data entry and navigation gets tricky and confusing.

 

[kbestle]

Works like a champ. Thanks for your help