Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-08-2018, 04:27 PM   #1
moodhi
Newly Registered User
 
Join Date: Oct 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
moodhi is on a distinguished road
Combo box items changed to continue to show previous field

I have a datasheet form with combo box containing roles for employees e.g.
Role Name 01
Role Name 02
Role Name 03

Therefore, Combox RowSource = "Role Name 01, Role Name 02, Role Name 03" form table which has ID, RoleNames.

The combo box is bound to the database table.

The user creates 3 records for each of the Role above. On reading back the field shows each of the 3 Roles.
Later the employee's role is reduced by one. He/She now has 2 roles - Role Name 01 and Role Name 03. i.e. the combo box now has 2 items and not 3 as originally.
On reading back the 2 records 2 records containing the value Role Name 01 and Role Name 03 show up fine the field of the record containing the value Role Name 02 is blank, obviously because the combo box does not have this item any more.
Can someone please advise what is the best way for the previously recorded fields to still show up even thought the item does not exist in the combo box?

moodhi is offline   Reply With Quote
Old 12-08-2018, 04:30 PM   #2
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 235
Thanks: 3
Thanked 37 Times in 36 Posts
theDBguy is on a distinguished road
Re: Combo box items changed to continue to show previous field

Hi moodhi,

Not necessarily the "best" approach but one of them is to overlay a textbox on top of the combobox bound to the same field as the combo to display what is actually in the table, rather than what is in the row source of the combobox.

Other approaches are available from the "Continuous Form Combo" demo on Leigh's website.

Hope it helps...
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 12-08-2018, 05:13 PM   #3
moodhi
Newly Registered User
 
Join Date: Oct 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
moodhi is on a distinguished road
Re: Combo box items changed to continue to show previous field

Apologies but the bound field contains the ID and the actual text is displayed form the combo box so I am not sure how will that work?

Also how will that work? does that mean to set combo box visible to false? then how will the user select an item from the combo box?

I am sorry I am a little confused, can you provide more info please?

moodhi is offline   Reply With Quote
Old 12-08-2018, 05:27 PM   #4
moodhi
Newly Registered User
 
Join Date: Oct 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
moodhi is on a distinguished road
Re: Combo box items changed to continue to show previous field

Sorry, I failed to mention that the the combo box RowSource = ID, RoleName
e.g.
1 Role Name 01
2 Role Name 02
3 Role Name 03

So the database field values are:
1
2
3

The next time as I said the combo box RowSource = ID, Role Name
i.e.
1 Role Name 01
3 Role Name 03

If I overlay textbox on top of the combo it which is bound to the database field then it will show values 1 and 3 but not "Role Name 01" and Role Name 03" ???
moodhi is offline   Reply With Quote
Old 12-08-2018, 05:56 PM   #5
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 235
Thanks: 3
Thanked 37 Times in 36 Posts
theDBguy is on a distinguished road
Re: Combo box items changed to continue to show previous field

Quote:
Originally Posted by moodhi View Post
Apologies but the bound field contains the ID and the actual text is displayed form the combo box so I am not sure how will that work?

Also how will that work? does that mean to set combo box visible to false? then how will the user select an item from the combo box?

I am sorry I am a little confused, can you provide more info please?
Hi moodhi,

If you download the demo from Leighís website, Iím sure it will do a better job of explaining to you how to do it than me trying to write it out. Essentially though, you overlay a textbox on top of the combo, so the user sees the textbox instead of the conbo. However, you donít cover the drop down arrow, so they can still make a selection.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 12-08-2018, 06:17 PM   #6
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 960
Thanks: 0
Thanked 217 Times in 217 Posts
June7 will become famous soon enough
Re: Combo box items changed to continue to show previous field

I am sure the demo will clarify. But I also answered in your duplicate thread https://access-programmers.co.uk/for...d.php?t=302791

To repeat:

This is a widely-discussed issue of cascading comboboxes using alias on Continuous or Datasheet form. There is no really clean way to handle. Could include the lookup table in the form RecordSource then bind a textbox to the descriptive field that sits next to the combobox. Lock the textbox and set TabStop to no.

In Continuous view, can set textbox on top of combobox.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 12-09-2018, 01:51 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Combo box items changed to continue to show previous field

not quite sure what you are saying, but this link shows how to apply cascading combo's when the selection criteria is changed

https://www.access-programmers.co.uk...ight=cascading

If you are saying that an employee has three roles selected from a role table and stored in a linking table (many to many relationship) and subsequently one of the roles from the role table is deleted, but the linking table is not then that is another matter and should be governed by your relationship rules.

To get round it perhaps would be to have something like this for your combo rowsource.

Code:
 
SELECT LinkTable.ID, nz(RoleTable.Role,"Role Deleted") as RName
FROM LinkTable LEFT JOIN RoleTableoN LinkTable.RoleID=RoleTable.RoleID
WHERE LinkTable.EmpID=[Forms]![myForm]![EmpID]

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 12-09-2018, 03:26 AM   #8
moodhi
Newly Registered User
 
Join Date: Oct 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
moodhi is on a distinguished road
Re: Combo box items changed to continue to show previous field

OK, I followed the demo and it makes sense, however my form's record source was the table itself but now when I have added the textbox to overlay combo box I need to bind the textbox to the Role Name for which I changed my form's record source to a query which contain the Role Name (text). It seems to work but my form is not editable any more and I am not getting the New Row to enter new records. Do you know how to make the form editable ?
moodhi is offline   Reply With Quote
Old 12-09-2018, 03:32 AM   #9
moodhi
Newly Registered User
 
Join Date: Oct 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
moodhi is on a distinguished road
Re: Combo box items changed to continue to show previous field

Apologies again guys. As I am using linked tables to the BackEnd database, for some reason one of my table did not have the primary key, adding back the primary key its now working. Many thanks.
moodhi is offline   Reply With Quote
Old 12-09-2018, 06:41 AM   #10
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 235
Thanks: 3
Thanked 37 Times in 36 Posts
theDBguy is on a distinguished road
Re: Combo box items changed to continue to show previous field

Hi moodhi,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Invoice report problem, report show items from previous invoices. saltyseadog Reports 1 04-16-2013 07:30 AM
Make a combo box only show based on a previous combo box entry... davida1992 Forms 13 03-15-2013 04:48 PM
Search form Code to show Select Items and also Same from Previous YearTable Trevor G Modules & VBA 1 04-18-2012 05:01 AM
Show multiple items in combo box SteveClarkson Forms 1 03-12-2004 08:26 AM




All times are GMT -8. The time now is 05:35 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 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World