469,323 Members | 1,575 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

VB compile error User defined Object

alpnz
113 100+
I am getting a Compile error on the following Code.
What have I missed

Expand|Select|Wrap|Line Numbers
  1. Private Sub create_eplab_Click()
  2. ' First sort out the variables. Including label content, quantity to print etc
  3.  
  4.  
  5.     Dim var1 As String
  6.     Dim var2 As String
  7.     Dim var3 As String
  8.     Dim var4 As String
  9.     Dim var5 As String
  10.  
  11. ' Sort out where the data is coming from. Aim for reusable code
  12.  
  13.     Dim mydb As Database, myset As Recordset
  14.     Dim sql_rst As String
  15.  
  16.  
  17.    sql_rst = "SELECT * FROM qry_elabel"
  18.  
  19.     Set mydb = CurrentDb
  20.     Set myset = mydb.OpenRecordset(sql_rst)
  21. ' Declare the variables values
  22.  
  23.     var1 = myset![var1]
  24.     var2 = myset![var2]
  25.     var3 = myset![var3]
  26.     var4 = myset![var4]
  27.     var5 = myset![var5]
  28.     Const quote As String = """"
  29.  
  30. ' Code each label combination required
  31.  
  32.     Dim lab1 As String
  33.  
  34. lab1 = "N" & vbCrLf _
  35.         & "S4" & vbCrLf _
  36.         & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
  37.         & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
  38.         & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
  39.         & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
  40.         & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
  41.         & "P1" & vbCrLf
  42.  
  43.  
  44. ' Print the label to the printer.
  45.  
  46. Open "COM2:" For Output As #1
  47.        Print #1, lab1
  48.  
  49. Close #1
  50.  
  51. End Sub
  52.  
  53.  
It debugs to the Dim mydb As Database , myset As Recordset Line.

What am I missing here?.

John S
Jan 14 '07 #1
22 7448
alpnz
113 100+
The qry_elabel is as below.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT PakTrak.PakTrakID, [Entity] & " - " & [RGN] AS var1, [VarietyDesc] & " " & [VGroup] AS var2, Sizing.SizeCode AS var3, "942001351234" AS var4, Runs.[RunCode] & "-" & [PakTrakID] AS var5
  3. 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)
  4. WHERE (((PakTrak.PakTrakID)=[Forms]![Pallet]![line_list_pal]));
  5.  
  6.  
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
Jan 14 '07 #2
alpnz
113 100+
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
Jan 14 '07 #3
ADezii
8,800 Expert 8TB
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.
Jan 14 '07 #4
NeoPa
32,173 Expert Mod 16PB
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.
Jan 14 '07 #5
alpnz
113 100+
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.OpenRecordset(sql_rst) line. I wonder why not refer directly to mydb."qry_elabel" as the recordset.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub create_eplab_Click()
  3. ' First sort out the variables. Including label content, quantity to print etc
  4.  
  5.  
  6.     Dim var1 As String
  7.     Dim var2 As String
  8.     Dim var3 As String
  9.     Dim var4 As String
  10.     Dim var5 As String
  11.     Dim qty As Integer
  12. ' Sort out where the data is coming from. Aim for reusable code
  13.  
  14.     Dim mydb As DAO.Database, myset As DAO.Recordset
  15.     Dim sql_rst As String
  16.  
  17.  
  18.    sql_rst = "SELECT * FROM qry_elabel"
  19.  
  20.  
  21.     Set mydb = CurrentDb()
  22.     Set myset = mydb.OpenRecordset(sql_rst)
  23.  
  24. ' Declare the variables values
  25.  
  26.     var1 = myset![var1]
  27.     var2 = myset![var2]
  28.     var3 = myset![var3]
  29.     var4 = myset![var4]
  30.     var5 = myset![var5]
  31.     Const quote As String = """"
  32.  
  33. ' Code each label combination required
  34.     qty = Int(InputBox("How many labels for the selected line do you wish to print?", "No of Labels"))
  35.     Dim lab1 As String
  36.  
  37. lab1 = "N" & vbCrLf _
  38.         & "S4" & vbCrLf _
  39.         & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
  40.         & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
  41.         & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
  42.         & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
  43.         & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
  44.         & "P" & quote & [qty] & quote & vbCrLf
  45.  
  46.  
  47. ' Print the label to the printer.
  48.  
  49. Open "COM2:" For Output As #1
  50.        Print #1, lab1
  51.  
  52. Close #1
  53.  
  54. End Sub
  55.  
  56.  
