Subform problems...

  • Thread starter Thread starter Ben Bayliss
  • Start date Start date
B

Ben Bayliss

Hey all,
I've started designing a database to catalog all the equipment within
the music school at University. So far I've done pretty well and have
managed to find some excellent tutorials online. I've never really used
Access before, so I think I'm doing really well.

Aaaanyway - I'm stuck. Hopefully someone can help me with this:

I'm making a Form into which the user will input parameters to drive a
Dynamic Query (thanks to some nifty SQL). It's working fine and
currently is opening the SubForm into its own window.

However I (obviously) want the subform to be drawn in the same Form
window (directly below the 'search' button). When I've tried putting it
there though the list of records doesnt update until I close the form
and reopen it.

I've tried Requery in a few places, but that doesnt seem to be working.
So basically, my question (I think) is how do I force the SubForm to
access the query upon which it is based?

Let me know if you need more info to help out...

Thanks,
Ben
 
Two common methods:

(1) use a command button on the main form to have the subform get its data
and be made visible:
Private Sub cmdButtonName_Click()
Me.Subform.Requery
Me.Subform.Visible = True
End Sub

where Subform is the name of the subform control (the control that actually
holds the subform) on the main form.


(2) use the AfterUpdate event of a control on the main form (not the best
answer if more than one control are being used to get the parameters):
Private Sub controlName_AfterUpdate()
Me.Subform.Requery
Me.Subform.Visible = True
End Sub
 
Ken said:
Two common methods:

(1) use a command button on the main form to have the subform get its data
and be made visible:
Private Sub cmdButtonName_Click()
Me.Subform.Requery
Me.Subform.Visible = True
End Sub

where Subform is the name of the subform control (the control that actually
holds the subform) on the main form.
Click to expand...

Ken,

This is the method I've been trying so far (the command button also
executes the script which builds the query) but the requery doesnt
update the list.

Any more ideas? :)

Ben.
 
Post what your code is. Also post the SQL of the subform's query. Do you
have any Link Master Fields and Link Child Fields set on the subform
control?
 
Ken said:
Post what your code is. Also post the SQL of the subform's query. Do you
have any Link Master Fields and Link Child Fields set on the subform
control?
Click to expand...
Heya.

Code is pasted below. I don't have any master/child links set up.

On pressing the 'search' button, the SQL defines the actual code of the
Query itself, then opens the subform seperately (for the moment) as well
as ReQuery-ing the subform 'Results'. The form opened in its own window
works when closing/reopening it, but it wont update when the button is
pressed. Equally, the subform wont update until the entire form is
closed then reopened.

Hope it's not too nasty to battle through!

Ben.

-----CODE BEGINS-----

Private Sub cmdOK_Click()

' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strBuilding As String
Dim strRoom As String
Dim strEnteredBy As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryDynamicQuery") Then
Set qdf = db.CreateQueryDef("qryDynamicQuery")
Else
Set qdf = db.QueryDefs("qryDynamicQuery")
End If
' Get the values from the combo boxes
If IsNull(Me.Building.Value) Then
strBuilding = " Like '*' "
Else
strBuilding = "='" & Me.Building.Value & "' "
End If
If IsNull(Me.Room.Value) Then
strRoom = " Like '*' "
Else
strRoom = "='" & Me.Room.Value & "' "
End If
If IsNull(Me.EnteredBy.Value) Then
strEnteredBy = " Like '*' "
Else
strEnteredBy = "='" & Me.EnteredBy.Value & "' "
End If
' Build the SQL string
strSQL = "SELECT Inventory.* " & _
"FROM Inventory " & _
"WHERE Inventory.Building" & strBuilding & _
"AND Inventory.Room" & strRoom & _
"AND Inventory.EnteredBy" & strEnteredBy & _
"ORDER BY Inventory.Building,Inventory.Room;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acForm,
"qrySearchDataBaseResults") = acObjStateOpen Then
DoCmd.Close acForm, "qrySearchDataBaseResults"
End If
' Open the results in its form
Me.Results.Requery
Me.Results.Visible = True
DoCmd.OpenForm "qrySearchDataBaseResults", acFormDS

cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub

cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

-----CODE ENDS-----
 
Whew...that's a complicated way to get to the result that you seek. Let's
simplify your setup this way.

Create the query based on your SQL, except for the parameters. Save the
query using the name qryDynamicQuery. Then, put expressions similar to the
following as the criteria for the indicated fields:
Inventory.Building
[Forms]![MainFormName]![Building] Or
[Forms]![MainFormName]![Building] Is Null

Inventory.Room
[Forms]![MainFormName]![Room] Or [Forms]![MainFormName]![Room] Is
Null

Inventory.EnteredBy
[Forms]![MainFormName]![EnteredBy] Or
[Forms]![MainFormName]![EnteredBy] Is Null

Save and close the query.

Then set the RecordSource of the subform's form object to this query.

