Subform Refreshing problem 2

[Terminus]

Hi there,

I have a form with a subform in it. The sub form is based on a query. There is a combo box which allows the user to filter the information in the table which hosts the query which in turn the subform is based. Unfortunetely i cant the subform to refresh on the main form to show the update.

I have tried:

forms!frmMain[frmsub].form.requery

and

forms!frmMain!frmsub.setfocus
requery

Neither seem to work. When i close the form down and reopen it the changes to the subform are made, but this seems the only way i can make the form refresh can any1 help!

I am just learning vb so please can the responses be gentle

Terminus

 

[KenReay]

Hi

Is the combo box on the main form?, if yes

Me.MySubFormControl.Requery

where MySubFormControl is the name of your subform CONTROL, note this is not neccessarily the name of the sub form contained within the sub form control

if the combo box is on the sub form, then a simple Me.Requery should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Terminus]

Hi

Wow that was a fast response and thanks.....but,

Okay im really new to programming. I tried entering the name of the sub form in:

Me.MySubFormControl.Requery

But nothing happens. Okay this may sound thick but what is the CONTROL in a subform? I dont know what this is to be able to enter it in the vb statement.

Please help

Terminus

 

[KenReay]

Hi

If you look on the toolbox, in design view, you will see there is a subform control, within that subform control you place a form, this is done by specifying the name of the form as the subforms .source property (out of interest but not relevant here, you can even change the form name at run time and display a different form..but back to the subject).

The form which is displayed within the sub form control, is a in fact a form like any other and may be displayed independantly.

OK?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Terminus]

Cheers once again for your fast response....but

the control is named subRestrict which is the name of the subform aswell.

Maybe i should be a little more detailed in my problem, here goes:

The subform is independant of the actual form itself as it has no links. What it provides is a list of contacts. On the main form is a function which allows sets parameters to restrict the information (basically a WHERE statement) this works and the query updates and can be seen in the actual query itself. The subform itself shows the information of that query however it just refuses to update the info on the form. The only way i can get it to work is by exiting and reentering the form and the info is there on the form.

Any other ideas? Its really bamboozeling me

If not could i send u a version on the db for you to look at?

Terminus

 

[KenReay]

Hi

Are you changing the SQL of the saved query ?

Form you explanation I suspect the answer is yes

The changed querydef is not effective within the (sub) Form until the form is reloaded (as you are finding), that is just the way it works

You could set the recordsource of the form to an SQL string which you cange at run time, or better in my view, based the subform on a query which includes the WHERE clause you are building in code and just change the values of the criteria, in which case the requery will work

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Terminus]

HI thanks for the advice,

Yes what i am doing is changing the saved query by building the SQL in the form. That must be the reason why it wont update.

but im not quite sure how u mean in your alternative to my problem, is there any chance u could give me a quick example of what you mean? I know this is a pain, but am really new to access and programming and stuff and really am apprciating the help

Terminus

 

[KenReay]

Hi

It is difficult to give an example, it would be easier with a sample mdb

But to change the datasource of the sub form I am taloking about code like

Me.MySubFormControl.FORM.Recordsource = strSQL

where strSQL is a string variable conatining an SQL statement

the other suggestion was say we wanted to show in the subform only records with column x = value in cboName on the main form (frmMain)

If you made your (saved) query have a criteria of Forms!frmMAin!cboName on column X, and based your subform on that query, if you put Me.MySubForm.Requery in the after update event of the combo box (cboName) that should work

Hope that is understandable, I am afraid my Access is better than my powers of explanation !!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Terminus]

YEah kind of get it, but still confused, sorry i know how difficult it is to explain access stuff is there any chance you could email a sample. Know this is really cheeky. If you dont want to, i quite understand.

Though if you could could u send it to me zipped to either Rossrod@hotmail.com or ross.mckenzie@nemhpt.nhs.uk?

Really am desperate for help.

If not thanks for all your time and help, it has been most valuable.

Terminus

 

[NRK]

Thanks for the input. I'd thought of that solution but was unsure how to implement. After more experimentation, it would seem that the filter is overriding the query.

Currently, a value in the combo box Name_ID must be selected before the Subform can be populated. The subform's underlying query has a SQL WHERE statement related to the combo box value - WHERE (((qryData.Name_ID)=[Forms]![frmCoverage]![cboName]));.

