ComboBox Requery doesn't Work

[hascons]

Hello,

I have an unbound Employee Time Card form that Inserts many records at once. I use a combobox to display the active employees. I'm trying to create a macro that would set a true / False field to true (in Employees table) after this employees records have been added. Once the employee records have been added I would like to have this persons name removed from the combobox.

The Following code does set the True / field to true, but after requerying the combobox it does not remove name from list. If I close form then reopen the name is removed from the list.

Is there another event that is more suited for this?

Private Sub ClearcboEmployee()
Dim strCriteria As String

rstEmployees.MoveFirst
strCriteria = "EmployeeID = " & "'" & Me.cboEmployee & "'"
rstEmployees.Find strCriteria, 0, adSearchForward

If Not (rstEmployees.BOF And rstEmployees.EOF) Then
With rstEmployees
![EmployeeTemporaryInactive] = True
.Update
End With
End If

Me.cboEmployee = ""
Me.cboEmployee.Requery
End Sub

 

[Poppa Smurf]

If the record source for your combo box a query, set the criteria for the field EmployeeTemporaryInactive in the query to false then your requery should work.

 

[hascons]

Poppa Smurf,

The ComboBox recordsource is a query and currently the (EmployeeTemporaryInactive) Field is set to false to filter out just active employees. One more item, An Active employee in my table is one in which there is a Field (DateFinished) is Null. So my query checks that both (DateFinished) is Null and (EmployeeTemporaryInactive) Is False. I only use the (EmployeeTemporaryInactive) to temporarily remove the Employee from the ComboBox List. It seems as though my code should work, All Macros are enabled.

 

[vbaInet]

Is the True/False field a Yes/No datatype?

 

[hascons]

VBAInet,

Yes the True/False field is a Yes/No Datatype

 

[vbaInet]

Can we see the sql statement of your combo box's row source?

 

[hascons]

VbaInet,

Here is the sql statement

SELECT [Employees Extended].EmployeeID, [Employees Extended].[Employee Name], [Employees Extended].EmployeeRateClassID, [Employees Extended].EmployeeDateTerminated, [Employees Extended].EmployeeTemporaryInactive FROM [Employees Extended] WHERE ((([Employees Extended].EmployeeDateTerminated) Is Null) AND (([Employees Extended].EmployeeTemporaryInactive)=False)) ORDER BY [Employees Extended].[Employee Name];

 

[vbaInet]

Ok, I've just had a look at your code and it's not fully there. You need to Edit the recordset before setting the value. Have a look at this link:

http://allenbrowne.com/ser-29.html

 

[hascons]

VbaInet,

I'm Using ADO recordset, ADO does not require the Edit statement before updating. The code above does set the selected employee field (EmployeeTemporaryInactive) to true.I can manually check to verify this.

If I close the Time Card form and then reopen the Employee is removed from the list, but I would like to remove without closing form so i can ensure this Employee won't be entered twice.

 

[jal]

I don't see where you've indicated what event you are using. All you told us was:

Private Sub ClearcboEmployee()......

but in what event is this code being called?

 

[hascons]

jal,

This sub is called from a cmdSave Sub, Right after the records are saved to the underlying table, I clear the form and then call this procedure to try and reset the cboEmployee ComboBox.

 

[hascons]

Update:

I Created this same procedure using a DAO Recordset and it works fine. Does anyone know why this won't work with ADO.

Below is the module I used with DAO .(This Works)
Private Sub ClearcboEmployee()
'This sub will be used on the btnSave sub Procedure
Dim rstEmployees As DAO.Recordset
Dim rstTimeCard As DAO.Recordset
Dim strCriteria As String

Set Dbs = CurrentDb()
Set rstEmployees = Dbs.OpenRecordset("Employees")

rstEmployees.MoveFirst
strCriteria = "EmployeeID = " & "'" & Me.cboEmployee & "'"
rstEmployees.FindFirst strCriteria

If Not (rstEmployees.BOF And rstEmployees.EOF) Then
With rstEmployees
.Edit
![EmployeeTemporaryInactive] = True
.Update
End With
End If

rstEmployees.Close
Set rstEmployees = Nothing
Set Dbs = Nothing

Me.cboEmployee = ""
Me.cboEmployee.Requery
End Sub

 

[Poppa Smurf]

In your first post you did not DIM RstEmployees.

 

[hascons]

Poppa Smurf,

In My first example I Dim the rstEmployees in the Global variable Section (The Very Top), I always do this when i run several different subs or functions that will use the Variable. I do this With Either DAO or ADO.

In This situation I got this working using DAO recordset. For some reason I can't get the combobox to requery when i'm using the ADO recordset. I'm really not sure why but I'd love to find out if someone has any Info on this.

Thanks For everyones help with this. It does work Great using my last Example with DAO. I guess i will just continue to use DAO for most of my Projects.