Then use this code on the cmdOK button's OnClick event:

Private Sub cmdOK_Click()
Me.SubformName.Requery
Me.SubformName.Visible = True
End Sub
 
Ken said:
Whew...that's a complicated way to get to the result that you seek. Let's
simplify your setup this way.
Click to expand...
*snip*

Thanks Ken, I'll try attacking that right now.

The reason I steered away from the 'Or ... Is Null' is because I
originally did it this way, but after expanding this to a few more
parameters the query became too complicated for Access to handle.

I'll let you know how we get on!

Ben.
 
Ben,

I think that Ken has got your question covered, and knows
a great deal more than I do, but here's my two cents. I
was having similar trouble and ended up using RunCommand
acCmdRefresh rather than Me.Control.Requery. Don't know
why it worked, but it for my circumstance.

Just a thought.

Anne
 
I've used this technique with many fields and have had no problems. However,
note that the query designer, after you save and close the query, will
"change" the appearnce of the criteria so that they'll look quite different,
and will not be easy to update/change without careful editing. So best to
get all the fields in there initially with this syntax.

But it'll still be easier as a setup than using the code sequence that
you've been trying to use.
 
Ken said:
I've used this technique with many fields and have had no problems. However,
note that the query designer, after you save and close the query, will
"change" the appearnce of the criteria so that they'll look quite different,
and will not be easy to update/change without careful editing. So best to
get all the fields in there initially with this syntax.

But it'll still be easier as a setup than using the code sequence that
you've been trying to use.
Click to expand...

Hi Ken,

Well - it all works programattically. Thank you!

I've now compared the two and I think I understand why the old version
didn't work:

It seems as though once the query has been opened, whether as a subform
or by itself, a requery wont work since the entire SQL code of the query
has been changed, not just the data upon which it is based. Requery
seems to only recheck the data, not the query's code itself. Is there
another command which fulfils this requirement?

I am having some problems with your code though. You're right in saying
that the query design view changes a huge amount, and it gets very
complicated very quickly. The end product will have to accomodate ~13
criteria values, some combo boxes allowing for nulls, and some text
boxes allowing for wildcards / only the first few letters.

Last night when I widened the query using your method, I found that
Access started crashing upon entering query view, so I'm a bit reluctant
to continue using this method, although if it is the only way then I
guess I must!

Can you remind me also how to modify your '[x] or [x] Is Null' so that
wildcards / few letters only responses become valid?

Thanks again,
Ben.
 
If your query crashes when you try to make changes, chances are that the
source of the crash is not the query, but rather the AutoCorrect option
(strange, but true!). Turn off that option (Tools | Options | General tab),
as it is the source of numerous, strange buggy things in ACCESS.

To do wildcards where you'd just enter the beginning letters of the field's
value,

[Forms]![MainFormName]![Room] & "*" Or [Forms]![MainFormName]![Room]
Is
Null


With respect to requery, any time you change the RecordSource of a form or
subform, the form/subform will automatically requery. If you change the SQL
programmatically in the query but the form is already bound to an earlier
version of the query, I don't believe the form will "find" the new version
unless you set the RecordSource to "" and then reset it to the query,
thereby forcing the requery.

In some cases, this latter technique may be an appropriate approach.
However, if I were doing that, I would not use a stored query and change its
SQL. Instead, I'd either hardcode the SQL in the form's module, or else I'd
copy the SQL from a stored query, and build the appropriate, complete SQL
statement, and then set the form's recordsource to that SQL statement that
I'd built.

--

Ken Snell
<MS ACCESS MVP>


Ben Bayliss said:
Ken said:
I've used this technique with many fields and have had no problems. However,
note that the query designer, after you save and close the query, will
"change" the appearnce of the criteria so that they'll look quite different,
and will not be easy to update/change without careful editing. So best to
get all the fields in there initially with this syntax.

But it'll still be easier as a setup than using the code sequence that
you've been trying to use.
Click to expand...

Hi Ken,

Well - it all works programattically. Thank you!

I've now compared the two and I think I understand why the old version
didn't work:

It seems as though once the query has been opened, whether as a subform
or by itself, a requery wont work since the entire SQL code of the query
has been changed, not just the data upon which it is based. Requery
seems to only recheck the data, not the query's code itself. Is there
another command which fulfils this requirement?

I am having some problems with your code though. You're right in saying
that the query design view changes a huge amount, and it gets very
complicated very quickly. The end product will have to accomodate ~13
criteria values, some combo boxes allowing for nulls, and some text
boxes allowing for wildcards / only the first few letters.

Last night when I widened the query using your method, I found that
Access started crashing upon entering query view, so I'm a bit reluctant
to continue using this method, although if it is the only way then I
guess I must!

Can you remind me also how to modify your '[x] or [x] Is Null' so that
wildcards / few letters only responses become valid?

Thanks again,
Ben.
Click to expand...
 
Back
Top