Results 1 to 14 of 14
  1. #1
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143

    Select Case Error

    I have a library table field BookName_ID ,Number, Relationship with tblBooks.ID (PrimaryKey)



    frmLibrary!BookName_ID is a combo box this is the data source is;

    SELECT [tblBooks].[ID], [tblBooks].[BookName], [tblBooks].[BookYear], tblBooks.[CopyNumber] FROM tblBooks ORDER BY [BookName];

    The code for the combo box is;
    Private Sub BookName_ID_AfterUpdate()
    Select Case Forms!frmLibrary!BookName_ID.Column(1)
    Case Is = Tables!tblLibrary!BookName_ID
    MsgBox "Book not available"
    End Select


    End Sub

    the error is in the Case Is = Tables!tblLibrary!BookName_ID
    but I do not know what is wrong.

    Help!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you can't refer to a table value that way. Use a recordset or DLookup(). Then it's simply

    Case YourValueHere
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    I did Dlookup("BookName_ID","tblLibrary","BookName_ID")
    and Dlookup("BookName_ID","frmLibrary","BookName_ID")

    not sure what this value is called to but it in the
    Select Case (?)
    Case is = (?)
    MsgBox"Book not available"
    End Select

    What do I put in where the ? marks are.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters you'd need to proper structure of a DLookup():

    DLookup Usage Samples

    Case DLookup(...)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    What exactly are you trying to do?
    If a book ID is in the result set of the query (combo row source), why are you trying to use Dlookup()??

    Is "tblBooks" a table of all books and "tblLibrary" a table of books available to loan?

    Might be easier:
    Code:
    Private Sub BookName_ID_AfterUpdate()
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        'ID is a number type
        sSQL = "Select ID FROM tblLibrary WHERE ID = " & Me.BookName_ID
    
        Set r = CurrentDb.OpenRecordset(sSQL)
        If r.BOF And r.EOF Then
            msgbox "Book not available"
    '    Else
    '        msgbox "Book is available"
        End If
        
        'clean up
        r.Close
        Set r = Nothing
        
    End Sub

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    r.BOF = r is the recordset name. BOF means Beginning of File (recordset)
    r.EOF = EOF means End of File (recordset)

    If you are at the BOF (BOF is TRUE) AND the EOF (EOF is TRUE) then there are no records in the recordset (the query).


    Code:
    sSQL = "Select ID FROM tblLibrary WHERE ID = " & Me.BookName_ID
    This is the query. It says select (return) all ID's from the table "tblLibrary" where the "ID" field is equal to the number in the control "Me.BookName_ID"


    You still haven't said what you are trying to do. You select a book from the BookName_ID combo box.
    OK, what then? What is the afterupdate event supposed to do?

  7. #7
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    if the book selected has already been checked out then MsgBox"book is checked out select another" and then have the combo box on the form clears the book selected. if the book has not been checked out then you can proceed to record the book check out.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hang in there...
    My crystal ball has been back ordered () and I don't have your dB or know what the dB is for. Trying to understand the logic so have to ask lots of questions.

    What is "tblBooks"? A table of all books available?
    What is "tblLibrary"?

    if the book selected has already been checked out
    How do you know a book has been checked out?

  9. #9
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    OK
    there are 3 tables
    tblBookAuthors tblBooks tblLibrary
    ID PK ID PK ID PK
    AuthorsName Author_ID BookName_ID (Lookup From tblBooks)
    BookName DateCheckedOut
    (Author_ID DueDate
    Has one to many CheckoutBY (lookup from tblPeople)
    Relationship) Notes

    when a book is checked out that record is stored in the tblLibrary.
    on the form to checkout books is the combobox to select a book from tblBook.
    if the book selected has been checked out the Book ID is recorded in the tblLIbrary. I want to have a message displayed "book already checked out" and the combobox cleared to select another book, when the book Selected ID matches the Book ID already in the tblLibrary

    what else do you need my DB is at work and I am home now but I can zip it tomorrow and send it to you if needed.

    Thank you so much for being patient and helpful.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    when a book is checked out that record is stored in the tblLibrary.
    Try this:
    Code:
    Private Sub BookName_ID_AfterUpdate()
        Dim r As DAO.Recordset
        Dim sSQL As String
    
        'ID is a number type
        sSQL = "Select ID FROM tblLibrary WHERE ID = " & Me.BookName_ID
        'open recordset
        Set r = CurrentDb.OpenRecordset(sSQL)
        'check for records
        If Not (r.BOF And r.EOF) Then
            'records found
            msgbox "Book already checked out"
           'clear combo box
            Me.BookName_ID = Null
           'set focus to combo box
            Me.BookName_ID.SetFocus
        End If
    
        'clean up
        r.Close
        Set r = Nothing
    
    End Sub

  11. #11
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    I created a new DB with the same tables forms and queries and inserted the code and it does nothing. I enter the same book several times ??????

  12. #12
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    good news. I was looking the code over and I changed this;
    sSQL = "Select ID FROM tblLibrary WHERE ID = " & Me.BookName_ID
    to sSQL = "Select BookName_ID FROM tblLibrary WHERE BookName_ID = " & Me.BookName_ID

    Now I get the message bu then the code stops at;
    'clear combo box
    Me.BookName_ID = Null
    Not sure what is wrong but here is the error code boxClick image for larger version. 

Name:	Librarycodeerror.jpg 
Views:	12 
Size:	40.3 KB 
ID:	22006

  13. #13
    LaughingBull is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Toledo Ohio
    Posts
    143
    I got it and here is the code: I put the code from the delete record button in place of what you had and it works great. Thank you very much
    Private Sub BookName_ID_AfterUpdate()
    Dim r As DAO.Recordset
    Dim sSQL As String

    'ID is a number type
    sSQL = "Select BookName_ID FROM tblLibrary WHERE bookName_ID = " & Me.BookName_ID
    'open recordset
    Set r = CurrentDb.OpenRecordset(sSQL)
    'check for records
    If Not (r.BOF And r.EOF) Then
    'records found
    MsgBox "Book already checked out"

    On Error Resume Next
    DoCmd.GoToControl Screen.PreviousControl.Name
    Err.Clear
    If (Not Form.NewRecord) Then
    DoCmd.RunCommand acCmdDeleteRecord
    End If
    If (Form.NewRecord And Not Form.Dirty) Then
    Beep
    End If
    If (Form.NewRecord And Form.Dirty) Then
    DoCmd.RunCommand acCmdUndo
    End If
    If (MacroError <> 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, ""
    End If

    End If

    End Sub

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Wonderful!

    The error was because it appears that the combo box is bound to a text or number type field - I assumed the control was unbound.
    (I would use an unbound combo box to search... but I don't know what the form is trying to accomplish. )

    For grins, you could try changing the code to:
    Code:
           'clear combo box
            Me.BookName_ID = Empty
           'set focus to combo box
            Me.BookName_ID.SetFocus
        End If

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

Similar Threads

  1. Should I /can I use a Select Case statement?
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 12-13-2014, 12:08 PM
  2. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 05:09 PM
  3. VBA for SELECT CASE logic
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 12-04-2012, 09:09 PM
  4. Select Case vs Dlookup
    By BRV in forum Programming
    Replies: 1
    Last Post: 10-28-2011, 03:18 PM
  5. Help with Select Case statement
    By focosi in forum Access
    Replies: 4
    Last Post: 08-09-2011, 12:01 AM

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