If statements for Yes/No fields

[Risk80]

Hi.
I'm relatively new to using Access, and I have a specific thing I can't get to work:

I have a table called tbl_visits and within that table a field for "signed_out", which is a yes/no (boolean) field. Basically, from within a form, when a button is pressed, the database needs to return whether the "signed_out" field is yes or no and take appropriate action. Current code:

If DLookup("signed_out", "[tbl_visits]", "") = True Then
 
(action goes here)
 
Else
 
(action goes here)
 
End If

However, this doesn't work and i have tried many combinations of the DLookup command to no avail. Please shed some light on this. Thanks.

[Nathan H]

Hi.
I'm relatively new to using Access, and I have a specific thing I can't get to work:

I have a table called tbl_visits and within that table a field for "signed_out", which is a yes/no (boolean) field. Basically, from within a form, when a button is pressed, the database needs to return whether the "signed_out" field is yes or no and take appropriate action. Current code:

If DLookup("signed_out", "[tbl_visits]", "") = True Then
 
(action goes here)
 
Else
 
(action goes here)
 
End If

However, this doesn't work and i have tried many combinations of the DLookup command to no avail. Please shed some light on this. Thanks.

If the table is the record source of the form:
From the Form:
[Code = vb]
If [Forms]![frmForm]![signed_out].Value = True Then

Else

End If
[/code]

[Risk80]

Thanks for your quick reply.

It needs to link to a table not a form, so i changed the code to:

If [Tables]![tbl_visits]![signed_out].Value = True Then
 
(do stuff)
 
End If

which is basically what i need to do, however this doesn't work. The form is referring to a table, not another form. Please advise.

[Nathan H]

Thanks for your quick reply.

It needs to link to a table not a form, so i changed the code to:

If [Tables]![tbl_visits]![signed_out].Value = True Then
 
(do stuff)
 
End If

which is basically what i need to do, however this doesn't work. The form is referring to a table, not another form. Please advise.

you stated that you wanted to call this from a form. What is the record source of that form?

[Dan2kx]

I think it is because you need a where clause to limit the returned values,
so have "tbl.id = [forms][frmform]![form]![control]"

[Risk80]

you stated that you wanted to call this from a form. What is the record source of that form?

The record source of the form is the visits table. Basically, the signed_out is not shown on the form, thus it only exists in the table. This is because it doesn't need to be changed manually and can be handled automatically if I can get this to work. Basically, I type something into the form, and whether or not the signed_out (yes/no object) is set to yes or no depends on whether a certain message box is shown.

The only part I can't cope with is accessing the table from within a form. Sorry for not being precise in my explanation of the problem previously.

[Nathan H]

The record source of the form is the visits table. Basically, the signed_out is not shown on the form, thus it only exists in the table. This is because it doesn't need to be changed manually and can be handled automatically if I can get this to work. Basically, I type something into the form, and whether or not the signed_out (yes/no object) is set to yes or no depends on whether a certain message box is shown.

The only part I can't cope with is accessing the table from within a form. Sorry for not being precise in my explanation of the problem previously.

If you put the signed_out field on the form, mark the "Visible" property to False (so that you can not see it), and then run the code I put in above, then it should work...

[jaxjagfan]

The signed_out value cannot be read from the table until the record is saved or refreshed.

I normally assign this value to a variable as well as the table. That way I don't have to refresh my records and can use the variable for multiple functions.

In the top of your form:

DIM iSignout

Private Sub cmdSign_Out_Click()

doSignout

End Sub

Function doSignout ()

iSignout = -1 '(Yes value)
If iSignout = -1 then
doStuff
Else
dontdoStuff
End if

End Function

[Nathan H]

The signed_out value cannot be read from the table until the record is saved or refreshed.

I normally assign this value to a variable as well as the table. That way I don't have to refresh my records and can use the variable for multiple functions.

In the top of your form:

DIM iSignout

Private Sub cmdSign_Out_Click()

doSignout

End Sub

Function doSignout ()

iSignout = -1 '(Yes value)
If iSignout = -1 then
doStuff
Else
dontdoStuff
End if

End Function

Alright...going back to your original code try this:

Private Sub Command1_Click()
      If DLookup("[signed_out]", "tbl_visits", "[Account] =" & Forms!Form1!Account) = True Then
 
      MsgBox "signed_out True"
 
      Else
 
      MsgBox "signed_out False"
      End If
 
