473,320 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ComboBox Value for Inactive Records

110 100+
In a Clients table ("tblClients"), I have a ComboBox "Employee" to select from a list of Employees contained in tblEmployees. I only to display / allow employees who are "Active" in the system, however, if any employee was already selected, but is now "Inactive", I would still like them to show up / display.

Scenario:
Tom is the salesperson on client record ABC. Tom then leaves the company and is marked inactive. By marking Tom in active, he is removed from the combobox list so that he is no longer selectable. However, Client Record ABC should still display Tom but does not because he is marked inactive. I have written vba to make this work - just wondering if I can make it work in a table.

Thank you in advanced for any assistance.

Gunner
Jun 27 '20 #1

✓ answered by NeoPa

DGunner:
Thank you again - sorry if this is a wild goose chase question; just wondering if it could be done...
Actually, it's an increasingly common one. I was hoping that wasn't where you were going but it is so let's deal with it.

This feature is something that many of us more experienced users would love never to have been introduced. The Access team has good reason for introducing it as it helps novices to better understand the sorts of things that can be done, but while it's an easy introduction, it is also something that can lead to complications & frustrations further down the line. My strongly held belief is that you are a lot better off without it. Once you start to know what you're doing you leave the table design as just that - table design. User interaction with the data is what Forms are for. However, I've already covered that approach in the earlier reply.

In the table design itself you don't have the same capabilities that are available to you in a Form. There are no Event Procedures that allow you to make changes on the fly when you move from one record to another. That's not a surprise. Table designs were never created to support user interaction beyond the basics.

So, what can you do?

In your position I would accept that direct configuration of the SQL is not possible so I would include all records in your SQL, but I would also include an extra column that shows [txtStatus] as well. Idiot users should never have access to the raw data anyway. If you plan to give access to anyone that can't be trusted to use it properly then more fool you. For users that can be trusted this should be perfectly adequate. Never select an item that has a value other than 'Active' in that column.

This approach should not be considered to be adequate in the standard user interface. In a Form do it as shown earlier. However, you should be safe to use it solely with those responsible enough to have direct access to the raw table data.

PS. Now I've reformatted your post I can see this is pretty much what you already have so I just have to say I don't believe there's a better way than what you're already using. Not in the table design.

4 2662
NeoPa
32,556 Expert Mod 16PB
Hi Gunner.

You talk about a ComboBox in your table. Technically, though you can have fields in your table set up to behave as if they are a ComboBox, a ComboBox is a Control - not a Field. Controls are found on Forms & Reports. I'll assume you're actually talking about a ComboBox Control on a Form.

So, where do we start. With the setup you already have of your ComboBox Control. Unfortunately not shared yet. That will delay matters. For now I can only say generally that your RowSource is likely to be something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [tblEmployees]
  3. WHERE  ([Active])
  4.    OR  ([EmployeeID]=Forms!YourForm!txtEmpID)
Does that start to make sense?
Jun 27 '20 #2
dgunner71
110 100+
As always, thank you, NeoPa - I actually was referring to the table (if possible.)

For the field in question, on the lookup tab in table design:
I have the Display Control type set to: ComboBox
The Row Source Type set to: Table/Query
The Row Source set to:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmployees.AID
  2.      , tblEmployees.txtLastName
  3.      , tblEmployees.txtStatus
  4. FROM   tblEmployees
  5. WHERE  (((tblEmployees.txtStatus)="Active"))
The question comes in where the employee was Active when selected but no longer active at a future point in time.

This may not even need to be done in the table as I can use code in the actual form.

Thank you again - sorry if this is a wild goose chase question; just wondering if it could be done...

Gunner
Jun 27 '20 #3
NeoPa
32,556 Expert Mod 16PB
DGunner:
Thank you again - sorry if this is a wild goose chase question; just wondering if it could be done...
Actually, it's an increasingly common one. I was hoping that wasn't where you were going but it is so let's deal with it.

This feature is something that many of us more experienced users would love never to have been introduced. The Access team has good reason for introducing it as it helps novices to better understand the sorts of things that can be done, but while it's an easy introduction, it is also something that can lead to complications & frustrations further down the line. My strongly held belief is that you are a lot better off without it. Once you start to know what you're doing you leave the table design as just that - table design. User interaction with the data is what Forms are for. However, I've already covered that approach in the earlier reply.

In the table design itself you don't have the same capabilities that are available to you in a Form. There are no Event Procedures that allow you to make changes on the fly when you move from one record to another. That's not a surprise. Table designs were never created to support user interaction beyond the basics.

So, what can you do?

In your position I would accept that direct configuration of the SQL is not possible so I would include all records in your SQL, but I would also include an extra column that shows [txtStatus] as well. Idiot users should never have access to the raw data anyway. If you plan to give access to anyone that can't be trusted to use it properly then more fool you. For users that can be trusted this should be perfectly adequate. Never select an item that has a value other than 'Active' in that column.

This approach should not be considered to be adequate in the standard user interface. In a Form do it as shown earlier. However, you should be safe to use it solely with those responsible enough to have direct access to the raw table data.

PS. Now I've reformatted your post I can see this is pretty much what you already have so I just have to say I don't believe there's a better way than what you're already using. Not in the table design.
Jun 28 '20 #4
dgunner71
110 100+
Answered my question perfectly (i.e. it's poor design to design a table this way.) Thank you!
Jun 28 '20 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Goos van Beek | last post by:
Hello. I'm using the following code to fill a combobox on a Windows form: private void FillCboRelation(){ using(SqlCommand sqlStatement = new SqlCommand("sp_RelationList",cnn)) {...
9
by: juli jul | last post by:
Hello ,I am trying to get the comboBox value but when I am writing: MessageBox.Show(this.comboBoxdb.SelectedValue.ToString()); I get the following result: System.Data.DataRowView instead of the...
2
by: Kevin Hodgson | last post by:
I have a DataBound ComboBox, which has a text value as the Value property, and the UniqueID for that value is bound to the Combobox.Tag property. When a user makes a new selection in the...
0
by: zhuang | last post by:
Hi, Adding combobox to datagrid has been posted many times. I have a datagrid which has multiple combobox columns and normal textbox columns. But how could I change other combo box values at...
1
by: leiger | last post by:
Hi, I need help with this problem as soon as possible (within a couple of days). This is the first time I have ever used Access 2007 and therefore I am having some problems - especially as I...
1
by: exxcusemee | last post by:
Hi to all, Basically my problem is that that i am trying to set the value of combobox.But problem is i dont have value of combobox.Whatever i have is the label which will render to the user...
3
by: rhepsi | last post by:
Hii all, How to join two tables and a combobox value?? i have 2 combo boxes and 2 tables i have aquery: 1) Dim myselectquery As String = "SELECT pr_project_id_pk FROM tbl_projects WHERE...
2
by: Ronald S. Cook | last post by:
At first, it's easy to say (have a policy) that we'll hard-code for statuscode='ACT' (or maybe instead statuscode <'INA') but I think that pigeonholes us and would cause limitations. Example: We...
5
by: shiwawa | last post by:
I have a problem filtering records in my subform using a ComboBox in the Main Form. I have tried options proferred in other forums and have still not git the desired result. I know I must be missing...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.