Design help please (NEWBIE)

[Acorna]

Hi All

I'm new to Access 2007 and only have a little knowledge from 1998? version some years ago. So please be gentle

I've put together a database for entering the bookings for my taxi. (One man band) and would like some advice on adding a few bells and whistles.

1. Some of my bookings are daily/weekly/monthly and I would like to be able to add them all in one go. I am currently putting the booking into Excel and copying into the number of cells needed. I then change the dates using Fill and then import the lot into the DB.

2. On the main report page that shows all upcoming bookings I would like to differentiate between dates using different colours. Monday = Yellow Tuesday = Blue Wednesday = Yellow and so on.

3. I would like to show the DAY as well as the DATE. Example MONDAY 4th Feb 2008. The best I can get is 4th February 2008

4. I would also like to be able to choose an address from a dropdown menu but also be able to enter a new address manually if its not listed. This new address would be added to the list. I've tried using LOOKUP COLUMN but without any success.

I would include a copy of the DB but its full of customers personal data so I cant.

The DB is made up of the following Fields in a Table called Bookings. ID, Time, Date, Name, From, To, Notes, Account (Y/N), Paid (Y/N), Cost

I'm asking a lot I know. I can use the DB as is but I would like to add these items if possible.

Thank you in advance for your help.

Ray

 

[pbaldy]

I actually work for a transportation company and have written 2 taxi reservation/dispatch systems.

1) I handled this with a table of recurring reservations. I only had to worry about daily/weekly, so mine has fields for each day of the week the reservation is required for, as well as the time and other info. Each night a process runs to add the reservations for the next day to the live reservation table.

2) If you search on Conditional Formatting you should find what you need. The built in function will only handle 3 conditions, so it won't really work for you, but the search should also turn up the code you can use.

3) You should be able to use the Format() function to get it all. You might start with "long date", as that includes the day spelled out.

4) Search for notinlist, which is the event typically used to add a new selection to a combo's source.

 

[Acorna]

Just a quick reply, for now.

Thanks for your answers, I will look into them. But, I cannot get the DAY to show using "Long Date". Am I doing something wrong?

Ray

 

[pbaldy]

I think it depends on the long date setting in Regional Settings, but on my PC

Format(FieldName,"Long Date")

produces

Monday, January 28, 2008

If yours is different, you can play with the custom formatting available. You should be able to find more info on them in VBA Help on the Format() function.

 

[Villarreal68]

I just quickly tried it in Access 2007 and i was able to get the full (Monday January 28, 2008) date with out the Format command. i just added a text box (with =Now() to test the date) and in Property/Format selected "long date" and it worked fine.

Just so you know.

 

[Acorna]

Very strange my =Now() shows 28 January 2008 with Property/Format set to "long date"

Ray

 

[pbaldy]

And what is the long date format in Control Panel/Regional Settings?

 

[Acorna]

It was the Regional Settings, I've now fixed that. I'm now working on the formatting. As you said in your reply the Conditional Formatting wont work but I've found that by using Group & Sort tool I can distinguish between the different days bookings. Thanks for that, it's appreciated. Two down, two to go BTW is there a way to put a line between each Date (Group)?

Could you expand on your idea for #1 above I'm not sure how I would go about this. I was wondering if maybe there was a way to enter two dates and have the Form enter the data in the Table every x number of days. I want to be able to see ALL bookings which I would not be able to do using your method of entering the data each day using code.

Thanks again for your help

Ray

 

[Acorna]

I have found the split FORM/TABLE view very useful but would like to use a split FORM/REPORT. Is this possible?

The problem with the FORM/TABLE is that it shows ALL records in the TABLE. I only want it to show records from TODAY onwards. In fact change that to show THIS MOMENT onward.

Any help would be appreciated.

BTW I'm still getting nowhere with the combo box (I know, I'm an idiot )

Thanks
Ray

 

[pbaldy]

I believe the split form is only available as a form, not a report. I haven't used it yet. Can you base it on a query that only returns records from today onward instead of the table? Not sure what you mean by the combo. If you mean your #4 above, did you search on notinlist and apply that code?

 

[Acorna]

pbaldy

Thanks for your reply. Yes I did mean #4 but at the moment I've given up on combo lists as I really cannot get my head around what I'm reading I can do the simple stuff like create tables and run queries to generate reports but anything that involves code I'm at a total loss to understand it.

I've added an empty copy of the DB just incase anyone fancies having a look.

Thanks again
Ray