Record selector from a combo box

[Guest]

How can I set up a combo box to "goto" a record on a continuous form? I have
all my records listed but when I select an option from my combo box it will
go to that record but not filter it. I want to be able to use one of my
fields in my table as the selections for my combo box.

 

[Albert D.Kallal]

just use the wizard to create a combo box for you...the wizard offers a
option to 'find a record'..and this combo box should work just fine on your
continues form....


The wizard will ask what field to search by..and when selected, the form
will move to your choice....

 

[Guest]

I forgot to mention that my continuous form is on a subform so when I run the
wizard it won't let me select any fields from my subform.

 

[Albert D.Kallal]

I forgot to mention that my continuous form is on a subform so when I run
the
wizard it won't let me select any fields from my subform.

Then, open up the sub-form in design mode....

So, you could open up the sub-form in design mode, and put the combo box
there?

Take a look at the code the combo box generates for a typical form.

The code for the combo box is quite simple. Assuming you build a combo box
that returns the 'id', but searchers by company name, in the after update
event, you could simply go

me.mysubform.form.RecordSet.FindFirst "id = " & me.MyComboBox

The above assumes that the table for the subform has a "id" as the primary
key (most tables do).

The more difficult problem here is actually building a combo box that
correctly loads the records that currently exists in the sub-form.

Your problem is not building the combo box (as above shows..it is about one
line of code). The problem here is LOADING the combo box with the given set
of records that the sub-form currently contains. If you are not restricting
records to the sub-form via the master/child settings, then you should be
able to open up the sub-form in design mode..and use the wizard to put a
combo box in the header.

If you are using the master/child settings of the main form to the sub-form,
then you going to have to load up the combo box in the main form (and, use
the forms on current event - and then use the above one line of syntax in
the combo box after update).

 

[Guest]

I am using the master/child settings so I will be putting the combo box on
the main form. Do I have to put the primary key field in brackets in the code
you mentioned? Also, what code goes in the current event of the main form?

I just tried to use that code but nothing happened. I can scroll through my
records but when I choose an option from the combo box nothing happens.

 

[Albert D.Kallal]

ok...so, we have two problems to now solve

1 - make the combo box function

2 - load the combo box with ONLY the current records in the sub-form....

Lets try and solve #2 first....

We will have to use the forms on-current event (and possibility the on-load
event for the first time) to load up the combo box.

So, use the wizard to build a two column combo box (don't use the find
combo...just a combo box).

place the combo box on the main form, but use the sub-forms TABLE as the
source for the combo box. You will make the combo box a 2 column (or, as
many as you want..but the FIRST column will be the primary key of this
sub-form table (usually ID in most cases).

At this point, the combo box will not work.

Next, we need to write a routine that will LOAD up the combo box (restrict)
it to 'same' records as the sub-form.

This routine will be in the main form..since that is where the combo box
is...

Sub SetUpCombo

dim strSql as string

strSql = "select id,Lastname from tblSub where main_id =" & me!id

me.mycombo.RowSource = strSql

end sub

The above is our small routine we will use to setup the combo box. Note that
"main_id" is the name of the foreign key field (child link field) you have
in the sub-form table.

So, in our on-load event, to setup the combo, we can go:

Call SetUpCombo

And, in the main forms on-current event (since we might move to another
record.....), we go

Call SetUpCombo

And, the code in the combo after update event of the combo will be something
like

me.mysubform.form.RecordSet.Findfirst "id = " & me.MyCombo


The above is just "air" code as I type this response..but the general layout
as above should work...

The "sql" as above needs to have the SAME columns as the sql in the combo
box. (You can even cut/past that sql from the combo box and use
that in your above code if you wish...

 

[Guest]

Ok I think have all the code set except for the sql part. The " & me!id is
where I'm a little unclear. I cut and pasted my sql to replace the select id
part but I'm not sure what needs to replace the me!id part.

strSql = "select id,Lastname from tblSub where main_id =" & me!id

 

[Albert D.Kallal]

Ok I think have all the code set except for the sql part. The " & me!id is

where I'm a little unclear. I cut and pasted my sql to replace the select
id
part but I'm not sure what needs to replace the me!id part.

strSql = "select id,Lastname from tblSub where main_id =" & me!id

The above is actually

strSql = "select id, Lastname from tblMyChildTableNameGoesHere
where NameOfFieldUsedToRelateToParentTable =" & me!id


me!id = name of primary key field name in the parent table

Remember, we are running this query in the main form, and trying to build a
list of legitimate child records. So, "ID" is our primary key, and we want a
list of child records that has the same "related" primary key. Also, it
would be quite surprising that you actually have a field name of "lastname",
and that is the only column you desire display? (if that is correct, then I
am a mind reader). So, the point here is that obviously you will change the
name of the fields (lastname) to whatever it is that you have in your combo
box. In fact, I suggested that you use the wizard to build the combo
box....and then steal the sql from that combo box for the above statement.
(you do this, since you need the same fields, and in the same order as the
sql used for the combo box...the sql has to match or the combo will likely
not display correctly).