Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-19-2019, 02:10 PM   #1
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 491
Thanks: 16
Thanked 42 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Hide data in combo box that has been previously selected

Hello, I have a continuous subform with a combo box (cboComponent). Every time I select an item from the combo box I want it unavailable for the next record until there is nothing to select. Can this be done? Below is the combo box SQL and the subforms record source is tbl_ComponentParts

Code:
SELECT tbl_Components.ComponentID, tbl_Components.Component, tbl_Components.IsInactive
FROM tbl_Components
WHERE (((tbl_Components.IsInactive)=False))
ORDER BY tbl_Components.Component;

__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 08-19-2019, 02:19 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Hide data in combo box that has been previously selected

In the combo after update event, could you add code to set the IsInactive field to True?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-19-2019, 02:19 PM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,132
Thanks: 40
Thanked 3,611 Times in 3,486 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Hide data in combo box that has been previously selected

perhaps something like this for your combo rowsource

Code:
SELECT tbl_Components.ComponentID, tbl_Components.Component, tbl_Components.IsInactive
FROM tbl_Components LEFT JOIN tbl_ComponentParts ON tbl_Components.ComponentID=tbl_ComponentParts.ComponentID
WHERE (((tbl_Components.IsInactive)=False) AND tbl_ComponentParts.ComponentID is null)
ORDER BY tbl_Components.Component;
this would require your subform to be saved and the combo to be re queried after each selection

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-19-2019, 03:14 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,953
Thanks: 13
Thanked 1,534 Times in 1,460 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Hide data in combo box that has been previously selected

Based on your query, it looks like the PK to the lookup table is an ID rather than the visible value. If you remove used items, the combo on existing rows will go blank on a continuous form.

Why do you want to remove the already used items? If you want to prevent them from being used, do that with code in the BeforeUpdate event of the combo.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-19-2019, 03:19 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,132
Thanks: 40
Thanked 3,611 Times in 3,486 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Hide data in combo box that has been previously selected

Quote:
If you remove used items, the combo on existing rows will go blank on a continuous form.
good point!
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-19-2019, 04:02 PM   #6
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 491
Thanks: 16
Thanked 42 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Hide data in combo box that has been previously selected

The reason I want to limit choices in the combo box is because they can only be used once per product. If you look at my example, open the (frm_UpdateProductComponentsParts) and move the records to select a new product then select a component and it cascades its data for the Parts data. You cant have the same component for the product chosen.
Attached Files
File Type: zip F3.zip (98.3 KB, 4 views)
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 08-19-2019, 06:31 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,238
Thanks: 86
Thanked 1,623 Times in 1,506 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Hide data in combo box that has been previously selected

OK, here is one of the old programmer's rules in action. "Access won't tell you anything you didn't tell it first." If you intend to "consume" a usage from the combo box, you need to include as part of the database the fact that a resource (one of the elements of the combo box) has been consumed. This sounds so trivial - but it is a point that we have seen too many people overlook.

Whether you have a flag for this or whether you have a temporary table that lists "consumed" entries really doesn't matter.

If you use a flag then your combo box .RowSource has to include as part of its WHERE clause "... AND ( UsedFlag = FALSE ) ..." - but then part two is to force a requery of the combo box after marking any flag TRUE.

If you have a table of used ID values, you would have a .RowSource to include as part of its WHERE clause "... AND ( IDValue NOT IN ( SELECT UsedID FROM UsedTable ) ) ..." - but then part two is to force a requery of the combo box after inserting any ID in the UsedTable.

In other words, it doesn't really matter HOW you do it, but you cannot expect Access to to show what isn't used yet unless it has a way to know what IS used.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Tera (08-20-2019)
Old 08-20-2019, 02:01 AM   #8
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,744
Thanks: 28
Thanked 522 Times in 495 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Hide data in combo box that has been previously selected

Take a look. This took some trickery and an update of your tables but does what was asked.
Attached Files
File Type: accdb F3_PLP_V2.accdb (672.0 KB, 12 views)

Last edited by MajP; 08-20-2019 at 02:30 AM.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
oxicottin (08-20-2019)
Old 08-20-2019, 01:30 PM   #9
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 491
Thanks: 16
Thanked 42 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Hide data in combo box that has been previously selected

Quote:
Originally Posted by MajP View Post
Take a look. This took some trickery and an update of your tables but does what was asked.

MajP thats exactly what I needed! Thank you very much!
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 08-20-2019, 01:39 PM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,744
Thanks: 28
Thanked 522 Times in 495 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Hide data in combo box that has been previously selected

