auto refresh subform on data entry

[definewebsites]

Hi,

This is the situation. I have a form in which I have embedded a subform. The subform pulls its contents from tables based on a query that is tied to one of the fields on the mainform.

This is the issue that I have. When I enter lets say data into one of the fields on the subform(in datasheet view) I would normally expect the rest of the information to appear in the subform since what I entered already corresponds to existing data in one of the tables. However, the contents only update themselves when I lets say browse to the previous record and then comeback to that same record.

Is there anyway I can get the subform to refresh itself the moment I type in some matching data?

Thanks

 

[Pat Hartman]

It depends on how you are doing this. You need to create a query that joins the subform's table with the lookup tables and use that as the recordsource for the subform. Make sure you set the locked property to yes for all the lookup fields because you don't want to accidentally update them on this form. When you type into the FK field or select it from a combo or listbox, the look up fields should immediately populate.

 

[definewebsites]

Re:

Hi, thanks for the response.

Well, the fields on the main form are already populated based on the fact that their data source comes from a table, and the fields on the subform are related to the main form based on a primary key link.

However, if I set the propertys of the subform fields to locked, that means I cannot enter any data into them.

Basically what I wanted to achieve was that when I enter data into the first field, the remaining fields should populate automatically when i press the "tab" key to move to the next field.

The subform is already based on a query.

This did work when I previously had the primary key of the main form as a foreign key in the subforms underlying tables. However due to a certain requirement, I had to create another table with the primary key of the main form and the primary key of the subforms underlying table, thus creating an additional inner join.


Thanks

 

[Pat Hartman]

Are you trying to copy data from the main form record and duplicate it in each subform record? You should not be doing that at all. The only field that both tables have in common is the linking field. The PK of the main table is stored in the table of the subform where it is referred to as a FK. To produce reports or other output that shows data from both tables, use a query that joins them.

 

[definewebsites]

Hi there,

Thanks for the response. I think it would be a little clearer if I describe my database to you. The database is for a car sales company, and their sales is associated with a certain car and the respective owner. I have a table in which exists data concerning certain cars, makes and their prices. (PK is carID) I then have another table which lists data about several owners of cars.(PK ownerID) The logic behind the database is that each car is associated with only a single owner, but an owner can own several cars. I originally had the carID as a FK in the owner table, but there is a requirement that supposing an owner returned their car and exchanged it for another, the company should be able to delete the previous car and still retain the owners details. With the above PK, FK relationship, if I delete a car, it will delete the entire record connected to that due to the referential integrity. However, with that configuration when I had my main sales form with the ownerID table as the subform, the moment I entered the ownerID and pressed tab, the remaining record automatically updated.

However, due to the additional requirement I created a new table with ownerID (FK)and carID(FK) such that if I deleted the car, it would only delete the associated data with this new table and maintain the original details in the owner table. However, with this configuration I had to base my subform on a query, thus when I enter the ownerID in the subform, the remaining data only appears when I say move to the previous record using the record navigation and come back to that same record.

Is there some way to get the query for the subform to execute immediately when one parameter is provided to get the same effect as the initial situation?

Thanks, I do appreciate..

 

[Pat Hartman]

You seem to have switched from a backwards 1-many (carID in the owner table which restricts the relationship to an owner having a single car and a car belonging to many owners) to a many-to-many.

If a car can have only one owner then the relationship is 1-many and the OwnerID goes in the car table as a FK.

 

[definewebsites]

Hi there,

Oh yes sorry, thats what I meant, I had the ownerID in the car table as a FK. But in terms of the underlying subform query, is there a way to get it to auto requery when I enter one piece of data as in the previous relationship, without having to move to lets say the previous record and then come back to that record again?

Thanks

 

[Pat Hartman]

In the AfterUpdate event of the field that the subform is linked to, put:

Me.yoursubformname.Form.Requery

 

[definewebsites]

Hi there Pat,

It worked. Thanks alot, I do appreciate all the time and effort you have put into assisting me with my problem.

Cheers

 

[Pat Hartman]

Sorry it took me so long to figure out what you needed.

 

[definewebsites]

No problem,

The fact that you did help me out is very much appreciated. Maybe I should have explained my problem better from the onset..

Cheers