Copy from Table to Table with a filter - SOOO CLOSE TO WORKING!

[tsgtnissen]

After two posts and ton of help from this community I have solved 98% of my Database issues.

My current WORKING copy will load all the "Events" from the EventMaster table to the EventsLog table and assign them to the selected user.

Private Sub pbbLoadSelectedBlock_Click()

'This code allows me to copy all items from EventsMaster : table to --> EventsLog : table AND assign then to the USERID of selected USERID on the form "UserDetails
If MsgBox("Do you want to load the selected BLOCK Default Events for this user? ", vbQuestion + vbYesNo) = vbYes Then
Me.Refresh
Else
MsgBox "Ok, good catch!", acSaveNo
End If

CurrentDb.Execute "INSERT INTO EventsLog(UserID,EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes) SELECT " & Me.UserID & ",EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes FROM EventsMaster;"
    MsgBox "New Events have been loaded!!!", vbInformation, "Success"

'This will refresh the form and updates the listbox to display the new Events
Form.Refresh


End Sub

The last question I have is.....

1. Is there a way to add a Combobox that will allow me to add events from the "EventMaster" table that's EventPhase is = to the Combobox value?



My goal is to add only certain phases not the whole list every time.

[pbaldy]

Add

...WHERE EventPhase = " & Me.ComboName

to the end. If the field is text:

...WHERE EventPhase = '" & Me.ComboName & "'"

[tsgtnissen]

CurrentDb.Execute "INSERT INTO EventsLog(UserID,EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes) SELECT " & Me.UserID & ",EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes WHERE EventPhase = '" & Me.cbbBlockLoadSelect & "'" FROM EventsMaster;"
     MsgBox "New Events have been loaded!!!", vbInformation, "Success"

its not working

The combo box values are "indoc, blk1, blk2"

[pbaldy]

I said to add it to the end, you put it in the middle. The clauses are out of order now. Try

CurrentDb.Execute "INSERT INTO EventsLog(UserID,EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes) SELECT " & Me.UserID & ",EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes FROM EventsMaster WHERE EventPhase = '" & Me.cbbBlockLoadSelect & "'"

[pbaldy]

By the way, most of us would use a string variable for the SQL, making it easier to debug.

[tsgtnissen]

IT WORKS!!!!!!! THANK YOU!!!!

Private Sub pbbLoadSelectedBlock_Click()
'This code allows me to copy all items from EventsMaster : table to --> EventsLog : table AND assign then to the USERID of selected USERID on the form "UserDetails
If MsgBox("Do you want to load the selected BLOCK Default Events for this user? ", vbQuestion + vbYesNo) = vbYes Then
Me.Refresh
Else
MsgBox "Ok, good catch!", acSaveNo
End If
' this may load 1 event//// CurrentDb.Execute ("INSERT INTO myTable (Major, Time, Ne, Escalation, Description) VALUES('" & myListBox.Column(1) & "', " & myListBox.Column(2) & ", '" & myListBox.Column(3) & "', '" & myListBox.Column(4) & "', '" & myListBox.Column(5) & "')")
' This will load only the selected events that match the PHASE/BLOCK that has been selected in the combobox. The WHERE command is the uniqie part.
CurrentDb.Execute "INSERT INTO EventsLog(UserID,EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes) SELECT " & Me.UserID & ",EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes FROM EventsMaster WHERE EventPhase = '" & Me.cbbBlockLoadSelect & "'"
    MsgBox "New Events have been loaded!!!", vbInformation, "Success"
'This will refresh the form and updates the listbox to display the new Events
Form.Refresh

[pbaldy]

Happy to help!

[tsgtnissen]

I got it working with ONE combo box, I would like to use two but my WHERE is messed up.
Can you help?

Private Sub pbbLoadSelectedBlock_Click()

'This code allows me to copy all items from EventsMaster : table to --> EventsLog : table AND assign then to the USERID of selected USERID on the form "UserDetails
If MsgBox("Do you want to load the selected BLOCK Default Events for this user? ", vbQuestion + vbYesNo) = vbYes Then
Me.Refresh
Else
MsgBox "Ok, good catch!", vbOKOnly
End If
' This will load only the selected events that match the PHASE/BLOCK that has been selected in the combobox. The WHERE command is the uniqie part.
CurrentDb.Execute "INSERT INTO EventsLog(UserID,EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes) SELECT " & Me.UserID & ",EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes FROM EventsMaster WHERE EventPhase = '" & Me.cbbSingleBlockLoadSelect & "' EventName = '" & Me.cbbSingleEvent & "'"
    MsgBox "New Events have been loaded!!!", vbInformation, "Success"
'This will refresh the form and updates the listbox to display the new Events
Form.Refresh


End Sub

[pbaldy]

The criteria need to be separated by AND. The end result would look like:

WHERE Field1 = 'abc' AND Field2 = 'xyz'

[tsgtnissen]

ITS WORKING!!!!!!! THANK YOU SOOOO MUCH!

Private Sub pbbLoadSelectedBlock_Click()
'This code allows me to copy all items from EventsMaster : table to --> EventsLog : table AND assign then to the USERID of selected USERID on the form "UserDetails
If MsgBox("Do you want to load the selected BLOCK Default Events for this user? ", vbQuestion + vbYesNo) = vbYes Then
Me.Refresh
Else
MsgBox "Ok, good catch!", vbOKOnly
End If
' This will load only the selected events that match the PHASE/BLOCK that has been selected in the combobox. The WHERE command is the uniqie part.
CurrentDb.Execute "INSERT INTO EventsLog(UserID,EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes) SELECT " & Me.UserID & ",EventName,EventDiscription,EventStart,EventComplete,EventPhase,EventInstructor,EventInstructorNotes FROM EventsMaster WHERE EventPhase = '" & Me.cbbSingleBlockLoadSelect & "' AND EventName = '" & Me.cbbSingleEvent & "'"
    MsgBox "New Events have been loaded!!!", vbInformation, "Success"
'This will refresh the form and updates the listbox to display the new Events
Form.Refresh

End Sub

[pbaldy]

Awesome!!!