Bind query to subform

[chriskellerx10]

Hello everyone. I am very new to access...but I am a .NET developer trying to do some forms.
I was looking for a grid control. I tried a list box at first, which worked okay, other than the fact I could not get it to auto-size columns and having to specify the number of columns.
I am trying to use the same control, whether it be a list box,sub-form,etc...

The sub-form is nice except when I bind the child-form to the query, the rows or row counts show in the data-sheet, but none of the fields are present. So I can see a row, but no fields.

Any help would be great.

 

[hk1]

A datasheet form is your answer but I'm not understanding this line:
"the rows or row counts show in the data-sheet, but none of the fields are present. So I can see a row, but no fields."

Will you have some fields on the main form and some on the subform? Are they two different tables linked through some common field? Is this sort of like a typical invoice header form with invoice detail records?

 

[chriskellerx10]

Thanks for your reply.

I was looking for a dynamic data presentation control that I could bind different queries from different tables to the same control.
I think my understanding is...I would have to create a form for each query and add that for to the sub-from, right?
What I am currently using is a blank form I just called 'test'
It has no controls or anything added to it, I just set its properties to be a data sheet.
So in the VB code of the parent form, on an on_click event of a button, I set the record source of the sub-form to a written query...not a saved query.
That all executes fine without error. No fields/columns are visible in the child-form, but the row counter of the data sheet to the left displays the proper number of rows that should be there for the query.
The bottom of the data sheet says that there are 4 records,(which is the number of records my query should return), but the rows are just blank.

I hope this makes sense...I keep urging the person I am doing this for to let me to a winForm for them...but they really want this first.

 

[hk1]

All you have to do is change the RecordSource for the form. I put my SQL statements directly in the RecordSource property. No need to develop and save your queries unless you prefer to do that. You're going to have to change the recordsource (or is it rowsource) for each textbox if you're changing tables and the tables do not have identically named fields.

If you're not changing tables you can just use the forms Filter property. It should look just like an SQL WHERE statement without the "WHERE". Be sure to set the FilterOn property to true after applying a filter.

 

[chriskellerx10]

OK. So I do have to make a form with text boxes in it for each query. Thats my problem I guess. I didn't put anything on the form at all... because the queries I am using pull different fields.
So if I'm correct...I need to make a form for each table I use...and change the childForms source object parameter to match the form I need?
Thanks for your help.

 

[hk1]

> So I do have to make a form with text boxes in it for each query.
No, not necessarily. You do need to create the textboxes yes. However, you can change the row source on the textboxes every time you change the forms recordsource. However, I don't really recommend this approach. This gets really complicated if you want to start putting some validation or logic in VBA.

It will probably be better to build different forms for your different queries, assuming that these queries are quite different from each other and not merely filters.

And yes, you can change out the source object to choose the correct subform based on some selection in your main form. I don't think this is a very common approach but I have used it in some databases.

 

[gemma-the-husky]

i think you are better having a new form for each record source (query)

the reason is that most forms tend to end up needing some code - and the code that works with data source A, may not be appropriate for data source B.

you can then actually choose 3 different form types (I think there are others in Access versions after A2003)

A single form. Shows all the fields for each record as a single entity,generally with navigation buttons to advance/move to different records

A data-sheet. Probably this is the grid you mention. Shows all the data in rows of similar fields - like a spreadsheet. You can resize/move/sort/filter fields dynamically.

A continuous form. Similar in content to the data-sheet, but with more control over presentation.

Often a matter of personal choice which of these last 2 you prefer to use, in general


there are wizards which will make these basic forms automatically. use these as a starting point, while getting used to designing forms.

don't worry about ending up with large numbers of queries and forms. That's pretty normal, I think.


In larger apps, you will need several forms open at a given time. Hard to do, if you are trying to reuse a single form!