Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Need help with performing a "Booking" involving 3 tables

    Hello, I am trying to develop a simple database which will allow for members of the public (users) to visit various libraries. The users visits can be booked in advance.

    There are 3 tables which basically consist of -

    tblUsers
    (PK)UserID
    UserName
    UserAddress
    UserAge

    tblLibraries
    (PK)LibraryID
    LibraryName
    LibraryAddress
    LibraryPhone

    tblVisits
    (PK)VisitID
    VisitDate
    VisitStartTime
    VisitEndTime
    UserID
    LibraryID
    ArrivedYesNo

    I have attached a rough image of the ERD.

    On my main form I have 2 listboxes - lstUsers, and lstLibraries. I want to be able to select a user from lstUsers, and select a library from lstLibraries, then click a button which will load up my UsersVisits form and subform and allow me to "book" a visit for the selected user and library. The VisitID is an auto number, so every booking will increment to the next VisitID.

    I can get the form to display the correct userID and LibraryID, but the subform will not automatically increment on load to the next VisitID, instead it just displays the first VisitID for that particular User.

    My button code to load the form and subform for when a user and library is selected in each listbox is as follows (I know it needs altering, and this is where I am stuck) -
    Code:
     Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "beneficiary_visits_frm"
        stLinkCriteria = "[beneficiaryID]=" & "'" & Me![lst_beneficiaries] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        
    Forms![beneficiary_visits_frm].Form![beneficiary_visits_subform]![beneficiaryID] = Forms!main_frm.lst_beneficiaries.Column(0)
    Forms![beneficiary_visits_frm].Form![beneficiary_visits_subform]![centreID] = Forms!main_frm.lst_centres.Column(0)
    What do I need to do to allow the VisitID to automatically increase?
    Attached Thumbnails Attached Thumbnails relationships.jpg  

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need to modify the code for the following line:

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    If you add ",acFormAdd" so it looks like:

    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

    It should open the form on a new record, or in your case a visit.

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    It does indeed now automatically increment the VisitID on the UserVisits subform.

    But, now there is a problem occuring on the UserVisits form, the libraryID field (textbox) is remaining empty - it is not taking the libraryID field selected in the lstLibraries listbox on the main form when the button is pressed.

    So I have an empty UserVisits form, but the UserVisits subform is populated. But of course I cant save the record due to the empty fields on the UserVisits form.

Posting Permissions

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