Macro - After event create multiple records (1 Viewer)

fixty100

New member
Local time
Today, 10:26
Joined
Nov 5, 2015
Messages
6
First time here guys, trying to solve my problem. I'm not very experienced in VBA but can understand and follow it.

I need to create a macro which after an event does some, in my mind, complicated things. Here's a quick screenshot of my database to set the scene (think its in the attachments).

On the left of the image I have tblStudents so after a new record is created for that table the macro will run and create multiple records for the new student record created. The new multiple records created will go into my tblLevelTracker where the StudentID (autonumber from tblStudent, just created record) will be copied from. It also needs to copy the ObjectiveID from the large pre set list of objectives in tblObjectives. This will mean that there will be about a hundred newly made records for each student. The TeacherID column in tblLevelTracker isn't relevant along with the other fields for now.

Would anyone be able to help me create a macro for this, I can upload more information if needed.

Thanks in advance, George
 

Attachments

  • ScreenHunter_10 Nov. 05 17.44.jpg
    ScreenHunter_10 Nov. 05 17.44.jpg
    90.4 KB · Views: 153

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:26
Joined
Aug 30, 2003
Messages
36,118
If I understand correctly, you should be able to create an append query based on the objectives table that inserts new records into the level tracker table, but gets the student ID from the form. You can execute that query behind a save button or wherever.
 

fixty100

New member
Local time
Today, 10:26
Joined
Nov 5, 2015
Messages
6
If I understand correctly, you should be able to create an append query based on the objectives table that inserts new records into the level tracker table, but gets the student ID from the form. You can execute that query behind a save button or wherever.

I'll give it a go and report back
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:26
Joined
Aug 30, 2003
Messages
36,118
Welcome to the site by the way!
 

fixty100

New member
Local time
Today, 10:26
Joined
Nov 5, 2015
Messages
6
With the append query, how do I allow all of my around hundred objectives to be made into new records on another table while using the the frmStudents to put in the currently open StudentID?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:26
Joined
Aug 30, 2003
Messages
36,118
Along the lines of

INSERT INTO tblLevelTracker(ObjectiveID, StudentID)
SELECT ObjectiveID, Forms!FormName.StudentIDTextboxName
FROM tblObjectives

Which would insert all the objective ID's. You could add a criteria if appropriate.
 

spikepl

Eledittingent Beliped
Local time
Today, 11:26
Joined
Nov 3, 2010
Messages
6,144
You might want to backup a bit and not getting lost in the finer Access details explain what this is about.

Because I smell something very very fishy here. To assign qualifications/passes your envisaged way, if perceived correctly, is not the customary way of doing such things in Access.

Further, you should know that what is called macro in Excel or Outlook are in fact chunks of VBA. But here, in Access, a macro is a different animal altogether, and lives its own life. You seem to be looking for VBA code, so next time post there, since the other fora are more frequented than Macros.
 

fixty100

New member
Local time
Today, 10:26
Joined
Nov 5, 2015
Messages
6
I'm basically creating a Student Objective Tracker database for a primary school to use. It is part of my IT coursework (A-Level) and I've asked the two of my IT teacher about this, they've both understand the issue I'm having but haven't been able to fix it. At the moment I'm learning about append queries because a brief look at them looks like the route to go down (thanks pbaldy).
 

spikepl

Eledittingent Beliped
Local time
Today, 11:26
Joined
Nov 3, 2010
Messages
6,144
I need more than just a title. Give an example please. Again, the seemingly envisaged structure does not seem the proper one.
 

fixty100

New member
Local time
Today, 10:26
Joined
Nov 5, 2015
Messages
6
Ok, I've attatched the relationships of my database so your can see everything going on. They all lead into the level tracker. The TrackerID can have many ObjeciveIDs and many StudentIDs over many records. It also stores the level and so on. Maybe this will be enough to give you an insight.

My next problem is to do with the append query I've created. At the moment it will create records over and over again even if the record for that student for that objective has already been made. Is there a way in the query to avoid duplicate results.

Thanks again
 

Attachments

  • ScreenHunter_11 Nov. 05 19.07.jpg
    ScreenHunter_11 Nov. 05 19.07.jpg
    52.4 KB · Views: 137

spikepl

Eledittingent Beliped
Local time
Today, 11:26
Joined
Nov 3, 2010
Messages
6,144
I need an example of data because I still suspect some things. 2-3 students, each with different requirements and accomplishments. Just enough to get the gist of how this thing is supposed to work but getting into all corners of this. Your desire of pre-making all those record for each student is unusual and probably not warranted. That is unless all this just is some do-and-forget-all-about-it school exercvise. Is it?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:26
Joined
Aug 30, 2003
Messages
36,118
My next problem is to do with the append query I've created. At the moment it will create records over and over again even if the record for that student for that objective has already been made. Is there a way in the query to avoid duplicate results.

One way is to check before running it, using perhaps a DCount() and the student ID in the criteria (testing to see if that student has any records. Only proceed if zero. You can also make the combination a compound key or index.
 

fixty100

New member
Local time
Today, 10:26
Joined
Nov 5, 2015
Messages
6
I need an example of data because I still suspect some things. 2-3 students, each with different requirements and accomplishments. Just enough to get the gist of how this thing is supposed to work but getting into all corners of this. Your desire of pre-making all those record for each student is unusual and probably not warranted. That is unless all this just is some do-and-forget-all-about-it school exercvise. Is it?

I've got very far with pbaldys help with the problem now fixed. Thanks for the help.

As for my database our IT class had a selection of areas to do our coursework on (this would mean the database would never be used and deleted shortly after). I went out to my primary school and asked if they had a need for such a system; they said yes and gave me a list of objectives for the database where the teacher would assess the students on. I have until Christmas to build the database and am getting on well with it.

I will post my finished data here in roughly a months time so you'll be able to see the progress I made.
 

Users who are viewing this thread

Top Bottom