Access 2007 - Autocheck (Yes/No) field by dropdown

[Blindguard]

Hey!

I have two tables setup in the database, one for customers and one for our cars. In the database it's possible to both click a checkbox (Yes/No) if it's rented, blank if not, I also have a dropdown menu created by Design view -> lookup wizard which is pulling data from the Customer database. This is added so it's easy to see who is renting what car, however I want the checkbox to automatically check itself if an option in the dropdown menu have been created, how do I do this?

In the end, I were thinking of adding a form where you can pull data from the customer database and the car database when a car is about to be rented. I.e. open the form, use dropdown menu to select which customer, renting which car, between which dates, what it will cost and such. This will obviously have to change the data within the car database to both check the checkmark and change the dropdown menu to the current customer.

Any help? I haven't used any code whatsoever, I tried a few of them by changing the raw source, but it just turns the dropdown menu blank.

[Minty]

I don't think your current tables can accurately model you business. You actually need a third table to make this work as you want.

I think you need a RentalDetails table that stores the CarID, and the customer ID , along with the rental in and out dates / rates etc.

You can then query this by date to show Car availability. A checkmark per car record as you have discovered needs constant updating, and leaves the possibility of errors creeping in.
By recording separately the Rental Activity you can avoid this, and also then have access to a history of rentals per car and/or customer, and payment historys etc.

[Blindguard]

Hmm, alright, I'm actually very determined to stick to two tables at the moment to keep it as small as possible. Would it be possible to create a form which:
First entry: Combo box, pulls data from Car table: Car manufacturer, model, color & chassi shown in the combo box by a dropdown menu.
Second entry: Combo box, pulls data from Customer table: First & Second name (Same column).
By doing this, would it be possible to store the name in the car table? If so, how? Cause it feels really damn simply, I just need to link the name in the form to the "rented by" column located in the Car table.

[Minty]

By doing what you suggest you won't be able to forward book a rental for a specific date(s), unless you plan on having a separate diary, which rather make the whole process a bit pointless?

You can do what you are suggesting, but it has no flexibility and is't actually storing any information for you. It's literally storing who has a car right now.
Not who had it, for how long or who might have pre-booked it and for how long in the future.

Take a step back and look at what you really want to achieve with this system you are putting together.
With one more table you can issue a customer with a unique Booking reference, record the invoice number, when they paid, how they paid, the additional "stuff" list is endless. Start and end Mileage , tyre wear, what they had for breakfast when it was returned !

[Blindguard]

That's true, might add another table later in the future, would it be possible to force check a checkbox whenever the dropdown menu have been filled then?

Say I press the dropdown menu, select a customer, by selecting this customer, the box also checks, but if the dropdown menu is blank, the box will remain unchecked? That feels like it should be really easy to add.

[Minty]

You'll need to use the after update event of the combo box. Something like (Assuming your combo is called cmbCustomer and your check box is called chkRentedOut)

Me.chkRentedOut = Len(Me.cmboCustomer & "") > 0

[Blindguard]

You'll need to use the after update event of the combo box. Something like (Assuming your combo is called cmbCustomer and your check box is called chkRentedOut)

Me.chkRentedOut = Len(Me.cmboCustomer & "") > 0

This is what it currently looks like, I assume the checkbox is called "Uthyrd?" and the dropdown box is called "Hyres av"? I created the checkbox by using the "Yes/No" string, and the dropdown by using lookup wizard.
View: https://i.imgur.com/FhRBV5Y.png
DesignView: https://i.imgur.com/8e5m6KT.png

I am very new to MS Access, I used MySQL to run a private server for a game for two years, started MS access yesterday so it's.. all brand new, I've chosen "After update", unsure what to click in the macro window, just paste the string (And edit names) after the + sign?

[pbaldy]

I've chosen "After update", unsure what to click in the macro window, just paste the string (And edit names) after the + sign?

What you've been given is VBA code, which goes here:

http://www.baldyweb.com/FirstVBA.htm

[Minty]

