Use Data From Form In Query

Re: Use Data From Form In Query

Hi AL,

I'm not savvy on using Access objects and their properties. I code it the hard way (only because I don't know/trust the easy way). I was, however, reading this thread:

http://www.ozgrid.com/forum/sh…d.php?p=391271#post391271

and thought that post number 5 might point you in the right direction.

Hope that helps,

Mav

Re: Use Data From Form In Query

RunSQL is meant to be used with action queries eg append, delete etc not select queries.

What you really should be looking at are parameters.

Re: Use Data From Form In Query

AL

What is it you actually want to do here?

If it's just to set the criteria for a report based on data from a form then it's covered pretty well in the Help files.

Re: Use Data From Form In Query

Sorry I don't quite think I follow.

What do these filenames look like?

Re: Use Data From Form In Query

Quote from abundant_lyfe

Hi Norie.

I've been playing around with parameters as you suggested and have come up with this

Code

DoCmd.OpenForm "QA_Check_View_Report", acNormal, , Lab_ID = LabID

however, the form is blank because the data is actually on a subform, called CNMT_Temp_Query, so I'm not sure how to reference that.

I'm really in a twist aren't I!

Display More

did you try

DoCmd.OpenForm "QA_Check_View_Report", acNormal, , Lab_ID = [CNMT_Temp_Query]![LabID]

Re: Use Data From Form In Query

Hi AL

Nuther nice problem to solve! I picked a really good time to have a spare moment or two! I have been really busy for the last few weeks so haven't responded to any posts for a while. Just couldn't resist this one though.

Quote

I have a form which contains a filename. I need part of this filename to be passed to a query which is viewed in a subform of a separate form when a button is pressed. I have written some code to deal with this, but am getting the error '2448 - you can't assign a value to this object' when I try to run it. Perhaps I need to open the query, assign the criteria, save and close the query, then open the form; but I'm not entirely sure how to do this!

I think you are trying to do this the wrong way round. Don't try to pass the value to the query, but let the query pick the value off the form. This is based on the assumption that the form is open at the time the query is activated, and the file name control has a value.

Regardless of the rest of the solution, the first thing to do is to have the determination of the value done just before the query is run. Then I would simply write the value into a hidden control on the form. That way you can make the control visible for debugging purposes so that you can see the value that is being generated from your code. No need to step through your code or to use the "Immediate Window" tool.

A simple method for Step Two is to open the query in design mode (preferably with the form with the hidden control open in design mode) and in the criteria cell, for the column you are using, right-click. You will see an option labelled "Build"; select it. A new dialog window opens. Expand the "plus" symbol on the "Forms" object and you will get two more options "Loaded Forms" and "All Forms". If you have the form open in design mode (or run mode for that matter) select "Loaded Forms" and you will find your form on the list. Add the control from your form into which you are loading the "calculated" value by double-clicking the name in the right-hand list. The result will be something like this ... "Forms![MYMasterFormName]![MyCalculatedValueControlName]" ... appearing in the expression builder edit field. Click OK. Save your query.

The result will be that the query, when it is run, will replace that form control reference with the value stored in it. Hey presto - a filtered query result ... I hope!

