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

[Desmond Shek]

Hi,

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

Thanks

[nico5038]

You can use the OnCurrent event of the subform to change the value of a field on the masterform like:

Parent.LPI = Me.LPI
 

I do however wonder what you want to do when a customer has two properties...

Nic;o)

[Desmond Shek]

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

[nico5038]

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:
http://www.geocities.com/nico5038/xR...nstruction.zip

Getting the idea ?

Nic;o)

[Desmond Shek]

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..!

DS

[nico5038]

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:
tblCustomer
tblProperty
tblRelationCust Prop
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:

Me.CustomerID = Me.subformname.form.CustomerID
 

Nic;o)