Trying to filter records with combo box

[BRC]

using access2000 windows 2000
I am trying to find a specific record by on a subform by selecting a value
in a combo box on the main form. I have several form in other access
application that do this and cannot figure out why (on this new form) it
will not work. Are the some rules about where the data in the combo box
comes from vs. the data on the subform?
I have 3 fields in the combo box from a query that just has those fields.
the combo box is bound to the recno (the key field in the table). The querys
are based on the same table. The query feeding the sub from has all fields
including the recno field. I notice that everytime i open the form I get a
"enter parameter value" dialog asking for the "recno" which is the field i
am tryhing to use to in the link child field/ link master field dialog? Any
help or some advise on where i can read a concise explanation ot this
process would be greatly apprecated.

thanks BRC

 

[tina]

sounds like the combo box control and subform are not linked correctly.

the combo box is bound to the recno (the key field in the table).

do you mean that the BoundColumn property in the combo box control is set to
the column that holds the recno value? if so, that's fine. if not, then put
the recno field as the first field in the combo box RowSource, and set the
BoundColumn property to 1. (btw, just to clarify, when a combo box - or any
control - is "bound" to a field, that means that the field is set as the
control's ControlSource; that's a completely separate property from the
BoundColumn property that is another attribute of a combo box control.)

so, let's say the recno field is the first field listed in the combo box
control's RowSource, and the BoundColumn is set to 1. i'll call the combo
box "cboRecord".

set the subform control's LinkMasterFields property to
cboRecord

make sure that the control name is spelled correctly. set the
LinkChildFields property to
recno

make sure that the field name is spelled correctly, and that the recno field
is included in the subform's RecordSource.

hth

 

[BRC]

Tina
Thanks for the help. I was not using the correct reference to the combo box
in the link master field. It is working now but I am still having a problem.
I have the following code in the "after update" event of the combo box:
Private Sub Combo4_AfterUpdate() 'line 1
' Find the record that matches the control. 'line 2
Dim rs As Object ' line3
Me.ed_rx2_sf.Visible = True 'line 4
Set rs = Me.Recordset.Clone 'line5
rs.FindFirst "[Recno] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
End Sub
***********
I have a form in another application and this works fine, but in the one i'm
working on I get the following error at line 5
"run-time error '91' ,object variable or With block variable not set".
Is there another way to filter records after combo box update?
Thanks again
BRC

 

[tina]

your code is looking for a matching record in the *main form*, not in the
subform. if your main form is bound, why are you linking the subform to the
combo box control, instead of to the primary key field of the main form? and
if your main form is unbound, then you don't need any code in the combo box
control to find the matching record(s) in the subform - the subform link
does that automatically.

hth

Tina
Thanks for the help. I was not using the correct reference to the combo box
in the link master field. It is working now but I am still having a problem.
I have the following code in the "after update" event of the combo box:
Private Sub Combo4_AfterUpdate() 'line 1
' Find the record that matches the control. 'line 2
Dim rs As Object ' line3
Me.ed_rx2_sf.Visible = True 'line 4
Set rs = Me.Recordset.Clone 'line5
rs.FindFirst "[Recno] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
End Sub
***********
I have a form in another application and this works fine, but in the one i'm
working on I get the following error at line 5
"run-time error '91' ,object variable or With block variable not set".
Is there another way to filter records after combo box update?
Thanks again
BRC

sounds like the combo box control and subform are not linked correctly.


do you mean that the BoundColumn property in the combo box control is

set

to
the column that holds the recno value? if so, that's fine. if not, then put
the recno field as the first field in the combo box RowSource, and set the
BoundColumn property to 1. (btw, just to clarify, when a combo box - or any
control - is "bound" to a field, that means that the field is set as the
control's ControlSource; that's a completely separate property from the
BoundColumn property that is another attribute of a combo box control.)

so, let's say the recno field is the first field listed in the combo box
control's RowSource, and the BoundColumn is set to 1. i'll call the combo
box "cboRecord".

set the subform control's LinkMasterFields property to
cboRecord

make sure that the control name is spelled correctly. set the
LinkChildFields property to
recno

make sure that the field name is spelled correctly, and that the recno field
is included in the subform's RecordSource.

hth

get

a

field

i dialog?
Any

 

[tina]

you're welcome

Again, thank you. after i did the post, i suspected that might be issue
because atfter you first post, the form was working accept for the error
mess problem. I deleted that code and it works fine.
thanks
BRC

your code is looking for a matching record in the *main form*, not in the
subform. if your main form is bound, why are you linking the subform to the
combo box control, instead of to the primary key field of the main form? and
if your main form is unbound, then you don't need any code in the combo box
control to find the matching record(s) in the subform - the subform link
does that automatically.

hth

Tina
Thanks for the help. I was not using the correct reference to the

combo
box

in the link master field. It is working now but I am still having a problem.
I have the following code in the "after update" event of the combo box:
Private Sub Combo4_AfterUpdate() 'line 1
' Find the record that matches the control. 'line 2
Dim rs As Object ' line3
Me.ed_rx2_sf.Visible = True 'line 4
Set rs = Me.Recordset.Clone 'line5
rs.FindFirst "[Recno] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
End Sub
***********
I have a form in another application and this works fine, but in the

one
i'm

working on I get the following error at line 5
"run-time error '91' ,object variable or With block variable not set".
Is there another way to filter records after combo box update?
Thanks again
BRC



sounds like the combo box control and subform are not linked correctly.

the combo box is bound to the recno (the key field in the table).

do you mean that the BoundColumn property in the combo box control

is
set

to
the column that holds the recno value? if so, that's fine. if not, then
put
the recno field as the first field in the combo box RowSource, and

set
the

BoundColumn property to 1. (btw, just to clarify, when a combo box - or
any
control - is "bound" to a field, that means that the field is set as the
control's ControlSource; that's a completely separate property from the
BoundColumn property that is another attribute of a combo box control.)

so, let's say the recno field is the first field listed in the combo box
control's RowSource, and the BoundColumn is set to 1. i'll call the combo
box "cboRecord".

set the subform control's LinkMasterFields property to
cboRecord

make sure that the control name is spelled correctly. set the
LinkChildFields property to
recno

make sure that the field name is spelled correctly, and that the recno
field
is included in the subform's RecordSource.

hth


using access2000 windows 2000
I am trying to find a specific record by on a subform by selecting a
value
in a combo box on the main form. I have several form in other access
application that do this and cannot figure out why (on this new

form)

it
will not work. Are the some rules about where the data in the

combo
box

comes from vs. the data on the subform?
I have 3 fields in the combo box from a query that just has those
fields.
the combo box is bound to the recno (the key field in the table). The
querys
are based on the same table. The query feeding the sub from has all
fields
including the recno field. I notice that everytime i open the form

I
get

a
"enter parameter value" dialog asking for the "recno" which is the field
i
am tryhing to use to in the link child field/ link master field dialog?
Any
help or some advise on where i can read a concise explanation ot this
process would be greatly apprecated.

thanks BRC