Form control default values from 'many-to-many' form (1 Viewer)

Hello there,

I'm quite busy laying out my table and relation structure for my project.
But now I got something I don't know how to do, and i guess you guys can help me.

It's a program designed for a community centre with multiple rooms a customer can rent.
It will be possible for the administrator to add a new customer and put in preferences from the customer. Preferences consist of things like the price for coffee which was agreed but also the rooms they like to have whenever they come the the community centre. They can give up preferences for multiple rooms (and they can also use multiple rooms at once during their appointment)

For the preferences I got this table setup: See attachments 'tableDesign1.jpg'

So far so good, i guess (didnt try on a form to add but as far as I know this is the good table setup)

Now, the problem, when an appointment is made, the administrator who adds an appointment gets to see which preferences are made. He gets to see that in a form, and he has the possibility to change one or more of the preferences for just that appointment. The changed values gets stored in the table.

But now I want the following. If you open the form, you have a control for the coffeePrice. It will be a textbox. The default value from the textbox will be taken from the field in the Preference table. Is that possible or is the control's default value property only for static values?
The administrator will be able to change that value and when the form is saved the value will be stored in the right field.

And the next one, a little bit harder, I want to create the same for the room preferences. But now it's not always just 1 value, since it could be multiple values and they are stored in the many-to-many table.
Is this even possible to create?


Long story, but I almost got a mental breakdown thinking of a solution.
Would be really nice if someone can help me with this.

If any more information or pictures are needed I would really like to hear it so I can clarify it.

Thanks in advance

Hey.

I don't really get 'coffeeprice'.
Where is the 'coffee' table? How does price relate to types of coffee?
At the risk of sounding flippant, wouldn't it always be 'free'?

I'd just have one preferences table, one to many.
Add a DateAdded field with default = date()

For coffee you can get the last preference added (with a built up history of preferences - or just flush 'em if that's not your bag.)
Rooms, you would just get the entire list.

edit++

So, if you have a coffee table, types of coffee have ID's. You are storing room names, but you should be storing RoomID's.

So, your preferences table could be

PrefernceID (autonumber)
PreferenceType (text [room/coffee])
Preference (long [roomID/CoffeeID])
PricePref (currency [preffered cost of room?/coffee])
DateAdded (date)
CustomerID (Long) (thanks Mark missed that one ¬_¬)

Just as a FYI, CustomerID in Customer Preferences table wouldn't be AutoNumber since it is being filled in from the Customer file.

Mark_ said:

Just as a FYI, CustomerID in Customer Preferences table wouldn't be AutoNumber since it is being filled in from the Customer file.

Click to expand...

Thanks for this. Changed it. And as I got to think about it, you are totally right ofcourse.

static said:

Hey.

I don't really get 'coffeeprice'.
Where is the 'coffee' table? How does price relate to types of coffee?
At the risk of sounding flippant, wouldn't it always be 'free'?

Click to expand...

Thanks for the reply, let me clarify some things.
Coffee aint free. Of I saw ‘community centre’ I also would think it would be free. But it’s not really a community centre, it’s just the only english word I could find to clarify a little bit what the centre is about, I made the database in dutch, so to make it a bit easier for you guys to understand I changed a small part of it to english just for this problem.

There is also just one type of coffee, no fancy expresso machine, just old fashioned coffee.
The preferences I showed in the image is just a small part, there are more.
Customers are groups/clubs, multiple people. In advance they agree upon a fixed price for different things. That could be a fixed rent for the ‘rooms’ they rent, a fixed price for coffee/thea, a fixed price for drinks etcetera.

Those things are stored in the preference table (also the reason why I made a one-to-one connection by sitting the customers table. The costomers table itself is containing more fields also, but it’s all irrelevant for this question right now.
You can also call the ‘preferences’ something like ‘things we have agreed on’ or something. The name preferences just makes it easier for me and my customer (which is anfsmily member, all for free except the coffee!) tomunderstand what we mean with it.

The ‘preferences’ for rent, coffee, thea and that kind of stuff all acts like the ‘Coffee’ i mentioned above in the starting post. And the ‘rooms’ have the same too, only difference is that the ‘rooms’ preference is stored with multiple values.

If I’m back from work today I can make some
Sort of ‘graphic’ to clarify the things a little bit more, it’s hard explaining something like this.