Okay - You are entering / changing data directly in a Table. This is a no no and means you have no control over events and data being deleted. Access is NOT a spreadsheet.

You have also therefore created a lookup field on your table - whilst this is how Access thinks it's making life easy for you, it's not in the long run, it's going to make your life much harder!
Remove the look, up we just want to store the CustomerID , and we will make the lookup manually, see here for some reason for this - http://access.mvps.org/access/lookupfields.htm

Before you get any further remove the special characters from your field names. (? / # spaces, etc) It makes all coding in future easier and less error prone.
Change your ID field name to CarID - when you have ten tables and every ID field is called ID you will get confused, and if you don't people trying to help you will !

Next create a form based on your table.

Then we can start on your coding.

[Blindguard]

Alright, I deleted the lookup wizard, changed IDs, removed all special characters including Å, Ä, Ö. Created a table from the Car sheet.
https://i.imgur.com/B72ROV3.png

And ye, my goal is to block anyone from making entries to the actual tables, and instead having it all controlled by forms. Which means I'd probably want to limit entries into the actual tables somehow as well.

Just wanna thank you for your help, been having very little luck googling issues regarding MS Access, and if I do find a solution, it's usually 2010 edition being shown.

[Minty]

That was quick!

You can lock down access to the navigation panel and create a menu form to access all the things you want to give end users access to.

I would seriously take a step back and work out just what you want to achieve. You can then build the tables correctly now rather than bodging them around later.
You don't need to have all the functionality working immediately, but if you store the data badly now (poor or inadequate table design and planning), it will be 20 times more effort to fix it later, or you'll end up starting from scratch again.

Plan Big , but implement it in small chunks.

Data first, what do you need to store to get your desired outputs. That's your Tables. If you don't tell access something, it can't use it later.
Think about your reports / what users / customers will want to see and what they will be able to tell you.
Business logic - what are the Business rules ?
Then you can start building forms and code to make that all happen.

[Blindguard]

Alright, I'll try to process this information, had to leave to buy groceries, and it's soon time to hit the hay. I will try to give a detailed an in depth explanation of what I'm trying to achieve as an end product, I'll write it before I hit the bed so within the hour.

Alright;
I want to have a database with two tables, one representing ALL the cars, within this database I need to represent:
*Their ID (listing number)
*Their manufacturer
*Their model
*Year of release
*Manual/Automatic
*Mileage
*Colour
*Chassi (Hatchback, sedan, SUV etc)
*Rental price/day (Will be simply displayed as "Price" for now on)
*Currently being rented or not? (Currently displayed as "Rented")
*Rented by who? (Displayed as "RentedBy")

The second table will contain all the customers, information provided will be:
*CustomerID
*Name (Including surname)
*Zipcode
*Phonenumber


I want to have queries to filter the car table. These queries will include (And currently do)
*Specific cars with different mileage
*By color
*Age
*Currently available for rental or not
I have had no trouble with any queries so far, they all work the way I want them to, so I guess those could be left alone.

There will be no queries for the customer table, that table is simply there to store all the current customers, their data will never be touched unless they unsubscribe (I guess I can call it?).
The customer table is only there to import data into the car table, more specifically under the "Currently rented by" tab, displayed as "RentedBy".

I have 4 forms at the moment, 3 of them being relevant.
The first two are made for registering a new customer, it's simply pulled from the customer table, and all the new information you add to it, will be added to the customer table, there is no point in ever opening the customer table, hence why it could and should be blocked from manual entry.
The second form is to add a new car, same goes for this one, it's a copy pulled from car table, and the information added to it, will add information to the car table.

The third form is a bit different, it's there for when you want to rent something, it's there to mimic being on a webpage, when in fact it will stay in the database and I will do all the entries.
The customer (Me in this case), can currently enter their name, which car they want to rent.

In the third form I'm thinking of adding a "between dates" box, I did it previously but I was unsure where to store the information, and clueless as to how I can get the dates to transfer into the car table. So instead I thought of showing the dates within a query only, I have several queries for currently unavailable cars, it would be great if the currently unavailable cars would show who rented what, and between what dates. Currently it can display who is renting what (If I add their name and check the checkbox in the car table).

And this is where the problem erupts, when a customer enter their name (combo box - drop down menu, select from the customer table), they select the car in the second dropdown menu (also a combobox, pulling info from the car table). After all fields have been completed I wish there were some sort of "enter" button, which would port their name to the car table, under the correct car. Currently it is possible to enter the information in the field, but it's not possible to port the data to any table, its simply a dead form, I need to link them somehow, if it was possible to fill out the form, press "enter", have the data sent to the car table and the form cleaned for the next entry that'd help massively.

To make it even more complicated, it would be awesome if I could choose my name in this form, then go in this order:
*Manufacturer (Audi, BMW, Volvo etc)
*Model (A6, XC60, V70 etc)
*Year of release
etc, same as the car table, and it would filter the results matching their searches, to give a concrete example, I have 3 Volvos, V70s, two of them are white (One manual, one automatic) and one gray manual.
If they select "Volvo" as the manufacturer, all the volvos will be available in the next dropdown, once they select V70 as model, only those 3 cars will be shown -> If they choose manual, both the gray and white one will be shown in the next menu -> If they choose gray, only the gray, manual V70 will be shown.

I hope I expressed myself, but this is the only information that I will have in the database, their payments will not be shown here, no transactions, no further information. I am doing this at the place I currently work at, and he told me that I need to keep within the limits during this task, I'm being send on a database course next summer, but until then he'd greatly appreciate if I could practice some on my own.

I hope the wall of text doesn't deter you, and it's probably littered with spelling errors!
Thanks again for the massive help.

Oh ye, I might add, I've set ALL my queries to snapshot so they cannot be altered, I figured that one out as it's pretty much the same as MySQL, so the queries are all done for, we can skip them entirely, I know how to filter searches. However, would it be possible for a third part (My boss), to open ONE query, and select what he wants to search for in a drop down menu, which will in turn run that exact query? Say he wants to find all cars... with a mileage over 4000, could he then enter this value in a field and get the filter for those cars?
Cause at the moment my queries are all pre-defined by me and limited as such, if a third part could open a query, form or whatever is required, it should preferably be a copy of the car table to match results in a nice manner. Within this query/form, whatever, he could enter the information HE is interested in, and get a list based on his interests? This would be great if you had a huge table filled with tons of cars, but you're looking for that special car with the exact mileage, in a specific colour, while being automatic. I hope I get the point across!

[Minty]

Okay. Read and digested.

Your car / model / type information drop downs can can be created using queries and cascading combo's. See here http://www.baldyweb.com/CascadingCombo.htm
You would create a grouped query on your car table to pull all the brands, then the same for models etc.

Have you considered how you will keep the mileages up to date ?.
If you kept track of the rental start and end rental mileages you could easily display the last returned mileage and know it was current. Do you see where this leads...

You need a third table for rental details.
I wouldn't consider doing this any other way, and if you are going on a course run by anyone with any real world experience they would hopefully agree.
It will make the entire operation and design much cleaner and easier to use. There are many "holes" in your current design that will trip you up.

A fundamental aspect of any good database design is that you shouldn't ever overwrite data, you should store it as a new event record.
Even a correction to something should really be recorded as exactly that; a correction record.
And you shouldn't store things that you can calculate.
So, as an example, your car tables Mileage field should actually be the PurchasedMileage and you can then see how many miles it is doing over a period of time.

It will make your life easier and your bosses. He isn't a database design man, he's asking you to do that bit.
So accept some advice from someone with 20 + years experience in the automotive world and a similar period fiddling with databases and do it properly. Please.

[Blindguard]

Okay, I'll get to it, I'll add a third table and work from there, cascading combos sounds interesting. And no, I haven't considered how to keep the mileage up to date really, I would probably have gone with a return form where you choose your name, the car(s) you are currently renting will be in the dropdown menu, and you'd have to type in the new mileage, this would overwrite the previous one (Which I won't do now!).

[Minty]

One day you'll thank me for being a pedant


Honest...