I use the builder for creating form reference syntax regularly. I am not developing regularly enough to committ the correct syntaxes to memory so this is a great little tool. Even if I was I would probably still defer to this as it save me typing!! and therefore saves time trying to fix up stupid typing errors (despite all the years spent on a keyboard I still can't type to save myself!).

You could get more complex using a querydef object and using that to create/change the sql in your query, but the above is a relatively simple method which is relatively easy to debug. It is also a useful technique to know about as it can be used in all sorts of ways. The trick is that the control storing the value must be on an object which is active at the time that it is called. It is a useful way, for example, of getting summary/static values from a subreport onto the master report as long as the control on the master report utilising the value, is located under the subreport in the designer ie it is evaluated by the report after the subreport has been evaluated (its all about sequence of events and timing).

I think the reason why your original code has failed is because you are trying to assign a value to a control which can't accept that value - you are trying to "push" data in a situation which probably suits a "pull" methodology better. The querydef method would probably rate as a "push" method and is probably something you should explore. They are quite useful and I use them often when I am using Access as an interface to a SQL Server db and I want to use a passthrough query to run a stored procedure to retrieve a small recordset.

I hope I have interpreted you initial post correctly. It seemed reasonably clear to me what you were after so I hope this helps.

Cheers

Rowan

Re: Use Data From Form In Query

Hi

I think the issue will be in the string you are creating. When I first suggested this idea I was thinking of a single value rather than a string, but I think we can still make this work. If not we can add another control to the form, have each calculate the value rather than the string, then create the string as part of your criteria.

But before we go downw that route or fall back on a querydef, lets take a look at your string.

Quote

BETWEEN 0851 AND 0877 or LIKE *15427

Because you have used the LIKE operator I am assuming that the data type of the column you are using this as a criteria for is a string. Therefore, when you create the string you will also have to concatenate in the single quotes around the values so that it looks like the following:

Quote

BETWEEN '0851' AND '0877' or LIKE '*15427'

You can test this theory by pasting the above directly into the criteria cell of your query for that column and make sure you get a result.

Hope this nails it.

Regards

Rowan

Re: Use Data From Form In Query

Hi Al

Yer, no probs with the iif statement, I knew what you meant but I didn't post very well in response.

I have just realised that you as using this criteria on your [LabID] column which is derived from the [*?CNMT_LBID] data. Because [LabID] contains no alpha characters, Access seems to be treating it as numeric, at least for the purposes of the criteria, so the single quotes are not required. However, when it displays the data, it is treating that column as text as it leaves in the leading zero and left aligns it. This leads me to believe we are seeing one of the less desirable sides of Access from a developers point of view - loose data typing ie in this case it is treating the data as a Variant data type which means it is less predictable in what it will do ... sigh! oh joy!! I hope all this makes sense, if not let me know.

We could force the issue by formatting the column in the query but for now lets just use it as it stands. Forcing the data type introduces issues of reliability as your Mid will fail and error if you get any alpha characters in the result (although the Mid result is a possible issue even as it stands but that is another issue).

OK, so we have established that it is essentially numeric data that we are testing, so no quotes are required. From your last post I am guessing that the IIf statement is on the "hidden" control. What I would do would be to use your original code that you had in the Open_Temp_Report_Click event, but use it to set the value of the control on the form.

Thus:

Private Sub Open_Temp_Report_Click()


    Dim Length As Integer
        
    Length = Len(File_Name)
        
    If Length = 10 Then
        me.Hidden_Control_Name = "BETWEEN " & Mid(File_Name, 2, 4) _
        & " AND " & Right(File_Name, 4)
    Else
         me.Hidden_Control_Name = "like *" & Mid(File_Check!File_Name, 3, 5) & "*"
     End If
    
    DoCmd.OpenQuery "CNMT_Temp_Query"   


End Sub

I would also test this only on the between string to start with. The LIKE operator and wildcard character (*) are generally reserved for searching strings and that is how I am accustomed to using them. With Access seemingly treating [LabID] values as numeric, I'm not sure if this syntax will work. If you use the LIKE operator, you might have to use a conversion function (to characters) but the Variant datatyping may work in your favour for this one.

If the form control is referenced OK in the query, this should work. If it doesn't modify the VBA code to simply place a value in the control, say 0851 (or some other value that you know is in the data) and test it again. If that doesn't work then we have to suspect that the form referencing is not picking up the value from the control. This can be tested by taking the control reference from your control and simply adding it as a column on the query. If it is working, you should see the appropriate value appearing on the end of each record in the result set.

Looking forward to the next update. Perhaps if you are still having difficulties after this, it may get the job done quicker if you post a sample mdb. I am getting concious of the time lag with you and me on opposite sides of the globe.

Cheers

Rowan

Re: Use Data From Form In Query

I think you will need to code the If and Between/And statements in the query's SQL and add a second textbox to the form so that each value fro the Between clause can be pulled into the query separaately or if you are using the Like clause you'll have to have the code hide one of the textboxes and the SQL will grab the correct value from the correct textbox via the coding in the iif statement in the where clause. Could that be any more of a run-on sentence? Sorry, in a hurry. Have a call in......now, doh![hr]*[/hr] Auto Merged Post;[dl]*[/dl]This is what I mean. hope it's what you're looking for.

Re: Use Data From Form In Query

Hi Zoe

I downloaded your test and had a play last night. Couldn't get it to work. I pulled out a fresh copy from the zip file and looked at it again today and found the problem.

Your query has a column specified which is no longer in the table thus it pops open the usual error dialog requesting a value. If you remove the column, it works fine with the between string.

When you open the query in design mode you will find one column which looks like this:

Expr1: CNMT_TEMP.[*CNMT_LIM]

You either need to fix the table or remove this column from the query.

Once I removed that column, the whole thing worked fine or at least returned the two records.

With the copy of the mdb I extracted from the zip file last night, I had tried the very same thing which Mavyak has suggested. But for the life of me I couldn't get it to work. If I added the form control names as columns in the query, they were returning values. If I added the form controls as criteria on the query, they worked ok. But if I included them in an IIF statement as a criteria on the query, they refused to work.

I hope you are experiencing the same thing in regard to the missing column being the problem. If not, then I suggest you dump the query from the mdb, compact and repair, then recreate the query (you copy the SQL text out into an editor so that you can re-use it). How big is your live MDB - do you compact and repair often?

I'll look forward to hear how it goes. In the meantime I will work on the querydef option.

Cheers

Rowan

Re: Use Data From Form In Query

Hi Zoe

I downloaded your test and had a play last night. Couldn't get it to work. I pulled out a fresh copy from the zip file and looked at it again today and found the problem.

Your query has a column specified which is no longer in the table thus it pops open the usual error dialog requesting a value. If you remove the column, it works fine with the between string.

When you open the query in design mode you will find one column which looks like this:

Expr1: CNMT_TEMP.[*CNMT_LIM]

You either need to fix the table or remove this column from the query.

Once I removed that column, the whole thing worked fine or at least returned the two records.

With the copy of the mdb I extracted from the zip file last night, I had tried the very same thing which Mavyak has suggested. But for the life of me I couldn't get it to work. If I added the form control names as columns in the query, they were returning values. If I added the form controls as criteria on the query, they worked ok. But if I included them in an IIF statement as a criteria on the query, they refused to work.

I hope you are experiencing the same thing in regard to the missing column being the problem. If not, then I suggest you dump the query from the mdb, compact and repair, then recreate the query (you copy the SQL text out into an editor so that you can re-use it). How big is your live MDB - do you compact and repair often?

I'll look forward to hear how it goes. In the meantime I will work on the querydef option.

Cheers

Rowan[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi Zoe

Sorry bout the double post. Which turns out to be totally wrong anyway. Except for the bit about the missing column.

But all that just makes me a double idiot because the penny has just dropped as to why pulling a value from a control is different from pulling a string such as your "between" clause. The answer is in the SQL. Your query SQL reads:

SELECT CNMT_TEMP.[*HOLE_ID], CNMT_TEMP.[*SAMP_TYPE], CNMT_TEMP.[*CNMT_TYPE], CNMT_TEMP.[*CNMT_TTYP], CNMT_TEMP.[*CNMT_RESL], CNMT_TEMP.[*CNMT_UNIT], CNMT_TEMP.[*CNMT_NAME], CNMT_TEMP.[*CNMT_LAB], CNMT_TEMP.[*?CNMT_LBID], IIf(Len([*?CNMT_LBID])=8,Mid([*?CNMT_LBID],5,4),Mid([*?CNMT_LBID],4,5)) AS Lab_ID
FROM CNMT_TEMP
WHERE (((IIf(Len([*?CNMT_LBID])=8,Mid([*?CNMT_LBID],5,4),Mid([*?CNMT_LBID],4,5)))=[Forms]![File_Check]![Query_Criteria]));

Can you see what is going to happen when the "[Forms]![File_Check]![Query_Criteria]" section gets replaced with "Between 0851 and 0852"? The resulting query is going to read:

SELECT CNMT_TEMP.[*HOLE_ID], CNMT_TEMP.[*SAMP_TYPE], CNMT_TEMP.[*CNMT_TYPE], CNMT_TEMP.[*CNMT_TTYP], CNMT_TEMP.[*CNMT_RESL], CNMT_TEMP.[*CNMT_UNIT], CNMT_TEMP.[*CNMT_NAME], CNMT_TEMP.[*CNMT_LAB], CNMT_TEMP.[*?CNMT_LBID], IIf(Len([*?CNMT_LBID])=8,Mid([*?CNMT_LBID],5,4),Mid([*?CNMT_LBID],4,5)) AS Lab_ID
FROM CNMT_TEMP
WHERE (((IIf(Len([*?CNMT_LBID])=8,Mid([*?CNMT_LBID],5,4),Mid([*?CNMT_LBID],4,5)))=Between 0851 and 0852));

The variant nature of your derived "Lab_ID" column is handling the "0851" and "0852" values ok so there is no need for quotes, but the "where" clause has just become nonsense. So that rules out a single control and having VBA work out whether the value is a "between" string or a "like" string. Neither will work.

Next we fall back on the dual controls on the form idea. My testing of that suggests it is fine until we come to deciding whether the "where" clause should use a "between" or "like" operator. That has to be done in the query criteria itself. My thought was to us an "iif" to test whether or not the second form control had a value or was null. Using the form controls in a "between" style criteria or a "like" style criteria worked ok, but as soon as I try to combine them inside an "iif", it fails to return anything. So, given your requirements go beyond single values or even using the "IN" operator and generating a list of values, we are left with the last option.

Querydef. I had hoped that we could get this working using less advanced methods, not that understanding Querydefs is difficult. Anyway, I have attached an updated version of your sample with the completed code.

What the code does is use the Querydefs collection (the set of pre-defined query objects you have developed and saved in the mdb) and your specific/named query in that collection. The code effectively updates the SQL of the query and saves the change (all done in the background) then we just do the normal "DoCmd" stuff. If you want, you can reset the code back to the original as a last action after the query has been run.

I like querydefs, especially for creating recordsets from a SQL database for populating a form. But in your case I am concerned about one thing. There could be an issue if you are using a common mdb and two people try to use this function at the same time. It is not a problem if everyone has a seperate copy of the mdb which is linked to a seperate data mdb. If this is going to be a problem, then I suggest you start a new thread and we can discuss it there.

I hope this finally gets you going.

Cheers

Rowan