By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Populate FK field of one table based on search results from continuous form

P: 3

I am new to this DB caper and am really struggling.

I have a form called customers with a subform called property search. The subform (a continuous form) allows users to search the table 'property' using multiple criteria (postcode, house num etc) and returns a set of matching results.

I want to be able automatically populate the a field called LPI (a unquie property ref which is the PK of table property and FK of table customers) on the customers form based on selecting a property address from the search results on the subform. Either by dbl click or a command button.

Can anyone help

Nov 1 '07 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,072
You can use the OnCurrent event of the subform to change the value of a field on the masterform like:
Expand|Select|Wrap|Line Numbers
  1. Parent.LPI = Me.LPI
I do however wonder what you want to do when a customer has two properties...

Nov 3 '07 #2

P: 3
Thanks Nico,

I can see how this would work in a 'normal case' but its not quite what I'm after. The problem is that my forms are unbound - no parent/child relationship -because if I establish that then the search funtion in the subform does not work (don't know why). Sorry, I have probably gone about this the wrong way.

To recap what I am trying to do...

The subform is a continuous form linked to a table called "property" and has the facility to search on either postcode, housenumber of a combination. For e.g. if I put 8 into the house number field and search I get 3 records returned in the detail section matching this criteria. On selection of one of these records, by clicking the record select area, I want to populate the field LPI in the main 'Customers' form and update the underlying field LPI in the "Customers" table

I am not sure if it is now possible the way I have set it up..?

Desmond Shek
Nov 5 '07 #3

Expert 2.5K+
P: 3,072
Hmm, still no answer to the question or a Customer can be related to multiple properties.
Let's assume that that's a requirement. Then you can imagine that the Customer -> Property relation is like an Order -> OrderDetails relation.
For an order you'll allow the user to add orderdetails in a subform, so your user could have a subform to add a property (or properties).
Additionally you have a requirement to select the properties in a flexible manner.
This can be achieved by offering a separate "Property Select" form. This can show the properties in a datasheet (sub) form and after selection pass the Property ID to the "PropertyDetail" of the customer window.

For the selectio you can instruct the user to use the right-click popup menu as described in:

Getting the idea ?

Nov 5 '07 #4

P: 3
Hi, I am starting to get this - sorry for being slow. It appears from what you say that I have to have my property search form as a seperate form (possibly with a button on the customers form to open it) and then pass the Property ID (and other details) to the Customer form. However the key thing I still don't understand is how I pass the Property ID from one form to another. Property ID is a key field that links many of my tables and I will have to do this on other forms I am building

To answer your question, yes there is a slim chance that a customer has more than one property but in terms of a property's history it can also have more than one customer. So it is potentially a many to many relationship. I guess I will have to split customers into 2 tables,

Tbl Customers - Name, contact details etc - ONE - CUST_ID PK
Tbl CustPropert - Details of property - MANY - CUST_ID FK
- PROPERTY ID FK (links with tbl Properties)

I should mention the Property DB is something I recieve from seperate organisation and is list of all properties in a Local Authority area each with a unqiue Property ID. For our work a customers property must be found in this property DB or we will not deal with it.

Confused? I think I am..!

Nov 6 '07 #5

Expert 2.5K+
P: 3,072
Hmm, still no answer or one customer can have two (or more) properties or a property two or more owners. When that's the case, then three tables are needed:
The last one is know as a relation table and will hold the ID's of both tables.
This will allow a property to have multiple owners or an owner to have multiple properties.

Assuming a property has to be linked to one owner, you can best use a Property mainform and a Customer subform.
By filtering the Properties without a customer you can get your "To do" list.
By selecting the proper customer from the subform you can use a button [Link Customer] to fill the value of the current subform into the mainform's CustomerID like:
Expand|Select|Wrap|Line Numbers
  1. Me.CustomerID = Me.subformname.form.CustomerID
Nov 6 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.