Append query after pasting into subform (1 Viewer)

jaryszek

Registered User.
Local time
Today, 01:15
Joined
Aug 25, 2016
Messages
756
Hi,

i am copying data and pasting it from Excel manually to subform.
After pasting i want to trigger append query and all IDs from pasted records insert into corresponding ID in junction table.
(many to many relationship).

What event should i use and how to show Access that i paste 5 new records with specific IDs.

So assume i have student1 and i want to assign him to 3 different classes with specific set of attributes.

For classes i am inserting 3 records with attributes, automatically i have IDs thanks to autonumber field in tblClasses.

And with these pasted new records IDs, for example 1,2,3 i want to assing it for Student1 with ID 1.

So i will have 1, 1
1,2
1,3 in junction table.

How can i achieve that?

Best,
Jacek
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:15
Joined
Jul 9, 2003
Messages
16,245
Having no idea about the structure of your data, I assume you have made the elementary mistake of having a separate column for each subject. If that's the case, then I think you should start here:-

http://www.niftyaccess.com/excel-in-access/

If I'm barking up the wrong tree, then please educate me....


Sent from my SM-G925F using Tapatalk
 

jaryszek

Registered User.
Local time
Today, 01:15
Joined
Aug 25, 2016
Messages
756
mistake of having a separate column for each subject.

i do not know what you mean.

I have junction table for many to many relationships.

This is StudentClass table.

And within this i have student and i have class foreign keys.
And for class table i am adding 3 attributes.

I can create subform for junction table and add comboboxes with Student and Class. But for class i have 3 attributes which i am manually adding from Excel.

And i want to have possibility to add records for Class and assing it for specific Student at once. (so it means more than one column in combobox for subform which i want to avoid).

Understand?
Best,
Jacek
 

Cronk

Registered User.
Local time
Today, 19:15
Joined
Jul 4, 2013
Messages
2,770
Import all of your Excel data into Access. Then use grouping queries to generate and create tables for your Classes and for your Students. Then use another query which joins the Classes and Student tables to your temporary table to create your data for the join table with PKs from tblStudents and tblClasses and any other data relating to the Student/Class from your temporary table.


Note the joins to the tblStudents may need to link two or more fields eg FirstName and Surname if you have students with the same surname.
 

jaryszek

Registered User.
Local time
Today, 01:15
Joined
Aug 25, 2016
Messages
756
Aa thank you Cronk.
I have to think about this.

All data inputting using queries...hmm.

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 01:15
Joined
Aug 25, 2016
Messages
756
Hi,

i created sample database.

In frmCommonsolution there is a solution which is most often used:

https://access-programmers.co.uk/forums/attachment.php?attachmentid=72679&stc=1&d=1542179998

So i have few columns (4 columns, 1 hidden) in combobox.

Problem is that i have to add these from Excel.
So somehow i have to paste few records to tblClass and in the same time connect it to specific Student.

I tried with creating frmMineSolution where you can take record 1 from Excel, copy it, and paste as new record in Access subform.
It would be ok but i have redundant data because of it.
So this is not a good solution.

Please help,
Best,
Jacek
 

Attachments

  • Screenshot_39.png
    Screenshot_39.png
    65.3 KB · Views: 54
  • classes.xlsx
    8.7 KB · Views: 56
  • Database7.accdb
    536 KB · Views: 40
Last edited:

Users who are viewing this thread

Top Bottom