Datasheet subform vs fields in recordsource

[hbrems]

Hi all,

as per the title I've got a subform formatted as datasheet. On the main form I'm changing the recordsource of this subform:

subOverview.Form.Recordsource = "SELECT * FROM tblCustomers"

Let's say this results in 10 columns being returned. Now I'm doing another query:

subOverview.Form.Recordsource = "SELECT FirstName, LastName FROM tblCustomers"

Now only 2 columns get returned and the other columns display #Name.

I understand that this is being caused by the fact that I added all these fields to my subform during design. But obviously I would like that fields that are not in my query are not in the datasheet.

Is there a simple solution?

Thanks in advance!

 

[missinglinq]

Is there a simple solution?

Depends on your definition of 'easy!'

You could have a second form, identical form, by simply copying the first one, superimposed over the first one. On this one leave the unused fields unbound by simply going into Design View and setting their Control Sources to nothing.

Then set each form's Visible Property to True or False, as appropriate, when you change it's RecordSet.

Linq ;0)>

 

[vbaInet]

Or since you're displaying the subform in Datasheet view then you can change the SQL string of a querydef and requery the subform.

 

[hbrems]

Or since you're displaying the subform in Datasheet view then you can change the SQL string of a querydef and requery the subform.

I've never worked with a QueryDef object. I don't mind looking into it. However, am I going to bind the recordsource property to this object then? And how will this change the number of fields in the subform. Won't they still be there?

 

[vbaInet]

The querydef will be the Source Object of the subform control. The querydef will handle the display of the fields and the subform control will just display the querydef as a view.

Aircode:

Dim qdf as DAO.Querydef

set qdf = Currentdb.querydef("NameOfQuery")

qdf.SQL = "SELECT ..."

set qdf = nothing

me.subformcontrol.form.requery

If you want meaningful names for your fields then you need to give them aliases.