Hi all,
Brand new to the forum but here because I was working on a highly similar situation.
Interestingly, the database I'm working on uses the DblClick event to do exactly what OP is describing: shunt the selected record into another table. The overall purpose is displaying a list of staff and selecting them (by clicking on each desired row) for a training roster.
I inherited the technique behind this, and don't fully understand how it works.
The code used is as follows:
Note that there's no FROM or WHERE clause in the INSERT statement. This sub is placed in the On Dbl Click event of a text box control containing the employee's last name. I have no freaking idea how this works (as it does select the correct record, row-specific) without that specificity.
That text box control is situated in a subform, which is displayed in the Datasheet view, so from a UX perspective it just looks like the last name cell can be doubleclicked on any row to move the selected employee to another table.
Thought I'd share, as this appears to be precisely what OP is looking for.
HOWEVER:
I'm now trying to do the same thing but in reverse. I insert the selected records to another table for temporary holding, and I have that table displaying in another subform on the same parent form. I want the user to be able to doubleclick on the left subform, and have the entry pop up on the right. That part is working flawlessly. Once the entry is showing up in the temporary table on the right subform, the idea would be to have the user be able to cancel their selection by doubleclicking to move back to the left.
But the black magic syntax being used in the legacy system doesn't lend itself to a "DELETE FROM" statement easily.
Anyone have any idea what's going on with that one?
Edited to add: I have my code working now--I did a DELETE FROM with a WHERE tblTempTraining.[PrivateKey] = Me.PrivateKey and it worked. Still curious for anyone else out there why you don't need a WHERE clause in the INSERT statement. Weird as all getout.
Brand new to the forum but here because I was working on a highly similar situation.
Interestingly, the database I'm working on uses the DblClick event to do exactly what OP is describing: shunt the selected record into another table. The overall purpose is displaying a list of staff and selecting them (by clicking on each desired row) for a training roster.
I inherited the technique behind this, and don't fully understand how it works.
The code used is as follows:
Code:
Private Sub Last_DblClick(Cancel As Integer)
' Disable warning message
DoCmd.SetWarnings False
' Save record
DoCmd.RunSQL "INSERT INTO tblTempTraining ( trbpEmp, trbpnumber, trbpdate, trbpnotes, trbptrainer )" & _
"SELECT EmployeeID AS trbpEmp, DocumentID AS trbpnumber, Forms.frmDeptSelect.txtDate AS trbpDate, Forms.frmDeptSelect.txtNotes AS trbpNotes, Forms.frmDeptSelect.cbSuperID AS trbptrainer;"
' Requery subform
Forms!frmDeptSelect!subfrmsubSelection2.Requery
' Re-enable warning message
DoCmd.SetWarnings True
End Sub
Note that there's no FROM or WHERE clause in the INSERT statement. This sub is placed in the On Dbl Click event of a text box control containing the employee's last name. I have no freaking idea how this works (as it does select the correct record, row-specific) without that specificity.
That text box control is situated in a subform, which is displayed in the Datasheet view, so from a UX perspective it just looks like the last name cell can be doubleclicked on any row to move the selected employee to another table.
Thought I'd share, as this appears to be precisely what OP is looking for.
HOWEVER:
I'm now trying to do the same thing but in reverse. I insert the selected records to another table for temporary holding, and I have that table displaying in another subform on the same parent form. I want the user to be able to doubleclick on the left subform, and have the entry pop up on the right. That part is working flawlessly. Once the entry is showing up in the temporary table on the right subform, the idea would be to have the user be able to cancel their selection by doubleclicking to move back to the left.
But the black magic syntax being used in the legacy system doesn't lend itself to a "DELETE FROM" statement easily.
Anyone have any idea what's going on with that one?
Edited to add: I have my code working now--I did a DELETE FROM with a WHERE tblTempTraining.[PrivateKey] = Me.PrivateKey and it worked. Still curious for anyone else out there why you don't need a WHERE clause in the INSERT statement. Weird as all getout.
Last edited: