Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Question Unanswered: another question....

    Right I have given up on the idea of a check box for my equipment out on loan in my booking system, I am going to focus on the booking table to stop any duplicate bookings (maniky concering the equipment) to be made. But Im not sure where abouts in my booking form I should do this and what cde to use???

    Can anyone push me in the right direction please??

    Much Appriciated

    Luc

  2. #2
    Join Date
    Sep 2004
    Posts
    10
    Have you set a primary key in the table? That'll prevent any duplicates of the same record.

  3. #3
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    yeah but...

    I have a pk in my booking table which is bookingID and a pk in the equipment table called equipmentID, but all i wanna do is not allow anyone to book out equipment which is already booked out...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I haven't been following your progression here.. what schema did you end up going with? In essence you're going to be running a check against the db before committing your records. My recommended schema would involve a booking table with in and out dates. If you go with that schema, you could run a DCOUNT(), or a flat out query to discover whether an item is available or not. dcount would be roughly like so:

    DCount("item", [booking], "item_id = " & ItemIdHere & " AND [in] Is Null")

    That returns a count of the field "item" from any records in the booking table where the item_id is = to whatever item you're wanting to look at, and [in] is null, meaning it hasn't been returned. If DCount returns 1, the item is out, if it returns 0, the item is in.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    does it mean that...

    On my booking form the query will chech whether he equipment ID I am looking for has been already booked out or if it hasnt then it means its in stock..

    where abouts would I stick that too???

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Depends on how your booking process works.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    my db

    here is my current db to show how things would progress....the teacher would make a booking using the booking form and then save it but at present its possibl to book the same equipment out to different people where this cannot happen in real life

    the starting form is 'Main Menu'

    thanks
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Sorry I havnt been following this either, I would create a field in the products table that says in stock / Out of stock and the in the drop down on the booking form add the In Stock / Out of Stock field to the drop down, make the columns visible = 2 but make the second column 0 cm wide then on the after update update a field on the booking form with the value of whether it is in stock or out of stock.

    me.field = combo1.column(1)

    it is equal to column 1 because it starts from column 0, that will tell you whether it is in stock or out of stock to save booking it out twice

    If you want more detail on that then i will go into more description but i am not sure if that is what you want?

  9. #9
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Smile well....

    The thing is i am trying to create an instant where if the teacher selected a the equipment ID (or soon to equipment name) when he selects it from the drop down box an box will appear telling the teacher that the equipment asked for is currently on loan to another student. Then the teacher can access another form which allows him to make a reservation for the student requiring the equipment which is already out on loan.

    :0)

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Lucy

    Put the following code in the AfterUpdate event of your EquipmentID field in you Booking form. It does the check you want and asks the user if they want to make a reservation using the method suggested by Teddy. The check criteria is based on the fact if a DateTakenOut exists and a DateDueBack doesn't then we can assume the equipment is still on loan (I think this logic is correct )

    You will have to add code to open your reservation form e.g. DoCmd.OpenForm "myReservationForm" etc.

    Code:
    Dim response, strEquipment
    strEquipment = [Forms]![Booking].EquipmentID
    
    If DCount("[BookingID]", "booking", "EquipmentID = " & strEquipment & " and (isnull(DateDueBack)=-1) and (isnull(DateTakenOut)=0)") <> 0 Then
        response = MsgBox("Equipment selected is already on loan, do you wish to make a reservation", vbYesNo, "Loan Problem")
            If response = vbYes Then
                'Launch required reservation form here e.g.
                'DoCmd.OpenForm "myReservationForm" etc.
                '
                'also, you can put the EquipmentID into your reservation form e.g.
                '[Forms]![myReservationForm].myEquipField = strEquipment
            End If
            
        'Must set EquipmentID field back to null otherwise user can save invalid record.
        [Forms]![Booking].EquipmentID = Null
        
    End If
    HTH
    Chris

Posting Permissions

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