dynamic query (1 Viewer)

[mdcory]

I hope I can explain this and make any sense.

I am trying to make a form where I can choose in a query what fields I want displayed.

I have a combobox that I choose the table, and another combobox that list the fields that are available to view. So I get: tblZincWastewater.Analyst. Combobox#1 gives me tblZincWastewater and combobox#2 gives me Analyst.

Is anyone still following me?

I want to have 5 sets of choices, and have them display in a subform on that form.

I was able to get the query to work and get the data that I was looking for by doing this and doing it 5 times and creating a new query for each set:

strSQL = "SELECT " & Forms!Form1!analysisname1 & "." & Forms!Form1!headingname1 & ", tbldate.Field1 FROM (tblDate LEFT JOIN " & Forms!Form1!analysisname1 & " ON tblDate.Field1= " & Forms!Form1!analysisname1 & ".CollectionDate)" & " WHERE (((tblDate.Field1) Between #" & [Forms]![Form1]![DateStart] & "# And #" & [Forms]![Form1]![DateEnd] & "#));"
Set qdf = dbs.CreateQueryDef("Summary1", strSQL)

But I can't get the subform to work since, I think, because the field names change.

Anyone have a better way of going about this? Thanks in advance for any help.

 

[DALeffler]

If you're displaying a subform on a main form, the field names of the subform won't change unless you change them, but referencing the fields on the subform with code running in the main form most certainly will.

To reference the subform controls from the main form, you must use the form property of the subform control:

Me!sbfrmControlName.Form!ControlNameOnSubForm

So if your MainForm has a subform named "sbfrmDetails" on it, to reference a control on sbfrmDetails named "MyField" with code running from MainForm is:

Me!sbfrmDetails.Form!MyField

or more explicitly:

Forms!MainForm!sbfrmDetails.Form!MyField

hth,

 

[mdcory]

If I read what you are saying correctly, I'm not having problems referencing the subform. It seems access is having problems inserting the subform with dynamic fields. I get an "Application defined or object defined error". I guess I am also kinda looking for a better way to do what I am doing. I feel that the way I am going about seems like it's the "hard way". I have looked through the sample databases and the like but haven't found anything like what I am trying to do. Sorry I am learning as I go.

 

[mdcory]

I finally got it. I stumbled across this site. Great info and very well explained. http://www.fontstuff.com/access/index.htm

 

[mdcory]

Any body know if there is a way to make a query to get all the field names in a table? I have a dynamic query that, from comboboxes, I select the table name, field name and the critiria for the query. I want the field name box to show what fields are in the table to choose.
Thanks,
Matthew

 

[Jon K]

Set these properties in the combo box:-

Row Source Type Field List

Row Source TableName
.

 

[mdcory]

Well duh that was easy, I had seen that before but didn't even think about it, I guess I can blame that on lack of sleep, but I think it's just stupidity. Thanks...