Append Query through Macros

[vadharah]

I have many forms and tables and experiencing problems appending the data to the tables as its repeating. In a nutshell say i have these tables: Student_Details, grades and a lookup table courses. Student_Details has a one-to-many relationship with Grades.
i.e. in bold are the primary keys

Student_Details...
StudentID Name Age


Grades....
StudentID Course Grade

Courses....
computing
history
law
politics

Suppose i wish to enter the following information
James, law - Pass, computing - Distinction, History - Merit, politics - Fail

im getting

StudentID Course Grade
1 - computing - Distinction
1 - history - Merit
1 - law - Pass
1 - politics - Fail

which is fine.

but when i wish to enter a second record Simon, 31 (law - Pass, computing - Merit, history - Fail, politics - Fail) i get

​

StudentID Course Grade
1 - computing - Distinction
1 - computing - Merit
1 - history - Fail
1 - history - Merit
1 - law - Pass
1 - politics - Fail
2 - computing - Merit
2 - history - Fail
2 - law - Pass
2 - politics - Fail

i want to have only 4 records for studentID 1

• i get a message saying 'appending 2 records'! - but i only want to append 1 record - Simon??. Instead if the 'course and grade' of the second are the same as the first they are not repeated if different they are added to the first student

Where am i going wrong? Your help/advise will be appreciated.

i have attached a sample..

Thank you in advance

 

[jal]

Attempting to do this sort of thing with macros and wizards is difficult (sort of like trying to do brain surgery with a large clumsy shovel). Personally, I think the time spent wrestling with macros and wizards is better spent learning VBA and SQL. (After all, there's no easy way to do brain surgery, with our without wizards).

Honestly I don't know how to fufill your request using macros and wizards. So I am not going to wrestle with that.

Instead, I'll give you a few tips on using VBA to solve the problem - but don't expect me to necessarily debug my suggested snippets. At the moment, I'm just trying to point you in the right direction.

In you btn_Click event, currently you have this code to run a macro:

On Error GoTo Err_Command16_Click
    Dim stDocName As String
    stDocName = "Macro1"
    DoCmd.RunMacro stDocName
Exit_Command16_Click:
    Exit Sub
Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click

Delete all that code. In its place, you can use commands such as

DoCmd.RunSQL "INSERT INTO...."

etc.

I'll upload some sample code for you, in a little while.

 

[jal]

Ok, give this a try.