Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Test for existing record

Status
Not open for further replies.

kbestle

Technical User
Mar 9, 2003
67
US
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
 
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
 
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.
 
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.
 
I think something like this
Code:
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.
 
Works like a champ. Thanks for your help
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top