Jan 14 '07 #6
NeoPa
32,173 Expert Mod 16PB
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.OpenRecordset(sql_rst) line. I wonder why not refer directly to mydb."qry_elabel" as the recordset.
That would seem to be a more straightforward way of doing it certainly.
Jan 14 '07 #7
alpnz
113 100+
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
Jan 15 '07 #8
alpnz
113 100+
At presant the code bombs out I think due to me not defining the data source correctly. The code at the moment.
Expand|Select|Wrap|Line Numbers
  1. Private Sub create_eplab_Click()
  2. ' First sort out the variables. Including label content, quantity to print etc
  3.  
  4.  
  5.     Dim var1 As String
  6.     Dim var2 As String
  7.     Dim var3 As String
  8.     Dim var4 As String
  9.     Dim var5 As String
  10.     Dim qty As Integer
  11. ' Sort out where the data is coming from. Aim for reusable code
  12.  
  13.     Dim mydb As Database
  14.     Dim myset As Recordset
  15.  
  16.     Dim rst As String
  17.  
  18.  
  19.  
  20.     rst = "SELECT * FROM [qry_elabel]"
  21.  
  22.  
  23.  
  24.     Set mydb = CurrentDb()
  25.     Set myset = mydb.OpenRecordset(rst)
  26.  
  27. ' Declare the variable data values
  28.  
  29.     var1 = myset![v1]
  30.     var2 = myset![v2]
  31.     var3 = myset![v3]
  32.     var4 = myset![v4]
  33.     var5 = myset![v5]
  34.  
  35.     Const quote As String = """"
  36.  
  37. ' Code each label combination required
  38.     qty = Int(InputBox("How many labels for the selected line do you wish to print?", "No of Labels"))
  39.     Dim lab1 As String
  40.  
  41. lab1 = "N" & vbCrLf _
  42.         & "S4" & vbCrLf _
  43.         & "A30,30,0,5,1,1,N," & quote & [var1] & quote & vbCrLf _
  44.         & "A30,70,0,4,1,1,N," & quote & [var2] & quote & vbCrLf _
  45.         & "A30,135,0,4,1,1,N," & quote & [var3] & quote & vbCrLf _
  46.         & "B560,425,1,E30,1,2,160,B," & quote & [var4] & quote & vbCrLf _
  47.         & "A300,300,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
  48.         & "P" & quote & [qty] & quote & vbCrLf
  49.  
  50.  
  51. ' Print the label to the printer.
  52.  
  53. Open "COM2:" For Output As #1
  54.        Print #1, lab1
  55.  
  56. Close #1
  57.  
  58. End Sub
  59.  
  60.  
"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.OpenRecordset(rst), with A Not enough operators in statement message. (I assume the sql SELECT statement).

Not much chop without data :-)

John S
Jan 15 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
John

Just try ...

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim mydb As Database
  3.     Dim myset As DAO.Recordset ' Just a precaution
  4.  
  5.     Set mydb = CurrentDb()
  6.     Set myset = mydb.OpenRecordset("qry_elabel")
  7.  
  8.  
Mary
Jan 15 '07 #10
alpnz
113 100+
John

Just try ...

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim mydb As Database
  3.     Dim myset As DAO.Recordset ' Just a precaution
  4.  
  5.     Set mydb = CurrentDb()
  6.     Set myset = mydb.OpenRecordset("qry_elabel")
  7.  
  8.  
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
Jan 15 '07 #11
MMcCarthy
14,534 Expert Mod 8TB
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?
Jan 15 '07 #12
alpnz
113 100+
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.OpenQuery "should show up in the object list at this point" ??? ..
JS
Jan 15 '07 #13
NeoPa
32,173 Expert Mod 16PB
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.
Jan 15 '07 #14
NeoPa
32,173 Expert Mod 16PB
Can someone patiently explain where I am going wrong. It Halts at the myset = mydb.OpenRecordset(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.
Jan 15 '07 #15
NeoPa
32,173 Expert Mod 16PB
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.OpenQuery "should show up in the object list at this point" ??? ..
JS
No it wouldn't.
This gets a little complicated but it's only where the interpreter recognizes properties of an object. Collections don't get shown. It's all about what the interpreter can know at that time.
Jan 15 '07 #16
MMcCarthy
14,534 Expert Mod 8TB
No it wouldn't.
This gets a little complicated but it's only where the interpreter recognizes properties of an object. Collections don't get shown. It's all about what the interpreter can know at that time.
Still for some reason the database is not recognising the query as a valid object when it is being called by the open recordset action. The first thing to check is the obvious. Is the spelling of the query name correct, does it have any capitalisation, spaces, underscores, etc.?
Jan 15 '07 #17
NeoPa
32,173 Expert Mod 16PB
Am I off course in Post #15 then Mary?
Or am I just out of step with which post you're referring to?
I get confused :(
Jan 15 '07 #18
MMcCarthy
14,534 Expert Mod 8TB
Am I off course in Post #15 then Mary?
Or am I just out of step with which post you're referring to?
I get confused :(
This is what I though as well put then this was posted ...

By that I presume, "Is there a Query in the query window of the database called 'qry_elabel'?, the answer is yes, ...
So now we need to find out why the vba won't recognise it.

John, I just had a thought (Oh! my brain hurts!)

Check the references list in the Tools menu. Make sure there is a Microsoft DAO library ticked on the list.

Mary
Jan 15 '07 #19
alpnz
113 100+
Thats a good question, I am not on the M$ box, so will have pop into the office and crank it up ... BUT
Something I have checked over and over is,
The spelling of the query and the 5-6 different ways of defining the query as a recordset.
I have provided text as the variables, and the label prints, so it is definately the definition of the data source. The query works if opened in the database window, (and I supply the criteria for the [PakTrakID])

The query consolidates text values for label lines, from about 4-5 tables.
I was driving for about 5 hours today, and I had an inspiration. One of the variables consolidated is 3 numbers into one to make up the EAN number for the bar code ... do you get it yet "number", I am about to Dim var4 As an Integer ... this might make a huge difference.
Jan 16 '07 #20
NeoPa
32,173 Expert Mod 16PB
Let us know how you get on with that :)
Jan 16 '07 #21
alpnz
113 100+
So what was the outcome. Well after being buried in another project and not having time to dilly about on this, I finally sat down and had another go tonight. And you guessed it, I got it to work. Essentially I eliminated the obvious. and defined the data in the simplistic manner below.
Expand|Select|Wrap|Line Numbers
  1.  
  2. 'A sub routine to print directly to thermal printers with their own language set
  3. Private Sub create_eplab_Click()
  4. ' First sort out the variables. Including label content, quantity to print etc
  5.     Const quote As String = """"
  6.  
  7.     Dim var1 As String
  8.     Dim var2 As String
  9.     Dim var3 As String
  10.     Dim var4 As String
  11.     Dim var5 As String
  12.     Dim qty As Integer
  13. ' Sort out where the data is coming from. Aim for reusable code
  14. ' Declare the variable data values. It assumes we have a simple query which deals with
  15. ' consolidating data from various tables into lines of text and values.
  16.  
  17.     var1 = DLookup("[gr]", "qy_elabel")
  18.     var2 = DLookup("[pr]", "qy_elabel")
  19.     var3 = DLookup("[sz]", "qy_elabel")
  20.     var4 = DLookup("[EAN]", "qy_elabel")
  21.     var5 = DLookup("[rp]", "qy_elabel")
  22.     qty = Int(InputBox("How many labels for the selected line do you wish to print?", "Number of Labels"))
  23.  
  24. ' Code each label combination required. This is where the Printer centric language is dealt with. EPL Or ZPL or Birch, etc etc.
  25.  
  26.  
  27.     Dim lab1 As String
  28.  
  29. lab1 = "N" & vbCrLf _
  30.         & "S4" & vbCrLf _
  31.         & "A30,30,0,4,1,1,N," & quote & [var1] & quote & vbCrLf _
  32.         & "A30,150,0,4,2,2,N," & quote & [var2] & quote & vbCrLf _
  33.         & "A30,225,0,4,2,2,N," & quote & [var3] & quote & vbCrLf _
  34.         & "B30,300,0,E30,2,4,150,B," & quote & [var4] & quote & vbCrLf _
  35.         & "A400,400,0,4,1,1,N," & quote & [var5] & quote & vbCrLf _
  36.         & "P" & [qty] & vbCrLf
  37.  
  38.  
  39.  
  40. ' Print the label to the printer.
  41.  
  42. Open "COM2:" For Output As #1
  43.        Print #1, lab1
  44.  
  45. Close #1
  46.  
  47. End Sub
  48.  
  49.  
  50.  
I reproduce it for all that may have an interest for EFTPOS and till printers, You can of course enter Esc ASCII sequences to open cash draws etc. Hope it might help in the future.

John S
Jan 28 '07 #22
NeoPa
32,173 Expert Mod 16PB
Thanks for posting that John.
We always prefer resolutions to be posted where possible. Many of the hits we get are from non-members looking for pre-packaged answers to their problems. This is a perfectly answered problem so will be useful.
PS Senior Member after two more posts ;)
Jan 28 '07 #23

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by newbiecpp | last post: by
6 posts views Thread by Peter Frost | last post: by
8 posts views Thread by mastermagrath | last post: by
10 posts views Thread by B Williams | last post: by
11 posts views Thread by Tim H | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.