To add a second WHERE statement seems straightforward, but since the combo box (cboCoverage) doesn't have to have a value, I kept getting a NULL subform unless the combo box was populated.

How would I overcome this?

 

[KenReay]

Hi

WHERE qryData.Name_ID)=[Forms]![frmCoverage]![cboName] AND (IsNull([Forms]![frmCoverage]!cboCoverage) OR qryData.Coverage = [Forms]![frmCoverage]!cboCoverage);

I have assumed the name of the column to be compared to cboCoverage is Coverage

Regards

Ken Reay

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[msacoor]

HI,

I jhave a similar problem along these lines. I am using Access 2003 and have a subform embedded in a main form. The main form has a command button which builds a SQL statement, which inturns writes records to a table. The subform's underlying data is this table. I caanot get the subform to refresh everytime i write new records to the table.

I have tried refresh and requery as follows in the command button of the main form to no avail.

me!subSearch.form.refresh
me!subSearch.form.requery

Please can someone help before I tear my ever receding hair out!

Mo

 

[KenReay]

Hi

In the on click event of the command button:

.. code to build and execute the INSERT SQL string

Me.MySubFormControlName.Requery

should do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[msacoor]

Ken,

Thanks for your reply.. I tried that and still the subform doesnt refresh

Here is the code I am using behind my command button

.##################

Set lrsSource = New ADODB.Recordset


lrsSource.CursorType = adOpenStatic

lrsSource.Open lsSQL, oConn


If lrsSource.RecordCount < 1 Then

MsgBox "No records found", , "SELECT DIFFERENT SEARCH CRITERIA"
' need to clear all text boxes on form
DoCmd.Hourglass False
GoTo Reset_Searches

Else

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDelSocsFound"
DoCmd.SetWarnings True

strcnn = "C:\LOTTERY_DEVT\LOTTERY_PROTOTYPE.mdb"
strcnn2 = "C:\LOTTERY_DEVT\LOTTERY_PT_FE.mdb"

Set oConn2 = New ADODB.Connection
oConn2.Provider = "Microsoft.Jet.OLEDB.4.0"

oConn2.Open strcnn2

Set lrsDest = New ADODB.Recordset

lrsDest.LockType = adLockOptimistic


lrsDest.Open "T_SOCS_FOUND", oConn2, , , adCmdTable

lrsSource.MoveFirst

While Not lrsSource.EOF
lrsDest.AddNew
lrsDest.Fields("SOCIETY_NO") = lrsSource.Fields("SOCIETY_NO")
lrsDest.Fields("FOLDER_REFERENCE") = lrsSource.Fields("FOLDER_REFERENCE")
lrsDest.Fields("SOCIETY_NAME") = lrsSource.Fields("SOCIETY_NAME")
lrsDest.Fields("ADD1") = lrsSource.Fields("ADD1")
lrsDest.Fields("ADD2") = lrsSource.Fields("ADD2")
lrsDest.Fields("TOWN") = lrsSource.Fields("TOWN")
lrsDest.Fields("COUNTY") = lrsSource.Fields("COUNTY")
lrsDest.Fields("POSTCODE") = lrsSource.Fields("POSTCODE")
lrsDest.Fields("CONTACT") = lrsSource.Fields("CONTACT")
lrsDest.Fields("PROMOTER") = lrsSource.Fields("PROMOTER")
lrsDest.Fields("SOCNUM") = Mid(lrsSource.Fields("SOCIETY_NO"), 1, 3) & "/" & Mid(lrsSource.Fields("SOCIETY_NO"), 4, 4) & "-" & lrsSource.Fields("FOLDER_REFERENCE")
lrsDest.Fields("ADDRESS") = lrsSource.Fields("ADD1") & ", " & lrsSource.Fields("POSTCODE")
lrsDest.Update
lrsSource.MoveNext
Wend

DoCmd.Hourglass False

Me.subSearch.Requery



End If


'########################


so basically I have copied a recordset into a new table. This works because I can see that the table does have the records in it.


The subform is called subSearch and the underlying form is frmResultsFound.

Your suggestion means I retype as follows Me.subsearch.requery

I have also tried me.subsearch.form.requery as well as numerous other combinations.

This doid not work............. Any other suggestions?