Update a field based on selection in other field

[Tigereyes78]

Good evening,

Its been a while since i did an access database so am a bit rusty! Can someone please advise?

I have two tables, Bookings and Properties, both are related using a Property Ref field.

On my data entry form bookings there is a field for security deposit amount. I want this to be automatically populated from the properties table based on the property ref entered in another field on the bookings form.

How do i do it?

Hope that makes sense
Thanks

 

[RuralGuy]

Create a query that joins the two tables on their common field. You can then treat all of the fields in both tables as if they were in the same table. That query should be the RecordSource of your form.

 

[Tigereyes78]

thank you but i want the security deposit field to populate only when the property ref is entered on the entry form. I was thinking a lookup function on the after update property of the property ref? but not sure how to achieve it - cant remember the code :/

 

[RuralGuy]

Normalization rules say the data should exist in only one field in the RDBMS. You should not have a security deposit field in both tables. Do you?

 

[pbaldy]

Another option:

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

I could make a case for saving the deposit amount in both tables, since it will probably change over time. Similar to saving a product price in both a products table and with the sales detail. Said differently, the deposit amount in the properties table is the current default deposit, the amount in the bookings table is the amount applicable for that booking.

It also allows for it to be edited, as perhaps the normal deposit is $500, but this customer is the owners son, so it's only $5 for him.

 

[Tigereyes78]

pbaldy you are a star got it sorted thank you

 

[pbaldy]

Happy to help!