I'm making a booking system and I've got most of it ironed out now except for one last thing. My start and end dates for the booking need to be made so when I click a button, say 'Check', it'll show me all the conflicting present bookings.
Not sure the best way to go about this? I was thinking VBA might be easier as opposed to a query?
I'm making a booking system and I've got most of it ironed out now except for one last thing. My start and end dates for the booking need to be made so when I click a button, say 'Check', it'll show me all the conflicting present bookings.
Not sure the best way to go about this? I was thinking VBA might be easier as opposed to a query?
At the moment though, it's also including the date I've just as booked as a 'clash' in the subform when I go to check. is there anyway to remove this? Either way, I'm also trying to use an IF statement to either 'save' the booking or not save depending on the clash result. Any other suggestions?
At the moment though, it's also including the date I've just as booked as a 'clash' in the subform when I go to check. is there anyway to remove this? Either way, I'm also trying to use an IF statement to either 'save' the booking or not save depending on the clash result. Any other suggestions?
You can include an additional qualifier for the query like dates between AND [booking number] <> current booking number. That way it won't include the current booking. You will have to work out the specific code because I don't know what your db fields are. But I presume you have some unique number or field associated with each booking (like booking number) that you could use.
You can include an additional qualifier for the query like dates between AND [booking number] <> current booking number. That way it won't include the current booking. You will have to work out the specific code because I don't know what your db fields are. But I presume you have some unique number or field associated with each booking (like booking number) that you could use.
I haven't actually got a unique field, except for customer ID which won't work I'm guessing. I didn't think I would need a booking ID but now I'm beginning to wonder...
I haven't actually got a unique field, except for customer ID which won't work I'm guessing. I didn't think I would need a booking ID but now I'm beginning to wonder...
Well, someone else might have a better idea, but I would think in order to exclude the current booking you would have to have a way to discriminate it from anything else. Otherwise I don't think a query would be able to be written in such a way as to exclude it.
You could add a visible but not editable field that automatically indexes (+1) for each booking and use that as the additional qualifier.
The query could then look at the form and exclude the current booking number with something like:
WHERE (BookingDate Between forms!yourform!StartDate AND Forms!yourform!EndDate) AND (Bookingnumber <> forms!yourform!BookingNumber)
Actually, you could probably use the Primary Key (which you should already have) using the same code. Just substitute the primary key control box name in the form for booking number.
I just did this using a search form that I have and it worked. It gives me all the records in the search except for the current PK record.
Well, someone else might have a better idea, but I would think in order to exclude the current booking you would have to have a way to discriminate it from anything else. Otherwise I don't think a query would be able to be written in such a way as to exclude it.
You could add a visible but not editable field that automatically indexes (+1) for each booking and use that as the additional qualifier.
The query could then look at the form and exclude the current booking number with something like:
Actually, you could probably use the Primary Key (which you should already have) using the same code. Just substitute the primary key control box name in the form for booking number.
I just did this using a search form that I have and it worked. It gives me all the records in the search except for the current PK record.
About having a textbox that automatically indexes 1 with every booking, that seems a good idea. This way it would stay unique. How should I implement it?
Sorry for all these questions. I'm pretty bad at Access. Do you know much about VB and if it would be any easier?
About having a textbox that automatically indexes 1 with every booking, that seems a good idea. This way it would stay unique. How should I implement it?
Sorry for all these questions. I'm pretty bad at Access. Do you know much about VB and if it would be any easier?
If you don't have a PK then you need to add one. You can call it BookingNumber. Make it autonumber and designate it as a primary key. Once you save the changes, the table will update and assign a record number to each record. After that, the query will work.
As far as VBA, there is little or no difference in doing it in code using VBA or as an external query. I prefer the external query because it is much easier to edit, and it can be used in other parts of the program much more easily. If you are still having problems - upload a small version of the db and we will work on it together.
Ok, I set up a BookingID and added to the SQL I already have on my query. It appears to be working except it's asking for me to enter the bookingID number instead of automatically picking it up from the field next to it on the form itself. Not sure what I'm doing wrong but I bet it's quite obvious. Looking like this so far, right under the statement so far:
And [bookingID]<>currentbookingID;
Ok, I set up a BookingID and added to the SQL I already have on my query. It appears to be working except it's asking for me to enter the bookingID number instead of automatically picking it up from the field next to it on the form itself. Not sure what I'm doing wrong but I bet it's quite obvious. Looking like this so far, right under the statement so far:
And [bookingID]<>currentbookingID;
You need to add a textbox to your form (visible or not) it's up to you - that has its control source set to the bookingID. Then change the SQL to:
Code:
AND ([BookingID] <> Forms!yourform!txtBookingID)
txtbookingID will be the name you gave the control that holds the value for the field BookingID on the form.
When you run the query, it will check for the BookingID in the form textbox and exclude it from the results. Since it is the PK, it will automatically number itself once you start to put data into a new record on the form - so it will always be the current record.
I've uploaded a small sample to show you how I did that on my example database. Go to SEARCH, then click on OPEN QUERY. You'll notice that it will exclude the Current ID number from the results. Scroll to a new record and run the query again, and THAT record will be excluded.
Go to the IDSquery and look at it in design view. Notice how I coded the last little bit starting at AND just before ORDER BY.
You need to add a textbox to your form (visible or not) it's up to you - that has its control source set to the bookingID. Then change the SQL to:
Code:
AND ([BookingID] <> Forms!yourform!txtBookingID)
txtbookingID will be the name you gave the control that holds the value for the field BookingID on the form.
When you run the query, it will check for the BookingID in the form textbox and exclude it from the results. Since it is the PK, it will automatically number itself once you start to put data into a new record on the form - so it will always be the current record.
I've uploaded a small sample to show you how I did that on my example database. Go to SEARCH, then click on OPEN QUERY. You'll notice that it will exclude the Current ID number from the results. Scroll to a new record and run the query again, and THAT record will be excluded.
Go to the IDSquery and look at it in design view. Notice how I coded the last little bit starting at AND just before ORDER BY.
