This is an important piece of my coursework. that i can't do. (1 Viewer)

[burton]

hi. im doing a garden tool database that loans out equipment.
what i want to be done is if an item has been booked i want to know. e.g. if i try to book an item that has already been booked then i would like a message box to come up and display some text.
could somebody help me please.
i know i have posted this before but i do need this to be done.
i could send somebody my database if they want to have a play around to help me do this please help me.

 

[Tay]

How do you currently book items? do you have a combo or something where you can choose either 'available' or 'unavailable'? If so, you could have some code to say

Private Sub Form_Current()

If Me.cmboAvailability = "unavailable" Then
msgbox "This item is currently booked"

End If

End Sub

Hope this gives you some idea of what you need to do. Without knowing your set-up, it's hard to know eactly what you need.

 

[burton]

at the moment i use a combo box to select the item.
if you want i could send you the file. and then you could tell me how to do it or send me the new file back.
thanks for the reply.

 

[neileg]

You can use a combo to select the item, but how do you record if the item is on loan or not.

For example, you might have a field in your item table to show if it is booked or not.

You might have the item allocated to a user, so you're holding the item ID in the user table.

Or you might be doing something else.

If you really want people to look at your db, zip it up and attach it to a post here.

 

[burton]

if you want i could send you the file. because that way you would know how to update my database.
i need to know your email address though.
i can't use outlook because it doesn't work so if you send me a pm then i could send the file to you.

 

[neileg]

Sorry but it won't get through our e-mail at work. You can attach it to a posting here. You don't have to use Outlook or anything. Just look for Attach File on the Post Reply Screen.

 

[burton]

the file is too big even when i zip it.
it is 1.24mb when zipped. that is too big to add to an attachment

 

[Hayley Baxter]

you could export only the table/query and form you need to a new database and zip it. At least this will reduce the size and allow you to attach to the forum

 

[burton]

i have tried to cut the database down. but it is still too big.
if someone wants to send me a pm with their email in then i could send them the file as an attachment in an email.

 

[burton]

to the previous post.
i do not use anything like available and unavailable.
i use returned.
this indicates if an item has been returned

 

[simongallop]

As with others the firewall at work will not allow email attachments. zipped and 1.24 mb sounds way too big for a dev db. Goto Tools and compact the database and then zip it and post to the forum. If that is still too large then remove unnecessary data ie if you have 200 items in the tools table then delete 150 of them. We only need a sample of the data rather than the full lot.

HTH

 

[neileg]

OK I've got your database. You could have stripped out most of the table entries to reduce the size There's an embedded gif that probably bloats the size too.

Your tblBorrowedItems has the ItemID as a key field. That means that you can only have one entry in this table per item. If you try to book an item that has already been booked, you are trying to create a duplicate key.
The only way this is going to work is to update this record every time the borrowing status changes. However, this means that you know if the item is booked if the Returned checkbox is null. (I would not have had both a date and a checkbox for ins and outs. If there is a date, that tells you the check box would be true. No date corresponds to false, you you have redundancy here.)
When you want to borrow this item again, you are going to have to delete the field values and populate this record again. Thus you are going to loose the history on the previous booking. Because of this, there seems little point in retaining the record of the end of the booking. Why not just delete the date the item was loaned out?

I would have had a log table instead that held a record for each borrowing, in and out, without the key on the item number, so that this would track the history of the use of the item. To identify whether the item is booked or not, you could either hold a flag in the tblItem that you update each time you record an in or out for that item, or look up the latest record in the log table and test for a null in the returned date.

Sorry if this isn't the simple bit of code you might have hoped for! Your design and layout looks nice, just the logic doesn't work. I think you had an idea this was the case.

 

[burton]

thanks for taking a look at my database.
i think i know what im supposed to do.
but if i was to change the tblBorroweditems wouldn't change my project completly.
what fields would you put in the new table?
thanks for looking i thank you once again

 

[neileg]

I'd ditch tblBorrowedItems. Change tblBorrow as follows:

tblBorrow
BorrowingID PK autonumber
MembersID Foreign key
EmployeeID Foreign key
ItemID Foreign key
BookedOutDate
ReturnDate

Does this help?

 

[burton]

thanks for the reply.
i have tried this out but it seems to have too many negative effects.
i think that if i was to change from 2 tables to 1 would be a major change.
i can't quite seem to get it to work.

 

[neileg]

OK, it's your project!

You've probably had far more formal training in Acces than I have (since I have none!).

It can be difficult to correct a major design flaw when the db is almost finished.

 

[burton]

you would think that i have been taught how to do this but i haven't. the thing is i have attempted to change this but the files said it isn't a unique code or something. ii know that i have o change the file around. indexed stuff like that. but i can't do it because i think that it will wreck my database. it is working fine at the moment but i would like a message box to tell me if an item has been bokoed out.

thanks for the help.

but i think this would change my database too much. also im not quite so sure what to do.

im i keeping tblBorrowedItems and just adding new fields.
and getting rid of tblBorrow. my database has forms, macros, queries and reports that rely on this table. i would in a sense have to change everything in my project.


would this be the only way to get a message box? or would this just make common sense?

i know that this would make it easier for me if i was to do this but it will take to much time and at the moment time is not much of what i have got

 

[burton]

i have now created a new query called qry current unavailable items.

this uses criteria "yes" on booked and "no" on returned.

this shows all the items that have been booked out that haven't been returned. would this be of any help for the quest of getting a message box to work?

 

[neileg]

I suspect you have done what I suggested, only changed a different table to the same effect. Send me your revised database and I'll have a look. Before you send it, delete the B&Q image and do a repair and compact to reduce the size.

 

[burton]

i haven't changed the table. it requires to much to do. im going to have to send the file to you by email sorry.