Double Click a Record Selector to add that record to a table (1 Viewer)

[jumnhy]

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:

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.

 

[Pat Hartman]

Hi, welcome to the forum. I moved your post from

Solved - Double Click a Record Selector to add that record to a table

Hi, I have a form which carries out a filter / search on a table and displays it in a subform using via query. The search essentially uses criteria selected on the form to filter down the data. What I want to do is in the subform showing the filtered results, double click the record selector...

www.access-programmers.co.uk

to make a new one since the one you posted in was marked as solved.

I'm not sure how your code is working. I'm guessing that it isn't actually correct.

These three fields:

Forms.frmDeptSelect.txtDate AS trbpDate, Forms.frmDeptSelect.txtNotes AS trbpNotes, Forms.frmDeptSelect.cbSuperID AS trbptrainer

Are references to the current form.

Since the query doesn't have a where clause, the Select is picking up some random record for EmployeeID and DocumentID The select clause should be:

"SELECT " & Me.EmployeeID & ", " & Me.DocumentID & ", " & Me.txtDat & ", Me.txtNotes^ ", " & Me..cbSuperID

That's assuming that all the fields are coming from the subform. If any are coming from the main form, use Me.Parent.controlname

I can't tell what DocumentID is supposed to be. If it is the autonumer for the subform, it should be removed from both clauses.

 

[arnelgp]

Still curious for anyone else out there why you don't need a WHERE clause in the INSERT statement. Weird as all getout.

because the Insert you have only Insert 1 record.
while on delete query, if do not specify the "Where clause", it will Delete All records.

 

[theDBguy]

Hi. Welcome to AWF!

Also, in your INSERT statement; since you are only inserting one record, you could have used the VALUES clause, rather than a SELECT statement. You would normally use a SELECT statement if you're inserting multiple records.

 

[jumnhy]

Hi Pat, thank you for your help here--I would agree that the statement shouldn't work, and yet it absolutely does, somehow?

As in, it successfully selects the appropriate record when you double-click the the lastname field, and inserts it to tempTraining table, adding the fields completed on the "training approval" section of the parent form including the Trainer, Date, and Notes.

Somehow there's an implied WHERE criteria where the row is the row of the lastname being clicked.

How would one move from referencing every row displayed by a control to a specific row? Like a reference to Me.[ControlName] would refer to that control's bound field, but I'd think it'd pull all the records in the recordset, not a specific row.

Like, in design view for any datasheet form you just see the controls:

Header	Header2
Control1	Control2

But of course, in the datasheet view you see all the rows that are in the control's recordset:

Header	Header2
Control1.Value1	Control2.Value2
Control1.Value2	Control2.Value2
Control1.ValueN	Control2.ValueN

Except that, of course, there's no way to reference ControlX.ValueN. I suppose you could use a DAO library and order your recordset specifically to have a pseudo private key, like the row number, but I'm already out of my depth as it is.

Since I'm seeing from yourself and others that this shouldn't work, and yet it seems to be working, I'm sure it's a case of "It'll work until it doesn't"... and when it doesn't, I'll come back for assistance.

Basically, I need to know how to reference a specific record, where the only thing selecting the record is "which row was clicked". There's no private key for the recordset, specifically; it's a query. I could do a kinda-sorta private key with a doubled criteria, maybe?

What would be the appropriate way to do this?

Further notes below:

The subform is based on a query that grabs suggested trainings that may potentially be missing/out of date, for the Department/SOP/Employee as selected by the controls in the main form. See below:

qrySubFormResults is a list of required/suggested trainings compiled by comparing what procedures a given job role should be trained on against what trainings are already in the training records table. This also returns any out-of-date trainings vis-a-vis the revision level of the procedure in the training table versus the current revision level for the procedure. This query joins the set of suggested trainings with the name info from the employees table. It also filters based on the dept/procedure/employee name from the selection form.

