Provate Sub Wont run from main menu, but will run from prior form!

[sanders720]

I have a sub that is doing everything I want it to. The problem is, when I go through the sequence of going to the reports menu and selecting the button associated with this sub from that form, nothing happens. If I open the reports form manually and proceed from there, the program works fine.

Any ideas? Thanks in advance for the help.


Private Sub cmdEReport_Click()

' **********************************************************
' Check RecordSet
MsgBox "Check RecordSet"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim sql As String

If LCase(Forms!frmReportsListing.Combo22) = &quot;<all>&quot; Then

sql = &quot;SELECT B.QTY, B.PartNo, P.PartDescription, P.Code AS Code, P.ManufacturedBy,&quot; & _
&quot; B.JobNo, B.SubAssy, B.ReleasedBy, B.ReleasedFlag, B.ReleasedDate&quot; & _
&quot; FROM tblPartsListing P INNER JOIN tblBOM B ON P.PartNo = B.PartNo&quot; & _
&quot; WHERE (((B.QTY) > 0) And ((P.Code) = &quot; & Chr(34) & &quot;Purchased&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Elec Comp&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Mech Comp&quot; & Chr(34) & _
&quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Major Comp&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Stock&quot; & Chr(34) & &quot And ((B.JobNo) = &quot; & [Forms]![frmReportsListing].[Combo8].[Value] & &quot And ((B.ReleasedFlag) = True))&quot;
' *** Too long to Order By *** &quot; ORDER BY B.SubAssy, P.ManufacturedBy, B.PartNo;&quot;

Else

sql = &quot;SELECT B.QTY, B.PartNo, P.PartDescription, P.Code AS Code, P.ManufacturedBy,&quot; & _
&quot; B.JobNo, B.SubAssy, B.ReleasedFlag, B.ReleasedBy, B.ReleasedDate&quot; & _
&quot; FROM tblPartsListing P INNER JOIN tblBOM B ON P.PartNo = B.PartNo&quot; & _
&quot; WHERE (((B.QTY) > 0) And ((P.Code) = &quot; & Chr(34) & &quot;Purchased&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Elec Comp&quot; & Chr(34) & &quot; Or (P.Code) =&quot; & Chr(34) & &quot;Purchased-Mech Comp&quot; & Chr(34) & _
&quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Major Comp&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Stock&quot; & Chr(34) & &quot And ((B.JobNo) = &quot; & [Forms]![frmReportsListing].[Combo8].[Value] & &quot&quot; & _
&quot; And ((B.SubAssy) = &quot; & Chr(34) & [Forms]![frmReportsListing].[Combo22].[Value] & Chr(34) & &quot; ) And ((B.ReleasedFlag) = True))&quot;
' *** Too long to Order By *** &quot; ORDER BY B.SubAssy, P.ManufacturedBy, B.PartNo;&quot;

End If

rs.Open sql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If rs.BOF Or rs.EOF Then GoTo Skip1

rs.Close
Set rs = Nothing

' **********************************************************
' Get Max RequisitionNumber
MsgBox &quot;Get Max RequisitionNumber&quot;

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot;
cn.Open &quot;\\deepblue\Manuals\IT Database\Requisition.mdb&quot;

sql = &quot;SELECT Max(RequisitionNumber) as Expr1 from tblRequisitions&quot;

rs.Open Source:=sql, _
ActiveConnection:=cn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
options:=adCmdText

Dim RN As Integer
RN = rs!expr1

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

' **********************************************************
' Update Max+1 RequisitionNumber with Associated Data
MsgBox &quot;Update Max+1 RequisitionNumber with Associated Data&quot;

sql = &quot;SELECT * from tblRequisitions&quot;

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot;
cn.Open &quot;\\deepblue\Manuals\IT Database\Requisition.mdb&quot;

rs.Open Source:=sql, _
ActiveConnection:=cn, _
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic, _
options:=adCmdText

rs.AddNew
rs.Fields(&quot;RequisitionNumber&quot = RN + 1
rs.Fields(&quot;DateSubmitted&quot = Date
rs.Fields(&quot;Department&quot = &quot;Engineering&quot;
rs.Fields(&quot;RequestingUser&quot = &quot;TEST&quot;
rs.Update

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

' **********************************************************
' Append Data from BOM to Requisition
MsgBox &quot;Append Data from BOM to Requisition&quot;

If LCase(Forms!frmReportsListing.Combo22) = &quot;<all>&quot; Then

sql = &quot;SELECT B.QTY, B.PartNo, P.PartDescription, P.Code AS Code, P.ManufacturedBy,&quot; & _
&quot; B.JobNo, B.SubAssy, B.ReleasedBy, B.ReleasedFlag, B.ReleasedDate&quot; & _
&quot; FROM tblPartsListing P INNER JOIN tblBOM B ON P.PartNo = B.PartNo&quot; & _
&quot; WHERE (((B.QTY) > 0) And ((P.Code) = &quot; & Chr(34) & &quot;Purchased&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Elec Comp&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Mech Comp&quot; & Chr(34) & _
&quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Major Comp&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Stock&quot; & Chr(34) & &quot And ((B.JobNo) = &quot; & [Forms]![frmReportsListing].[Combo8].[Value] & &quot And ((B.ReleasedFlag) = True))&quot;
' *** Too long to Order By *** &quot; ORDER BY B.SubAssy, P.ManufacturedBy, B.PartNo;&quot;

Debug.Print sql

' Me.RecordSource = &quot;qryMetroProcessSheets2FINAL&quot;

Else

sql = &quot;SELECT B.QTY, B.PartNo, P.PartDescription, P.Code AS Code, P.ManufacturedBy,&quot; & _
&quot; B.JobNo, B.SubAssy, B.ReleasedFlag, B.ReleasedBy, B.ReleasedDate&quot; & _
&quot; FROM tblPartsListing P INNER JOIN tblBOM B ON P.PartNo = B.PartNo&quot; & _
&quot; WHERE (((B.QTY) > 0) And ((P.Code) = &quot; & Chr(34) & &quot;Purchased&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Elec Comp&quot; & Chr(34) & &quot; Or (P.Code) =&quot; & Chr(34) & &quot;Purchased-Mech Comp&quot; & Chr(34) & _
&quot; Or (P.Code) = &quot; & Chr(34) & &quot;Purchased-Major Comp&quot; & Chr(34) & &quot; Or (P.Code) = &quot; & Chr(34) & &quot;Stock&quot; & Chr(34) & &quot And ((B.JobNo) = &quot; & [Forms]![frmReportsListing].[Combo8].[Value] & &quot&quot; & _
&quot; And ((B.SubAssy) = &quot; & Chr(34) & [Forms]![frmReportsListing].[Combo22].[Value] & Chr(34) & &quot; ) And ((B.ReleasedFlag) = True))&quot;
' *** Too long to Order By *** &quot; ORDER BY B.SubAssy, P.ManufacturedBy, B.PartNo;&quot;

Debug.Print sql

' Me.RecordSource = &quot;qryMetroProcessSheetsFINAL&quot;

End If

Set rs = New ADODB.Recordset
rs.Open sql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

rs.MoveFirst

Dim sqlA As String
sqlA = &quot;SELECT * from tblItemList&quot;

Dim cnA As ADODB.Connection
Dim rsA As ADODB.Recordset

Set cnA = New ADODB.Connection
Set rsA = New ADODB.Recordset

cnA.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot;
cnA.Open &quot;\\deepblue\Manuals\IT Database\Requisition.mdb&quot;

rsA.Open Source:=sqlA, _
ActiveConnection:=cnA, _
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic, _
options:=adCmdText

Do While Not rs.EOF
Debug.Print rs.Fields(&quot;PartNo&quot

rsA.AddNew
rsA.Fields(&quot;RequisitionNumber&quot = RN + 1
rsA.Fields(&quot;Quantity&quot = rs.Fields(&quot;QTY&quot
rsA.Fields(&quot;Manufacturer&quot = rs.Fields(&quot;ManufacturedBy&quot
rsA.Fields(&quot;MfgPartNo&quot = rs.Fields(&quot;PartNo&quot
rsA.Fields(&quot;Description&quot = rs.Fields(&quot;PartDescription&quot
rsA.Fields(&quot;JobNo&quot = rs.Fields(&quot;JobNo&quot
rsA.Fields(&quot;SubNo&quot = rs.Fields(&quot;SubAssy&quot
rsA.Update

rs.MoveNext

Loop

rsA.Close
Set rsA = Nothing

cnA.Close
Set cnA = Nothing

rs.Close
Set rs = Nothing

MsgBox &quot;Purchased Requsition Database Appended...&quot;
' Close Out Form
DoCmd.Close

' **********************************************************
' Send Verification E-Mail
MsgBox &quot;Send Verification E-Mail&quot;

GoTo proceed1

Skip1:

MsgBox &quot;No Records to Process at this time...&quot;

proceed1:

End Sub

 

[markgrizzle]

since the sub is private to the form, it can't be run from other modules. You could try moving the code to a public module to make it available elsewhere in your app, but that would mean updating all the me. references in your sample.

 

[markgrizzle]

Clicked too soon. You could make the procedure public in the same form module, and then call it from the command button click. this would make it available to other modules, but i think you'll still need the form open.

 

[sanders720]

The bottom line is, all of this is local! Could there be another issue with open recordsets or something? Off the top of your head, is anyone familiar with this problem?

Thanks for the help...