Remove item from combo list without affecting records. (1 Viewer)

Hello all,

I have a database that has been in use for a long time. It tracks project history as well as who is entering the history comment.

I have a table with employee names and a combo box in the history form that shows those names so the user can just pick his name from the list and enter in in the History comment by field. The entries in this field are limited to the combo box entries.

If I lose an employee, how can I remove him from the combo box without removing him from the employee table (I need to keep the info) and without affecting previous project history? (I would not want previous history comments to lose the name of the employee who entered the comment because I removed him from the combo box)

Thanks in advance

mafhobb

Hi. One way this can be addressed is to put in a field in the employee table. Let's call it Active for this example. Let's also suppose this is a checkbox (it could be anything - just an idea).

Then you have it checked or not-checked in the table. For the data source of your combo box (if it is a query based on the employee table), you could include the Active field with the appropriate criteria. In this example a -1 for a checked check box. This would effectively remove it from the combo box but leave it in the table.

Now, whatever method you use it will require updates to the table either directly or through a form. I am supposing you have some method of adding new employees, thus possibly editing employee information. Therefore, you can uncheck them from the editing screen when they become nonactive.

Again, just one way to do it - hope that helps.
-dK

All right, I understand the logic. I think I want to try this.

How do you do it so the combo box only shows the "active" employees. I mean, right now my combo box just lists all names that are in the employee table, but assuming that I add a check box to that table, then how do I tell to the combo box "only pick the names that are active"?

Is this some sort of query? If so, when is it run, on "click" for the combo box?

mafhobb

this is easy enough to do, as you will find - its probably just a matter of using a query rather than a table, as the record source - but the trouble is, if you adjust the cbobox source to show only active employees, you run into problems when you revisit old tasks etc, as the same cbobox will not show the originally selected employee, if he is no longer active

its a real problem, and hard to resolve successfully

One way that I have found in the past around this is something along the lines of the following.

Place an invisible control on the form and set it's bound property to the foreign key of the employee ID field. Next, use the following code on the OnCurrent() event of the form.


This probably goes against many no-no's but something like this worked for me in the past. I am definitely sure there is a better way to do this without possibly violating any rules.

What this does is change the recordsource of the combo box depending on if the checkbox is checked or not. What it will not allow you to do is to 'remove' an inactive employee from a record because the combo box will only populate with their name. If the record is populated with an active employee or is a new record, then all active employees will be in the combo box.

For the definitions so you can appropriately substitute ...
Inactive = yes/no field in employee table (checked if inactive employee)
tEmployee = employee table name
pkEmployeeID = primary key of the employee table
txtfkEmployeeID = invisible control on the form (w/ combo box) that has the foreign key of the employee ID
EmpName = employee name
cbofkEmployeeID = combo box name for the fkEmployeeID

Other parameters are that the combo box is bound to fkEmployeeID and it's rowsource is empty.

Hope that helps ...
-dK

DK - i agree - i think you need to do something like that

it was a note to the OP that solving the issue may well introduce another issue

You might also look into how you are using the forms. A Have a DB where users, salesman, add new jobs. So there are two ways to open the form, to add a job, or view jobs. In the view of course you want to see salesman no longer active. So, I tie the combo box to the way the form is used.

gemma-the-husky said:

DK - i agree - i think you need to do something like that

it was a note to the OP that solving the issue may well introduce another issue

Click to expand...

Yah - an excellent and very good point I overlooked. I forgot that it would introduce the other issue for 'archived' records. Felt bad about forgetting so figured I would try and give a total solution.

-dK

I'm dealing with what sounds to be the very same issue.

I have a time and billing db. The timesheet has subforms for billable hours, expenses and non-charge hours. What I would like to happen is that once a job is completed it is not listed in the combo box on the billable hours and expenses. Both these forms are populated from a query.

In the query I can set criteria for the job status as <>9 and <> 10 and it filters out those jobs matching that criteria. Problem is that in prior timesheets where those jobs had hours and/or expenses the job name doesn't appear. The data is still there but you don't know who it pertains to.

I've used the examples above modifying them to fit but all I get now is a combo with nothing in the list at all. All previous timesheets are blank and then nothing shows up in the combo to enter new data.

The RowSource for the subform is:
SELECT [JobNameSQ].[Employer], [JobNameSQ].[FundID], [JobNameSQ].[JobName], [JobNameSQ].[JobID], [JobNameSQ].[Status] FROM JobNameSQ ORDER BY [Employer], [FundID];

Here is the VB code for the subform On Open:
Private Sub Form_Open(Cancel As Integer)
Dim strSQL1 As String
Dim strSQL2 As String
strSQL1 = "SELECT [JobNameSQ].[Employer], [JobNameSQ].[FundID], " & _
"[JobNameSQ].[JobName], [JobNameSQ].[JobID],[JobNameSQ].[Status] " & _
"FROM JobNameSQ WHERE (((JobNameSQ.Status)<>9 And <>10)) ORDER BY [Employer], [FundID];"
strSQL2 = "SELECT JobNameSQ.EmpID, JobNameSQ.Employer FROM JobNameSQ ORDER BY [Employer], [FundID];"
If Form.AllowEdits = True Then
Me.JobIDCombo.RowSource = strSQL1
Else
Me.JobIDCombo.RowSource = strSQL2
End If
End Sub


It would be very useful to be able to filter the completed jobs so that hours can't be inadvertantly posted to them but I'd also like to be able to view previous timesheets and be able to see which job the hours/expenses were billed to.

