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

[Blindguard]

I most likely will, I appreciate your honesty, shortcuts are never the way to go imo.

[Blindguard]

Alright, a huge dissappointment... My boss told me that I am not allowed to use more than two tables, don't ask me why, that's his decision... So... He really wants the two basic tables filled with all the required information, I have no idea what his intentions are with it.

That puts me back a step as I've already created the third table (RentalDetails), where I stored all relevant information regarding the currently rented cars.

Guess I need to solve this somehow, would it at least be possible to make a customized query with just two tables?
All I have left to do in this case, as he refuse to let me use three tables:
*Is to have a custom query where the end user and search for specific details
*Have the checkboxes to autocheck
*Have the "RentalForm" port the "Rented", "RentedBy" and their "Name" to the CarTable.. <-- Probably the most important one, if that one works, it means I have to fill out the form containing their name, and the select car, which would in return check the box next to the rented car (In the CarTable), add the name provided in the dropdown menu within the form to the column next to it. But I can't use lookup wizard for that.

[Minty]

Hide the third table and don't tell him about it. (I'm being serious).
If you program it correctly he'd never know how you were getting at the data.

Try it this way - Does he want you to bodge around for the next three weeks making a pigs ear system, or spend 10 days doing it the easy proper way.

I'd genuinely ask why he thinks two tables works. He wouldn't expect to buy a car with just 2 wheels instead of 4 ?
PM me his phone number and I'll explain

Back to your bodge it method - It will involve the use of an After update event on the combo boxes, and possibly an separate Update query.

[Blindguard]

Hide the third table and don't tell him about it. (I'm being serious).
If you program it correctly he'd never know how you were getting at the data.

Try it this way - Does he want you to bodge around for the next three weeks making a pigs ear system, or spend 10 days doing it the easy proper way.

I'd genuinely ask why he thinks two tables works. He wouldn't expect to buy a car with just 2 wheels instead of 4 ?
PM me his phone number and I'll explain

Back to your bodge it method - It will involve the use of an After update event on the combo boxes, and possibly an separate Update query.

Haha, I have already hidden all the tables to prevent anyone else from easily accessing it, if I could block anyone from editing the tables without a password thatd be great, but doesn't seem like access has such a function.

What would such a code look like?

[Minty]

If you compile the database to an accdr it becomes a lot harder to get to things.
Have a search on here or google for securing an access database.
There isn't an completely secure access database, but you could certainly make it very difficult to get to the data.

Search on here for Ridders sample security challenges.

Is this planned on being used by multiple users within the organisation? You probably need to look at splitting the database to a Front and Back End (FE and BE) system.

[Blindguard]

No, it's only being used to me, and my boss will have a look at the end product, after that, it will be scrapped.

Alright, here's my plan:
I need to show the tables somehow, I will do this by using a report, which will mimic the actual table but it will be void from edits. To actually edit a table you need to fill out one of the forms.
I removed the lookup from the car table (as stated earlier) and instead replaced it with a text field. In the form, I need to somehow link the Name combobox, to the car combobox, and force it to insert their name within the cartable, which will then be viewed by opening the car report.

I'm thinking this would easily be solvable if I had the cars ID hidden in the dropdown menu of the form when choosing your car, specify the button in the form to port the data from the name field, to the "rentedby" field in the car table, but I have no damn idea how to do this :P I'm lost. Are relationships the solution, if so, they only seem bound to query and tables, not forms.

In the forms table for the cars and customers, I can see all the current customers and cars in the forms, would it be possible to just have one blank page and use it to add them? I've figured out the button thing, and each time I fill out the table and click "Add", it gets added as a new ID, last in the table so it works perfect. But the fact that you see all the current customers from the car/customer table pisses me off.

[pbaldy]

Is this a school project or something? My bosses have never tried to dictate the "how". They know they don't know anything about database design. They tell me what they have and what they need out of it, I figure out how to get it done. They wouldn't have the vaguest idea how many tables were in an app. If he's in the US I'll call him and save Minty the international phone call rates.

[Blindguard]

Haha nah, I'm studying biotech at the moment, but got a part time job as IT, so basically fixing stuff not working, i.e. restarting computers all day long.
Our last database operator is leaving next year, and he asked me to take the same course he did earlier (Which will be held the upcoming year). In preparation for this, he gave me simple instructions for a simple database that I should do, within limitations, probably so I cater to his needs.

I've shown him the current database, and so far I only have one more thing to do, this form thing, then its exactly how he wants it.

I added a third table and hid it well, he did not find it! Anyways, I'm using VBA code to add data from a form to the newly made table, and after some fiddling it works.


Now I just need to fix the autocheck box thingie and to link the "HyrDetaljer" (RentalDetails) name to the car table so I can show it up in reports, apparently putting a relationship between the two tables doesn't work. It's just blank. If I can get the rented by to showup in both rentaldetails (as it does atm) and in the cartable file, I can form a report which will show all the current cars, their current status, with mileage, who is renting it and such. This is the only thing I have left to do, form some kind of relationship between the two tables, so the correct name (customer) is shown next to the car they are currently renting.

[Blindguard]

Alright, let's jump to this then.

I only want the checkbox to be checked if the field next to it is filled, otherwise it should remain unchecked. Is this possible?

[pbaldy]

You can adapt the second method here:

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

but why have the checkbox if the other field being filled gives you the same info?

[Blindguard]

You can adapt the second method here:

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

but why have the checkbox if the other field being filled gives you the same info?

No idea, his instructions are as:
"We want to connect a rented car, to the customer who is currently renting it, and we also want a checkbox (Yes/No) next to it."

[Minty]

I think the words Database Operator might be relevant here , unless that is a translation hiccup.
His instructions don't seem to show any understanding of a data model, or how you should implement business rule logic.

Anyway - back to your immediate problem, you need one bit of code one in the after update of the customer drop down;

Me.ChkRentedOut = Len(Me.YourCustomerCombo & "")

This should work as if your combo is Null or ZLS (zero length string) it will = 0 which is false, and anything else will evaluate to 1 or more which Access will interpret as true.

As Paul stated you could simply have a calculated field for this for display purposes and not bother storing it, which would be safer, as you wouldn't need to capture the update of the customer at every possible point in your application.

[Blindguard]

Hmm alright, so if I open my car table in design view, mark the dropdown menu, click create data macros -> after update -> type in the code in the row and save it, it seems to reset everytime I close the tab? Am I missing something?

[pbaldy]

It goes here:

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

[Minty]

I wouldn't (hadn't thought to in fairness) do it at a table level.
Although it may seem to make sense, most of use avoid data macro's as they are quite limited, and don't upsize to other DMBS well.

You should do this in your update form.

I've never used a Data macro's so wouldn't know where to start, but would have thought you need to refer to the field names directly, not any form controls.
The table won't know what they are.