I want to build a room scheduler. Before I get too far in to my first attempts, does anyone know if there any examples out there I can use to give me ideas?
If not I wonder can anyone advise me as to a difficulty I currently can see with what my attempts so far. I have built a very basic database with fields for each room and time slots through each day. This works ok. The problem I see is finding a way of setting a room booked for say "every Thursday between 14.00 and 15:00". I have considered using a query to do it but cannot see how to formulate it.
I am in the process of creating a Room Bookings database using the limited graphical tools of Access 2003. I intend to break up the day from 0700 to 1900 into 48 X 15 minute chunks (the minimum bookable time) and have these graphically represented on the form as boxes whose colour change depending on whether it is booked and who by. It would be normal to have up to 8 colours available for this as any one room would be unlikely to be booked by more than 8 people in one day. I would envisage that each day would be covered by a separate subform.
Has anyone else tried a graphical representation of a booking form?
It would be interesting to hear others views or experiences...
Can anyone point me in the direction of a room scheduling example or a tutor site. I originally wanted to use Outlook but my boss, after seeing another Acces Project I did, asked for it in the Access. I can build a basic data base on a daily basis showing rooms against hours, but my problem is forward scheduling eg "every Thursday meeting room 1 will be for Group A" etc. Can anyone please get me started - I'm tearing my hair out on this one!
Hi Everyone, hope someone out there can figure this one out.
I am developing an access database and forms based on a hotel booking system. There is a tblRoomState table, which holds the roomID, the date and the state of the room, (so for a 3 night stay, this table holds three entries). I have a query which returns any roomID's from the tblRoomState table which have a state value of 1 at a specified date. I want to run another query which returns roomID's from the tblRoom table which are not part of the recordset of the first query.
Since there is no records held in the database for rooms which are not booked, the only way to access "availability" is to use a recordset of rooms which are booked and test against that.
Hi, I have been reading throguh various articles and learning a lot from you guys, so thank you very much. This place is a fantastic source of information, by far the best of anything i have found online.
I have a question which i would be grateful if someone could advise upon.
Access 2003. I have 4 tables:
list_accommodation (holding host family address details who offer accommodation to students) id (autonum) name (text) address (text) etc.....
list_rooms (table of rooms offered by host families) room_id (autonum) host_id (number) - joined to list_accommodation.id room_name (text) capacity (number)
students (student contact details who want accommodation!) id (autonumber) name (text) dateofbirth (date) address (text_ etc.....
bookings_accommodation (to hold the accomm bookings) booking_id (autonum) room_id (num) - joined to list_rooms.room_id student_id (num) - joined to students.id start_date (date) end_date (date)
This structure therefore enables me to query if rooms are booked between dates:
SELECT bookings_accommodation.room_id, bookings_accommodation.start_date, bookings_accommodation.end_date FROM list_rooms INNER JOIN bookings_accommodation ON list_rooms.room_id = bookings_accommodation.room_id WHERE (((bookings_accommodation.start_date) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d",-1,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR ((([end_date]-1) Between DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date]) And DateAdd("d","0",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date]))) OR (((bookings_accommodation.start_date)<DateAdd("d",0,[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_start_date])) AND (([end_date]-1)>DateAdd("d","-1",[Forms]![workflow]![workflow_sub_add_accom_booking]![cbo_end_date])));
and therefore, using the 'booked' query i can therefore find out rooms are available.
SELECT list_rooms.room_id, list_rooms.room_name, list_rooms.capacity, list_rooms.description, list_rooms.host_id FROM list_rooms LEFT JOIN qry_rooms_reserved ON list_rooms.room_id = qry_rooms_reserved.room_id WHERE (((qry_rooms_reserved.room_id) Is Null));
----
so, now i have found out that there is a necessity to enable students to share a room (the bookings for a room may not happen at the same time). This means that i have to determine the capacity of a room and therefore if the capacity is not full, then show as available.
I was wondering what the best approach to this may be? I have added 'capacity' to list_rooms, but i am unsure as to how to go from there....
Any thoughts and advice woudl be really appreciated.
I have to create a query to calculate the Base Income per room.
Guest 1-4 is the standard room rate say $125/day, guests 5 & 6 pay additional $20 charge per day. Guests staying for seven days or more receive a 10% discount. How to build this expression into a query in access. If it was Excel that would be easier.
Table info I have is: Room Arrival Date Departure Date No of Guest (per room) Daily Rate
So I'm trying to do this database for my ICT coursework and its a full system for dog kennels.
So in actuality the rooms are kennels.
I have a table tblbookings that amongst others has fields:
Kennel No Date In Date Out
I need a way of users entering the requested dates for a new booking and getting an output of a list of all kennels that are available to book for that full date range or even better, a way of running this straight from the form for a new booking frmbookings to just leave the first available kennel no. in the field KennelNo?
I have Table for rooms called Rooms, and the data in the table is roomNumber which is in the format Letter and two Digit number, so A01 would be dorm building A and room building 1, and I then have a seperate row named roomType that is either VIP or Semi Private.
Now I am creating a form where a worker will put in there scheduledCheckin date and scheduledCheckout date and it will be written to the Bookings table. I would like this form however to take the dates they have put in, as well as room type (Semi-Private or VIP, and assign them an available room that isn't booked at all in that range) or list all available rooms for that range of time and they could then just select the room. I would rather it automatically assigns an available room based on room type though because this check in system is going to have about 500 rooms.
I've attached a link for what I have so far. I know how to make a query to list anyone who has booked rooms over that date, but need one for just preventing booking the same room.
I'm currently working with a form, which is in datasheet view. I have many rows which are combo boxes (yes/no), and the name is rather long. So each line (each row) spreads on to 2-3 pages to the right.What I would like to do is make the namebar, on top of every column, a little bit higher, so the name would be split into two lines, or three. Allowing me to make the width allot smaller.Here is an example of my problem:http://213.213.137.96/~terminal/columns.jpgSo my question is, can I change the height of the column name? Or is there some trick I can use?regardsFrímann Kjerúlf
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check - transfer - taxes - cash
Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.
Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount.So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".
What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".Search button, on click code:
If IsNumeric(Me.busq_chq_med) Then Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR Else Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'" Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'" End If Me.FilterOn = True
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
I am using MS Acess2000 and need to make a report that will be passed around with production work. My primary key is the invoice number of the work order. Currently to open an invoice i have a macro, attached to a query with the following qualifiers; Like [Enter invoice]
This pulls up the current record fine. for the form and flags the folloing in the property filter sectin of the form
(((([CustomerTableMasterRef].[Invoice]) Like [Enter invoice])))
PART 2
Now I am trying to use microsofts how to filter a report using a forms filter... This picks up on the Invoice query as shown above but does not just insert the query results...
Is there better code or another way to approach this... Currently i am using:
Name:cmdOpenReport Caption: Open Report OnClick: [Event Procedure]
Private Sub CmdOpenReport_Click() If Me.Filter = "" Then MsgBox "Open an Invoice First" Else DoCmd.OpenReport "rptCustomers", acViewPreview, , Me.Filter End If End Sub
Using this code not only does my report not detect the correct fields to import data (no data is filled in) but it requerys the invoice or atleast should, which I could do with out all of that code...
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
Hello Everyone,Been a while since I needed to post on this forum, but I found something rather disturbing today; either that or I have missed something obvious. I have a large query of financial data, and I filter out certain codes which I don't want to include. The filter is;<>"BL01" And <>"SS01"Which works as it should, filtering out all BL01 and SS01. However, it also filters out any NULL values, which is something I would not expect it to do. When I enter NULL as a filter, I get the records I expect. What am I doing wrong here? I am using Access 2003 SP3 on Windows XP Service Pack 2
My continuous form (frmThisDate) is based on a query. The table has, among others, a field called 'PODate' which is a date field.
I like to have a box on the form in which I can put a date. Then the records are being filtered by day and month only. So, when I put in 04/29/2005, I like to get all records for 04/29 regardless of the year.
i have a form with three Option groups and one combo box and i am trying to filter it out to the subform that i have on the same form. here is the code, everytime i try to click on Apply Filter. its not working. my filter is not showing on the subform.
Dim strmarket As String Dim stradvertisers As String Dim strheading As String Dim strudac As String Dim strfilter As String
If IsNull(Me.txtmarket.Value) Then strmarket = "Like '*'" Else Select Case Me.fraMarket.Value Case 1 strmarket = "Like '" & Me.txtmarket.Value & "*'" Case 2 strmarket = "Like '*" & Me.txtmarket.Value & "*'" Case 3 strmarket = "Like '*" & Me.txtmarket.Value & "'" Case 4 strmarket = "= '" & Me.txtmarket.Value & "'" End Select End If
If IsNull(Me.txtAdvertisers.Value) Then stradvertisers = "Like '*'" Else Select Case Me.fraAdvertisers.Value Case 1 stradvertisers = "Like '" & Me.txtAdvertisers.Value & "*'" Case 2 stradvertisers = "Like '*" & Me.txtAdvertisers.Value & "*'" Case 3 stradvertisers = "Like '*" & Me.txtAdvertisers.Value & "'" Case 4 stradvertisers = "= '" & Me.txtAdvertisers.Value & "'" End Select End If
If IsNull(Me.TxtHeading.Value) Then strheading = "Like '*'" Else Select Case Me.FraHeading.Value Case 1 strheading = "like '" & Me.TxtHeading.Value & "*'" Case 2 strheading = "Like '*" & Me.TxtHeading.Value & "*'" Case 3 strheading = "Like '*" & Me.TxtHeading.Value & "'" Case 4 strheading = Me.TxtHeading.Value & "" End Select End If
If IsNull(Me.CboUDAC.Value) Then strudac = "like '*'" Else strudac = "='" & Me.CboUDAC.Value & "'" End If
strfilter = "[market] " & strmarket & " and [advertisers] " & stradvertisers & " and [heading]" & strheading
With SubForm![frmMeterAdSubform] .filter = strfilter .FilterOn = True
I wanted to take it a step further and give the user the option to find records that contain FilterBY I tried the following code but got a type mismatch error
I have a form which runs a piece of code on close.
The code:
Code:Prior to this code I populate with a recordset "rstChkConfirm"strEmail = rstChkConfirm("EmailContact")DoCmd.SendObject acSendReport, "rptConfirm", acFormatXLS, strEmail, , , "Trade Confirms from Kyte Options" & date, , No, False
I want the report "rptConfirm" to filter on open with the string "strEmail" Code:Private Sub Report_Open(Cancel As Integer)'Filter report by the email from the previous recordsetMe.filter = "EmailContact = '" & strEmail & "'"Me.FilterOn = TrueEnd Sub
Now the filter does not work, I presume it's because the strEmail is somehow not recognised by the new VB that has opened in the new report doc. Any ideas how I can make it a global var?
I have a General Info form (Form 1) which gathers the info for a specific project. On that form I have an invoice button that pops up the Invoice Form (Form 2) that gathers all the invoice info for the main file in Form 1. Now, I may have more than one entry in Form 2 for the same record in Form 1. How can I apply a filter that will only show records associated to the record specified in Form 1 in Form 2.
Example:
In Form 1, I am at record A and I press the invoice button. How can I only show the records associated to record A in Form 1 in Form 2.
I created a Form in a database that has text boxes relating back to a table. The form also has subform that shows the records from two other tables related to the First. There is a many to one relation back to the main table. As I scroll through the main table the corosponding records in the Subform show up....Everythign is working great.
What I want to add is a Filter to filter the amount of records down.
I am thinking of a button that opens another form. THis form will have a few dropdowns and text boxes which the users can input values These values will then be used to create the filter. Once the filter is ran I need a List box on the main form to pop up with the returned records Finally when the user selects one of these records in the List box the record will be represented in the Main Form.
Does anyone have any examples of how to do this....
I can create the other form adn add the dropdowns.....I just need help creating the Filter and how to get it to Return the records to a list box in the main form and then connect a selection in the listbox to the main form...
