RecordSource problem

[mikej]

I'm trying to update a subform using record source. As you can see when I state:

Forms![main]![sub].Form.RecordSource = "Select * from [query]"

this only results in my subform having one record whereas the actual query has 507 records. Please could you tell me how to assign the data in the query to the subform,

I've been trying to sort this out for hours. Any thoughts very much appreciated,

Mike

Dim dbs As Database
Set dbs = CurrentDb
Dim rst As Recordset

Forms![main]![sub].Form.RecordSource = "Select * from [query]"
Me.Requery
Me.Refresh


Set rst = dbs.OpenRecordset("Select * from [query]")
Debug.Print rst.RecordCount & " Query Records" //outputs 507 records


Set rst = Forms![a_f_contacts]![k_f_list].Form.RecordsetClone
Debug.Print rst.RecordCount & " Records" //outputs 1 record

 

[CyberLynx]

Forms![main]![sub].Form.RecordSource = "YourQueryName"

I'm assuming of course that you have the relative field within the query for the Link Master and Link Child Fields properties and if not then ensure there is nothing in the those properties.

.

 

[mikej]

Thanks for the suggestion. It does not make any difference sadly.

 

[CyberLynx]

If you have a query that pulls up over 500 results and your SubForm was originally based from and is now bound to that query then your SubForm should display over 500 records.....unless you have an active Filter on the SubForm.

Go to the SubForm from the Database Objects Window and place the SubForm into Design View. See if there is something within the Filter property of the Form. If there is....delete it.

.

 

[mikej]

There is nothing in the filter. If I open the subform as a separate form I can see all the data, but when the subform is in the main form I can only see the form's header and footer.

 

[CyberLynx]

Make sure the Link Master Fields and the Link Child Fields properties are properly set. The properties are located under the Data tab in the properties window for the SubForm Control.

Try removing these links. What happens?

.

 

[mikej]

Both Link Master Fields and the Link Child Fields properties are blank, As I say if I open the sub form separately or look at its underlying query I can see the data displaying correctly.

 

[missinglinq]

this only results in my subform having one record

when the subform is in the main form I can only see the form's header and footer.

Your story appears to be changing as we go along! Does one record show, as in your first statement, or are there no records showing, as in your later statement?

 

[mikej]

Generally no records sometimes one record, never the same as the underlying query. Tried copying the code to a new database with the same forms recreated and it works. Very confusing!

 

[missinglinq]

That would tend to indicate some kind of corruption, which is confusing, because corruption can take on all kinds of appearances.

 

[CyberLynx]

I would suggest you remake your SubForm based on your Query. Perhaps there is something in the Query the Form can't handle. Use the SubForm wizard and see what happens.

.

 

[missinglinq]

Didn't he say he has it working now?

 

[mikej]

Very confused now....

I created a new database and main form, then copied just the troublesome parts to the new database. The search worked fine which seems to indicate the code is okay.

The part of the system that has been causing problems consists of two subforms. A module, triggered by a command button, takes data from the first subform and populates the second subform. All is well until I assign a query (or table) to the main form's record source property. Then the search runs, but the subform is not updated. Not sure what the form's record source has to do with the updating of the subform, but this is what is causing the problem and I don't know why.

Any ideas?

Thanks again for you input,

Mike