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

[alpnz]

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

Hi Mary, Thanks for the reply ..

I had tried this early on, (Admittedly after sorting out the DAO stuff) but got a Runtime Error of '3061', Too few parameters expected 1. (It highlights the myset = statement in the debug )

The Query SQL is above somewhere in this thread, it works just fine if you run it on its own and feed the criteria of the PakTrakID, so we know the dataset is there. I have also tried a long winded SQL in this button click, with little success.

Anyway ... its been a long hot (low 30 deg C) day here ... so will no doubt awake with a fresh idea or two..

Many thanks for your help.
John S

[MMcCarthy]

Hi Mary, Thanks for the reply ..

I had tried this early on, (Admittedly after sorting out the DAO stuff) but got a Runtime Error of '3061', Too few parameters expected 1. (It highlights the myset = statement in the debug )

The Query SQL is above somewhere in this thread, it works just fine if you run it on its own and feed the criteria of the PakTrakID, so we know the dataset is there. I have also tried a long winded SQL in this button click, with little success.

Anyway ... its been a long hot (low 30 deg C) day here ... so will no doubt awake with a fresh idea or two..

Many thanks for your help.
John S

John have you actually saved the query as an object; i.e. it appears on the object list?

[alpnz]

By that I presume, "Is there a Query in the query window of the database called 'qry_elabel'?, the answer is yes, but that is a good question, because if I select 'crtl' 'space' at each step as I type in the objects, I would have expected the queries to have shown up, under say "DoCmd.OpenQuer y "should show up in the object list at this point" ??? ..
JS

[NeoPa]

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

Unless you know of a reason not to - I would.

[NeoPa]

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

Mary's provided some replacement code but what was wrong was that you were providing a RecordSet object variable (used only within code) instead of a saved QueryDef, Table or SQL string.