Requery ONLY field in current record

[nelcarrasco]

I am using MS ACCESS 2003
I have a form with 2 fields (Combos): TYPE and CONCEPT
There are only two choices in TYPE (1 and 2)
In CONCEPT, there are several choices, depending on the selection of the previous field (TYPE). When 1 is chosen in TYPE, only those records associated to that will show up in the CONCEPT combo, and the same when 2 is selected.
I am requerying CONCEPT after updating TYPE (the underlying rowsource for CONCEPT is set to display only those records associated with TYPE selected)
The problem is that when I select a different choice in TYPE (different to the previous record, let's say), CONCEPT field goes "BLANK" in the previous record with a different TYPE, though in the table they are all saved OK
My question ... is there any way to requery the CONCEPT field only for the current record, without affecting any other records
Thanks for you in advance your any advice or assistance

 

[vbaInet]

Follow this way of changing the selection of CONCEPT based on TYPE and you wouldn't have a problem:

http://baldyweb.com/CascadingCombo.htm

And welcome to AWF!

 

[nelcarrasco]

dear vbaInet
thank you for for replay and my apologies for my delay.
I will give you a better picture of my problem because i am afraid that i was not clear enough
I have a table with TYPE (two records: 1 FIXED, 2 Variable). This table is never updated or modified.
Another table related to TYPE, named CONCEPT with 21 records. Two fields:
- IDType, which pulls the records in TYPE (1 or 2) (Combo)
- Concepts (there are 12 Concepts related to TYPE 1 and 9 related to TYPE 2
Then a GENERAL table (with a several fields, including TYPE and CONCEPT). So, there will be records matching the criteria 1 of TYPE and consequently, related ONLY to those records with IDType = to either FIXED or VARIABLE
In a continuous form (for Table GENERAL), I select (combo TYPE) a TYPE (FIXED or VARIABLE) and move to CONCEPT. In Afterupdate event, I requery the CONCEPT field (Rowsource =
SELECT T_ExpenseConcept.IDExpType, T_ExpenseConcept.TypeExpense, T_ExpenseConcept.ConceptExpense, T_ExpenseConcept.ConceptExpenseRUS FROM T_ExpenseConcept WHERE (((T_ExpenseConcept.TypeExpense)=Forms!F_ImportExpenses!ExpType)) ORDER BY T_ExpenseConcept.ConceptExpense;
Notice that only those records related to the selection in TYPE (1 0r 2) will be display. This works fine, but ....
when I move to the next record in GENERAL, and selected (for example) a TYPE which is different to any previous record already updated, the CONCEPT field goes BLANK (because it is not meeting the criteria in CONCEPT rowsource), though the data is perfectly saved in the GENERAL table but agoin .... it shows in the form view a BLANK field.
My question .... is there any way to requery the CONCEPT field ONLY FOR THE CURRENT record I am working on?
Please, if still I dont make myself clear, do not hesitate to contact me.
Your assistance is greatly appreciated
Thanks in advance
Nelson

 

[vbaInet]

Did you have a look at the link I gave you? It shows you a way of filtering based on a particular record, in your case the current record.

You can setup a field criteria in your query to a form's control value. E.g.

SELECT Employee_Name 
FROM Employees 
WHERE EmployeeID=[B][COLOR=Red][Forms]![frmEmployee_Details]![Combo1][/COLOR][/B];

Notice the part in red, it's looking up the value of a combo box as the criteria.

You can now set the RowSource of your 2nd combo box (i.e. Combo2) to that query.

 

[nelcarrasco]

vbaInet .... thanks!!! that was fast!!!
Definitely there is something I can get on all this ... I think for the sake of your time, it is better to send you a version of the DB with the epecific tables and form (only one)
I would appreciate if you can take a look at the form (especifically the afterupdate event in field ExpType .... my problem lies there, in the REQUERY line
Thanks in advance

Nelson

 

[vbaInet]

Can't seem to open the attachment. Comes up as corrupt.

 

[nelcarrasco]

vbaInet, dont really knows what happens because I can open the zip file and the db.
I compressed it again and attached it
pls let me know if you can open it now

thanks

nelson

 

[vbaInet]

Hmm... from what I can see it is filtering CONCEPT based on Export Type. Wasn't that what you wanted?

Just had another look. I see the problem. Will get back to you on that.

 

[nelcarrasco]

hi vbaInet ...... any progress? did u have a time to look at it?

thanks

 

[vbaInet]

Had another look. Normally, you can't have this level of control over controls within a subform. If it were just a Parent form control then that would be possible. The only aspect of a control within a subform that can be manipulated is the value per record (except conditional formatting).

 

[nelcarrasco]

Understand .... I changed the ways (though not very pro, but ....)
I added another field to the table (ExpConcept1), but not a combo, just a text field.
then after updating the ExConcept ..... ExpConcept1=ExpConcept.Column(x)
So, the text is displayed in the form and then the Combo ExpConcept is = 0
It is just a way out

Antway ..... I DO Appreciate your time and assistance

 

[vbaInet]

You can use the concept of an unbound "floating" combo box which will appear in the exact position (Left and Top) where the Cursor is positioned. That combo will overlap ExpConcept and be filtered by ExpType.

Changing the value of the floating combo will set the value of ExpConcept.

This will require some good planning if you understand the concept

 

[LPurvis]

The floating control is a bit of a mare though.

(Bearing in mind I've not looked at your example file - no time...)
It's much more common to include the related text control - but have it bound to a table in the form's source query. (Using an Outer join enforces that you don't need to have selected a value from the field for the record to still be present).

Umm... <tries to rememeber which one it is>
Yeah the "Row Varying Combo" in the List Select demo in the examples page linked to in my sig.
That's the usual "hide the combo" with a textbox technique.

See if it's vaguely like your scenario.

Cheers.

 

[nelcarrasco]

LPurvis ....
thank u very much for your suggestions ..... I am looking at them now
the ROW VARYING COMBO seems to work for me. I will tale a closer look at it and try to modify it to my needs
Will let you know
Thanks again

 

[eacollie]

Thank you. I am posting a reply to be able to see the example.