Find a record and more...

[krester]

Find a record and more…​

• I have a form – "employee" that based on tblEmploee. After update a field in the form, I want to check in another table - tblMovements if there a recored where fieldA=1 and fieldB=5, and to get an answer – true or false.
• If the answer is – true, I what to update a field in the record that was found.

Is there a function/s that I can use?​

Thanks in advance​

 

[ErikSnoek]

Function yourFunction() As Boolean
 
Dim rstTable as DAO.recordset
Set rstTable = CurrentDB.OpenRecordset("SELECT * FROM yourTable WHERE FieldA=1 AND FieldB=2")
If rstTable.EOF Then
yourFunction = False 'no records
Else
yourFunction = True
Endif
 
End Function

 

[krester]

Dear ErikSnoek,
Thank you for the function – it's help me a lot.
I'm still looking how to update the record I found and how I can add a record to the table from the question above.
Thanks in advance​

 

[ErikSnoek]

Ah, I must have missed that part of your question. Check this out:

Function yourFunction() As Boolean
 
    Dim rstTable As DAO.Recordset
    Set rstTable = CurrentDb.OpenRecordset("SELECT * FROM yourTable")
    
    rstTable.FindFirst "FieldA=1 AND FieldB=2"
    If rstTable.NoMatch Then 'no records
        'maybe add the record?
        'rstTable.AddNew
        'rstTable!yourField = 1
        'rstTable.Update
    Else 'found the record, now edit it
        rstTable.Edit
        rstTable!yourField = 1
        rstTable.Update
    End If
    rstTable.Close
    Set rstTable = Nothing
    
End Function

Hope it helps

 

[krester]

Dear ErikSnoek,
It's great. You helped me a lot.
Have a nice day,
krester​