Checking Records in Another Table Efficiently

[ddevil]

I have a table that I create using a DAO recordset. Users can pull records up from this table and then manipulate certain fields, when they click the save button, then the record gets writen to the permanent table.

What would be the most efficient way to check to see if the record they are trying to save is in the permanent table already?

I was thinking of doing a dlookup or dcount (on the permanent table)on the WR# field when the form opens and if the record already exists in the permanent table to give a message box or set the saved check box to -1 or checked.

I do something similiar to this when checking for security, such as below, but I haven't had very good luck replicating this method:

Public Function SecurityCheck() As String
SaveBtn.Enabled = Not IsNull(DLookup("Area", "SecurityQuery", _
"AreaCode='" & [Area] & "' AND AdministratorID='" & [Text57] & "'"))
End Function

Any suggestions would be appreciated. Thanks!

 

[mp9]

Could you not just create a unique key index on your table comprising the two fields you're checking against? That way, when a save operation is attempted an error will be thrown if the field combination violates the unique key constraint. You can trap the error using standard error handling code to display whatever message you like...

 

[ddevil]

Honestly I tried that and couldn't get the error trapping to work, so I was going to try to do it this way as a quick fix until I get the other figured out. I do have a constraint on now and they can't enter duplicates, but they get no message telling them because I shut the warnings off. I know this is cobbling a bit, but I need to do something quick. Thanks!

 

[Remou]

You can use:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
      If DataErr = 3022 Then
         MsgBox "Duplicate key!"
         Response = acDataErrContinue
      End If
End Sub

If you have code that is not working the way you want, it is best to post that code.

 

[ddevil]

Thanks for the help, I am working on this and I'm real close, but regardless, I would like the user to be able to tell if the record exists in the permanent(final) table before they actually enter all the information and click on the save button.

So, when the user enters the form, I want to look up the WR they have entered too see if it exists in the permanent table and flag that immediately, so they don't waste their time filling out the form to find out it already exists and they have to go to another form to update the information.

Does this make sense?

 

[PHV]

What is your actual DLookUp attempt ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ddevil]

I just figured something out, but I'm sure it's not the most efficient way to do it. See below:

Public Function RecordCheck() As String
RecordExists = DLookup("ParentWRNumber", "RefundRecordExists", _
"ParentWRNumber = [CD_WR] ")

If IsNull(RecordExists) Then
Saved = 0
Else
Saved = -1
End If

End Function

Thanks again for the help!