Subform - Filtered Data.

[LEXCERM]

Hi All,

In form MAINFORM (of the attached db) there is a subform.

The idea is to have the subform automatically display the current list of trucks available (based on qry_truck_types) when the form opens. This way, a user only needs to enter the license "expiry date" alongside each one. Consequently, when a new truck is added in the DB, the MAINFORM will show the additional truck.

I could simply change the Truck_Type field in the subform to combo's, but I'd like the subform to be dynamic (if that's the right word!)

I believe an APPEND QUERY could do the trick, but am not sure on how to implement this.

ALl help greatly appreciated.

Regards,
PAUL.

 

[daveUK]

I think you're going about this the wrong way - you don't need to use a subform.

You would only use a subform if each truck type had several expiry dates.

From what I understand you want a form, which displays all of the truck types with an expiry date field for each truck type, which users can complete themselves.

You could place all of this information on one form. Also place a cmd button on the form, which says something like 'Add new truck type', this would open a new form with a field for the user to enter the new truck type. When they close this form, an append query would add the new truck type to the truck type table and you could then requery the main form and the new truck details would appear.

I may have mis-understood your explanation of what you're trying to achieve, as having looked at the database, I'm not too sure what you're trying to do with the employee data table.

 

[LEXCERM]

Hi Dave,

Thanx for yr reply. I originally had the data on one form and all in one table i.e. NAME > NAT_INS_NO > COMBI EXPIRY DATE > REACH EXPIRY DATE etc.

The problem I had is that I wanted to create a query which produced the expiry dates, for all trucks, combined into one (in ascending order.) For example:

JOHN SMITH - 12/04/05 - COMBI
JOHN SMITH - 12/05/05 - REACH
PETER JONES - 12/06/05 - COUNTER
PETER JONES - 15/07/05 - RIDE
JOHN SMITH - 20/07/05 - REACH ... etc etc.

People advised me that I needed to normalize my Db to achieve this, hence the different tables.

I could have easily created combos in the subform for the user to select the truck types, but it could create errors like the user selecting the same truck type twice etc. Unless there is a way to stop the user selecting a truck that has already been used in a previous combo???

Regards,
PAUL.