Set Subform REcordsource on loading of main form (1 Viewer)

Hi. I am trying to alter two things on opening of my form. One is the recordsource of the main form which i have achieved with following code:


HOwevcer i would also like to amend the subform recordsource on opening of the form however i am not having much luck here:


Any suggestion would be gratefully appreciated.

Reason i am using the StrTier1 string is i have numerous tables with simialr fields and if i can make this form work to create a form for over 10 different tables i would only need to then change the definition of StrTier1 in each one rather than make various amends to make them all releavnt for each table.

Having similar tables hints at a normalization issue. What do they contain?

In any case, I think you're over-complicating it. If the form has a single subform control, you'd simply have::


Me.SubformControlName.Form.Recordsource = "YourTableNameOrSQL"

I must second Paul's comment... If they are similar enough that the same FORM would work by simply changing which table you point at, they should be all in the same table with an added field that tells you which (since you've used "Greenhouse") [Location] they are at.

Hi, thank for feedback.

Each of the tables contains a top level variable.

For example i can use this form to create a:
New Category,
New Site Location,
New Compost Mix,
New Supplier,
New Customer,
New PotSize

However none of these variables above are linked to each other so i assumed they all need separate tables. However each table is the same in that it had a Unique_ID(Category_ID), Name (Category), and Name_Active (Category_Active) field.

Your description doesn't make much sense, those 3 fields shouldn't be in each table, and they shouldn't have the same Unique_ID field if they are different entities.

Can you a) describe what your database is for in plain English - no database speak, and
b) post up a picture of your tables relationships. Something isn't right in your design.

Hi

I have attached a smaple database of what i am trying to acheive.
In the smaple database there are 4 tables, Category, Compost, PotSize and Greenhouse.

Rather than create 4 different forms and go through hassle of changing all the labels etc and message boxes i have designed a master form Called Frm_Tier1Add. Idea is once i am happy with this form i copy it 4 times and change the deifned StrTier1 string in each of the copied forms and then it will populate all the correct labels message boxes etc for each form.

For example Tier1Add at the moment has StrTier1 deifned as COmpost so all labels say word compost and the forms record source is Tbl_Compost and the bound textbox links to the compost field in Tbl_Compost.

IF you change the StrTier1 to say Greehouse and StrTier11 to say greenhouse then the greenhouse table will be linked and able to be populated.

My thinking was in time if i suddenly notice an error or have to add something to each of the forms, like error handling or anyhting like that i only have to do it once.

THe subform is not working as intended yet.

I think you are creating much more work that you will ever save.

On the whole if your design is sound you won't add fields (horizontally), just more data (vertically). The chances of your tables needing and having exactly the same structure should be tiny, or non existent.

(If all your data is that similar just dump it one table and have some group identifier to distinguish them apart)

By the time you have figured this out you could have built the master form - and 3 or 4 sub forms and just change the sub form object.

You would still have to code / store all the label changes anyway. Once they are fixed you generally forget about such things, unless you decide to change the format or layout in a major way.

ChrisJames,

It sounds like you have a lookup table.
Primary Key -
Category -
Lookup -
Description -

If you want to get exotic you can put in an active Yes/No field and a Sort (Numeric).

Should be one file.