Results 1 to 15 of 15
  1. #1
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    80

    Adding records to query

    Hi to all,
    I am working on a library database and I want keep track of the books being borrowed from the library.


    So,
    A user uses a form to select a certain book to borrow from the library, The form frmBooksUsers shows only that specific book (Filtered). The user clicks a button on the form to register the book.


    I need to do two things when the button is clicked:


    1- Go to that specific book to print it's information using a report rptBorrowBook with a Record Source : qryPrintCurrentBook. (Done succesfully)


    2- Add this book (record) to the query named qryBorowwingBokks which has fields from three tables: tblBooks, tblMembers, tblBorrowedBooks (tblMembers is a many-to-many relationship with tblBooks). Additional info needed is the Member (fkMemberId) and date borroed to be added; date borrowed is todays date.


    Note: All members are listed in tblMembers. I think I might need a frmMemberDialogue that has a combobox to select the member's name who borrowed the book.


    How can this be done?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,003
    you add data to tables. the query pulls that data.
    if you mean edit the existing query to pull more data out, then use the query editor and add needed fields.

    when user clicks 'borrow book' the 'code/macro' must run the append queries to add the data all the needed tables

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713

  4. #4
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    80
    Quote Originally Posted by ranman256 View Post
    you add data to tables. the query pulls that data.
    if you mean edit the existing query to pull more data out, then use the query editor and add needed fields.

    when user clicks 'borrow book' the 'code/macro' must run the append queries to add the data all the needed tables

    So,
    How can I add the information to the table tblBorrowedBooks?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,877
    ...and this doesn't seem right either
    tblMembers is a many-to-many relationship with tblBooks
    A member should only be in this table once, and any one member can borrow any number of books, thus it's a one to many relationship. If you cannot edit qryBorrowingBooks (I'm assuming what you wrote is a typo) as a test, then you might need to stop there. Your forms may not be designed correctly, or the tables/queries may not be designed correctly. In other words, just try playing directly with the query you say you want to update. If you cannot and I have correctly interpreted that your borrowing form will be based on this query, then you have a problem that needs to be fixed first.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    Here is one way - form with subform.
    Maybe it will get you started....
    Click image for larger version. 

