Hi all - my first post on the Access forum and I am very rusty. I'm working on our purchase order log written by somebody else a while ago (now left). I want it to email the report to an approver if a PO is raised with a particular cost centre. The single line of code runs fine in isolation when the form is open that supplies the value (Forms!PO_Standard!Combo71.Value Like "[C-FLA]) , but I just can't get it to run within the context of the rest of the macro. It looks like the form should still be open at that point.
This is how it's supposed to work: the user fills out a form (PO_Standard) then hits a button to run the code below. My line is highlighted in bold. As far as I can see, the form should still be open when the line executes, but nothing happens - it's like the line is just skipped over.
Sub doPOInsert()
Dim stDocName As String
Dim response
response = MsgBox("Are you sure all details are correct?", vbYesNo, "Confirm add new purchase order")
If response = 6 Then
Set rstOrders = New ADODB.Recordset
rstOrders.CursorLocation = adUseClient
rstOrders.Open "Select PONUMBER From [PO Database table] order by PONUMBER ", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rstOrders.EOF = True Then
Forms!PO_Standard!PONUMBER = 1
Else
rstOrders.MoveLast
Forms!PO_Standard!PONUMBER = rstOrders!PONUMBER + 1
End If
'add new record
DoCmd.GoToRecord , , acNewRec
' get ponumber and open report
rstOrders.Requery
rstOrders.MoveLast
Forms!PO_Standard!PONUMBER = rstOrders!PONUMBER
'sign-off alert
If Forms!PO_Standard!Combo71.Value Like "[C-FLA]*" Then SendMail
'open report
stDocName = "PO Standard Report early"
DoCmd.OpenReport stDocName, acPreview
rstOrders.Close
' close po entry form
DoCmd.Close acForm, "PO_Standard", acSaveNo
End If
End Sub
Any advice very welcome, and thanks in advance
Jackie
This is how it's supposed to work: the user fills out a form (PO_Standard) then hits a button to run the code below. My line is highlighted in bold. As far as I can see, the form should still be open when the line executes, but nothing happens - it's like the line is just skipped over.
Sub doPOInsert()
Dim stDocName As String
Dim response
response = MsgBox("Are you sure all details are correct?", vbYesNo, "Confirm add new purchase order")
If response = 6 Then
Set rstOrders = New ADODB.Recordset
rstOrders.CursorLocation = adUseClient
rstOrders.Open "Select PONUMBER From [PO Database table] order by PONUMBER ", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rstOrders.EOF = True Then
Forms!PO_Standard!PONUMBER = 1
Else
rstOrders.MoveLast
Forms!PO_Standard!PONUMBER = rstOrders!PONUMBER + 1
End If
'add new record
DoCmd.GoToRecord , , acNewRec
' get ponumber and open report
rstOrders.Requery
rstOrders.MoveLast
Forms!PO_Standard!PONUMBER = rstOrders!PONUMBER
'sign-off alert
If Forms!PO_Standard!Combo71.Value Like "[C-FLA]*" Then SendMail
'open report
stDocName = "PO Standard Report early"
DoCmd.OpenReport stDocName, acPreview
rstOrders.Close
' close po entry form
DoCmd.Close acForm, "PO_Standard", acSaveNo
End If
End Sub
Any advice very welcome, and thanks in advance
Jackie