Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2015, 09:54 AM   #1
fixty100
Newly Registered User
 
Join Date: Nov 2015
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
fixty100 is on a distinguished road
Macro - After event create multiple records

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
Attached Images
File Type: jpg ScreenHunter_10 Nov. 05 17.44.jpg (90.4 KB, 68 views)

fixty100 is offline   Reply With Quote
Old 11-05-2015, 10:23 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Macro - After event create multiple records

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
fixty100 (11-05-2015)
Old 11-05-2015, 10:24 AM   #3
fixty100
Newly Registered User
 
Join Date: Nov 2015
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
fixty100 is on a distinguished road
Re: Macro - After event create multiple records

Quote:
Originally Posted by pbaldy View Post
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

fixty100 is offline   Reply With Quote
Old 11-05-2015, 10:29 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Macro - After event create multiple records

Welcome to the site by the way!
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-05-2015, 10:47 AM   #5
fixty100
Newly Registered User
 
Join Date: Nov 2015
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
fixty100 is on a distinguished road
Re: Macro - After event create multiple records

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?
fixty100 is offline   Reply With Quote
Old 11-05-2015, 10:52 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Macro - After event create multiple records

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
fixty100 (11-05-2015)
Old 11-05-2015, 10:57 AM   #7
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Macro - After event create multiple records

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.

__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 11-05-2015, 11:01 AM   #8
fixty100
Newly Registered User
 
Join Date: Nov 2015
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
fixty100 is on a distinguished road
Re: Macro - After event create multiple records

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).
fixty100 is offline   Reply With Quote
Old 11-05-2015, 11:03 AM   #9
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Macro - After event create multiple records

I need more than just a title. Give an example please. Again, the seemingly envisaged structure does not seem the proper one.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 11-05-2015, 11:11 AM   #10
fixty100
Newly Registered User
 
Join Date: Nov 2015
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
fixty100 is on a distinguished road
Re: Macro - After event create multiple records

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
Attached Images
File Type: jpg ScreenHunter_11 Nov. 05 19.07.jpg (52.4 KB, 55 views)
fixty100 is offline   Reply With Quote
Old 11-05-2015, 11:42 AM   #11
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Macro - After event create multiple records

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?
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 11-05-2015, 11:48 AM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,306
Thanks: 13
Thanked 4,113 Times in 4,045 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Macro - After event create multiple records

Quote:
Originally Posted by fixty100 View Post
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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
fixty100 (11-05-2015)
Old 11-05-2015, 11:49 AM   #13
fixty100
Newly Registered User
 
Join Date: Nov 2015
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
fixty100 is on a distinguished road
Re: Macro - After event create multiple records

Quote:
Originally Posted by spikepl View Post
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.

fixty100 is offline   Reply With Quote
Reply

Tags
create , event , macro , record

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a MACRO that generates multiple reports and automatically e-mails them craig1988 Macros 4 09-02-2014 08:09 AM
multiple log records agains an event MattF Tables 1 10-21-2013 04:05 AM
Question Design help & Macro to create table fields from another tables records gregal0711 General 1 07-10-2012 06:58 AM
Using Command Button/macro To Create Additional Records? Repent Macros 6 05-21-2012 12:12 PM
Create Records Macro / Loop jagstirling Modules & VBA 9 07-01-2008 12:26 AM




All times are GMT -8. The time now is 01:14 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World