473,287 Members | 1,868 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,287 software developers and data experts.

More Help With Requery

Hey people...

I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my side.

I am trying to learn how to manipulate Dynamic Queries by forms via the example database: QrySampl.MDB, offered by Microsoft (as a learning tool, I suppose.) In particular, I am working with code from the example: "Query By Form (QBF) Using Dynamic QueryDef." Found here...

What I would like to change and adapt to my own project is a way in which I can specify the SQL and resulting query from a form (as shown in the example) but instead of displaying the query data in a plain data table, output it to a subform under the search/query criteria.

I've gotten as far as being able to display the first query, but have not figured a way to make the subform data update each time as I change the query criteria on the main form. The query itself will change, but the linked subform will not reflect these changes. The only way I have been able to show the new data in the subform is to close/open the "parent" form, or to open/close the VB debugger. The main form and subform are not linked with any references to one another. The main form is just serving as a place holder for unbound text boxes which help build the SQL statement, which in turn creates the query criteria. The subform was designed separately, linked to the query and was dragged onto the main form.

The example employs one table called orders. A form called: frmDynamicQBF acts as the front-end for the query. 6 unbound text boxes are placed on this form: Customer ID, Employee ID, Ship City, Ship Country, Order Start Date, and Order End Date. A Command button executes the following code on click, which builds, and/or deletes the dynamic query - qryDynamic_QBF as follows:

Private Sub cmdRunQuery_Click()

Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim where As Variant

Set MyDatabase = CurrentDb()

' Delete the existing dynamic query; trap the error if the query does
' not exist.
If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
End If
' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].

where = Null
where = where & " AND [ShipCountry]= '" + Me![Ship Country] + "'"
where = where & " AND [CustomerID]= '" + Me![Customer Id] + "'"
where = where & " AND [EmployeeID]= " + Me![Employee Id]

'NOTE: In Microsoft Access 97, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
'
' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.

' The following section evaluates the ShipCity criteria you enter.
' If the first or last character of the criteria is the wildcard
' character (*), then the function uses the "LIKE" operator in the
' SQL statement instead of "=". Also note the single quotation
' marks surrounding the text field [ShipCity].
If left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
where = where & " AND [ShipCity] like '" + Me![Ship City] + "'"
Else
where = where & " AND [ShipCity] = '" + Me![Ship City] + "'"
End If

' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![Order End Date]) Then
where = where & " AND [OrderDate] between #" + _
Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
Else
where = where & " AND [OrderDate] >= #" + Me![Order Start Date] _
+ " #"
End If

' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
'
' Select * from Orders where AND [CustomerID] = 'CACTU'

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from orders " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "qryDynamic_QBF"
End Sub

A second command button allows visual verification of the SQL statement built by the first command button. No need to post this code, as it is working fine.

Also to get this working properly, two modules must be present: basSPTExamples, and basCommonFunctions.

I quickly designed a separate form called: frmDynamicQBF_subform, which contains textboxes based on the fields in the table, linked the recordsource to the query, incorporated it as a subform under frmDynamicQBF, and set it to continuous. All text boxes were properly bound.

What I would like to do is get the query to output to the subform: frmDynamicQBF_subform placed under the form: frmDynamicQBF and have it update itself automatically each time the first command button is clicked, just as the query itself updates. I could probably cheat and have the data in the subform open in a new window as a separate form, which would force a requery each time (I suppose)

Any help would be greatly appreciated. I know many versions of this have been hashed over and over, but I am pretty much a novice with Access.

Sorry for such a long-winded post, I know eyes must be rolling... ;-)

Regards,

- BSM ->
Dec 22 '06 #1
3 3304
MMcCarthy
14,534 Expert Mod 8TB
You just need to requery the subform ...

Expand|Select|Wrap|Line Numbers
  1. [Forms]![frmDynamicQBF]![frmDynamicQBF_subform].RecordSource = "Query Name or Select Statement"
  2. [Forms]![frmDynamicQBF]![frmDynamicQBF_subform].Requery
  3.  