Name:	Relationsjip41.png 
Views:	21 
Size:	28.9 KB 
ID:	40910


    No error checking, no check to see if a book of quantity 1 is checked out 2 or more times - just a basic demo
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    80
    Hi,
    Thank to all for the replies.

    I already have a form / subform that I am using as a librarian and it works fine with except that I am using a combo box instead of a list for the members.
    I am trying to do something else for the members so they can do the "borrowing process" themselves.

    What I did until now is that the user have a list of different books in a subform based on search criteria. When the user double click on any field in the record / book a new form is opened showing all the details for that book. This form has a button labeled "Register Book" , clicking that button open another form frmRegisterBook having some of the fields with a combo box showing names of all users/members . The user selects his name from the cboMemberName and fills the related fields such as date due.... then clicks on a command button to save the information.

    Note:
    1- the frmRegisterBook has a source a query similar to the one in (libray database demo ss.zip) file.
    2- I can add / edit the qryRegisterBook manually.

    I am still working on the VBA code for this; not successful yet.

    Again thank to all and thanks for the demo file.

    Khalil

  8. #8
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    80
    Hi again,
    Here is where I am now:


    The user double click any field in a datasheet view of a subform to open a form frmBookUser to see the full details of the book. Here is the code:

    Function OpenRecordForEditing()
    On Error GoTo ProcError


    If Not IsNull([pkBookId]) Then


    DoCmd.OpenForm "frmBooksUsers", OpenArgs:=1, _
    WhereCondition:="[pkBookId] = " & [pkBookId]


    End If


    ExitProc:
    Exit Function
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in OpenRecordForEditing event procedure..."
    Resume ExitProc
    End Function


    The form frmBookUser has a command button to open another form frmRegisterBook. This form is supposed to open with the same book. Here is the code of the button:


    Private Sub cmdCheckOut_Click()


    On Error GoTo ProcError


    Dim strWhere As String
    strWhere = Me.pkBookId

    DoCmd.OpenForm "frmBookRegister", OpenArgs:="pkBookId = " & strWhere

    ExitProc:
    Exit Sub

    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description & ", " _
    & vbCritical, "Error in cmdCheckOut_Click Procedure..."
    Resume ExitProc


    End Sub

    My problem is that form frmRegisterBook opens with a different record and not the same record in form frmBookUser. If form frmbookUser opens the record with pk=1250, form frmRegisterBook opens the form with record of pk=2150 (shows a different book).


    Note: Both forms are Single Form view.


    Any suggestions?

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    Hmmmmmm, why are you using the OpenArgs parameter?? Is there code in "frmBookRegister" to move to the specified record when "frmBookRegister" opens?

    It would be easier to use the WHERE parameter
    Code:
        DoCmd.OpenForm "frmBookRegister", , , "pkBookId = " & strWhere
    or (using named parameters)
    Code:
        DoCmd.OpenForm "frmBookRegister", WHERE:="pkBookId = " & strWhere
    What is the data type of Me.pkBookId? The suffix "ID" implies Me.pkBookId is a Number.
    Why is strWhere declared as a string?


    Code:
    Private Sub cmdCheckOut_Click()
        On Error GoTo ProcError
    
        Dim strWhere As String
    
        strWhere = Me.pkBookId
        
        '    DoCmd.OpenForm "frmBookRegister", OpenArgs:="pkBookId = " & strWhere
    
        '    DoCmd.OpenForm "frmBookRegister", WHERE:="pkBookId = " & strWhere
        DoCmd.OpenForm "frmBookRegister", , , "pkBookId = " & strWhere
    
    ExitProc:
        Exit Sub
    
    ProcError:
        MsgBox "Error " & Err.Number & ": " & Err.Description & ", " _
               & vbCritical, "Error in cmdCheckOut_Click Procedure..."
        Resume ExitProc
    End Sub
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    80
    Hi
    I tried the WHERE parameter but the form frmBookRegister opened without any records.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713
    Can you post a copy of your latest database with an explicit example of what you want to happen? We only need enough records to address your example.

  12. #12
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    80
    Hi,
    My database has over 50000 records 31 MB, I will try to reduce it to smaller size so I can send it.
    Mean while here is what is happening:

    The photo below shows the pkBookId of the requested record (pkBookId=13)
    Click image for larger version. 

Name:	SeeBook.PNG 
Views:	12 
Size:	7.0 KB 
ID:	40943
    when I click on the button Borrow Book the form frmRegisterBook opens and shows a different book:
    Click image for larger version. 

Name:	RegisterBook.PNG 
Views:	12 
Size:	4.8 KB 
ID:	40944
    As you can see the pkBookId = 11563 and not 13 as it should be.

    Note:
    The optional OpenArgs argument, I think , is to make the Record Selector and Navigation Buttons not visible.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,713
    My database has over 50000 records 31 MB
    So you aren't developing. You certainly don't need a database of that size for developing/prototyping some processes/logic.
    Access can be a very efficient rapid-development-tool, but you have to focus on you issue/logic. My recommendation is to work on a subset of your problem, get some feasible options, make a plan and do the physical development.
    Good luck with your project.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,877
    You store objects (images, documents) in this db? 31Mb seems a bit large for that many records - unless you also have a whack of forms and reports or forms/reports with embedded objects (and stored objects). You might have to create a db and import only what you need for this, then upload that.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    You didn't answer my questions.......

    1) What is the data type of Me.pkBookId? The suffix "ID" implies Me.pkBookId is a Number.
    2) Why is strWhere declared as a string?


    Have you set a breakpoint to see what is happening in the code?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 1
    Last Post: 12-09-2016, 08:58 AM
  2. Adding records to a join table using a query
    By brharrii in forum Queries
    Replies: 5
    Last Post: 04-12-2013, 11:08 AM
  3. Replies: 3
    Last Post: 10-25-2011, 11:38 PM
  4. Replies: 10
    Last Post: 01-10-2011, 07:52 PM
  5. Adding Records
    By Rick West in forum Forms
    Replies: 7
    Last Post: 05-17-2010, 02:31 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
  •  
Tech Forums: Microsoft Office Forums