Form/subform Multiple record source question

[thewrightdesign]

I am using Access 2003.

I have a form containing student information that shows information from more than one table. I know I can create subforms to show some of this information, but the main trouble I have with subforms is that they are not searchable from the main form; I'm having trouble with that on my main information form but that's another thread.

I guess my question is: is it possible to pull information from more than one record source on a form without using subforms? The expression builder that I can access from the control source in properties for each text box seems to build an expression referencing a table that is not the control source for the basic form, but when I go into form view it still shows #Name? error in each box. The expression built is:

=AssetInfo![English Score]

where AssetInfo is a second table and English Score is a field in that table. Main record source for the form however is CounselingData. Text boxes referencing this table of course show up fine.

I hope this made sense to someone besides me, if not I'll be happy to provide more information.

 

[pbaldy]

Well, a form can be based on a query instead of a table, and that query can pull from multiple tables. The problem is that often the query will be read-only, and the presentation of one-to-many data will probably not look as good as the form/subform approach.

 

[guinness]

I built 2 forms with 4 subforms each of which are editable if that's what your after. In the header of the subform I have a dropdown to select payroll number. Each subform also contains the field payroll number (hidden). When I added each subform I followed the wizard but then took the option to define my own link. Form payroll number=subform payroll number.

When you select a payroll number on the main form all of the subforms display table data of that record. In my update form you select payroll number and week commencing date on a new form each of the subforms is then ready for inputs into a new record for that payroll number on that date.

Don't know if that helps but good luck

 

[thewrightdesign]

I built 2 forms with 4 subforms each of which are editable if that's what your after. In the header of the subform I have a dropdown to select payroll number. Each subform also contains the field payroll number (hidden). When I added each subform I followed the wizard but then took the option to define my own link. Form payroll number=subform payroll number.

When you select a payroll number on the main form all of the subforms display table data of that record. In my update form you select payroll number and week commencing date on a new form each of the subforms is then ready for inputs into a new record for that payroll number on that date.

Don't know if that helps but good luck

What I need is to be able to search on all fields, in the form and in the two subforms that I have on the main form. So far I've not found a way to do that. Filter by form simply will not search on the subforms. We are fairly well resigned to the fact it's a design flaw in Access (lotus approach does it with no problem) and that we'll have to do any searches on the subforms directly from their main page rather than from the front screen which is our main database page.

I don't want combo boxes or dropdowns since ours isn't structured that way. It's a database containing student information including enrollments and grades, and we need to be able to search based on what school they are attending, what institution they are in, what semester they attended, what year, what class etc. Drop downs or combo boxes are impractical for this, we want simple text input fields to search on.

 

[thewrightdesign]

Well, a form can be based on a query instead of a table, and that query can pull from multiple tables. The problem is that often the query will be read-only, and the presentation of one-to-many data will probably not look as good as the form/subform approach.

Thanks for your reply, but as you said read-only won't work for us. I actually solved this particular issue by splitting it into separate forms, we didn't need to have all the information grouped onto just one so now each one is searchable on it's own form and works fine.

Still have the issue of not being able to search on the two subforms on my main form though. That one I really need to find a way to make happen so that we don't have to go to each subform page to search individually.

 

[pbaldy]

You might not be able to search the subforms using built-in tools, but I can't imagine it would be hard to code. More info about what you're trying to do might help, but looking into another form or subform's data isn't hard.

 

[thewrightdesign]

You might not be able to search the subforms using built-in tools, but I can't imagine it would be hard to code. More info about what you're trying to do might help, but looking into another form or subform's data isn't hard.

I've not been able to find out how to code it to work so any help you might give would be much appreciated. Not sure what more info you might need though.

I have a main form StudentInfo that has two subforms Enrollment and Grades. I want to be able to do a ctrl+F (already have a macro set for this) that will bring up a blank form with editable fields that I can type in anything into any of the fields on form and subform and it will filter the records.

What I have right now after much talking to others here and on other boards, is filter by form. Filter by form will filter out exactly as I want it to but it will not include the subforms, it will only filter on the main StudentInfo form. Our current database is Lotus Approach and it does this, but I can't seem to get help or find the answers on how to make Access do this function. We do have Crystal Reports that we can tie in if needed (we were told the reason that Approach can do this advanced find is because File Maker Pro is tied to it).

I was working with another method where I created a blank form to use for searching (query by form) but that's where I ran into coding issues of not being able to pull from more than one table for the search.

What other info would help you understand what we are needing to do?