Results 1 to 6 of 6
  1. #1
    Emma G is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    13

    Using a Yes/No box as criteria in an If/Else statement vba

    Good afternoon,



    I was wondering if you could advise me on some coding issues I have been having.

    I have a library database which through queries successfully changes the field ´available´ field in my ´tblBook´ from a ´Yes´ to a ´No´ when that particular book is checked out and the reverse when it is checked back in. Each book has an individual barcode which is the primary key in the book table.

    I would like to include an ´If/Else´ statement to address the situation should somebody go to check out a book that has already been checked out by another pupil but has not been checked back in properly. I would like the following events to happen:

    1) If the ´available´ field in ´tblBook´ displays ´no´, a message box appears displaying ´Book already checked out´.
    2) If the ´available´ field in ´tblBook´ displays ´yes´, a message box appears displaying ´Book checkout out successfully´.

    Please could you advise me as to the coding I need for this. I have tried If/Else statements but I have made a syntactical error somewhere as it does not seem to be working.

    I presume an ´If/ElseIf´ statement is the best way to go for this, unless anyone has any other suggestions.

    Many thanks,

    Emma

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The first thing you need to do is determine the data type of your column and also it is helpful to know the name you assigned the column. The following would work with a data type of Yes/No
    Code:
    If Me.ControlName.Value = True Then
        MsgBox "This book is not avaialble."
    ElseIf Me.ControlName.Value = False Then
    'TODO Create logic to process the
    'book being checked out
    Else
    'TODO Create Error Trapping
    End If
    The above code depends on a control that is bound to the field in your table. Aslo, you may be able to get away without the ElseIf.
    Code:
    If Me.ControlName.Value = True Then
        MsgBox "This book is not avaialble."
    Else
    'TODO Create logic to process the
    'book being checked out
    End If

  3. #3
    Emma G is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    13
    Thank you for your reply.

    The field is called ´Available´ and the data type is ´yes/no´.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Emma G View Post
    Thank you for your reply.

    The field is called ´Available´ and the data type is ´yes/no´.
    Then you could use some VBA behind a form that looks something like ...
    Code:
    If Me![Available]= True Then
        MsgBox "This book is not avaialble."
    Else
    'TODO Create logic to process the
    'book being checked out
    End If
    You just need to determine what Event will be raised when the user does something. What is your plan for initiating and executing a book being checked out?

  5. #5
    Emma G is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    13
    The event runs when the user clicks the ´btncheckout´. The record is then created using a query and an update query changes the available field from a ´yes´ to a ´no´.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    So you can try some code similar to what is posted in post#4 in your btncheckout command button click event handler. You can replace the TODO notes with something that updates a record. What field(s) are you trying to update? Is it part of the current form's recordset?
    Code:
    Me![something here] = 0
    'Save the record
    If me.dirty = True then
    me.dirty = false
    end if

Please reply to this thread with any new information or opinions.

Similar Threads

  1. IIF Statement with OR criteria
    By McArthurGDM in forum Queries
    Replies: 8
    Last Post: 03-16-2015, 05:01 PM
  2. If then statement using Yes/No criteria
    By jpiazza in forum Macros
    Replies: 5
    Last Post: 07-12-2014, 11:06 PM
  3. IIf Statement in Query Criteria
    By kyle87 in forum Queries
    Replies: 8
    Last Post: 07-29-2013, 12:31 PM
  4. Help with two criteria in an IIF statement.
    By Orozvik in forum Queries
    Replies: 1
    Last Post: 04-26-2013, 02:58 PM
  5. Please Help: Combo Box Criteria Statement
    By mikeone610 in forum Queries
    Replies: 15
    Last Post: 01-27-2013, 09:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums