Crosstab Query and Parameters

[chaostheory]

Well i am creating a form of standard queries that people will run so they aren't actually in the database and can't change anything.

All the queries i have created so far worked fine.

I create a query
I create a simple form. Label, text box and a button
I link the source of the form to the query
I link the field i want to search in the crosstab query to the txtbox in the form like so:
Like "*" & [Forms]![frmLookupPPbyStrNumber]![txtLookupStrNumber] & "*"
With the other forms it works perfectly...it opens the form i type in a number and hit search and i get results.
With the crosstab query, it is popping up a parameter box everytime i run the query, before it opens the form. It still works, but i can't get that stinking parameter box to go away.
Also note with the crosstab i had to enter a parameter under the query menu for that query, i didn't have to do this on any other ones.
If i dont enter the parameter under Query i simply get an error.

Is there an easy way i can use VBA to do this instead of using parameters and all that. All of my vba experience is with Excel.

For instance can i simply put code on the button that when clicked, stores the data in the text box then runs the query using that data? rather than dealing with linking sources, creating parameters and all that hooplah?

 

[JamesMcS]

Your for instance would work, but is there anythign in the form_open/load event that might cause it to ask for the parameter?

I take it the query doesn't ask for it when run by itself.

 

[chaostheory]

All of my queries when run separately pop up a parameter box.

When i run the form and choose an option for instance searching a ticket number, which calls a sub form which is linked to that query, those parameter boxes never pop up. The only one it pops up on from the form, is the crosstab query. I've redone it like 8 times i can't figure it out.

And i haven't done enough in vba in access to know how to do it any differently yet.

 

[JamesMcS]

OK - if your queries are asking for a parameter, they'll ask no matter where they are. Just to clarify, is this what you've got in your criteria of the crosstab?

Like "*" & [Forms]![frmLookupPPbyStrNumber]![txtLookupStrNumber] & "*"

If that's the case, and it's still asking for a parameter, the value in txtlookupstrnumber is null.

As far as I know, if you're talking about parameters, that's something like [Enter Value] in the criteria of the query, rather than an expression like the one above.

 

[chaostheory]

Ok let me see if i can explain it a little better.

I have a main form with some buttons on it. Each button takes me to a sub form which has a label, a unbound empty textbox and a button.

Each sub form uses a query as the record source. So in each query i put the like "*" & [forms].etc.etc & "*" and used the names of the text boxes in each sub form. When i run the query from query menu, it prompts for a parameter box.

When i run the sub forms, i am not prompted for anything. I type in a number and i hit the button which "runs a query" and supplies the parameter.

The problem is with the cross tab query. As soon as i click the button to get to the sub form. I'm getting asked for a parameter before it even loads the sub form for me to fill in the text box!

So the regular queries load the form, allow me to type in a value and hit search. The form linked to the crosstab, asks me for a parameter before the form even loads.

I've researched this a lot online and the only thing i find is that when dealing with crosstabs there is a bug in access when declaring parameters of type text. I simply don't know how to do it any other way. It works perfect if i don't use a crosstab, but, the data is in a much nicer format when i do.

 

[chaostheory]

On a regular query, i have this

Like "*" & [Forms]![frmTicketLookup]![txtLookupTicket] & "*"

When i run the query i get a parameter popup asking me to supply a parameter

When i link a form to this query, and run the form, i type in a number in the text box and it supplies the query with the parameter that i typed into "txtLookupTicket"

When i create a crosstab query....the query acts the same way, it pops up a parameter box as expected when i run the query.

But when i try to load the form i link to it, it asks me for a parameter before it even loads the form. Like it is working out of order? Does that make sense?

 

[JamesMcS]

Right. If you've got an expression as the criteria in your query, it will work fine in the form, as the form is open and the query can look at whatever the expression is looking at. If you just run it from the database window, the form isn't open and it'll ask you for a value.

The reason your crosstab is asking for a value is because whatever expression you've put in there evaluates as a null - be it a closed object that it's looking at, or just a blank value in whatever control/field the expression is looking at. Check the expression - a good way to test is to take the expression out and substitute it for a value you know is going to pull back some results. If that works it's the expression. If it doesn't it's the data at fault.

I've not heard of problems with text parameters before but ta!

 

[chaostheory]

Ill create a brand new form right now and give you an account of how it goes and record my steps, ill edit this post when im done.

:Steps:

I create a new blank form in design view (a subform to the main)
I right click the form and set the form source to the crosstab query
I create a textbox and name it txtLookupStrNumber
I create a button and its action is set to "run query"
I select the same query the form is linked to for the button to run
I save it as frmLookupStrNumber
I edit main form and create a button, to open the new subform
I edit the crosstab query and input the parameter i want to search by
I use the build function so it can't be typed in wrong
Like "*" & [Forms]![frmLookupStrNumber]![txtLookupStrNumber] & "*"
I save the Query
I try to run the Query and i get the error "The Microsoft Jet Database Engine does not recognize 'Forms!frmLookupStrNumber!txtLookupStrNumber' as a valid field name or expression.
I read this is because crosstab requires you to declare your parameters.
So i click on Query from the menu, Then click Parameters
I put in [Forms]![frmLookupStrNumber]![txtLookupStrNumber] type Text
I Save it
Now when i run the query i get the popup to enter the parameter as expected.
I go back to the forms menu and run the main form
I click the button to open the sub form
I get the parameter popup box (WHHYYYYYYYYY)
I type in any random value whether it exists or not
The subform opens
I type in a real value that does exist
I get search results

It works exactly like all the other queries with the one exception that everytime i click the button, i get a parameter popupbox before the sub form loads.

Now i will try what you suggested (the code) and report back.

 

[JamesMcS]

It's strange that it's asking for input even before the form opens. That'll be something to do with the load order but I don't know the proper solution - make the subform unbound and see if this works in form_open:

[subformname].sourceobject="[crosstab name]"

Super clunky, I'll try and find the proper solution for ya.

 

[JamesMcS]

Good luck bud, but I don't think it's the form at fault. If you do decide to recreate it from scratch, make sure all the object names are the same, otherwise the queries will ALL come up asking for parameters

 

[chaostheory]

Before i try your solution i wanted to paste the code currently in there that access created.

This is on the main form when i created a button to open the sub form. All access created code un modified

Private Sub btnOpenLookupstrNumberFrm_Click()
On Error GoTo Err_btnOpenLookupstrNumberFrm_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmLookupStrNumber"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btnOpenLookupstrNumberFrm_Click:
    Exit Sub
Err_btnOpenLookupstrNumberFrm_Click:
    MsgBox Err.Description
    Resume Exit_btnOpenLookupstrNumberFrm_Click
    
End Sub

This is the code in the sub form. Again access created unmodified in any way

Private Sub btnLookupStrNumber_Click()
On Error GoTo Err_btnLookupStrNumber_Click
    Dim stDocName As String
    stDocName = "qryPhysicalPropertiesCrosstabByStrNumber"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_btnLookupStrNumber_Click:
    Exit Sub
Err_btnLookupStrNumber_Click:
    MsgBox Err.Description
    Resume Exit_btnLookupStrNumber_Click
    
End Sub

 

[JamesMcS]

Like "*" & [Forms]![frmLookupStrNumber]![txtLookupStrNumber] & "*"

should read

Like "*" & [Forms]![frmLookupStrNumber].form![txtLookupStrNumber] & "*"

The error you're getting is because your syntax was wrong, not because of crosstab parameters.

I put in [Forms]![frmLookupStrNumber]![txtLookupStrNumber] type Text

I'm not sure if there's a difference, but you want this as a criteria in the query's design, not a parameter.

Try changing these things and let me know how you get on.

 

[chaostheory]

I made the sub form unbound, no record source.

I added this:

Private Sub Form_Open(Cancel As Integer)
[frmLookupStrNumber].SourceObject = "[qryPhysicalPropertiesCrosstabByStrNumber]"
End Sub

It broke...with error:
Standard Queries can't find the field '|' referred to in your expression.

Edit: ok ill try your next suggestion now

 

[JamesMcS]

Yeah forget about that, it was just a stupid workaround - try the other things I've mentioned.... we'll get there!

 

[chaostheory]

If i remove the parameter entry and put in what you said

Like "*" & [Forms]![frmLookupStrNumber].form![txtLookupStrNumber] & "*"

I get the same error "microsoft jet database engine does not recognize etc etc"

If i add what you typed as the parameter like i did with the criteria the expression builder generated, i still get the same jet database engine error with your criteria statement.

 

[chaostheory]

CORRECTION, it does the same thing mine did.

For some reason it added an extra column at the end of my query which was a duplicate of the first column and had one of the criteria statements in it. I corrected it

I added your criteria, i added your criteria as a parameter under the query menu.

When i run the query i get the popup for a parametere
When i run the form it still pops up before the form loads

So it worked the same as what i had so far. This is so annoying lol...

 

[JamesMcS]

So just to clarify, parameters and criteria are different things.

Does the error say it can't find a form, or a field?

Take everything out of parameters. In the crosstab's query design view, not expression builder, put the like "*".... as a criteria, not a parameter. This means that it will look at the txtlookupstrnumber object on the frmlookupstrnumber form. If the form isn't open when the query opens, it'll ask you for input because it can't find the text box.

If it's still coming up saying it can't find the FIELD txtlookupstrnumber, check the name of the text box. If it's saying FORM then, honestly, I'm not sure...... I'm positive the syntax is right.

 

[boblarson]

So just to clarify, parameters and criteria are different things.

Since when? A parameter is just criteria that is provided interactively.

 

[JamesMcS]

I'm trying to differentiate between the parameter dialog box, and the criteria row in the query design... I think the OP is getting confused, and that helps big time

 

[chaostheory]

None of my queries work if i take the parameters out....Unless im doing something totally wrong.

If i take out the parameter, and leave in your criteria i get this error.

"The Microsoft Jet Database Engine does not recognize "[Forms]![frmLookupStrNumber].[form]![txtLookupStrNumber]" as a valid field name or expression.

If i put [Forms]![frmLookupStrNumber].[form]![txtLookupStrNumber] in as a parameter which i read somewhere online to do, then the whole process works with the one exception of it always asks me for the parameter first.

If i remove the parameter i put in each standard query, then they all break with the same jet database engine error