workorder has been approved

[Christopher Glaeser]

When a customer approves a workorder, the code below is used to create an
approval record for that workorder. The code first checks to determine if
the appoval record for the workorder already exists, and if not, creates a
new approval record for that workorder.

If IsNull(DLookup("[intApprovalID]", "tblApprovals", "[intWorkOrderId] =
" & Me.WorkOrderID)) Then
CreateApproval Me.WorkOrderID
Else
DoCmd.OpenForm "frmApproval", , , "[intWorkOrderID]=" &
Me.WorkOrderID
End If

This code is pretty close to working, but does not check to see if
frmApproval is already open for an unrelated workorder, in which case this
code overwrites an unrelated approval record with information from the new
workorder approval. Suggestions for correcting this code fragment are much
appreciated.

Best,
Christopher

 

[strive4peace]

Hi Christopher,

what triggers the code you posted to run?

What is code for
CreateApproval
?

you can test to see if the form is open and save its record before you
do your DLookup...

'~~~~~~~~~~~~~~~~~~~~
if currentproject.allforms("frmApproval").IsLoaded then
'save record if it has been changed
if forms!frmApproval.dirty then
forms!frmApproval.dirty = false
'refresh changes made by other processes
currentdb.tabledefs.refresh
DoEvents
end if
'if you want to switch them to that form...
docmd.selectobject acform, "frmApproval"
else
'whatever if it is not loaded

end if

'... then do the dLookup if you want to...
'~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Christopher Glaeser]

what triggers the code you posted to run?

A button on the workorder.

you can test to see if the form is open and save its record before you do
your DLookup...

Thanks!

Best,
Christopher

 

[strive4peace]

you're welcome, Christopher happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Christopher Glaeser]

What is code for CreateApproval

Here is the code for CreateApproval.

Sub CreateApproval(WorkOrderID As Integer)
DoCmd.OpenForm "frmApproval", DataMode:=acFormAdd
Forms!frmApproval!intWorkOrderID = WorkOrderID
Forms!frmApproval.NavigationButtons = False
End Sub

The OpenForm with acFormAdd does not work the way I expected. If
frmApproval is already open, acFormAdd does not create a new approval
record. Instead, the existing record is overwritten. What is the code to
ensure a new record is created? Does the form need to be saved and/or
closed first, and then use OpenForm?

Best,
Christopher

 

[strive4peace]

Hi Christopher,

putting it all together... try this behind your Work Order form :

'~~~~~~~~~~~~~~~~~~~~
dim mRecordID as long

'save current Work Order if changes have been made
if me.dirty then me.dirty = false

if me.newRecord then
Msgbox "You do not have a current Work Order",, _
"Cannot create Approval Record"
exit sub
end if

if currentproject.allforms("frmApproval").IsLoaded then
'save record if it has been changed
if forms!frmApproval.dirty then
forms!frmApproval.dirty = false
'refresh changes made by other processes
'may not need to do this
currentdb.tabledefs.refresh
DoEvents
end if
else
DoCmd.OpenForm "frmApproval"
endif

mRecordID = nz(DLookup("[ApprovalID_fieldname]", "tblApprovals", _
"[ApprovalID_fieldname] = " & Me.WorkOrderID_controlname),0)

If mRecordID > 0 then
'record was found -- move to it

forms!frmApproval.RecordsetClone.FindFirst _
"ApprovalID_fieldname = " & mRecordID

If Not forms!frmApproval.RecordsetClone.NoMatch Then
forms!frmApproval.Bookmark = _
forms!frmApproval.RecordsetClone.Bookmark
else
'there must be a filter set -- remove it
'and look for the record again
'actually, if you think there will usually be a filter,
'these next 2 lines could be moved
'to right after the record is saved
'before the first Findfirst...
forms!frmApproval.FilterOn = false
forms!frmApproval.requery

forms!frmApproval.RecordsetClone.FindFirst _
"ApprovalID_fieldname = " & mRecordID

If Not forms!frmApproval.RecordsetClone.NoMatch Then
forms!frmApproval.Bookmark = _
forms!frmApproval.RecordsetClone.Bookmark
end if

End If
else
'record was not found -- create new
forms!frmApproval.AddNew
Forms!frmApproval.WorkOrderID_fieldname = Me.WorkOrderID_controlname
Forms!frmApproval.NavigationButtons = False
end if

'you only need to do this if it was already open
'you can set a boolean variable to determine
'if this needs to be done

docmd.selectobject acform, "frmApproval"

'may need next line -- uncomment if data doesn't show
'DoEvents

'~~~~~~~~~~~~~~~~~~~~

Naturally, you will have to check all the control and field references
and use the names that you have assigned.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*