Run-Time Error 2501

[ServiceEnvoy]

I'm trying to set up a double click function so I can double click on
a sub form and open a main form called "FRM_Tickets-All" and open it
to the record that has the matching "internalticketnum" field. When I
double click it I get the dreaded "Run-time error 2501. Here is the
code I have now:

Private Sub Ticket_DblClick(Cancel As Integer)
DoCmd.OpenForm "Frm_Tickets-All", , , "[internalticketnum] = '" &
Me.InternalTicketNum.Value & "'"
End Sub

What am I doing wrong?

 

[Allen Browne]

The message means that the WhereCondition string is not right.

If internalticketnum is a Number type field when you open its table in
design view (not a Text type field), you don't need the extra quotes.

If the internalticketnum is blank (null) the filter string would not work.

If the record was not saved yet, trying to open it in another form would not
work.

This code example addresses those issues:

Private Sub Ticket_DblClick(Cancel As Integer)
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save any edits.
If IsNull(Me.InternalTicketNum) Then 'Check there is a ticket
MsgBox "No ticket num"
Else
strWhere = "[internalticketnum] = " & Me.InternalTicketNum
DoCmd.OpenForm "Frm_Tickets-All", WhereCondition:=strWhere
End If
End Sub

That should avoid the error. It still won't work correctly if the target
form is already open (even in design view.)

 

[ServiceEnvoy]

Thanks for the thorough explanation. I am really trying to learn the
logic behind the code so I can ask for help less often. Your code
worked great and it gave me an idea so I modified it slightly. I
added the following line so if the internalticketnum field was empty,
it would take the user to our New Ticket Data Entry Form instead of
just telling them it was empty:
stDocName = "DataEntry-Fireytech"
DoCmd.OpenForm stDocName, , , acFormAdd

So now the code reads:

Private Sub Ticket_DblClick(Cancel As Integer)
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save any edits.
If IsNull(Me.InternalTicketNum) Then 'Check there is a ticket
stDocName = "DataEntry-Fireytech"
DoCmd.OpenForm stDocName, , , acFormAdd
Else
strWhere = "[internalticketnum] = " & Me.InternalTicketNum
DoCmd.OpenForm "Frm_Tickets-All", WhereCondition:=strWhere
End If
End Sub

Do you see any problems with that modification? It works I just
thought I would check to see if you saw any potential problems.

 

[ServiceEnvoy]

I do have a followup question. Is there a way to use the preceding
code and do one other thing....Can I open the new data entry form and
automatically have it enter the customer id into the new ticket from
the "CustomerID" field?

 

[Allen Browne]

So this kind of thing:

Private Sub Ticket_DblClick(Cancel As Integer)
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save any edits.

If IsNull(Me.InternalTicketNum) Then 'Check there is a ticket
stDocName = "DataEntry-Fireytech"
DoCmd.OpenForm stDocName, , , acFormAdd
If Not IsNull(Me.CustomerID) Then
Forms(stDocName)![CustomerID] = Me.[CustomerID]
End If
Else
strWhere = "[internalticketnum] = " & Me.InternalTicketNum
DoCmd.OpenForm "Frm_Tickets-All", WhereCondition:=strWhere
End If
End Sub