Want control to add new record to separate table using data from main form

[rleepac]

Newbie here...I've been reading these forums for hours and I'm learning so much! I'm a Physician Assistant by trade but necessity has converted me to a pseudo-Access db designer. I have a db that is barely cutting it and I'm designing a new one "done right" so I can use the full potential of Access.

Anyway...I know this is a simple task but I think I can't see the forest through the trees.

I have a demographics form F_Employees with source table M_Employees and it has empSSN, empLastName, empFirstNamet, etc.

I have a separate table for notes M_Notes which I have made a pop-up form to enter a new note. These tables are linked by empSSN and notesSSN. The problem that in order to add a new note I have to enter the SSN on the pop-up form. What I want to do is have the SSN populate on the F_Notes form notesSSN field and insert a new record into the M_Notes table when I click on a control (we can call it ctlAddNote) from the main form F_Employees.

I know this is a simple thing but I'm lost. Please help!

[NeoPa]

Newbie here...I've been reading these forums for hours and I'm learning so much! I'm a Physician Assistant by trade but necessity has converted me to a pseudo-Access db designer. I have a db that is barely cutting it and I'm designing a new one "done right" so I can use the full potential of Access.

In that case my first and best advice would be to study up on Normalisation and Table structures. Invaluable.

Anyway...I know this is a simple task but I think I can't see the forest through the trees.

That's very rarely true. It's generally a sign that a really complicated question is about to follow (No-one is lying. They simply don't know what is and isn't simple).

I have a demographics form F_Employees with source table M_Employees and it has empSSN, empLastName, empFirstNamet, etc.

I have a separate table for notes M_Notes which I have made a pop-up form to enter a new note. These tables are linked by empSSN and notesSSN. The problem that in order to add a new note I have to enter the SSN on the pop-up form. What I want to do is have the SSN populate on the F_Notes form notesSSN field and insert a new record into the M_Notes table when I click on a control (we can call it ctlAddNote) from the main form F_Employees.

Is NotesSSN an AutoNumber field, or is it assigned some way? If the latter then we need to know how.

The SSN needs to be clarified. There are clearly two fields in the record that you could be referring to. I think I know which it is you mean, but it needs to be clear for anyone reading this afterwards.

I'll assume that the form F_Notes that you refer to is the same as the pop-up form you refer to earlier. Also that the linkage is actually by empSSN exclusively, and that notesSSN is a unique reference to the M_Notes record.

I think your best solution (no details here as we don't have the question properly specified yet) is to use F_Notes as a SubForm on your main (F_Employees) form. The Linked fields would need to be on empSSN.

[rleepac]

Ahhh ok gottcha - more info needed.

Let me back up a bit. First I have the following tables:

M_Employees which includes fields such as;
empSSN (PK manually entered)
empLastName
empFirstName
etc.

then I have a table M_Notes which includes fields such as;
notesID (autonumber PK)
notesSSN
notesDate
notesNote
etc.

I have a relationship established between these two tables using the SSN (empSSN and notesSSN).

My understanding is that in order to relate a note to an employee I need the SSN in both tables however the M_Notes table does have an autonumber PK for each note. Did I set up my tables incorrectly?

If I can figure out how I'll attach a jpg of my relationships..

[NeoPa]

No. You're good. That clarifies things a lot.

The only change to my advice though, would be to link in the SubForm as Link Master Fields=empSSN & Link Child Fields=NotesSSN.

Try this out and let us know how you get on.

[NeoPa]

I have a relationship established between these two tables using the SSN (empSSN and notesSSN).

Usually they have the same name (EG. [SSN]), but this is not critical now you've explained.

My understanding is that in order to relate a note to an employee I need the SSN in both tables however the M_Notes table does have an autonumber PK for each note. Did I set up my tables incorrectly?

Now this is clearer I can say no. You set them up fine (as far as we can see).

Welcome to Bytes!

[missinglinq]

The advantage to them having the same name, in both tables, is that when you add a subform to your mainform, if the names are the same, Access will take care of the Link Master Field/Link Child Field thing automatically.

Welcome to Bytes!

Linq ;0)>

[rleepac]

Got it working with a tabbed control using a subform in that tab. I decided on a tabbed control because I have several other tables that I want to use the same way.

Thank you!

[NeoPa]

Good for you :)

Sounds like you have a handle on it now.