Help with Lostbox problem

[philb42]

Help with Listbox problem

I have designed an Access 2007 programme to take bookings. The day's bookings are displayed in a series of listboxes each covering a 15min time slot. Data is derived from 2 tables - the first storing the booking details , date, client, employee etc and the second storing the time and appointment type etc. When a booking is cancelled Access deletes the booking and cascade deletes the appointment times. My problem is that after the delete the and listboxes have been requeried, although the list box for that the timeslot is blank, I cannot add a new appointment into it because Access says there is still data there. If I add a MsgBox after the requery in the macro it still shows me the old AppointmentID. The only way I can clear the data is to close the form and reopen it which is not a very practical solution. Can anyone suggest a way to clear the listbox? I have attached a screenshot of the form.

 

[JamesMcS]

Hi - welcome to the forum!

Can you uplaod a stripped out copy of the DB so we can see exactly what's going on?

 

[philb42]

Re: Help with Listbox problem

Hi James
Thanks for your reply. The DB had already been split hence 2 files. The easiest way to link the data would be to put both files in a folder labelled "Styleworks" in C: as that's how it has been set up.
Double click in the required timeslot to create an appointment. Each task will fill the appropriate No. of timeslots. Once saved double click on the appointment timeslot so see details or to cancel the appointment. The problem arrises when an appointment has been cancelled and a new appointment is to be set in that vacated timeslot. The listbox for the initial beginning timeslot although blank, still has the original AppointmentID (hidden)
Hope that makes sense.
Cheers Phil

 

[philb42]

I gather by the time that has passed that the problem is not a simple one to solve!

 

[MarkK]

It doesn't make sense to me to chop up time like you do in 15 minute chunks. I get that it may make displaying that item somewhat simpler, but it makes dealing with a block of time as a single thing very very difficult.
Time is a scalar quantity just like money or weight or length or whatever. Clearly if you were writing an accounting system you would not split up dollar amounts into discrete chunks of 25c and then store each one as a child of the original amount, and then sum those quarters to find out how much the deposit was. All you store is the amount, whatever it is.
That said, what I would do is store a booking as having a start time and a duration. You can round the start time and the duration to the nearest 15 minutes if you want.
I would not have a TimesTbl. You do not, in an accounting system, have a DollarsTbl that references the various allowable values. You simply store the value. You can round that value, like if I type $123.45678 into an accouting package it'll probably round it to $123.4568 and so if someone wants to book an appointment at 12:10pm you can round that to 12:15pm, but I wouldn't store that value somewhere as a threshold. Have a min value and a max value, maybe referencing system values like the time the store opens and closes on the day of the appointment, but the main point is that to work with time efficiently, treat it just like you're working with some other kind of number. Compare it to a min or a max, and round it.
Then if you need to display it in some kind of graph, you can choose whatever size chunks you want to display it in at that time. Like if I have an appointment that starts at 12:15p and has a duration of 1 hour (or that ends at 1:15p) I can easily divide that by 15 minute chunks and determine that if my graph shows 15 minute intervals I need to display 4 of them for this given appintment.
Hope this helps,
Mark

 

[philb42]

Thanks for your suggestions Mark. I have managed to find a solution to my original problem but I take what you say on board. In this case it is too far down the track to redesign and everything is working as it should, allbeit unwieldy to the experts!
Phil