Diminishing Look Up Lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a continuious from with a single field combobox. I would like to
set up the combobox to have a look up list that diminishes as you select
items from the list. This is so the users only have the option to select an
item once from the list. If that item is deleted from the form, the combobox
is refreshed and the item becomes available again in the list.

If this is possible I would appreciate any assistance to get me started

Regards
Nick
 
I am using a continuious from with a single field combobox. I would like to
set up the combobox to have a look up list that diminishes as you select
items from the list. This is so the users only have the option to select an
item once from the list. If that item is deleted from the form, the combobox
is refreshed and the item becomes available again in the list.

If this is possible I would appreciate any assistance to get me started

Regards
Nick
Click to expand...

I'd base the combo on an "unmatched" query - i.e. if the valid values
come from tblSource, and you're putting them into tblDest, you could
use a query

SELECT tblSource.IDField
FROM tblSource LEFT JOIN tblDest
ON tblSource.IDField = tblDest.IDField
WHERE tblDest.IDField IS NULL;

This will pick up only values that are NOT already in tblDest.

You may need to Requery the combo in the form's AfterUpdate event.

John W. Vinson[MVP]
 
Hi John,
I got the query set up and assigned to the combo box look up list no problems
And the query works.
But, there always seem to be, a but. Anyway I’ve tried to use Me.Requery in
the after up date, no luck so I then tried
[Forms]![frmSystem]![frmSystemList]![SystemList].Requery This sort of worked
only after the second time the look up was used the previous item disappears.
I am obviously doing the re-query wrong

I would appreciate your help again.

Nick
 
Hi John,
I got the query set up and assigned to the combo box look up list no problems
And the query works.
But, there always seem to be, a but. Anyway I’ve tried to use Me.Requery in
the after up date, no luck so I then tried
[Forms]![frmSystem]![frmSystemList]![SystemList].Requery This sort of worked
only after the second time the look up was used the previous item disappears.
I am obviously doing the re-query wrong
Click to expand...

I take it that frmSystem is the mainform, and frmSystemList the name
of a subform - and SystemList the combo box?

What event are you using to do the requery?

John W. Vinson[MVP]
 
after up date

John Vinson said:
Hi John,
I got the query set up and assigned to the combo box look up list no problems
And the query works.
But, there always seem to be, a but. Anyway I’ve tried to use Me.Requery in
the after up date, no luck so I then tried
[Forms]![frmSystem]![frmSystemList]![SystemList].Requery This sort of worked
only after the second time the look up was used the previous item disappears.
I am obviously doing the re-query wrong
Click to expand...

I take it that frmSystem is the mainform, and frmSystemList the name
of a subform - and SystemList the combo box?

What event are you using to do the requery?

John W. Vinson[MVP]
Click to expand...
 
Hi John,
I have tried to set out what I am trying to achieve. There is two sub-forms
in the main form. The first sub-form is frmSystemList which has the combobox
which set the criteria for the second sub-form. I have included the SQL for
the queries of both forms.
So I hope this gives you an understanding of what I have done wrong with the
requery of the combobox.

Nick

frmSystem - This is the main form

frmSystemList (Sub-Form) – This is a continious form that has one field
SystemList combobox. That up-dates the table tbSystemList.

Private Sub SystemList_AfterUpdate()

[Forms]![frmSystem]![frmSystemList]![SystemList].Requery

DoCmd.GoToRecord , , acNewRec

[Forms]![frmSystem].Requery

End Sub

Query SQL’s for the form frmSystemList
(1) qSystemLookup
SELECT tbSamples.s, tbSamples.MAINTPLANT, [Forms]![frmSystem]![Plant] AS Expr1
FROM tbSamples
WHERE (((tbSamples.MAINTPLANT)=[Forms]![frmSystem]![Plant])) OR
((([Forms]![frmSystem]![Plant]) Is Null))
ORDER BY tbSamples.s;

(2) qSystemLookup-1
SELECT qSystemlookup.s, Count(qSystemlookup.s) AS CountOfs,
qSystemlookup.MAINTPLANT
FROM qSystemlookup
GROUP BY qSystemlookup.s, qSystemlookup.MAINTPLANT;

(3) qSystemLookup-1 Without Matching tbSystemList
SELECT [qSystemLookup-1].s
FROM [qSystemLookup-1] LEFT JOIN tbSystemList ON [qSystemLookup-1].s =
tbSystemList.SystemList
WHERE (((tbSystemList.SystemList) Is Null));

frmSystemSub (Sub-Form) – This is the second sub form which re-query’s
after the up-date of FrmSystemList ! SystemList combobox.

[Forms]![frmSystem].Requery

Query SQL’s for the form frmSystemSub
(1) qSamples
SELECT tbSamples.SampleID, tbSamples.[Select], tbSamples.CHECKED,
tbSamples.GREASE_SAMPLE, tbSamples.OIL_SAMPLE, tbSamples.VA,
tbSamples.FLOC_ID, tbSamples.s, tbSamples.BHP_EQ, tbSamples.VA_CODE,
tbSamples.Transfered, tbSamples.FLOC_DESCRIPTION, tbSamples.[COST CENTER],
tbSamples.[CATALOG PROFILE], tbSamples.OILTYPE, tbSamples.FillQuantity,
tbSamples.MAINTPLANT, [Forms]![frmSystem]![Plant] AS Expr1
FROM tbSamples
WHERE (((tbSamples.MAINTPLANT)=[Forms]![frmSystem]![Plant])) OR
((([Forms]![frmSystem]![Plant]) Is Null));

(2) qSamples-1
SELECT qSamples.SampleID, qSamples.[Select], qSamples.CHECKED,
qSamples.GREASE_SAMPLE, qSamples.OIL_SAMPLE, qSamples.VA, qSamples.s,
qSamples.FLOC_ID, qSamples.BHP_EQ, qSamples.VA_CODE, qSamples.Transfered,
qSamples.FLOC_DESCRIPTION, qSamples.MAINTPLANT, qSamples.[COST CENTER],
qSamples.[CATALOG PROFILE], qSamples.OILTYPE, qSamples.FillQuantity
FROM qSamples
WHERE (((qSamples.CHECKED)=True) AND ((qSamples.s) In (SELECT [SystemList]
FROM [tbSystemList])))
ORDER BY qSamples.s, qSamples.FLOC_ID;
 
Hi John,
I fried this and it seems to work ok after I added the save command.

Regards
Nick

Private Sub SystemList_AfterUpdate()

DoCmd.RunCommand acCmdSaveRecord

[Forms]![frmSystem]![frmSystemList]![SystemList].Requery

DoCmd.GoToRecord , , acNewRec

[Forms]![frmSystem].Requery


End Sub
 
Back
Top