Using a filter query 1

[puforee]

I have a Target Form based on a table. It has several fields. One of the fields is "Model".

This Split DB can be used for several different "Models". To keep the clutter down I set each front end DB to only show the model that has been designated for it. So, many front in DB's going to the same Back end....but each front end limited to one model. I make this selection by having a model table, having a "Model" and a "Use" field next to each model. When I set up the individual front ends I just select the proper "Use" field (Yes/No) for the Model information I want the DB to process.

Back to my target Form, with the model field. I want to open this form so it only shows the "Use" model from the Model Table. By the way, the Form I want to open is being opend from another form (Form B). (there is other functionality on this form not part of this discussion) So on Form B when I slect a command button, in VB I DoCmd open the target Form using a query as a filter. The query returns the Model from the model table that has the Use field set to yes. This should work...right?? Well it doesn't. Since the filter query has both the Model field and Use field in it the target form looks for the Use field information. The target form does not have a cooresponding Use field. The filter query when run by itself only returns the model...the Use field is not select for output.

I have used filter queries before and I am not sure why ths one is not working.

Can anyone help? I can supply code if needed.

 

[dhookom]

The target form does not have a cooresponding Use field" why not add the table with the Use field to the form's record source?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I in the form open query I linked the Target Form Model to the Model table and set the criteria for the Use field to "Yes". Worked....but...I then cannot update any of the information in the target forms fields...they are all locked.

 

[dhookom]

Is the Model field a primary key in both tables?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

The model table has two fields....Model (text) and Use (Yes/No). Their is NO primary key.

The other table has many fields, Model (text) being one of them. Their is NO primary key in this table either.

 

[dhookom]

puforee,
Don't expect the record source to be updatable if the join isn't on at least a primary-foreign key relationship.

I would never create a table that doesn't have a primary or unique index. 
Having indexes and primary/foreign key relationships is basic database programming.

Bottom line is to make some fields into primary keys and/or foreign keys.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I agree with you and as I looked at my table to answer your question I was suprised that I did not create a primary key our of the Model field. I will try that.

If I make the Model the primary key in the Model table...does that automatically make the Model field in the other table a foreign Key? Or do I have to create the relationship also. I usually use ID fields for Keys but I will just use the model field here...Or would it be better to create and autonumber in the Model table and a simulare field in the other table? Numbers work better than text?? And the model field is text.

Your recomendation please.

 

[dhookom]

If the Model field is guaranteed to be unique, I would use it as a primary or unique index. The critical table I believe is your Model table.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I did it. Created the primary Key for Model in the Model table. Created a one to many relationship between the two tables connected at Model.

Built a query that included all fields from the big table. Linked the model table to the big table...model field.
Added Use field to the query and set its criteria to "Yes". Ran the query and got my return valuse from the big table and I can now modify them as I wish.

Funny how the little things are so important.

I thank you so much.