Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Exclamation Unanswered: Searching a database using SQL and VBA

    Hi there Im new to access 2003 and this is for my coursework project. I am trying to create a library loaning system, where by the system will manage all the users, books and loans throughout the system.

    Tbl_Users
    School_Logon_ID (PK)
    Title
    First_Name
    Surname
    Form

    Above is the structure of the table, and I am currently trying to search by using the School_logon_ID. I have tried to display the results in a list box. There is also a delete button that will delete the selected record. I managed to create the following code:

    Code:
    Private Sub Btn_DeleteDetails_Click()
    
    User_Results.RowSource = "DELETE FROM Users WHERE School_Logon_ID = School_Logon_ID.value;"
    
    End Sub
    
    
    Note: User_Results is the name of my list box
            School_logon_ID is the textbox that will allow you to enter the School ID to search the database
    However it only removes it from the list box and not the database. Any suggestions?

    Furthermore, I also have a button on the same form that when clicked will open another form which will copy all the data from the search result into the textboxes, ready for the librarian to edit, and then save. Do you also have any suggestions for this?

    As I say I am new to access 2003, and would be much appreciated if you can help me. If the way that I am tackling this problem is wrong, then could you suggest a better option?

    Many thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Asked your lecturer?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    i have managed to work out how to do it now, i replaced:

    Code:
    Private Sub Btn_DeleteDetails_Click()
    
    User_Results.RowSource = "DELETE FROM Users WHERE School_Logon_ID = School_Logon_ID.value;"
    
    End Sub
    with:

    Code:
    Private Sub Btn_DeleteDetails_Click()
    
    DoCmd.SQL "DELETE FROM Users WHERE School_Logon_ID = School_Logon_ID.value;"
    
    End Sub
    and works a treat!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •