Tables & Forms

[Guest]

My table and form are tied together; however, I want to create a situation
where when I double-click on a record in my table, it automatically opens the
related form. Is there an easy way to do this? Thanks.

 

[Dirk Goldgar]

My table and form are tied together; however, I want to create a
situation where when I double-click on a record in my table, it
automatically opens the related form. Is there an easy way to do
this? Thanks.

You can't do that with a table datasheet directly. What you can do is
create a datasheet form to view the table -- that is, a form that will
be displayed in datasheet view -- instead of just opening the table
datasheet. Then you can create event procedures to handle the DblClick
events of the form and/or the controls on the form, and use the event to
open your details form in form view, filtered to the matching record.

 

[John Vinson]

My table and form are tied together; however, I want to create a situation
where when I double-click on a record in my table, it automatically opens the
related form. Is there an easy way to do this? Thanks.

Not really. Table datasheets have no usable events; it's generally
recommended that users never see table datasheets at all, and
developers look at them only for design and debugging purposes.

You could have two *FORMS* based on the table - one in datasheet or
continuous view, showing multiple records, and have a command button
or dblclick event on some control on that form to open a single-record
view form - but you cannot do so from a Table.

John W. Vinson[MVP]

 

[Guest]

Thanks! Can you give me a quick idea of what that event expression would
look like? If I double click on one element of a form that's in datasheet
view, how do I get it to go to another form that's a detailed view?

 

[Dirk Goldgar]

Thanks! Can you give me a quick idea of what that event expression
would look like? If I double click on one element of a form that's
in datasheet view, how do I get it to go to another form that's a
detailed view?

For example, here's code for the DblClick event of a hypothetical text
box named "ClientName", to open a form named "frmClientDetails",
filtered by a field on both forms named "ClientID", which I'm also
assuming is numeric, not text:

'----- start of example code -----
Private Sub ClientName_DblClick(Cancel As Integer)

With Me!ClientID

If IsNull(.Value) Then
' do nothing -- probably a new record.
Else
' If the current record has been modified, save it
' first.

If Me.Dirty Then Me.Dirty = False

' Now open the corresponding detail form.

DoCmd.OpenForm "frmClientDetails", _
WhereCondition:="ClientID = " & .Value

End If

End With

End Sub
'----- end of example code -----

 

[Guest]

This works now, in that if I double-click my form datasheet, it takes me to
the forms contact page with more detailed information; however, it doesn't
take me to the corresponding record, only the first record in the form
database. Both forms (the form datasheet table and the form contacts
detailed form) have a unique field, Contact ID. Here's what I did, and it
doesn't take me to the corresponding record:

Private Sub ContactID_DblClick(Cancel As Integer)

With Me!ContactID
If IsNull(.Value) Then
' do nothing -- probably a new record.
Else
' If the current record has been modified save it
' first
If Me.Dirty Then Me.Dirty = False
' Now open the corresponding detail form.
DoCmd.OpenForm "Contacts Form"
WhereCondition = "ContactID=" & .Value
End If

Can you let me know what I'm doing wrong? Thanks!

 

[Dirk Goldgar]

This works now, in that if I double-click my form datasheet, it takes
me to the forms contact page with more detailed information; however,
it doesn't take me to the corresponding record, only the first record
in the form database. Both forms (the form datasheet table and the
form contacts detailed form) have a unique field, Contact ID. Here's
what I did, and it doesn't take me to the corresponding record: [snip]
Can you let me know what I'm doing wrong? Thanks!

Your version of the code:

DoCmd.OpenForm "Contacts Form"
WhereCondition = "ContactID=" & .Value

Doesn't match my version:

in that your second line above is not a continuation of the
DoCmd.OpenForm statement. That's what the ", _" at the end of my
version was doing: continuing the statement onto another line.

You should change your code to:

DoCmd.OpenForm "Contacts Form", _
WhereCondition = "ContactID=" & .Value

Incidentally, you must not have the Option Explicit line at the top of
that module, so Access thinks "WhereCondition" in your code is the name
of a new variable. I recommend that you set the option "Require
Variable Declaration" in the VB Editor's Options dialog, and that you go
back to any modules you've already created, including this form module,
and add the line

Option Explicit

in the Declarations section of each module. That will keep Access from
guessing you want to create a new variable when you happen to misspell a
variable name, or when you make a typo like the one above.

 

[Guest]

Now it's telling me that it doesn't like the underline symbol after the comma
in the first line. Sorry to be a pest!

Private Sub ContactID_DblClick(Cancel As Integer)

With Me!ContactID

If IsNull(.Value) Then
' do nothing -- probably a new record.
Else
' If the current record has been modified save it
' first
If Me.Dirty Then Me.Dirty = False
' Now open the corresponding detail form.
DoCmd.OpenForm "Contacts Form",_
WhereCondition = "ContactID=" & .Value
End If
End With

This works now, in that if I double-click my form datasheet, it takes
me to the forms contact page with more detailed information; however,
it doesn't take me to the corresponding record, only the first record
in the form database. Both forms (the form datasheet table and the
form contacts detailed form) have a unique field, Contact ID. Here's
what I did, and it doesn't take me to the corresponding record: [snip]
Can you let me know what I'm doing wrong? Thanks!

Your version of the code:

DoCmd.OpenForm "Contacts Form"
WhereCondition = "ContactID=" & .Value

Doesn't match my version:

in that your second line above is not a continuation of the
DoCmd.OpenForm statement. That's what the ", _" at the end of my
version was doing: continuing the statement onto another line.

You should change your code to:

DoCmd.OpenForm "Contacts Form", _
WhereCondition = "ContactID=" & .Value

Incidentally, you must not have the Option Explicit line at the top of
that module, so Access thinks "WhereCondition" in your code is the name
of a new variable. I recommend that you set the option "Require
Variable Declaration" in the VB Editor's Options dialog, and that you go
back to any modules you've already created, including this form module,
and add the line

Option Explicit

in the Declarations section of each module. That will keep Access from
guessing you want to create a new variable when you happen to misspell a
variable name, or when you make a typo like the one above.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Now it's telling me that it doesn't like the underline symbol after
the comma in the first line. Sorry to be a pest!

Private Sub ContactID_DblClick(Cancel As Integer)

With Me!ContactID

If IsNull(.Value) Then
' do nothing -- probably a new record.
Else
' If the current record has been modified save it
' first
If Me.Dirty Then Me.Dirty = False
' Now open the corresponding detail form.
DoCmd.OpenForm "Contacts Form",_
WhereCondition = "ContactID=" & .Value
End If
End With

You need a space between the comma and the underscore.

 

[Guest]

Hey, I had tried this, but it didn't work, and then I tried it again but also
added the colon after the = in the WhereCondition, and it worked! Yahoo!
Thanks so much for your help. I knew it would be something small like this .
.. .

 

[Dirk Goldgar]

Hey, I had tried this, but it didn't work, and then I tried it again
but also added the colon after the = in the WhereCondition, and it
worked! Yahoo!

Ah, sorry, I hadn't noticed that you had dropped the colon from my
original code, and then I perpetuated by posting the correction without
the colon. I'm sorry for the confusion caused by my mistake, and I'm
glad you were able to fix it.

Thanks so much for your help. I knew it would be
something small like this . . .

You're welcome.