Run a search on load for one-to-many related tables

[QuantifyRisk]

So, I have a small database for work that I am trying to create. This little problem is not major but it's annoying me because I feel the answer is simple. However, it's so complex to me that I don't even know how to search for it.

Enough about me. I have a form that is bound to a table Scorecard. Company is related to Scorecard in a one-to-many relationship. When I run a search form I created to load a record from Scorecard, the data on the form that is from Company does not load. I hope that explains it.



Any help would be greatly appreciated. Thank you.

[June7]

What is purpose of 'search' form? To open report based on located record? Is form also used for data entry?

[QuantifyRisk]

It's to search for a record to view or edit the data. You can filter down the field with two textboxes and select a record to open. It is not set to allow edits or additions.

[June7]

If you don't allow edits, then how can you edit data?

A form can enter/edit data for only one table.

If this a form/subform arrangement?

[keviny04]

If you don't allow edits, then how can you edit data?

I'm guessing he has multiple separate forms, and one has uneditable fields, while others are fully editable. If so, then the forms have to communicate with one another properly to show correct values. It could be something simple, like a missing Requery action, or other more complicated issues.

A form can enter/edit data for only one table.

Not true. A form can enter/edit data for one data set, which can comprise of more than one table. Of course, some data sets are not updatable, but some are.

[June7]

I should have said 'Normally' only one table because creating new records on multiple tables from one query/form is not possible (or what do I not know?). I do have a db with 1 to 1 related tables (don't ask why) where I do use one form to edit the existing records from multiple tables. Was just trying to keep reply simple.

[QuantifyRisk]

You search on the non-editable form. Select a record. Then it loads on the editable form, but it's not pulling in the data that comes from the related table ("Parent" table doesn't load). I'm not expecting it to work without code, I just have no idea what that VBA code is. Searching for it has turned a million different things but not what I'm looking for.

[orange]

I suggest you post a copy of your database along with a description --point form - of exactly what you are trying to do. An example with what you'd put in and what you'd get out etc would be helpful.
Good luck

[QuantifyRisk]

I would prefer to not do this for security reasons.

[keviny04]

You search on the non-editable form. Select a record. Then it loads on the editable form, but it's not pulling in the data that comes from the related table ("Parent" table doesn't load). I'm not expecting it to work without code, I just have no idea what that VBA code is. Searching for it has turned a million different things but not what I'm looking for.

I was just assisting someone in another thread who also had trouble linking tables together on a form. Is your form layout similar to his (screenshot)? I'm guessing a main form with a search box, one uneditable subform showing Scorecard data, and one editable subform showing Company data? If so, then your Scorecard subform is not passing the right value to the Company subform. Let us know if this is your layout, and if not, at least describe what the layout is.

The solution I gave to the other person did NOT require VBA coding. The only thing I feel that needs coding is the searching part, but you seem to have figured that out.

[QuantifyRisk]

My search form is similar to Allen Browne's website. When I say "similar" I mean exactly. The problem is not with the search form. The problem is with the form it opens when you select the appropriate record. The form it opens is linked to a table. The table (many) is related to another table (one). I'm trying to get the form to also load the information in a few textboxes from the "one" table.

I'm assuming it'll have to query the "one" table for the primary key orf the "many" record. Not sure if it can do that though even though the "one" table has the "many" table as a sub-table with the "+" drop-down.

[keviny04]

My search form is similar to Allen Browne's website. When I say "similar" I mean exactly. The problem is not with the search form. The problem is with the form it opens when you select the appropriate record. The form it opens is linked to a table. The table (many) is related to another table (one). I'm trying to get the form to also load the information in a few textboxes from the "one" table.

Check if the form's record source has the correct query. If the SQL statement is composed properly, it should show data from the "one" table. Is the correct type of join used in the SQL? Is the join linking the correct fields? If possble, show us what the SQL statement is. Even if the SQL is correct, the query would not work if your tables didn't have good data, which brings up my next point:

I'm assuming it'll have to query the "one" table for the primary key orf the "many" record. Not sure if it can do that though even though the "one" table has the "many" table as a sub-table with the "+" drop-down.

Does the sub-table show correct data? If your tables don't have the proper relationship and normalization, no query would help. You said you couldn't upload the database, but can you at least upload a screenshot of the table relationship diagram?

[QuantifyRisk]

The Record Source = Scorecard (the "many" table). I am trying a few ways through the forms On Load VBA because I do not want it to do this search when you're entering in a new record; only when you load a record from the search form. On Load checks to see if a required field is null or not (obviously a new record is null and a loaded record is not null). After that, I have no idea. This is my logic but it doesn't work because it's not set as a subform.

Is Not IsNull(field) Then
me.number=me.parent.number
End if

Do I need something like this: http://www.techonthenet.com/access/m.../recordset.php

[keviny04]

The Record Source = Scorecard (the "many" table). I am trying a few ways through the forms On Load VBA because I do not want it to do this search when you're entering in a new record; only when you load a record from the search form. On Load checks to see if a required field is null or not (obviously a new record is null and a loaded record is not null). After that, I have no idea. This is my logic but it doesn't work because it's not set as a subform.

Is Not IsNull(field) Then
me.number=me.parent.number
End if

Do I need something like this: http://www.techonthenet.com/access/m.../recordset.php

You DON'T need code to pull value from another table if your record source has the right SQL. Right now, you only have Scorecard in your SQL, so of course it doesn't pull values from the Company table. Use an SQL that links the two tables together.

Is this the Allen Browne form you are trying to copy? Must be, because it also uses a single-table record source.

Forget the technothenet function you linked to. There is a Dlookup built-in function that does the same thing. Two rules of thumbs: use built-in functions over custom functions whenever possible, and use SQL over VBA code whenever possible.

Let me know if that's the Allen Browne form you are using. I may be able to modify it to show you how it should be done, and it will require NO CODE in pulling out the value from the table (except all the code Browne put in for other events in the form).

[QuantifyRisk]

You DON'T need code to pull value from another table if your record source has the right SQL. Right now, you only have Scorecard in your SQL, so of course it doesn't pull values from the Company table. Use an SQL that links the two tables together.

Is this the Allen Browne form you are trying to copy? Must be, because it also uses a single-table record source.

Forget the technothenet function you linked to. There is a Dlookup built-in function that does the same thing. Two rules of thumbs: use built-in functions over custom functions whenever possible, and use SQL over VBA code whenever possible.

Let me know if that's the Allen Browne form you are using. I may be able to modify it to show you how it should be done, and it will require NO CODE in pulling out the value from the table (except all the code Browne put in for other events in the form).

Yes, that's the form I copied. Thanks for your help. I've been able to answer a lot of my own questions by using the Internet and some of the basics I know from programming but this one different to me. It makes sense that joining them in SQL would help. I'm just not that good with SQL yet.