newbie - requery (?) questions

[mark kubicki]

(excuse my ignorance; I'm a self taught XLVBA office geek now transferring
to ACCESS, and this is my first time around the block...)


I have the following
TABLE - tblTable, w/ fields: [ Type, Manufacturer, Product, Notes...]
QUERY - qryTable, based on tblTable and includes fields: [Type,
Notes...], the criteria of the filed [Type] is set to a combo box on the
frmForm ( [forms]![frmForm]![type] ) with its row source : [tblTable]![Type]
FORM - frmForm, this is a "pop-up (modal) " form, where the user will
select a value from a combo box, and the remaining fields "ought" to fill in
automatically;
it includes:
- combo box cboType, row source [tblTable]![Type]
- textbox tbxManufacturer, control source
[qryTable]![Notes]
- ...

I am keeping both the frmForm and qryTable open (qryTable does not need to
be, but I am doing so to watch it's status...)

when I select a [Type] at frmFrom!cboType, I do not see the qryTable update
until I close it and then re-open it (?)
when qryTable is updated the text boxes on frmForm which use the fields in
qryTable as their control source do not show values (they show #Notes? as a
value..)

I suspect that I am missing some requery commands, and after update
events... (and have not been able to find a working combo...)


this may be too big a question to ask in a forum, but , for me, it is also
an "key problem" in that its answer will help clarify much of the "big
picture" of how ACCESS thinks...
thanks in advance,
mark

 

[Steve Schapel]

Mark,

I am not 100% sure what you are trying to achieve.

Is my understanding correct?...
1. qryTable is a query that is the Record Source of the frmForm form.
2. The Type field in the qryTable query has a Criteria that references
the value of an unbound combobox on the frmForm form.
3. You want to select an item in the combobox, and then have the
frmForm form only display records for the selected Type.

If that is so, I think there are three fairly common approaches, all
involving code on the After Update event of the combobox...

1.
Me.Requery

2.
Me.RecordSource = "SELECT * FROM tblTable WHERE [Type]='" &
Me.cboType & "'"

3.
Me.Filter = "[Type]='" & Me.cboType & "'"
Me.FilterOn = True