473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 7762
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,834 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,556 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,556 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,556 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,556 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,556 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,556 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,556 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,556 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

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

Similar topics

0
by: Xiaofeng Zhang | last post by:
when I compile my project setting, there are many LINK errors. But when I compile my project under debug setting, everything is OK. How can I deal with it? The errors is followed. Linking......
17
by: newbiecpp | last post by:
I have hard time to understand run-time environment. Let assume that I have a program that has a simple variable alpha. When this variable is statically allocated, the compiler can use the...
10
by: Jean-David Beyer | last post by:
I have some programs running on Red Hat Linux 7.3 working with IBM DB2 V6.1 (with all the FixPacks) on my old machine. I have just installed IBM DB2 V8.1 on this (new) machine running Red Hat...
4
by: Monte | last post by:
It compiles fine in one of my ms2002 databases, but on another db (I inherited from somebody else), I get this compile error: "User-defined type not defined" Is something turned off in this...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
8
by: mastermagrath | last post by:
Hi, I'm about half way through Bruce Eckels thinking in C++ Vol 1. He gives a very short example of how compiler function name decoration helps remove subtle bugs by type-safe linkage. However,...
10
by: B Williams | last post by:
I have been working with this code for a better part of the day and I can't figure out where I am making a mistake. I can only imagine it is when I declare multiple paramaters on the constructor...
2
by: john | last post by:
I'm trying to use the code below. But when the code is run I get: Compile error: an infotype defined by user is not defined. <dbClient As Databaseis then highlighted. Somebody an idea on how do I...
3
by: blakerrr | last post by:
Hi everyone, I am trying to export a table to an excel file using vba on a form's button click event. I am getting the error: Compile error: User-defined type not defined. And it highlights...
11
by: Tim H | last post by:
The following program compiles just fine in C, but not in C++. Can anyone explain why? I have a chunk of code that defines stuff like this in headers (without the extern) that I can not easily...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.