473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

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

22
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!
Nov 3 '09 #1

✓ answered by NeoPa

@rleepac
In that case my first and best advice would be to study up on Normalisation and Table structures. Invaluable.
@rleepac
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).
@rleepac
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.

7 2147
NeoPa
32,556 Expert Mod 16PB
@rleepac
In that case my first and best advice would be to study up on Normalisation and Table structures. Invaluable.
@rleepac
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).
@rleepac
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.
Nov 3 '09 #2
rleepac
22
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..
Nov 3 '09 #3
NeoPa
32,556 Expert Mod 16PB
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.
Nov 3 '09 #4
NeoPa
32,556 Expert Mod 16PB
@rleepac
Usually they have the same name (EG. [SSN]), but this is not critical now you've explained.
@rleepac
Now this is clearer I can say no. You set them up fine (as far as we can see).

Welcome to Bytes!
Nov 3 '09 #5
missinglinq
3,532 Expert 2GB
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)>
Nov 3 '09 #6
rleepac
22
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!
Nov 3 '09 #7
NeoPa
32,556 Expert Mod 16PB
Good for you :)

Sounds like you have a handle on it now.
Nov 3 '09 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Scott Kinney | last post by:
I have an inventory database. I want to delete out-of-stock items from the main database, but keep them in a separate table so that I can reference data about them. I created a copy of the item...
0
by: Carl | last post by:
I have a main form with navigation buttons on it and a label showing for example Record 1 of 15 using recordsetclone on it and eveything works fine. When I move through the records the record...
9
by: the_grove_man | last post by:
I guess my question can go in two directions. I create applications that run multiple queries against a database. Generally speaking in the past I have used a Data Control (calling it dat1)...
6
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after...
9
by: Joshua.Buss | last post by:
I am trying to move a record from one linked table to another within access, but I'm a complete beginner to VBA and don't know exactly where to begin. I have an access file that has the two...
1
by: Wes Brooks | last post by:
Hello expert, Please help me with the following problems. I have spent ages to resolve them but no luck. I have two forms. (1) "Document Reception Input Form" is the main form. The search...
3
by: blakerrr | last post by:
Hi All, I have a strange situation that I can't figure out. The task is quite simple, delete a record from a table. Here is my situation: I have a form called Order Create which cycles through...
4
by: QntmPg | last post by:
Hi all, I have read through what I could find on previous questions regarding using the OpenArgs property, but I'm still not able to get my form to open correctly. I'm not sure exactly where the...
25
by: tekctrl | last post by:
Anyone: I have a simple MSAccess DB which was created from an old ASCII flatfile. It works fine except for something that just started happening. I'll enter info in a record, save the record,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.