By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

If statements for Yes/No fields

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. If DLookup("signed_out", "[tbl_visits]", "") = True Then
  2.  
  3. (action goes here)
  4.  
  5. Else
  6.  
  7. (action goes here)
  8.  
  9. 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.
Dec 19 '07 #1
Share this Question
Share on Google+
14 Replies


Nathan H
100+
P: 104
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:


Expand|Select|Wrap|Line Numbers
  1. If DLookup("signed_out", "[tbl_visits]", "") = True Then
  2.  
  3. (action goes here)
  4.  
  5. Else
  6.  
  7. (action goes here)
  8.  
  9. 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]
Dec 19 '07 #2

P: 11
Thanks for your quick reply.

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

Expand|Select|Wrap|Line Numbers
  1. If [Tables]![tbl_visits]![signed_out].Value = True Then
  2.  
  3. (do stuff)
  4.  
  5. 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.
Dec 19 '07 #3

Nathan H
100+
P: 104
Thanks for your quick reply.

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

Expand|Select|Wrap|Line Numbers
  1. If [Tables]![tbl_visits]![signed_out].Value = True Then
  2.  
  3. (do stuff)
  4.  
  5. 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?
Dec 19 '07 #4

100+
P: 365
I think it is because you need a where clause to limit the returned values,
so have "tbl.id = [forms][frmform]![form]![control]"
Dec 19 '07 #5

P: 11
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.
Dec 19 '07 #6

Nathan H
100+
P: 104
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...
Dec 19 '07 #7

jaxjagfan
Expert 100+
P: 254
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
Dec 19 '07 #8

Nathan H
100+
P: 104
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.       If DLookup("[signed_out]", "tbl_visits", "[Account] =" & Forms!Form1!Account) = True Then
  3.  
  4.       MsgBox "signed_out True"
  5.  
  6.       Else
  7.  
  8.       MsgBox "signed_out False"
  9.       End If
  10.  
  11. End Sub
  12.  
The field "Account" should be a field that is both on your form and in the table.
Dec 19 '07 #9

P: 11
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.
Dec 19 '07 #10

Nathan H
100+
P: 104
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.
Dec 19 '07 #11

P: 11
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.
Dec 19 '07 #12

Nathan H
100+
P: 104
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]
Dec 19 '07 #13

P: 11
[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. :)
Dec 19 '07 #14

Nathan H
100+
P: 104
Works absolutely flawlessly! Thankyou very much for your time. This has helped me a lot. :)

Great news. Good luck
Dec 19 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.