By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

More Help With Requery

P: 3
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
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 3
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
Expert Mod 10K+
P: 14,534
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

Post your reply

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