Copy Table to Table with Command Button - 85% solved -Just need one last thing

[tsgtnissen]

Im such a Access Noob! I have everything done and working but I have one last hurdle. In my Access Program I would like a command button to copy from one table to a master table. I have this code working perfect.

Private Sub btnLoadIndoc_Click()' Copies One table to another .
 CurrentProject.Connection.Execute "INSERT INTO Table1(Event,StartDate,Completed,Student,Description) SELECT Event,StartDate,Completed,Student,Description FROM GradeBookIndoc;"
    MsgBox "Record Saved !!!", vbInformation, "Success"

End Sub

My question is.... on the "Form" I have the "Command Button" on, I also have a text box that has a students name.
My desired outcome is when i click the "Command Button" it would look at the text box and read the students name, then load that name to the "Students" column on Table1.


The theory of how this program will work is.



1. Table1 = will hold all events my students need to accomplished
2. GradeBookIndoc (table) = will have a template of a few events.
3. When i pull up a student I can click a button and assign those task to them.


ThANK YOU SO MUCH FOR THE HELp!

[June7]

If both tables are in same db, just use CurrentDb object.
Concatenate student identifier. However, should not be student name, should be a StudentID. Presume there is a Students table. Select student with a combobox. However, if entering a new student, new record in Students must be committed before saving related records in Table1.

Event should probably also save an ID, not an event name.

If there is no data for a field, don't need to include it in the INSERT. I assume StartDate and Completed will be entered later. What is Description for?

CurrentDb.Execute "INSERT INTO Table1(Event,Student,Description) SELECT Event," & Me.cbxStudent & ",Description FROM GradeBookIndoc;"

Suggest you use a more meaningful name than Table1.

[tsgtnissen]

OK, after some reworking, I now have this

Private Sub pbxLoadBooks_Click()

If MsgBox("Do you want to load this gradebook? ", vbQuestion + vbYesNo) = vbYes Then
Me.Refresh
Else
MsgBox "Ok, good catch!", acSaveNo
End If




CurrentDb.Execute "INSERT INTO Tasks(Title,Assigned To,Description,Block) SELECT Title," & Me.ID & ",Description,Block FROM TasksGradeBooksEvents;"
    MsgBox "Record Saved !!!", vbInformation, "Success"


End Sub

The ID is the "Students" ID number from the "Students : Table"

but in my TASKS table I was dumb and put "Assigned to"......Silly me has already added a ton of stuff that works with that (I NOW KNOW) very bad naming convention.



Goood new....... EVERYTHING WORKS if i take out the "Assigned to" bit in the code. Its writing everything to the tasks database just fine. and my Querys are working to filter by block.


SO... the last problem is getting it to assign the Currently selected student to the "Assigned to" field .



ONCE AGAIN !! THANK YOU LORDS OF THE CODING WORLD! once i get this working i will go back and clean everything up, and make it much cleaner. I also need to add and delete some fields. I have been cut and pasting tons of tutorials(I KNOW ITS UGLY ATM)

I will also post a completed working build to help other users. This will help all of the instructors at our school manage a massive amount of data once we get this going.

[ssanfu]

Minor Error:

You have

If MsgBox("Do you want to load this gradebook? ", vbQuestion + vbYesNo) = vbYes Then
Me.Refresh
Else
MsgBox "Ok, good catch!", acSaveNo
End If

"acSaveNo" is not a parameter for the function "Msgbox". I think you would want "vbOKOnly".

[Gicu]

Assuming the ID field is numeric, is the AssignTo in table Tasks also numeric (long integer)? If the two data types match your current append (insert into statement) should work.

Cheers,