Help with VBA code on a form

[t568aort568b]

Here is my current code. It works but it is a little to slow for my
liking.
What I am trying to do is:
I have an form called ""assignments"" open. When I change the status
on the assignment form I want to change the status of the employee with
on the "employee input" form with the same EMPID#. I would like to do
this hidden so the user do not see this happening.

Thanks for the help as always,
Eric


Private Sub Status_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.Hourglass True
stDocName = "Employee Input"

stLinkCriteria = "[SocialSecurityNumber]=" &
Me![SocialSecurityNumber]
DoCmd.OpenForm stDocName, , , , acFormEdit, acHidden
Set rs = Forms(stDocName).Recordset.Clone
rs.FindFirst stLinkCriteria
If Not rs.EOF Then Forms(stDocName).Bookmark = rs.Bookmark

If (Me.Status = "filled") Then
Forms![Employee Input]!Status = "Working"
'End If

ElseIf (Me.Status = "complete") Then
Forms![Employee Input]!Status = "Avail"
'End If

ElseIf (Me.Status = "terminated") Then
Forms![Employee Input]!Status = "Do Not Use"
'End If

ElseIf (Me.Status = "cancelled") Then
Forms![Employee Input]!Status = "Avail"
'End If

ElseIf (Me.Status = "ncns") Then
Forms![Employee Input]!Status = "Do Not Use"
'End If

ElseIf (Me.Status = "went perm") Then
Forms![Employee Input]!Status = "Went Perm"
'End If

ElseIf (Me.Status = "quit") Then
Forms![Employee Input]!Status = "Do Not Use"
'End If

ElseIf (Me.Status = "open") Then
Forms![Employee Input]!Status = "Avail"

End If

DoCmd.Close acForm, "Employee Input", acSaveYes
DoCmd.OpenForm stDocName
Set rs = Forms(stDocName).Recordset.Clone
rs.FindFirst stLinkCriteria
If Not rs.EOF Then Forms(stDocName).Bookmark = rs.Bookmark
DoCmd.Minimize
DoCmd.Hourglass False


End Sub

 

[Cyberwolf]

Try using an update query. I use them all the time and they are pretty
quick. But I would set the warnings off before running and back on after
running. Something like this

Dim stDocName as string

stDocName = "qry:UpdateEmpID"

DoCmd.SetWarnings False

DoCmd.OpenQuery stDocName, acViewNormal, acEdit

DoCmd.SetWarnings True

This is just example code so don't take it literally.

HTH

 

[Klatuu]

One thing that is obviously slowing this down is using the other form to make
changes to the underlying data of that form. It would be much faster if you
use the table that provides the data for the Employee form, regardless of
whether the record source for the Employee form is a table or query.

Private Sub Status_AfterUpdate()
Dim ds As Recordset
Dim strEmpStatus As String

DoCmd.Hourglass True
Set rs = CurrentDb.OpenRecordset("EmployeeTable", dbOpenDynaset)
rs.FindFirst "[SocialSecurityNumber]=" _
& Me![SocialSecurityNumber]
If Not rst.NoMatch Then
Select Case Me.Status
Case "filled"
strEmpStatus = "Working"
Case "complete"
strEmpStatus = "Avail"
Case "terminated"
strEmpStatus = "Do Not Use"
Case "cancelled"
strEmpStatus = "Avail"
Case "ncns"
strEmpStatus = "Do Not Use"
Case "went perm"
strEmpStatus = "Went Perm"
Case "quit"
strEmpStatus = "Do Not Use"
Case "open"
strEmpStatus = "Avail"
End Select
With rs
.Edit
!Status = strEmpStatus
.Update
.Close
End With
End If
DoCmd.Hourglass False
Set rst = Nothing

End Sub

Here is my current code. It works but it is a little to slow for my
liking.
What I am trying to do is:
I have an form called ""assignments"" open. When I change the status
on the assignment form I want to change the status of the employee with
on the "employee input" form with the same EMPID#. I would like to do
this hidden so the user do not see this happening.

Thanks for the help as always,
Eric


Private Sub Status_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.Hourglass True
stDocName = "Employee Input"

stLinkCriteria = "[SocialSecurityNumber]=" &
Me![SocialSecurityNumber]
DoCmd.OpenForm stDocName, , , , acFormEdit, acHidden
Set rs = Forms(stDocName).Recordset.Clone
rs.FindFirst stLinkCriteria
If Not rs.EOF Then Forms(stDocName).Bookmark = rs.Bookmark

If (Me.Status = "filled") Then
Forms![Employee Input]!Status = "Working"
'End If

ElseIf (Me.Status = "complete") Then
Forms![Employee Input]!Status = "Avail"
'End If

ElseIf (Me.Status = "terminated") Then
Forms![Employee Input]!Status = "Do Not Use"
'End If

ElseIf (Me.Status = "cancelled") Then
Forms![Employee Input]!Status = "Avail"
'End If

ElseIf (Me.Status = "ncns") Then
Forms![Employee Input]!Status = "Do Not Use"
'End If

ElseIf (Me.Status = "went perm") Then
Forms![Employee Input]!Status = "Went Perm"
'End If

ElseIf (Me.Status = "quit") Then
Forms![Employee Input]!Status = "Do Not Use"
'End If

ElseIf (Me.Status = "open") Then
Forms![Employee Input]!Status = "Avail"

End If

DoCmd.Close acForm, "Employee Input", acSaveYes
DoCmd.OpenForm stDocName
Set rs = Forms(stDocName).Recordset.Clone
rs.FindFirst stLinkCriteria
If Not rs.EOF Then Forms(stDocName).Bookmark = rs.Bookmark
DoCmd.Minimize
DoCmd.Hourglass False


End Sub

 

[t568aort568b]

Thank Klatuu!! That works great!!! Can you recommend any good VBA
Access books?

Eric

 

[Klatuu]

My very favorite is Access2002 Desktop Developer's Handbook by Paul Litwin,
Ken Getz, and Mike Gunderloy, published by Sybex. It is the most recent
edition. There is also a companion book Access2002 Enterprise Developer's
Handbook that deals with more advanced topics. I got mine at a used book
store, but I would guess Amazon or some place like that might have it.

I'll bet you got a big performance boost from the change. You were having
to suffer the time it takes to load a form each time you made a change.