Can't call a subprocedure even when it's Public

  • Thread starter Thread starter EAB1977
  • Start date Start date
E

EAB1977

Hi guys,

I have a form where users input data into a form. I created another
form, using a listbox, to show the user some of the data that is
already entered (RecordID, date, person, print name is all). I did it
this way because there was no room on my input form to place the
listbox.

Anyway, when I select a item in my listbox to see what the user
entered in the for a particular entry, I get a compile error: variable
not defined. Here is my code:

Private Sub btnViewData_Click()
On Error GoTo Err_btnViewData_Click

frmAnalysis2.ViewExistingData

Exit_btnViewData_Click:
Exit Sub

Err_btnViewData_Click:
MsgBox Err.Description
Resume Exit_btnViewData_Click
End Sub

---------------------------------------------------------------

Public Sub ViewExistingData()

Dim db As DAO.Database, rstData As DAO.Recordset, rstPlant As
DAO.Recordset
Dim rstPrint As DAO.Recordset, frm As Form

On Error GoTo ErrorHandler
Set db = CurrentDb
Set rstData = db.OpenRecordset("SELECT * FROM tblData WHERE
tblData.RecordID = " & Forms!frmExistingData!
lstExistingData.Column(0))

Set rstPlant = db.OpenRecordset("SELECT dbo_Plant.Name FROM dbo_Plant
INNER JOIN tblData" _
& " ON dbo_Plant.Code = tblData.Plant
WHERE tblData.RecordID = " _
& Forms!frmExistingData!
lstExistingData.Column(0))

Set rstPrint = db.OpenRecordset("SELECT
dbo_vwProductPlateAttribute.LLDSCP FROM tblData INNER JOIN" _
& " dbo_vwProductPlateAttribute ON tblData.
[Print Name] =" _
& " dbo_vwProductPlateAttribute.LLUPRD
WHERE tblData.RecordID = " & Forms!frmExistingData!
lstExistingData.Column(0))

Set frm = Forms!frmAnalysis2

With rstData
Stop
Me.datDate.Value = !Date
Me.datWeekEndDate.Value = !WeekEndDate
Me.cboPlant.Value = rstPlant!Name
Me.cboProdCode.Value = ![Product Code]
Me.cboPrintName.Value = rstPrint!LLDSCP
Me.txtPrinterNum.Value = ![Printer Number]
Me.txtPrintAlert.Value = ![Print Alert]
Me.txtTotalInsp.Value = ![Total Inspections]
Me.txtCupQuality.Value = ![Cup Quality]
Me.txtWetInk.Value = ![Wet Ink Ink Inside]
Me.txtHgtLapGap.Value = ![Height Gap Lap]
Me.txtColor.Value = !Color
Me.txtColorVar.Value = ![Color Variation]
Me.txtSmearing.Value = !Smearing
Me.txtBuildup.Value = !Buildup
Me.txtSpeckling.Value = !Speckling
Me.txtBlanketMarks.Value = ![Blanket Marks]
Me.txtRegister.Value = !Register
Me.txtInkOutside.Value = ![Ink Outside of Print Area]
Me.txtBeadBlanket.Value = ![Bead on Blanket]
Me.txtOther.Value = !Other
Me.txtGhosting.Value = !Ghosting
Me.txtMissingPrint.Value = ![Missing Print]
Me.txtWaterSpots.Value = ![Water Spots]
Me.txtScaling.Value = !Scaling
Me.txtFillingIn.Value = ![Filling In]
Me.txtWrongColor.Value = ![Wrong Color]
Me.txtUVPostExp.Value = ![UV Post Expansion]
Me.txtComments.Value = !Comments
Me.txtCaseCount.Value = ![Case Count]
Me.txtSleeveCount.Value = ![Sleeve Count]
Me.txtCaseCodeID.Value = ![Case Code ID]
End With

ErrorHandler_Exit:
Set rstPlant = Nothing
Set rstData = Nothing
Set rstPrint = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Description, , "Error " & Err.Number
Resume ErrorHandler_Exit

End Sub
 
EAB1977 said:
Hi guys,

I have a form where users input data into a form. I created another
form, using a listbox, to show the user some of the data that is
already entered (RecordID, date, person, print name is all). I did it
this way because there was no room on my input form to place the
listbox.