As for your tables. You need a Products table, a component table with a component ID, and a parts table with a parts ID and a foreignKey to the component table. Because all parts are related to a component your linking table really does not need the component ID foreign key. Tbl_ProductComponentParts really only needs a product ID and a Part ID because you can get the component ID from the relation to the parts table. However, in order to do the cascading you need a "fake" component id in the tblProductComponentParts. So just be aware having that value is a non-normal design because it is repeating data, but there was no other solution to get that interface. Fyi, if you are not aware of how that works there is actually a textbox on top of each combobox. This is one trick for cascading combos on a continous form.
MajP is offline   Reply With Quote
Old 08-30-2019, 07:58 PM   #11
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 491
Thanks: 16
Thanked 42 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Hide data in combo box that has been previously selected

Quote:
Originally Posted by MajP View Post
As for your tables. You need a Products table, a component table with a component ID, and a parts table with a parts ID and a foreignKey to the component table. Because all parts are related to a component your linking table really does not need the component ID foreign key. Tbl_ProductComponentParts really only needs a product ID and a Part ID because you can get the component ID from the relation to the parts table. However, in order to do the cascading you need a "fake" component id in the tblProductComponentParts. So just be aware having that value is a non-normal design because it is repeating data, but there was no other solution to get that interface. Fyi, if you are not aware of how that works there is actually a textbox on top of each combobox. This is one trick for cascading combos on a continous form.

MajP, its been bothering me about how much you had to go through to shorten the list for components so I looked at your code for days and came up with an less code way with basically just a SQL row source for cboComponent that you were using as VBA code in the form and some refreshing the subfom by using:

Forms!frm_UpdateProductComponentsParts!sfrm_Update ProductComponentsParts.Form!cboComponent.Requery

Because me.requery wouldnt work. Anyways, I posed the example if anyone want to look.

I do however want to do one more thing, I want to stop the allow additions once there isn't any choices left in the combo box... Ideas?
Attached Files
File Type: zip F3 V3.zip (92.8 KB, 5 views)
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 08-31-2019, 03:18 AM   #12
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,744
Thanks: 28
Thanked 522 Times in 495 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Hide data in combo box that has been previously selected

Check the recordcount of the combo. If 0 set additions to false
MajP is offline   Reply With Quote
Old 08-31-2019, 08:33 AM   #13
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 491
Thanks: 16
Thanked 42 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Hide data in combo box that has been previously selected

Quote:
Originally Posted by MajP View Post
Check the recordcount of the combo. If 0 set additions to false
how would I get the recordcount of a combobox? I tried below with no luck

Code:
Private Sub cboComponent_Dirty(Cancel As Integer)
If cboComponent.RecordsetClone.RecordCount > 1 Then
  Cancel = True
Else
  Me.AllowAdditions = False
End If
End Sub
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 09-05-2019, 06:37 AM   #14
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 491
Thanks: 16
Thanked 42 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Hide data in combo box that has been previously selected

MajP, the below works but only if I'm entering all the data and using every option in cboComponent. If I close the form and reopen it shows the extra blank record.

Code:
Private Sub cboComponent_Dirty(Cancel As Integer)
If cboComponent.ListCount > 1 Then
  Cancel = True
Else
  Me.AllowAdditions = False
End If
End Sub
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 09-05-2019, 06:53 AM   #15
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,744
Thanks: 28
Thanked 522 Times in 495 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Hide data in combo box that has been previously selected

The easiest would be to make a query and save it, which is the same as the rowsource of the combobox. Then on the current event do a dcount on that query. If not the timing to get the comb listcount would be difficult.
Code:
SELECT tbl_Components.ComponentID, tbl_Components.Component
FROM tbl_Components
GROUP BY tbl_Components.ComponentID, tbl_Components.Component, tbl_Components.IsInactive
HAVING (((tbl_Components.ComponentID) Not In (SELECT ComponentID FROM tbl_ComponentParts WHERE ProductID= [Forms]![frm_UpdateProductComponentsParts]![cboProduct])) AND ((tbl_Components.IsInactive)=False))
ORDER BY tbl_Components.Component;

MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
oxicottin (09-10-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Display Previously Selected Value in Combo Box mmchaley Forms 11 11-16-2017 08:59 AM
Hide a Textbox When Combo is selected Dale1992 Modules & VBA 3 03-31-2016 04:26 AM
Highlight previously selected fields in listbox spudracer Modules & VBA 5 02-03-2011 06:01 AM
Updating cascading combo box dropdowns based on previously selected values Paulypaul Forms 1 06-20-2008 10:49 PM
Hide Fields Unless Combo Option is Selected andy_dyer Modules & VBA 2 07-17-2003 07:05 AM




All times are GMT -8. The time now is 12:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World