Mary
Dec 22 '06 #2
Thanks for the reply...

The requery command still won't output to the subform. The recordsource for the subform was always set to "qryDynamic_QBF", which is the name of the dynamic query. It's also necessary to state this in code as well?

As far as the second line of code, I suppose this is supposed to be appended to the event procedure I supplied in my original question(?) I tried adding it to the bottom, again, no result. I recognize the command, as it is similar to other solutions to problems not unlike my own.

What am I doing wrong? Is the requery code supposed to be on the main form's properties, object properties, or under the code supplied which runs the query, or should it be placed under the subform's global properties, or should it be pointed to the subform's individual objects (thus requiring multiple requery lines?) Or does the command requery ALL the individual objects in the subform in one line of code?

Is there a link somewhere that perhaps supplies a working example (obviously on a different database) that I could download and compare to what I am doing?

As always the subform does update itself when it is closed then opened or when I make a change to the form which requires a save.

TIA!!

- BSM ->
Dec 23 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
Thanks for the reply...

The requery command still won't output to the subform. The recordsource for the subform was always set to "qryDynamic_QBF", which is the name of the dynamic query. It's also necessary to state this in code as well?

As far as the second line of code, I suppose this is supposed to be appended to the event procedure I supplied in my original question(?) I tried adding it to the bottom, again, no result. I recognize the command, as it is similar to other solutions to problems not unlike my own.

What am I doing wrong? Is the requery code supposed to be on the main form's properties, object properties, or under the code supplied which runs the query, or should it be placed under the subform's global properties, or should it be pointed to the subform's individual objects (thus requiring multiple requery lines?) Or does the command requery ALL the individual objects in the subform in one line of code?

Is there a link somewhere that perhaps supplies a working example (obviously on a different database) that I could download and compare to what I am doing?

As always the subform does update itself when it is closed then opened or when I make a change to the form which requires a save.

TIA!!

- BSM ->

Can you post a reply to this thread on Wednesday/Thursday to remind me to have a look at it.

Happy Holidays.

Mary
Dec 25 '06 #4

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

Similar topics

3
by: Evil | last post by:
Hi, i have a problem with a treeview and some subforms in MS Access97. I have a form with a treeview on the left side which lets me navigate thru some projects. Then on the right side, i have...
2
by: Bob | last post by:
I have a form, and a subform based on a query. I want to delete specific records from my table based on certain conditions. I mean, when the user adds a new record to the subform, the form saves...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
2
by: F. Michael Miller | last post by:
I need to requery a subform from a third form and can't seem to get it to work. frmForm1 has frmAddress as a subform. The button cmdReviseAddress opens the form frmUpdateAddress where all of my...
15
by: Deano | last post by:
I've posted about this subject before but haven't really got anywhere yet. I have now come up with a plan of action that takes into account my strong desire to implement save/discard functionality...
1
by: Robert | last post by:
Every thing worked fine until all of a sudden, I get the following error whenever I try to do anything on one of my forms that calls an Event Procedure. I get the error as soon as the form opens...
4
by: midlothian | last post by:
Hello, I have conditional formatting set up on a subform based on a calculated value in the underlying query. For instance, if Sales are >$1000, the query displays "Yes," otherwise it displays...
6
by: gerbski | last post by:
Hi all, I am relatively new to ADO, but up to now I got things working the way I wanted. But now I've run into somethng really annoying. I am working in MS Access. I am using an Access...
3
by: sbaird | last post by:
Hello again.. I have a form (contacts), with a subform (calls). Tables are in a one to many relationship, and work fine in queries, etc.. I can't get my form to work the way I want though ...
2
by: mslagle1 | last post by:
Hi all, I am trying to requery a subform "frmOpenWorkOrders" when a value list "status" on my main form "frmWorkOrderMain" is changed. If I change "status" then click on "frmOpenWorkOrders" and...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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...

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.