End Sub
 

The field "Account" should be a field that is both on your form and in the table.

[Risk80]

Still can't get it to work. I just realised that the form doesn't have a record source, and i was unable to get the code above to work. This is turning into a headache.
Edit, posted this before i saw the reply directly above, however I can't get it to work because I dont know what to replace the word "account" with. I haven't had much experience with this sort of stuff however I feel that the Dlookup method is the method I'm looking for.

[Nathan H]

Still can't get it to work. I just realised that the form doesn't have a record source, and i was unable to get the code above to work. This is turning into a headache.
Edit, posted this before i saw the reply directly above, however I can't get it to work because I dont know what to replace the word "account" with. I haven't had much experience with this sort of stuff however I feel that the Dlookup method is the method I'm looking for.

Can you post what fields you have in your table and a short description. Also tell me which of these fields you plan to show on your form.

[Risk80]

Can you post what fields you have in your table and a short description. Also tell me which of these fields you plan to show on your form.

The fields in the table are:

Visit ID ( Auto number)
Visitor ID ( Number)
Date of visit ( Date/time)
Time of sign in (Date/time)
Time of sign out ( Date/time)
Staff name ( Text)
Reason for visit ( Memo)
Signed out ( Yes/No)

I am only dealing with the sign out part of the system at the moment.

So basically a visitor gets a unique number (visit ID) and they enter this into the system upon leaving. The form therefore has a visit ID text box (where visitor enters their Visit ID). Below this I have Time (just takes current time). And below that, there is a button saying "Sign out". Basically I have done the difficult stuff by creating a query to update the database based on what is in the Visit ID field, and basically everything works fine. When the query is ran, the system Time is entered in time of sign out field and "signed out" is set to yes. This all works fine.

However, the problem is that the query will only need to run if the user hasn't yet signed out, thus the database needs to read the "Signed out" field. The query already doesn't update the field if it already contains data, so this improvement is more to avoid confusion for the user than for core workings of the system.

All i need to do is for the system to be able to read the yes/no field and show a message explaining what the database is already doing, eg "Sign out complete", or "You entered an invalid visit ID or have already signed out, database not updated".

Sorry about the excessive length - just giving the full picture.

[Nathan H]

The fields in the table are:

Visit ID ( Auto number)
Visitor ID ( Number)
Date of visit ( Date/time)
Time of sign in (Date/time)
Time of sign out ( Date/time)
Staff name ( Text)
Reason for visit ( Memo)
Signed out ( Yes/No)

I am only dealing with the sign out part of the system at the moment.

So basically a visitor gets a unique number (visit ID) and they enter this into the system upon leaving. The form therefore has a visit ID text box (where visitor enters their Visit ID). Below this I have Time (just takes current time). And below that, there is a button saying "Sign out". Basically I have done the difficult stuff by creating a query to update the database based on what is in the Visit ID field, and basically everything works fine. When the query is ran, the system Time is entered in time of sign out field and "signed out" is set to yes. This all works fine.

However, the problem is that the query will only need to run if the user hasn't yet signed out, thus the database needs to read the "Signed out" field. The query already doesn't update the field if it already contains data, so this improvement is more to avoid confusion for the user than for core workings of the system.

All i need to do is for the system to be able to read the yes/no field and show a message explaining what the database is already doing, eg "Sign out complete", or "You entered an invalid visit ID or have already signed out, database not updated".

Sorry about the excessive length - just giving the full picture.

[Code =vb]
Private Sub Command2_Click()

If DLookup("[signed_out]", "tbl_visits", "[Visit ID] =" & Forms![YOUR FORM NAME]![Visit ID]) = True Then

MsgBox "signed_out True"

Else

MsgBox "signed_out False"
End If

End Sub
[/code]

[Risk80]

[Code =vb]
Private Sub Command2_Click()

If DLookup("[signed_out]", "tbl_visits", "[Visit ID] =" & Forms![YOUR FORM NAME]![Visit ID]) = True Then

MsgBox "signed_out True"

Else

MsgBox "signed_out False"
End If

End Sub
[/code]

Works absolutely flawlessly! Thankyou very much for your time. This has helped me a lot. :)

[Nathan H]

Works absolutely flawlessly! Thankyou very much for your time. This has helped me a lot. :)

Great news. Good luck