Combo box items changed to continue to show previous field (1 Viewer)

moodhi · Dec 9, 2018

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?

theDBguy · Dec 9, 2018

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...

moodhi · Dec 9, 2018

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 · Dec 9, 2018

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" ???

theDBguy · Dec 9, 2018

moodhi said:
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.

June7 · Dec 9, 2018

I am sure the demo will clarify. But I also answered in your duplicate thread https://access-programmers.co.uk/forums/showthread.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.

CJ_London · Dec 9, 2018

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/forums/showthread.php?t=275155&highlight=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]

moodhi · Dec 9, 2018

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 · Dec 9, 2018

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.

theDBguy · Dec 9, 2018

Hi moodhi,

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

Combo box items changed to continue to show previous field (1 Viewer)

moodhi

Registered User.

theDBguy

I’m here to help

moodhi

Registered User.

moodhi

Registered User.

theDBguy

I’m here to help

June7

AWF VIP

CJ_London

Super Moderator

moodhi

Registered User.

moodhi

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread