Question Load data from two tables

[babaroga]

Hello, I have same issue and dont know how to solve that.

My database have two tables.

GENERATORS
|ROUTE|GEN_NUMBER|LOCATION|LAST_SERVICE_HOURS|CURENT_HOURS|ORDER_NUM

and

GEN_CHECKS
|GEN_NUMBER|DATE|CURENT_HOURS|PHASE1|PHASE2|PHASE3|REMARKS


All generators are split on 6 routes, and every mechanic came with check sheet for everyday so I need to update all information from check sheet in table GEN_CHECK


I make form frm_checks, put input field for date and combo box show all six routes. Than I insert subform subform_checks where I load all information from table GEN_CHECKS.

I make already to filter by dates because I have date in GEN_CHECK table but I need now to filter by ROUTE and to order by ORDER_NUMBER, this information is in GENERATOR table.

If I make query thats fine, work perfect but problem is than I cant update information, because sometime I need to make changes and than I will put date and route, access will show me all generator and information for that day.

How to order or filter information based on value from another table?

I have relationship beetween those two table, its field GEN_NUMBER

Thanks.

 

[ArtMiles]

Hello,

I am not sure if I understand what you would like to do. I think that you are trying to filter records and then update the information in 2 tables. If this is what you are doing the you could make a new form for this. On the form that you have now place a button and use the code builder to open the new form to the record you would like to update. You could use 'DoCmd.OpenForm "frmName", , , "[GEN_NUMBER]=" & Me!GEN_NUMBER' if [GEN_NUMBER] is a number datatype or 'DoCmd.OpenForm "frmName", , , "[GEN_NUMBER]=" & Me!GEN_NUMBER & "'" if [GEN_NUMBER] is a text datatype.

Hope this helps. If not give me more information and I will try again.

Art

 

[babaroga]

OK, sorry for my English. Attached is database.

When first form open you will see I can change information on fly, just click and change, but I cant filter data by date and route because I don’t have that field and I don’t know how to make that relation.

However, inside database is working example, exactly what I need, in that case I use query, and I can filter by route and date, and its sorted by Order_num but I cant make any change.

BTW , only change which I need to do is on table GEN_CHECKS
Other field are disabled, I don’t need.

Now I need to do same but with possibility to make changes.

 

[ArtMiles]

Sorry it took so long to get back to you.

The reason you can not make changes to the second form is that it is based on a query that is not updatable. The form is based on the table Checks and the subform is based on a query tha is based off of tables Checks and Generatori. Maybe another approach to this would be to have the main form based on the table Generatori and the subform based on table Checks.

Hope this helps.

Art