Please keep in mind that I am an extreme novice with Access, I'm getting old, my head is thick and the brain no so nimble anymore. Bottom line: please go slow and speak in plain English so this old dunderhead can understand.

Thanks for any help.

I am thinking that you are not using some sort of refresh or requery method after you set the row source. You see, just because you set the row source doesn't mean it has done anything anything with it (or is it did?)

You bring up another good point which I also failed to annotate. In my code, I use Me.Refresh because I made assumtions and sought a blanket fix. Since the code I have is on the OnCurrent event, I needed to tell the the combo boxes to requery. I could have used Me.cboName.Requery to same effect - but because of assumptions, I wasn't sure of what else is going on with the user's application so just refreshed (even though not the best example of the usage) the entire form.

If you use active navigation buttons to go from record to record, I would suggest you look at the requery or refresh method and that should take care of your issue. However, if for instance, you filter each form for one record only - then I would move the code to the OnLoad() event.

-dK

I tried using both refresh and requery and neither seems to work but then I'm not even sure I put them in the right place.

I put refresh on form load and again in On Click for the control source. That didn't work.

I then place the requery, Me.JobIDCombo.Requery after each SELECT statement.

Any chance you can get me clue on which path I need to be heading down?

I am not sure where you are placing the code - so try the requery right after your End If and hopefully that will handle all cases. It will all (all of the code, not just the requery) need to stay in the OnCurrent() if you flip from record to record in the same form.

Recall that you've told the combo which SQL string to use (=), but you haven't told it to do anything with it. So to save a step, just wait until the logic decides which string to use and then force it to requery after the decision.

-dK

WHERE (((JobNameSQ.Status)<>9 And <>10))
Is that correct? Status equals 2 values?

Yes, that is correct. Jobs that either completed (9) or cancelled (10) I don't want displayed in the Employer control of the subform.

My thought behind that is that once a job is either completed or has been cancelled, no more billable charges (hours/expenses) should be posted to it. The only way I can see to prevent that is to filter the list.

I can get it to filter in the query no problem but if I do that, then those job names do not display in the time sheets, the Employer control is empty although the hours/expenses are still there.

Does that make sense?

WHERE (((JobNameSQ.Status)<>9 And <>10))
Your statement says AND...not OR .....
They would need to be both 9 and 10 to pull in here...

CEH said:

WHERE (((JobNameSQ.Status)<>9 And <>10))
Your statement says AND...not OR .....
They would need to be both 9 and 10 to pull in here...

Click to expand...

I'm not sure I follow you here. I don't want the Employer/Job name to display if the status is equal to 9 or 10. When I use OR as the criteria in the query itself, nothing is filtered. If I used AND, then the Employer/Jobs that have a status of either 9 or 10 do not display and therefore are not available in the list which is what I want.

The problem with doing that in the query though is that these Employer/Jobs don't display on previous timesheets but I would them to display the name.

Are you saying that in the OnCurrent event for the subforms it should be OR?

Okay .... what is "Employer/Job name to display"?

You don't want the control to be displayed at all? Or you don't want them to be displayed in the combo box?

If the previous, then it has nothing to do with the query of the combo box. You can do a simple DLookup or whatever and set the .Visible = False in your IfThen statement and exit the statement and avoid the SQL bits for the combo box.

Also, be sure to test for new records to make sure whatever setup you have works for new records. This is because new records will never be satisfy your logic because the query will return a null record set.

-dK

I think your where clause needs to be OR, something like.....
(JobNameSQ.Status)<>9 OR (JobNameSQ.Status)<>10

CEH:

Well, that got me closer however if I use the OR statement nothing is filtered. But if I change it to AND, then those jobs meeting that criteria aren't available in the combobox list which is what I want but for one small item; the Employer (or job) field is blank on prior period timesheets.

I've attached a screenshot to show you what I'm referring to. You'll notice on the 7th record down the Employer field is blank although there was .6 hours of review time entered.

This particular record was for Ashford Electric which was completed during the 2/15/09 pay period. What I'm hoping to accomplish is that for any and all entries on or before that date, Ashford Electric would still show up but it would not be available in the combobox list once completed.

Does that make any sense?

Here's the code as I now have it in TimeHrsSfrm. Do I even have it in the right spot? Putting it in OnCurrent has the same effect.

Private Sub Form_Load()

Dim strSQL1 As String
Dim strSQL2 As String
strSQL1 = "SELECT [JobNameSQ].[Employer], [JobNameSQ].[FundID], " & _
"[JobNameSQ].[JobName], [JobNameSQ].[JobID],[JobNameSQ].[Status] " & _
"FROM JobNameSQ WHERE (((JobNameSQ.Status)<>9 AND (JobNameSQ.Status)<>10)) " & _
"ORDER BY [Employer], [FundID];"

strSQL2 = "SELECT JobNameSQ.EmpID, JobNameSQ.Employer FROM JobNameSQ ORDER BY [Employer], [FundID];"
If Form.AllowEdits = True Then
Me.JobIDCombo.RowSource = strSQL1
Else
Me.JobIDCombo.RowSource = strSQL2
End If
Me.JobIDCombo.Requery
End Sub


As I mentioned, using AND instead of OR filters the list but ends up doing a bit more than I want.

I appreciate your help and patience.

Well, as I said when I posted this code, mine is a little different. I use 2 buttons to open a form... One to add new records, this would be allow edits = true, the other to view only, allow edits = False... I am not sure how you are opening this form... Also your using 07.... I'm still developing the majority of my apps in 03. Someone with a bit more SQL knowledge might chime in here and take a look....