OpenForm will not work

[zonk2439]

I have a form (Form1) in datasheet view that is made from Table1, and has a
field (which is the primary key in the table and is an autonumber) named
"ISSUEID". This shows all the records of Table1.
I have also created Form2 which shows a single record from table1( This also
has the field "ISSUEID".

What I would like is when I double click on the "ISSUEID" on the datasheet
(form1) it will open up form2 and show that record.

Below is the code I have on double click for ISSUEID on the datasheet
(form1) it will open form2 but does not filter it to the proper ISSUEID.


Dim VarIssueID As Object
Dim stLinkCriteria As String
Dim stDocname As String

stDocname = "Issues"
Set VarIssueID = [IssueID]
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

DoCmd.OpenForm stDocname, , , stLinkCriteria


End Sub

I am new to all of this, so if this all jacked up I apologize.
Thanks for any help

 

[fredg]

I have a form (Form1) in datasheet view that is made from Table1, and has a
field (which is the primary key in the table and is an autonumber) named
"ISSUEID". This shows all the records of Table1.
I have also created Form2 which shows a single record from table1( This also
has the field "ISSUEID".

What I would like is when I double click on the "ISSUEID" on the datasheet
(form1) it will open up form2 and show that record.

Below is the code I have on double click for ISSUEID on the datasheet
(form1) it will open form2 but does not filter it to the proper ISSUEID.

Dim VarIssueID As Object
Dim stLinkCriteria As String
Dim stDocname As String

stDocname = "Issues"
Set VarIssueID = [IssueID]
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

DoCmd.OpenForm stDocname, , , stLinkCriteria

End Sub

I am new to all of this, so if this all jacked up I apologize.
Thanks for any help

What is the datatype of the [IssueID] field?
Your [IssueID] field is a Number datatype, so:

Private Sub IssueID_DblClick(Cancel As Integer)
Dim stLinkCriteria As String
Dim stDocname As String

stDocname = "Issues"
stLinkCriteria = "[IssueID] = " & [IssueID]
DoCmd.OpenForm stDocname, , , stLinkCriteria

End Sub

If, however, [IssueID] was a Text datatype, then you would use:
stLinkCriteria = "[IssueID] = '" & [IssueID] & "'"

This above syntax will open the Issues form in single or continuous
view, depending upon the form's default view property setting.

If you wish to open the Issues form in datasheet view you must use:
DoCmd.OpenForm stDocName , acFormDS, , stLinlCriteria

I would suggest you look up, in VBA help...
OpenForm method
as well as
Where Clause
as well as
Restrict data to a subset of records

 

[Klatuu]

This line:
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

is incorrect. The form doesn't know what [Issues] is. You only put the
name of the field and it must be a field name of the recordset of the form
you are opening. Also, you have the variable name in quotes and the two
pieces are not concantenated. What VBA is going to try to do is to evaluate
whether the string [Issues].[IssueID] is equal to the string "VarIssueID".
Because they are not the same, the evaluation will be False. Then since
strLinkCriteria is Dimmed as a string, the value of strLinkCriteria after the
line executes will be "False"

This line is also incorrect:

Set VarIssueID = [IssueID]

VarIssueID is named so that the reader would expect it to be a variant, but
you Dim it as an Object.

All you really need to do is pass the name of the field in the other form's
recordset and the value from the current form's recordset. This would be the
correct syntax:

stLinkCriteria = "[IssueID] = " & Me.IssueID

 

[zonk2439]

Thank you both for the help and explaination!! I did manage to get it
working and with the explainations that you gave I have learned quite a bit
in the process.

Thank you!

This line:
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

is incorrect. The form doesn't know what [Issues] is. You only put the
name of the field and it must be a field name of the recordset of the form
you are opening. Also, you have the variable name in quotes and the two
pieces are not concantenated. What VBA is going to try to do is to evaluate
whether the string [Issues].[IssueID] is equal to the string "VarIssueID".
Because they are not the same, the evaluation will be False. Then since
strLinkCriteria is Dimmed as a string, the value of strLinkCriteria after the
line executes will be "False"

This line is also incorrect:

Set VarIssueID = [IssueID]

VarIssueID is named so that the reader would expect it to be a variant, but
you Dim it as an Object.

All you really need to do is pass the name of the field in the other form's
recordset and the value from the current form's recordset. This would be the
correct syntax:

stLinkCriteria = "[IssueID] = " & Me.IssueID

--
Dave Hargis, Microsoft Access MVP

I have a form (Form1) in datasheet view that is made from Table1, and has a
field (which is the primary key in the table and is an autonumber) named
"ISSUEID". This shows all the records of Table1.
I have also created Form2 which shows a single record from table1( This also
has the field "ISSUEID".

What I would like is when I double click on the "ISSUEID" on the datasheet
(form1) it will open up form2 and show that record.

Below is the code I have on double click for ISSUEID on the datasheet
(form1) it will open form2 but does not filter it to the proper ISSUEID.


Dim VarIssueID As Object
Dim stLinkCriteria As String
Dim stDocname As String

stDocname = "Issues"
Set VarIssueID = [IssueID]
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

DoCmd.OpenForm stDocname, , , stLinkCriteria


End Sub

I am new to all of this, so if this all jacked up I apologize.
Thanks for any help

 

[Klatuu]

Glad I could help.
--
Dave Hargis, Microsoft Access MVP

Thank you both for the help and explaination!! I did manage to get it
working and with the explainations that you gave I have learned quite a bit
in the process.

Thank you!

This line:
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

is incorrect. The form doesn't know what [Issues] is. You only put the
name of the field and it must be a field name of the recordset of the form
you are opening. Also, you have the variable name in quotes and the two
pieces are not concantenated. What VBA is going to try to do is to evaluate
whether the string [Issues].[IssueID] is equal to the string "VarIssueID".
Because they are not the same, the evaluation will be False. Then since
strLinkCriteria is Dimmed as a string, the value of strLinkCriteria after the
line executes will be "False"

This line is also incorrect:

Set VarIssueID = [IssueID]

VarIssueID is named so that the reader would expect it to be a variant, but
you Dim it as an Object.

All you really need to do is pass the name of the field in the other form's
recordset and the value from the current form's recordset. This would be the
correct syntax:

stLinkCriteria = "[IssueID] = " & Me.IssueID

--
Dave Hargis, Microsoft Access MVP

I have a form (Form1) in datasheet view that is made from Table1, and has a
field (which is the primary key in the table and is an autonumber) named
"ISSUEID". This shows all the records of Table1.
I have also created Form2 which shows a single record from table1( This also
has the field "ISSUEID".

What I would like is when I double click on the "ISSUEID" on the datasheet
(form1) it will open up form2 and show that record.

Below is the code I have on double click for ISSUEID on the datasheet
(form1) it will open form2 but does not filter it to the proper ISSUEID.


Dim VarIssueID As Object
Dim stLinkCriteria As String
Dim stDocname As String

stDocname = "Issues"
Set VarIssueID = [IssueID]
stLinkCriteria = "[Issues].[IssueID]" = "VarIssueID"

DoCmd.OpenForm stDocname, , , stLinkCriteria


End Sub

I am new to all of this, so if this all jacked up I apologize.
Thanks for any help