Check if record already exists in recordset based on 2 conditions (1 Viewer)

[davidb88]

Hello -

I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.

I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null. It isn't working though. If anyone can spot where I am going wrong or suggest a different approach that would be great!

dim acct as long 
dim rstfiltered as DAO.Recordset 

Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'") 

acct = Me.cd_number.Value 

If Not IsNull(rstfiltered.FindFirst("CD #]=" & acct)) Then 
      msgbox "Record already exists!"
      exit sub 
Else
End If

 

[pbaldy]

I would add the criteria to the SQL above and then test for EOF. You've got a bracket at the end of the field name but not at the beginning. If you stick with the FindFirst, I suspect you want to test for NoMatch, not Null.

 

[davidb88]

Thanks pbaldy for your response. When you say add the criteria to the SQL above and test for EOF what do you mean? Sorry I am relatively new to VBA and DAO.

Also, I fixed the bracket issue and tried the NoMatch function versus null and still am getting an error. The error is coming up on the .FindFirst and it reads "Expected function or variable". Any idea what that means?

Dim acct As Long
Dim rstfiltered As DAO.Recordset
Dim rst As DAO.Recordset
Dim acctchk As Long
Dim strCriteria As String

Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")

acct = Me.cd_number.Value
strCriteria = "[CD #]=" & acct & ""
acctchk = rstfiltered.FindFirst(strCriteria)


If Not NoMatch(acctchk) Then
    
        MsgBox "There is already an open request with that CD # logged in the tracker. If you are trying to enter a " _
            & "change to an already logged request please use the Edit form otherwise check the CD # you have entered", vbOKOnly + vbExclamation, "Duplicate Entry"
        
        Set rstfiltered = Nothing
        Exit Sub
End If

 

[pbaldy]

Like:

Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed' AND [CD #]=" & acct)

Then test for EOF:

If Not rstfiltered.EOF Then

If you stay with what you have (less efficient):

rstfiltered.FindFirst(strCriteria)

If Not rstfiltered.NoMatch Then

 

[davidb88]

Hi pbaldy - What does EOF mean? And what does testing for it do? Also, you say in parenthesis "less efficient". Is there a more efficient way to test for the two conditions?

Thanks for your help!

 

[pbaldy]

EOF means End of File, or in other words the recordset not returning any records (nothing matched the criteria). You're currently opening the recordset on one condition, then searching for the other. I'd open the recordset on both conditions. That does all the work in one step and usually lets the server do all the work. You're pulling many records over the network, I'm pulling at most one.

 

[davidb88]

That makes a lot of sense. I would like to proceed with your method of pulling in the records. My one remaining question though is in your following post are you mixing in my method and your method? Would your method just be to use the following:

If Not rstfiltered.EOF Then
If Not rstfiltered.NoMatch Then

Or am I misunderstanding your post? Thanks again! This is a very helpful learning experience

Like:

Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed' AND [CD #]=" & acct)

Then test for EOF:

If Not rstfiltered.EOF Then

If you stay with what you have (less efficient):

rstfiltered.FindFirst(strCriteria)

If Not rstfiltered.NoMatch Then

 

[davidb88]

Nevermind. I misread your post a couple of times! I know understand what you are doing. I tested it and it works great. Thanks so much!

 

[pbaldy]

No the line "If you stay with what you have (less efficient):" separated the two. To use mine:

Dim rstfiltered As DAO.Recordset

Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed' AND [CD #]=" & Me.cd_number)

If Not rstfiltered.EOF Then
  MsgBox "There is already an open request with that CD # logged in the tracker. If you are trying to enter a " _
            & "change to an already logged request please use the Edit form otherwise check the CD # you have entered", vbOKOnly + vbExclamation, "Duplicate Entry"
        
End If

Set rstfiltered = Nothing
Exit Sub

 

[pbaldy]

Ah, we were typing at the same time. Glad it worked for you.