User-Defined data type error

[rutkus]

Hi,

I'm kinda new to VBA and I'm not quite sure where(or how) i should go to fix an error.

I'm running code that dims a variable as a "QueryDef":

Dim qdfDynamic As QueryDef

and then equals the variable to the query approveddate:

qdfDynamic = CurrentDB.QueryDefs ("ApprovedDate&quot

it told me that the user-defined type was not defined, so i read around and i know i have to declare it in public, or something to that effect.

My question is, what, where and how do i set it????

I hope i explained this correctly,

Any and all help would be appreciated

Thank You
Omar Qureshy
DSC Networks

 

[jebry]

Hi Omar!

When you are working with object variables you need to use the keyword Set:

Set qdfDynamic = CurrentDB.QueryDefs ("ApprovedDate&quot

hth
Jeff Bridgham

 

[rutkus]

hey jebry,

im sorry, the code does have the set before qdfdynamic.

My problem is the dim statement, i keep getting an error stating that the user-defined type is not defined. So what i need to do is set QueryDef as "something" in public so that itll be a valid argument for the private statement.

I dont know how to set global variables or anything like that. So i wouldnt know how to go about setting QueryDef as an object? string? or even if those are valid options for the public statement. The variable needs to define a query, thats all i really know.

I hope i made it a bit clearer

Thanks though Jebry

Omar Qureshy
DSC Networks

 

[jebry]

Hi!

I'm sorry, I guess I misread your post. You certainly should not need to define the QueryDef object. Go to the code screen and select from the menu bar Tools - References and make sure you have a reference to the DAO object library checked. If you don't, find it in the list and check it.

hth
Jeff Bridgham

 

[Databaseguy]

Sounds like you using 2000 and ADO.

Easy, sloppy way. Go to tools/references and check the DAO 3.6 and/or DAO 3.51 object library(ies)

Better way. Use ADO.

Here's an example that grabs the SQL out of an existing query.

Note. The entire object library, by default, is not exposed. In order to work with most of the objects, properties and methods you're used to working with make sure that in addition to "Microsoft ActiveX Data Objects 2.1 Library" , that you include the "Microsoft ADO Ext. 2.1 for DDL and Security" and "Microsoft OLE DB Service Component Type 1.0 Type Library" references in your project.

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim prc As ADOX.Procedure
Set cnn = CurrentProject.Connection
Set cat.ActiveConnection = cnn
Set prc = cat.Procedures("YourQuery&quot
MsgBox cmd.CommandText
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[rutkus]

hey Databaseguy,

I checked:
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security
Microsoft OLE DB Service Component Type 1.0 Type Library

and i continued getting the same user-defined type is not defined error.

So i tried adding DAO 3.6 object library and now i get this error:

Reserved error (-3015); there is no message for this error

Maybe im doing something wrong,

any suggestions would be greatly appreciated

thank you
Omar Qureshy
DSC Networks

 

[Databaseguy]

If you're going to use DAO, make sure you move your DAO 3.51 or 3.6 reference up this list using the arrows in the reference tool. I'd recomedn migrating to ADO.

Is your query a crosstab query. Crosstab queries have to have named columns when you call them from code. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[nickjar2]

If all else fails, maybe try running the code on another pc. If it works then possibly a reinstall. I can't thnk of anything else as I have done a copy and paste of the code and it worked fine for me (using DAO 3.6).

Nick

 

[rutkus]

hey,

i keep getting this error when i open the form ReportIntro and click on the print preview button:

Reserved error(-3015); there is no message for this error

Here is my code for the print preview button:

Private Sub Preview_Click()

On Error GoTo Err_Preview_Click

Dim stDocName As String, intcurrmonth As Integer, strTemp As String
Dim qdfdynamic As QueryDef

For intcurrmonth = 0 To 11
strTemp = strTemp & "'" & _
Format(DateAdd("m", intcurrmonth, Me!BegDate), "mmm yyyy&quot & "'"
If intcurrmonth < 11 Then
strTemp = strTemp & &quot;,&quot;
End If

Next

Set qdfdynamic = CurrentDb.QueryDefs(&quot;TtlApprovedDate&quot

If InStr(qdfdynamic.SQL, &quot; In &quot > 0 Then
qdfdynamic.SQL = Left(qdfdynamic.SQL, InStr(qdfdynamic.SQL, &quot; In &quot - 1) & &quot; In (&quot; & strTemp & &quot;&quot;
Else
qdfdynamic.SQL = Left(qdfdynamic.SQL, Len(qdfdynamic.SQL) - 3) & &quot; In (&quot; & strTemp & &quot;&quot;
End If
qdfdynamic.Close


stDocName = &quot;Processing Time by Vendor&quot;
DoCmd.OpenReport stDocName, acPreview
DoCmd.Close acForm, &quot;ReportIntro&quot;, acSaveNo


Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub


The first part should error handle if there are more than 12 months in my report, the second part should open the queries and set the column headings, the last part should open the report.

the error may not even be in this coding for all i know, but this is my best bet.

Thank you guys for your time, i know im an ass

Peace
Omar Qureshy
DSC Networks

 

[nickjar2]

try placing a breakpoint on the line:
For intcurrmonth = 0 To 11

At least if the error is in this code you wil know which line.

Nick

 

[Databaseguy]

Well, first, make sure you've set column heading for all of the crosstabs.Go to query design, right click, and type in the values in the column headings property. I.E. &quot;Heading1&quot;;&quot;Heading2&quot;;&quot;Heading3&quot;, Etc.

Then, you've gotta set your parameters. In your code, open the query def. Then set, the parameters like this:

qdef![Your Paramaterl] = yourVariable

Lastly, make sure your parameters have been declared in the query. Again, go to the query, right click, choose parameters, and type in your parameters. View your SQl to make sure they're in there. It should look like this:

PARAMETERS [Param1] Text, [Param2] Text;
TRANSFORM [Fieldname]
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[rutkus]

hey databaseguy,

Since im using a union query i only get it in SQL view, so this is what that code looks like:

PARAMETERS Forms!ReportIntro!BegDate DateTime, Forms!ReportIntro!EndDate DateTime;
SELECT * FROM ttlApprovedAmount UNION (SELECT * FROM ttlInvoice)
ORDER BY Vendor In ('Jan 2001','Feb 2001','Mar 2001','Apr 2001','May 2001','Jun 2001','Jul 2001','Aug 2001','Sep 2001','Oct 2001','Nov 2001','Dec 2001');

Im assuming whats missing is the TRANSFORM part of declaring the parameter.

Now, should i be setting the parameters in my two crosstab queries or in the union query?? i figure it would be faster to &quot;weed out&quot; the unnecessary info as early as possible. If thats the case then this is what i get the code looking like on the approvedamount crosstab query.

PARAMETERS Forms!ReportIntro!BegDate DateTime, Forms!ReportIntro!EndDate DateTime;
TRANSFORM Sum([Invoices].[ApprovedAmount]) AS SumOfApprovedAmount
SELECT [Invoices].[Vendor], Sum([Invoices].[ApprovedAmount]) AS SumOfApprovedAmount1
FROM Invoices
WHERE ((([Invoices].[ApprovedDate]) Between [Forms]![ReportIntro]!BegDate And [Forms]![ReportIntro]!EndDate))
GROUP BY [Invoices].[Vendor]
ORDER BY [Invoices].[Vendor]
PIVOT Format([ApprovedDate],&quot;mmm yyyy&quot;

arent the parameter and where statement just repeating the same thing?? which should stay??

As far as the column heading, does the ORDER BY section of the union query, since the report is getting its info from there cover that??

Lastly, where in my code should i set the parameter?? referring to the post of my code, should it be before or after:

Set qdfdynamic = CurrentDb.QueryDefs(&quot;TtlApprovedDate&quot

and should it look like this:

qdef(??)!forms![ReportIntro]![BegDate] = monkeys(??)

Sorry for the bother, thanks for the help
Omar Qureshy
DSC Networks

An eye for an eye would only make the world blind
--Gandhi

 

[rutkus]

OK

i think im somewhat closer to getting this report working.

I moved the parameters to just the union query, so now heres how that sql statement looks:

SELECT *
FROM ttlApprovedAmount
WHERE ((Invoices.ApprovedDate Between [Forms]![ReportIntro]![BegDate] And [Forms]![ReportIntro]![EndDate]))
UNION (SELECT * FROM ttlInvoice)
ORDER BY Vendor In ('Jan 2001','Feb 2001','Mar 2001','Apr 2001','May 2001','Jun 2001','Jul 2001','Aug 2001','Sep 2001','Oct 2001','Nov 2001','Dec 2001');

i keep getting an error saying 'invoices.approveddate is not a valid field name or expression.

invoices.approveddate is a field in a table, that &quot;filters&quot; two crosstab queries to columnize(if thats even a word) the data by month. So i dont think that it should define the WHERE statement as it isnt a field in the ttlApprovedAmount or ttlInvoice(the 2 crosstabs)queries.

So the question is what should i define the WHERE statement with?? Can you use queries to define where statements??

Maybe i shouldnt have the parameter set during this query.

i feel so close, yet i know im so far

thanks
Omar Qureshy
DSC Networks

 

[Databaseguy]

Yes, you are close.

Set up the parameters in the underlying crosstab queries. Then, make sure you &quot;feed&quot; the parameters in:

Set qdfdynamic = CurrentDb.QueryDefs(&quot;TtlApprovedDate&quot

qsfdynamic![BegDate] = monkeys(??)
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[rutkus]

hey databaseguy,

Are you recommending that i abandon setting the parameters in the union query?? I seem to be rather close to getting this to work through here, but i wouldnt doubt if inherently ill find myself in an endless loop of errors.

This is what my union query code is looking like:

PARAMETERS Forms!ReportIntro!BegDate DateTime, Forms!ReportIntro!EndDate DateTime;
SELECT *
FROM ttlApprovedAmount, ttlInvoice, Invoices
WHERE ((Invoices.ApprovedDate) Between Forms!ReportIntro!BegDate And Forms!ReportIntro!EndDate)
UNION (SELECT * FROM ttlInvoice)
ORDER BY Vendor In ('Jan 2001','Feb 2001','Mar 2001','Apr 2001','May 2001','Jun 2001','Jul 2001','Aug 2001','Sep 2001','Oct 2001','Nov 2001','Dec 2001');

the problem now is that its telling me:

The specified field 'Vendor' could refer to more than one table listed in the FROM clause of your SQL statement.(The 3 of them contain the field vendor, as its the row heading for both queries and the first field in the table Invoices)

So what should i have in my from clause?? i have the two ttl(queries) and Invoices(which is the table that defines the where clause).

If I should use the crosstabs for the parameters, then what should i do with the variable(monkeys),

Thank You
Omar Qureshy
DSC Networks