SELECT qrySubFormResults.*
FROM qrySubFormResults INNER JOIN tblEmployeeInfo ON qrySubFormResults.employeeID = tblEmployeeInfo.ID
WHERE (
    
--Filter to selected dept, or not if no dept is selected
(
IIf([Forms]![frmDeptSelect]![deptCombo] Is Null, True,
(tblEmployeeInfo.Dept=[Forms]![frmDeptSelect]![deptCombo]))
<>False
)

AND
--Filter to selected SOP, or not if no SOP is selected
(
IIf([Forms]![frmDeptSelect]![cbSOP] Is Null, True,
IIf([DocumentID]=[Forms]![frmDeptSelect]![cbSOP], True, False))
<>False
)

AND
--Filter to selected employees, or not if no employee is selected
(
IIf([Forms]![frmDeptSelect]![cbEmployee] Is Null,True,
([employeeID]=[Forms]![frmDeptSelect]![cbEmployee]))
<>False
)

AND
--Exclude include temp staff (job ID under 20  = temp staff)
(
IIf([Forms]![frmDeptSelect]![chkIncludeTemps] = True, True,
(tblEmployeeInfor.[Job#OrAgency#] >=20))
<> False
)

AND
--Exclude results that already appear in the list of "selected" employees tblTempTraining
--Hacky double criteria b/c of non-normalized structure (no good PK in qrySubFormResults)
(
NOT EXISTS
(SELECT * FROM tblTempTraining WHERE qrySubFormResults.EmployeeID = tblTempTraining.trbpEmp AND qrySubFormResults.DocumentID = tblTempTraining.trbpnumber)
)

AND
--Include exclude associate staff category
(
IIf([Forms]![frmDeptSelect]![chkIncludeAssocs] = True, True,
(ForQData.[Job#OrAgency#] <> 506))
)

)
ORDER BY qrySubFormResults.LastName, qrySubFormResults.FirstName;

The insert is taking the specific result that's clicked on and moving it over to the "tblTempTraining". I have these displaying side-by-side in subforms. Click the entry on one side, it pops over to the other, and vice versa. I exclude any entries that are already in tblTempTraining, so after insertion, when the subform re-queries they drop off the list on the left, and show up on the list on the right with the insert. Clicking on the right hand side entries runs a Delete query on the tblTempTraining to delete the clicked record.

 

[jumnhy]

Hi. Welcome to AWF!

Also, in your INSERT statement; since you are only inserting one record, you could have used the VALUES clause, rather than a SELECT statement. You would normally use a SELECT statement if you're inserting multiple records.

Thanks @theDBguy . These forums seem super friendly and helpful.

Tagging @arnelgp too here--
Weirdly, the SELECT query could/should potentially refer to more than one record.

It's in a textbox control displaying a field from an underlying recordset, which does frequently contain multiple records.

Eg, design view:

Header1	Header2
txtLastName	txtProcedure

Datasheet view:

Last	Procedure to be trained
Smith	Purchasing
Smith	Severe Weather
Alvarez	Purchasing
Alvarez	Severe Weather

And I could doubleclick on Smith in the [Smith, Purchasing] row and it would only insert that row. Where based on my understanding of control references, I'd think the select statement would return all the rows.

It's weird. I don't know what the right way to accomplish what I'm looking to do is, because this doesn't feel right it just will work, until it won't, and I'll be sad and confused.

 

[Pat Hartman]

and yet it absolutely does, somehow

Somehow there's an implied WHERE criteria where the row is the row of the lastname being clicked.

Keep testing. There is no implied where. Maybe there is other code that is running that we haven't seen.

Basically, I need to know how to reference a specific record,

You reference a specific record by its primary key. Using Me. references the current record. When you reference a control in the subform from the main form, you are referencing whatever record Access thinks is current in the subform.

EVERY table needs a PK, preferably an autonumber. If you have a single field that is unique, you can use that but autonumber is better. If it takes more than one field to define uniqueness, use an autonumber (see the pattern here) but use a unique index to enforce the business rule.

Copy the select part of the query to the QBE and run it. What do you get?

 

[jumnhy]

Using Me. references the current record. When you reference a control in the subform from the main form, you are referencing whatever record Access thinks is current in the subform.

Ah, okay. That's one step closer. I'll play around and see what I can do.

Forgive my ignorance, what's QBE?

 

[Pat Hartman]

QBE = Query By Example. It is the way you build queries. You have to switch to SQL view to paste in the SQL string.

 

[jumnhy]

QBE = Query By Example. It is the way you build queries. You have to switch to SQL view to paste in the SQL string.

So the SQL view you can switch to when you create a query?

Interesting. I expected it to throw an error for not having a FROM clause, it didn't--it threw up prompts for each of the fields (EmployeeID, DocumentID, Forms...txtDate, Forms...txtNotes, Forms...cbSuperID).

If I open the form before going to the QBE environment and running the query, it doesn't prompt for the form-based fields (though they were empty on the form during the test), just the EmployeeID and DocumentID.

I also tried selecting one row of the subform the DblClick event is embedded in before hitting run (just to see if that played in somehow) and still got the prompts for the Employee and Document IDs.

Basically, it's behaving like it doesn't have a domain to reference (eg, prompts for fields that are "unknown"), but is treating the subform as the domain in practice. Why it's restricting the range to the clicked record I do not know.

I guess another way to reframe this would be: if I wanted to move all the entries in the subform into another table by doubleclicking any entry's last name, you'd think the query would do that. But it doesn't.

Regarding a primary key: the rowsource is a query rather than a table in this case. So I would love to be able to reference the current record in the DblClick event, and typically I'd do that with a PrimaryKey = "Me.PrimaryKey" condition (it's what I did for the other half--where I had a table with a proper PK) but I don't have that here--does MS ACCESS support something like a COUNTER() function? I could add that as a column to my rowsource query if so, and feel at least a little better about the solution I have in place...

Lol as an aside, yeah, I know that every table needs a while I'm mildly familiar with RDBMS normalization, the real lessons have been how I've had to kludge around some of the the non-normal forms that are already there, and I don't have the time and resources to rebuild the db properly.

 

[Pat Hartman]

If you run the query with the form open, the form references will pick up the field values from the form.

Include the PK in your query.

Relational databases operate on sets of data. They do not support a record number concept since changing the sort order would change the record number. If you want to copy multiple rows, you are copying them from the underlying tables of the query or table NOT a form. Forms don't store data. Tables store data. So if you want to copy a set of child records from one parent to another, you need two variables in the query. You need the PK of the "from" parent to use in your where clause and you need the "pk" of the to parent to use as the FK in your append query.

Although it is not unheard of, copying records can be an indication of a design flaw. Data should NEVER be duplicated but there are reasons for wanting to copy child records For example, if you want to copy all the students in the first semester of a class to the second semester. That is easier than entering them one at a time and you can go back and add/delete to clean up after the copy.

 

[jumnhy]

To help me understand a little better:

My form is using a query, not a table, as the recordsource. I get that forms don't hold data, that tables do.

What do you use to identify uniqueness in the recordset resulting from the query?

 

[Pat Hartman]

The PK of each of the records in the hierarchy. If there are only two tables, you have two PK's

It is really easier if you can explain exactly what you want to do. Is the subform also a "parent"? Because if you are talking about copying the rows of the subform, you only need the ID of the record on the main form. If you are not using a main form with a subform but are using a continuous form to show a query that joined a 1-m relationship, that may be what is causing the confusion. If each of the "many" side records has child records and that is what you want to copy, you would use the PK of the many side table as the "from" PK. You won't have a "to" PK unless you have already added the "to" record.