UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Eliminating Already Selected Choices From Combo Box Source, Any Version    
 
   
DominicG
post Nov 20 2019, 10:01 AM
Post#1



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


Version: Office 365 (didn't see it listed in the pull down box)

I have a subform that is set to display in Datasheet Format. In that subform is a ComboBox called "cmbCase". The record source for cmbCase is the table tblCase and is just a list from A to Z (without I, O, Q, S, and W)

Since this is a Datasheet, each line will have a cmbCase combobox. My goal is to limit the choices left in the list after the user initially selects one. In other words, they select "A" for line one. When the user pulls down the list in the combo box, "A" will not be among the choices. Say they choose "B" and then move to the next line. The choices in the combo box in line 3 are missing both A & B.

How do I do this? I imagine this would be accomplished with a compare query but I'm really confused at how to set it up

Edit to add: the data for this subform is held in a table called tblSC_Data. I'm thinking if I evaluate tblCase versus tblSC_Data it should give me "what's left". But I'm wondering how I implement this to update the list dynamically
This post has been edited by DominicG: Nov 20 2019, 10:07 AM
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:07 AM
Post#2


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


What field is cmbCase bound to (what's its ControlSource) and what is the name of the table you are recording this selection in?

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 10:17 AM
Post#3



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


In the subform (which is called "Scenario Checklist") cmbCase is bound to the Field Name "Case" in the table "tblSC_Data"
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:24 AM
Post#4


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


Try using the following as the RowSource of cmbCase:
CODE
SELECT
  [Case]
FROM tblCase
WHERE [Case] NOT IN (
  SELECT DISTINCT
    [Case]
  FROM tblSC_Data
);

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 10:33 AM
Post#5



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


That worked perfectly!

I'm not very fluent in SQL. But I can follow the flow of the code to get what you did

Slick. Very slick!

Thank you
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:34 AM
Post#6


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Good luck with your project!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 10:42 AM
Post#7



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


OOPS! Looks like I spoke too soon. The subform is linked to the parent form by a Field called "PRD". This Field Name is also in tblSC_Data

That SQL routine needs to look only at all the records that have the same value in the field PRD
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 10:49 AM
Post#8


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


OK, remember I'm flying blind here!

Try:
CODE
SELECT
  [Case]
FROM tblCase
WHERE [Case] NOT IN (
  SELECT DISTINCT
    [Case]
  FROM tblSC_Data
  WHERE PRD = [Forms]![MainFormName]![[PRD]
);

Change the MainFormName to the name of the parent form

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 11:29 AM
Post#9



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


Oh I totally get that you are relying on the data I'm giving you! And I appreciate the help immensely!!

I think there was an extra "]" in there. I updated the SQL statement and it runs. But it doesn't update dynamically. Any idea how I can get it to refresh the table when going from row to row in the table?

Additionally, if I start with a blank tblSC_Data I get no selection. The way around that is to pre-populate the table with at least one entry. Then it appears to address that issue
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 11:44 AM
Post#10


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


Can you post an example db with just the relevant tables and a form that displays the issue?

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 12:01 PM
Post#11



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


OK, I uploaded a stripped down version of the database.

As I mentioned above, the main form is "frmLeadSheet" and the subform "Scenario Checklist" is under the tab "Scenario Checklist"
Attached File(s)
Attached File  PRD_Calc_Test_Copy.zip ( 471.21K )Number of downloads: 5
 
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 12:10 PM
Post#12


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


OK, change the RowSource of cmbCase to:
CODE
SELECT
  [Case]
FROM tblCase
WHERE [Case] NOT IN (
  SELECT DISTINCT
    [Case]
  FROM tblSC_Data
  WHERE PRD = [[PRD]
);



--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 01:25 PM
Post#13



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


Dave,

That seems to work for limiting the list to previously un-selected values. This assumes that there are entries in the subform already. But here's the scenario I'm coming across. A user starts entering data into an empty subform and they select "A as the first Case in the first line. When they get to the second line and access the pull down list from the combo box "A" shouldn't be included among the options. But it IS.

I believe this is because the table hasn't had a chance to update yet. Is there a way to force a refresh of the table? Maybe embed something in the AfterUpdate event of the combo box?
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 02:43 PM
Post#14


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


No, the table will have updated.

I think you just need to force the combo to requery.

Add this code to cmbCase GotFocus event:
CODE
Private Sub cmbCase_GotFocus()

  Me.cmbCase.Requery

End Sub


and see if that helps.

--------------------


Regards,

David Marten
Go to the top of the page
 
DominicG
post Nov 20 2019, 03:25 PM
Post#15



Posts: 270
Joined: 3-July 07
From: Fort Washington PA


Thanks, that was it!

I needed to refresh the combo box and not the table.

Another case of "right church, wrong pew" thumbup.gif

Thanks again for your help!
Go to the top of the page
 
cheekybuddha
post Nov 20 2019, 05:20 PM
Post#16


UtterAccess Moderator
Posts: 11,857
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th December 2019 - 08:13 PM