I am tracking the movements of people in my database. I have a Log table that stores all the recorded moves and multiple other tables that provided the data entry options, such as a clients table, actions, address, ect.. Part of the client table is an Yes/No field that I have converted to In/Out (-1 & 0 either way you look at it). Based on the users "action" selection in the Form for the Log table I would like the clients In/Out status to be updated accordingly. Each action has an associated numerical value, for example "calls in from" have a value of 1 as I have a call in counter for each client. "signs in from" has a -1 value and "signs out to" has a 0 value because of the Yes/No field. So I am doing this is a Dlookup, which works great, except for when it doesn't fit the criteria and returns a null value which the Yes/No always fills with a value and tables the clients as "Out". I can't figure out how to deal with this. I will show you what I've done so far, my brain is fried right now. I tried the NZ(Dlookup and IF Else and combos. Nothing works right.
Private Sub Action1_AfterUpdate()
Dim InOutT As String
InOutT = DLookup("[Effect]", "ActionT", _
"[Action] = '" & Action1 & "' And [Effect] = -1 Or 0")
If InOutT = 0 Then
Cancel = True
Else
Me.InOut = InOutT
End If
I also tried...
Private Sub Action1_AfterUpdate(Cancel As Boolean)
Dim InOutT As String
InOutT = Nz(DLookup("[Effect]", "ActionT", _
"[Action] = '" & Action1 & "' And [Effect] = -1 Or 0"))
If InOutT = "" Then
Cancel = True
Else
Me.InOut = InOut
End If
End Sub
Thank you very much for your interest. I know im missing more info that you might need to solve this. I've been staring at the screen for hours. My apologies.