Anyway, when I select a item in my listbox to see what the user
entered in the for a particular entry, I get a compile error: variable
not defined. Here is my code:

Private Sub btnViewData_Click()
On Error GoTo Err_btnViewData_Click

frmAnalysis2.ViewExistingData

Exit_btnViewData_Click:
Exit Sub

Err_btnViewData_Click:
MsgBox Err.Description
Resume Exit_btnViewData_Click
End Sub

---------------------------------------------------------------

Public Sub ViewExistingData()

Dim db As DAO.Database, rstData As DAO.Recordset, rstPlant As
DAO.Recordset
Dim rstPrint As DAO.Recordset, frm As Form

On Error GoTo ErrorHandler
Set db = CurrentDb
Set rstData = db.OpenRecordset("SELECT * FROM tblData WHERE
tblData.RecordID = " & Forms!frmExistingData!
lstExistingData.Column(0))

Set rstPlant = db.OpenRecordset("SELECT dbo_Plant.Name FROM dbo_Plant
INNER JOIN tblData" _
& " ON dbo_Plant.Code = tblData.Plant
WHERE tblData.RecordID = " _
& Forms!frmExistingData!
lstExistingData.Column(0))

Set rstPrint = db.OpenRecordset("SELECT
dbo_vwProductPlateAttribute.LLDSCP FROM tblData INNER JOIN" _
& " dbo_vwProductPlateAttribute ON tblData.
[Print Name] =" _
& " dbo_vwProductPlateAttribute.LLUPRD
WHERE tblData.RecordID = " & Forms!frmExistingData!
lstExistingData.Column(0))

Set frm = Forms!frmAnalysis2

With rstData
Stop
Me.datDate.Value = !Date
Me.datWeekEndDate.Value = !WeekEndDate
Me.cboPlant.Value = rstPlant!Name
Me.cboProdCode.Value = ![Product Code]
Me.cboPrintName.Value = rstPrint!LLDSCP
Me.txtPrinterNum.Value = ![Printer Number]
Me.txtPrintAlert.Value = ![Print Alert]
Me.txtTotalInsp.Value = ![Total Inspections]
Me.txtCupQuality.Value = ![Cup Quality]
Me.txtWetInk.Value = ![Wet Ink Ink Inside]
Me.txtHgtLapGap.Value = ![Height Gap Lap]
Me.txtColor.Value = !Color
Me.txtColorVar.Value = ![Color Variation]
Me.txtSmearing.Value = !Smearing
Me.txtBuildup.Value = !Buildup
Me.txtSpeckling.Value = !Speckling
Me.txtBlanketMarks.Value = ![Blanket Marks]
Me.txtRegister.Value = !Register
Me.txtInkOutside.Value = ![Ink Outside of Print Area]
Me.txtBeadBlanket.Value = ![Bead on Blanket]
Me.txtOther.Value = !Other
Me.txtGhosting.Value = !Ghosting
Me.txtMissingPrint.Value = ![Missing Print]
Me.txtWaterSpots.Value = ![Water Spots]
Me.txtScaling.Value = !Scaling
Me.txtFillingIn.Value = ![Filling In]
Me.txtWrongColor.Value = ![Wrong Color]
Me.txtUVPostExp.Value = ![UV Post Expansion]
Me.txtComments.Value = !Comments
Me.txtCaseCount.Value = ![Case Count]
Me.txtSleeveCount.Value = ![Sleeve Count]
Me.txtCaseCodeID.Value = ![Case Code ID]
End With

ErrorHandler_Exit:
Set rstPlant = Nothing
Set rstData = Nothing
Set rstPrint = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Description, , "Error " & Err.Number
Resume ErrorHandler_Exit

End Sub
Click to expand...


It's a little hard for me to tell which block of code is on which form, and
which form is which. perhaps you could clarifyt that? But this line of
code:
frmAnalysis2.ViewExistingData
Click to expand...

in btnViewData_Click() doesn't show any indication that "frmAnalysis2" is a
variable that is defined and set anywhere. Did you mean it to be a direct
form reference, in which case it should have been like this:

Forms!frmAnalysis2.ViewExistingData

?

Also, in your ViewExistingData() procedure, I see this line setting a Form
object variable:
Set frm = Forms!frmAnalysis2
Click to expand...

But I don't see anywhere that variable is used. That wouldn't cause an
error, but it seems pointless.
 
Back
Top