Another question about choice limits

[grep]

Okay, so I've got my query working on my PO details form. Now, when I
select a manufacturer and go to the Model field, the only models
displayed are those associated with that manufacturer.

Problem is that, since part of the code does a requery, when the next
line item gets a different manufacturer than the last, the display field
in the previous record goes blank.

In other words:

Line Item Manufacturer Product
~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~
1 Linksys WRT54G
2 Microsoft <Here is where it happens>

As soon as I get to the Product field in the second record, the first
records Product field value goes blank. I know it's because of the
requery, but I don't know what to do about it.

grep

 

[Marshall Barton]

Okay, so I've got my query working on my PO details form. Now, when I
select a manufacturer and go to the Model field, the only models
displayed are those associated with that manufacturer.

Problem is that, since part of the code does a requery, when the next
line item gets a different manufacturer than the last, the display field
in the previous record goes blank.

In other words:

Line Item Manufacturer Product
~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~
1 Linksys WRT54G
2 Microsoft <Here is where it happens>

As soon as I get to the Product field in the second record, the first
records Product field value goes blank. I know it's because of the
requery, but I don't know what to do about it.

I don't remember the details of your setup here, but it
sounds like you have a dependent combo box on a continuous
form. If so, you're right that the requery in the form's
Current event (and the mfg combo's AfterUpdate) is filtering
the product combo box so the bound value in the other
records is not in the row source (so it displays blank).

The way around this is kind of tricky. First, modify the
form's record source to be a query that joins the product
table on the product id and add the product name to the
query's field list.

Next, place a locked text box bound to the that new product
name field exactly on top of the text portion on the combo
box. This will then display the correct product name on all
records.

The remaining problem of how to get back to where the user
can work on the combo box is dealt with by setting the text
box's GotFocus event to immediately (re)set the focus to the
combo box (which will automatically bring the combo box in
front of the text box).

 

[grep]

Marshall,

What you're saying makes sense, except that the field in question is in
a subform in datasheet view, so field placement isn't really an option.

The goal here - and I know it's doable because I did it before, but can
no longer find it - is to have a Manufacturer field (Field value
examples: Cisco, Microsoft, Linksys) and a Model field (Field value
examples: WIC-1DSU-T1, E75-00956 and WRT54G, respectively) set up such
that, once you select the Manufacturer, the next combo box will *only*
show associated products. In other words, I don't even want to see
WRT54G as an option if the manufacturer is Microsoft.

So now I have a datasheet view with various product makes and models as
line items. I'd like to keep my selections showing, once they're made.
This seems like it should be simple, but I'm beating my head against the
wall with it.

Thanks,

grep

 

[Marshall Barton]

What you're saying makes sense, except that the field in question is in
a subform in datasheet view, so field placement isn't really an option.

The goal here - and I know it's doable because I did it before, but can
no longer find it - is to have a Manufacturer field (Field value
examples: Cisco, Microsoft, Linksys) and a Model field (Field value
examples: WIC-1DSU-T1, E75-00956 and WRT54G, respectively) set up such
that, once you select the Manufacturer, the next combo box will *only*
show associated products. In other words, I don't even want to see
WRT54G as an option if the manufacturer is Microsoft.

So now I have a datasheet view with various product makes and models as
line items. I'd like to keep my selections showing, once they're made.
This seems like it should be simple, but I'm beating my head against the
wall with it.

That steel reinforced concrete wall you're banging your head
against is called datasheet view ;-)

Change it to a continuous form that looks just like sheet
view and you can then use the approach I outlined before.

 

[grep]

Argh!! Not the answer I wanted, although your solution works, basically.
Thanks. I just recalled how I did it before... it was a slightly
different scenario. All the line items were for the same manufacturer,
unlike this time. Slight, but huge difference, eh.

Thanks again!

grep

 

[Marshall Barton]

Argh!! Not the answer I wanted, although your solution works, basically.
Thanks. I just recalled how I did it before... it was a slightly
different scenario. All the line items were for the same manufacturer,
unlike this time. Slight, but huge difference, eh.

Yeah, that is a huge difference. If you don't filter the
RowSource differently for each record, you don't have this
issue ;-)