Setting a Field Automatically from another table

[chipperly]

Hi, I'm new to access and am having a really tough time with this one task. :banghead:

I have a query (QRY_full_data_census) that I use to populate a continuous form. The source of this query is two data tables (TBL_Census and TBL_Notes) which link together using an account_number. I can enter a new note on the form for the account, but it doesn't record a corresponding account_number in the table.

I want the TBL_Notes.account_number that matches the current TBL_Census.account_number to be filled in automatically once a new note is added via the form. Hope this makes sense, and thank you very much for any assistance.

 

[essaytee]

Sounds like you need to add a sub-form to your form. That sub-form will only show records that are linked to the parent form. If entering records into the sub-form, if linked correctly, your account number will be automatically entered.

For further see this:

 

[chipperly]

Ok, thank you. I'm hoping not to have to re-design the whole form, but if I can't find a solution to this soon, then I'm going to get started. All I need to do is enter the account number from one table into another when a new note is created.

 

[The_Doc_Man]

The thing you need to look up in Access is called a "relationship" and it is how you start the process of telling Access that the stuff you added in a form X is actually data dependent on the active record in form Y (which often is the parent form of a parent/child form pair with form X). Setting up the relationship properly tells Access what to do when those two tables and the related fields appear together in queries, reports, and master/sub or parent/child forms. (The latter two items are two names for the same thing.)

 

[chipperly]

Ok, thank you. I have a relationship setup between both tables as the Account number. Just that the account number is blank in the note table because a note hasn't been created for the account yet. When I create a note, i want to populate the corresponding account # into the table. Should it be doing this automatically? I thought maybe I need to set the default value, or do a lookup,etc.

 

[essaytee]

As regards your Notes table. Can you have many notes per census data record (one to many) or is it a one-to-one type relationship?

 

[The_Doc_Man]

In general, if you have set up the relationship between tables correctly and have also set up the parent/child link relationships between the form and sub-form correctly, you CAN'T store anything from the sub-form unless the main form saves the record containing that number, which is usually the prime key (PK) of the parent record - probably in your case, the account number you mentioned.

Usually when people talk about this topic on this forum, it is because the main form's record gets saved too early and they want to save the subs without saving the main. The first time you go to save a properly linked record from the sub-form, Access SHOULD automatically save the linked main form record. It should be almost transparent. However, if the PK isn't an autonumber, I'm not sure the saving would be automatic because a non-autonumber PK would be more likely to run into key violations (of uniqueness, which is implied by being a PK).

The IDEAL sequence is that you create the main record in its entirety and then start entering data to the sub-forms in a way that allows Access to just save the sub-form records as needed by circumstances. That way you don't violate key integrity. From your prior discussion, I don't know if that is the way you have it set up. (Not saying it isn't; saying I can't tell.)