Soliciting procedural advice

[Monty]

I have a main form with an embedded subform that gets populated from a
query dependent on a value in the main form. The subform is presented
in datasheet format for easy perusal, with column headings matching the
field names of the underlying table, record selectors, etc. It all
works very nicely.

My next step is to figure out a way to identify a specific record in
the subform's underlying table when one of the subform records is
selected or when one of its fields is double clicked. I'm guessing
there's an identifier leading back to the query. Once I can get there,
the subform's query that is, I can reference additional fields that
will produce a unique record in the underlying table.

I hope this is clear enough to generate suggestions.

Thanks

 

[Graham Mandeno]

Hi Monty

The table on which your subform's recordsource is based should have a
primary key field. If you include this field in your subform's recordsource
query then you can find the primary key value for the current record:

(within the subform's class module)
KeyValue = Me.[KeyFieldName]

Once you have the primary key value you have a unique handle to identify the
record.

Is this what you mean, or have I completely misunderstood you?

 

['69 Camaro]

Hi, Monty.

My next step is to figure out a way to identify a specific record in
the subform's underlying table when one of the subform records is
selected or when one of its fields is double clicked.

Use the subform's OnCurrent( ) event and the control bound to the record's
primary key to identify which record has been selected. Use the control's
OnDblClick( ) event to find which column has been double clicked. For example:

Private Sub Form_Current()
MsgBox "The current record's primary key is: " & Me!txtID.Value
End Sub

Private Sub txtDiscount_DblClick(Cancel As Integer)
MsgBox "txtDiscount was double clicked."
End Sub

Once I can get there,
the subform's query that is, I can reference additional fields that
will produce a unique record in the underlying table.

Use the primary key to identify the unique record.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Monty]

Hi, Monty.


Use the subform's OnCurrent( ) event and the control bound to the record's
primary key to identify which record has been selected. Use the control's
OnDblClick( ) event to find which column has been double clicked. For example:

Private Sub Form_Current()
MsgBox "The current record's primary key is: " & Me!txtID.Value
End Sub

Private Sub txtDiscount_DblClick(Cancel As Integer)
MsgBox "txtDiscount was double clicked."
End Sub


Use the primary key to identify the unique record.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Both responses, from Gunny and Graham, seem to be what I'm looking for,
but I'd like to clarify something you both use: "Me!"...is that some
sort of shorthand convention?

 

['69 Camaro]

but I'd like to clarify something you both use: "Me!"...is that some
sort of shorthand convention?

 

['69 Camaro]

Hi, Monty.

I'd like to clarify something you both use: "Me!"...is that some
sort of shorthand convention?

Yes. When used on a form, it means "this form." When used on a report, it
means "this report." It can only be used in the modules for forms and reports.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[David W. Fenton]

Yes. When used on a form, it means "this form." When used on a
report, it means "this report." It can only be used in the
modules for forms and reports.

No, it can also be used in standalone class modules, because what it
actually refers to is the class module in which it is used, whether
that is a standalone one, or is attached to a report or form.

 

['69 Camaro]

Thanks for the reminder, David. I'd completely forgotten about that. One of
those "Doh!" moments I keep running into.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.