VB compile error User defined Object

[alpnz]

I am getting a Compile error on the following Code.
What have I missed

Private Sub create_eplab_Click()
' First sort out the variables. Including label content, quantity to print etc
 
 
    Dim var1 As String
    Dim var2 As String
    Dim var3 As String
    Dim var4 As String
    Dim var5 As String
 
' Sort out where the data is coming from. Aim for reusable code
 
    Dim mydb As Database, myset As Recordset
    Dim sql_rst As String
 
 
   sql_rst = "SELECT * FROM qry_elabel"
 
    Set mydb = CurrentDb
    Set myset = mydb.OpenRecordset(sql_rst)
' Declare the variables values
 
    var1 = myset![var1]
    var2 = myset![var2]
    var3 = myset![var3]
    var4 = myset![var4]
    var5 = myset![var5]
    Const quote As String = """"
 
' Code each label combination required
 
    Dim lab1 As String
 
lab1 = "N" & vbCrLf _
        & "S4" & vbCrLf _
        & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
        & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
        & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
        & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
        & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
        & "P1" & vbCrLf
 
 
' Print the label to the printer.
 
Open "COM2:" For Output As #1
       Print #1, lab1
 
Close #1
 
End Sub
 
 

It debugs to the Dim mydb As Database , myset As Recordset Line.

What am I missing here?.

John S

[alpnz]

The qry_elabel is as below.

 
SELECT PakTrak.PakTrakID, [Entity] & " - " & [RGN] AS var1, [VarietyDesc] & " " & [VGroup] AS var2, Sizing.SizeCode AS var3, "942001351234" AS var4, Runs.[RunCode] & "-" & [PakTrakID] AS var5
FROM VGroup INNER JOIN (Variety INNER JOIN (Sizing INNER JOIN ((Entity INNER JOIN Runs ON Entity.EntityID = Runs.EntityID) INNER JOIN (Packaging INNER JOIN PakTrak ON Packaging.PackageingID = PakTrak.PackageingID) ON Runs.rn_id = PakTrak.rn_id) ON Sizing.SizeID = PakTrak.SizeID) ON Variety.VarietyID = Runs.VarietyID) ON (Runs.VGroupID = VGroup.VGroupID) AND (VGroup.VGroupID = Variety.VGroupID) AND (VGroup.VGroupID = Sizing.VGroupID)
WHERE (((PakTrak.PakTrakID)=[Forms]![Pallet]![line_list_pal]));
 
 

Basically it sets up the text for a label, based on a selection in a listbox on the controlling form.

Any help very gratefully received.
John S

[alpnz]

The error is

"User-defined type Not defined" and the mydb As Database is highlighted.

The Data tables are linked via ODBC, the qry_elabel consolidates data from various tables to create text strings for each variable in the label.

John S

[ADezii]

The error is

"User-defined type Not defined" and the mydb As Database is highlighted.

The Data tables are linked via ODBC, the qry_elabel consolidates data from various tables to create text strings for each variable in the label.

John S

Make sure that the proper Type Libraries are defined in the References Dialog, e.g. Microsoft DAO 3.6 Object Library, Microsoft ActiveX Data Objects 2.X Library, etc.

Explicitly Declare the Type Libraries to which Objects belong, e.g.
Dim MyDB As DAO.Database,
Dim MyRS As DAO.Recordset,
Dim YourRS As ADODB.Recordset , etc.

Hope this helps.

[NeoPa]

You can also use Auto-Completion.
After typing "Dim MyDB As DAO.Datab" hit Ctrl-Space. If it can find the Database object in that object it will list it. If it is not listed then you don't have the correct library (reference) listed.

[alpnz]

Such a simple thing. Why is it selected in the development database, I certainly did not select it?.

The code now debus to the mydb.OpenRecord set(sql_rst) line. I wonder why not refer directly to mydb."qry_elabe l" as the recordset.

 
Private Sub create_eplab_Click()
' First sort out the variables. Including label content, quantity to print etc
 
 
    Dim var1 As String
    Dim var2 As String
    Dim var3 As String
    Dim var4 As String
    Dim var5 As String
    Dim qty As Integer
' Sort out where the data is coming from. Aim for reusable code
 
    Dim mydb As DAO.Database, myset As DAO.Recordset
    Dim sql_rst As String
 
 
   sql_rst = "SELECT * FROM qry_elabel"
 
 
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset(sql_rst)
 
' Declare the variables values
 
    var1 = myset![var1]
    var2 = myset![var2]
    var3 = myset![var3]
    var4 = myset![var4]
    var5 = myset![var5]
    Const quote As String = """"
 
' Code each label combination required
    qty = Int(InputBox("How many labels for the selected line do you wish to print?", "No of Labels"))
    Dim lab1 As String
 
lab1 = "N" & vbCrLf _
        & "S4" & vbCrLf _
        & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
        & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
        & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
        & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
        & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
        & "P" & quote & [qty] & quote & vbCrLf
 
 
' Print the label to the printer.
 
Open "COM2:" For Output As #1
       Print #1, lab1
 
Close #1
 
End Sub
 
 

[NeoPa]

Such a simple thing. Why is it selected in the development database, I certainly did not select it?

I think this means that ADezii's answer enabled the problem to be fixed.

The code now debugs to the mydb.OpenRecord set(sql_rst) line. I wonder why not refer directly to mydb."qry_elabe l" as the recordset.

That would seem to be a more straightforward way of doing it certainly.

[alpnz]

I think this means that ADezii's answer enabled the problem to be fixed.
That would seem to be a more straightforward way of doing it certainly.

Adrian,
Would that be the more sensible way to define the recordset. As you know I do not do enough of this to be conversant, in the howto. I might have done it long ago, does not mean I know how to do it now. :-)

John S

[alpnz]

At presant the code bombs out I think due to me not defining the data source correctly. The code at the moment.

Private Sub create_eplab_Click()
' First sort out the variables. Including label content, quantity to print etc
 
 
    Dim var1 As String
    Dim var2 As String
    Dim var3 As String
    Dim var4 As String
    Dim var5 As String
    Dim qty As Integer
' Sort out where the data is coming from. Aim for reusable code
 
    Dim mydb As Database
    Dim myset As Recordset
 
    Dim rst As String
 
 
 
    rst = "SELECT * FROM [qry_elabel]"
 
 
 
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset(rst)
 
' Declare the variable data values
 
    var1 = myset![v1]
    var2 = myset![v2]
    var3 = myset![v3]
    var4 = myset![v4]
    var5 = myset![v5]
 
    Const quote As String = """"
 
' Code each label combination required
    qty = Int(InputBox("How many labels for the selected line do you wish to print?", "No of Labels"))
    Dim lab1 As String
 
lab1 = "N" & vbCrLf _
        & "S4" & vbCrLf _
        & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
        & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
        & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
        & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
        & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
        & "P" & quote & [qty] & quote & vbCrLf
 
 
' Print the label to the printer.
 
Open "COM2:" For Output As #1
       Print #1, lab1
 
Close #1
 
End Sub
 
 

"qry_elabel " is a query that consolidates various fields from multiple tables into 5 variables for the label. v1,v2,v3,v4,v5 the tables are linked tables from another Access Database, that just has tables in it. Can someone patiently explain where I am going wrong. It Halts at the myset = mydb.OpenRecord set(rst), with A Not enough operators in statement message. (I assume the sql SELECT statement).

Not much chop without data :-)

John S

[MMcCarthy]

John

Just try ...

 
    Dim mydb As Database
    Dim myset As DAO.Recordset ' Just a precaution
 
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset("qry_elabel")
 
 

Mary