Double Click Field Conflicts

[ServiceEnvoy]

I am using the following double click event to open another form to
the record that matches the product id to the item number. The
problem is that the item number sometimes has characters that confuse
the double click event. How do I preserve the event and isolate the
item number so it doesn't hurt the code of the event?

Private Sub PartNumber_DblClick(Cancel As Integer)
If IsNull(Me.PartNumber) Then
DoCmd.OpenForm "FRM_Inventory", , , , acFormAdd
Else
DoCmd.OpenForm "FRM_Inventory", , , "ItemNumber=" &
Me.PartNumber
End If
End Sub

 

[Carl Rapson]

I am using the following double click event to open another form to
the record that matches the product id to the item number. The
problem is that the item number sometimes has characters that confuse
the double click event. How do I preserve the event and isolate the
item number so it doesn't hurt the code of the event?

Private Sub PartNumber_DblClick(Cancel As Integer)
If IsNull(Me.PartNumber) Then
DoCmd.OpenForm "FRM_Inventory", , , , acFormAdd
Else
DoCmd.OpenForm "FRM_Inventory", , , "ItemNumber=" &
Me.PartNumber
End If
End Sub

What do you mean by "sometimes has characters that confuse the double click
event"? Can you give an example?

Carl Rapson

 

[ServiceEnvoy]

What do you mean by "sometimes has characters that confuse the double click
event"? Can you give an example?

Carl Rapson

Sometimes the "itemnumber" will have characters like "-" or "/" or
blank spaces. That seems to mess up the formula. If it was just a
standard number (12345) it doesn't cause any problems.

 

[Carl Rapson]

Sometimes the "itemnumber" will have characters like "-" or "/" or
blank spaces. That seems to mess up the formula. If it was just a
standard number (12345) it doesn't cause any problems.

If your item number is a text value instead of numeric, you'll need to put
quotes around it in the Where clause:

"ItemNumber='" & Me.PartNumber & "'"

The same thing exaggerated for clarity:

"ItemNumber= ' " & Me.PartNumber & " ' "

If there's a chance that the item number could also contain a single quote,
you'll need to use Replace to "double-up" the single quotes:

"ItemNumber='" & Replace(Me.PartNumber,"'","''") & "'"

Again, exaggerated for clarity:

"ItemNumber= ' " & Replace(Me.PartNumber," ' "," ' ' ") & " ' "


Carl Rapson

 

[ServiceEnvoy]

If your item number is a text value instead of numeric, you'll need to put
quotes around it in the Where clause:

"ItemNumber='" & Me.PartNumber & "'"

The same thing exaggerated for clarity:

"ItemNumber= ' " & Me.PartNumber & " ' "

If there's a chance that the item number could also contain a single quote,
you'll need to use Replace to "double-up" the single quotes:

"ItemNumber='" & Replace(Me.PartNumber,"'","''") & "'"

Again, exaggerated for clarity:

"ItemNumber= ' " & Replace(Me.PartNumber," ' "," ' ' ") & " ' "

Carl Rapson

Ok, now I stumbled onto another help site and it says something about
the field needing to be "numeric" or non/text. I tried to implement
the changes you suggested on a similar form but I'm still having
trouble. Here is what I have. The field "workordernum" will need to
be "text" to allow both alpha and/or numeric characters.

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = " & Me.
[WorkOrderNum] & ""
End Sub

 

[Pieter Wijnen]

you need to enclose in ' or "

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = '" &
Me.WorkOrderNum.Value & "'"
End Sub

hth

Pieter

If your item number is a text value instead of numeric, you'll need to
put
quotes around it in the Where clause:

"ItemNumber='" & Me.PartNumber & "'"

The same thing exaggerated for clarity:

"ItemNumber= ' " & Me.PartNumber & " ' "

If there's a chance that the item number could also contain a single
quote,
you'll need to use Replace to "double-up" the single quotes:

"ItemNumber='" & Replace(Me.PartNumber,"'","''") & "'"

Again, exaggerated for clarity:

"ItemNumber= ' " & Replace(Me.PartNumber," ' "," ' ' ") & " ' "

Carl Rapson

Ok, now I stumbled onto another help site and it says something about
the field needing to be "numeric" or non/text. I tried to implement
the changes you suggested on a similar form but I'm still having
trouble. Here is what I have. The field "workordernum" will need to
be "text" to allow both alpha and/or numeric characters.

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = " & Me.
[WorkOrderNum] & ""
End Sub

 

[ServiceEnvoy]

you need to enclose in ' or "

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = '" &
Me.WorkOrderNum.Value & "'"
End Sub

hth

Pieter

Ok, now I stumbled onto another help site and it says something about
the field needing to be "numeric" or non/text. I tried to implement
the changes you suggested on a similar form but I'm still having
trouble. Here is what I have. The field "workordernum" will need to
be "text" to allow both alpha and/or numeric characters.

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = " & Me.
[WorkOrderNum] & ""
End Sub

Thanks. That fixed the workordernum one. I'll try the same principle
on the first form later and let you know.

 

[ServiceEnvoy]

you need to enclose in ' or "

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = '" &
Me.WorkOrderNum.Value & "'"
End Sub

hth

Pieter

news:[email protected]...

On Oct 19, 9:14 am, "Carl Rapson" <[email protected]>
wrote:

On Oct 12, 9:22 am, "Carl Rapson" <[email protected]>
wrote:

I am using the following double click event to open another form to
the record that matches the product id to the item number. The
problem is that the item number sometimes has characters that
confuse
the double click event. How do I preserve the event and isolate the
item number so it doesn't hurt the code of the event?
Private Sub PartNumber_DblClick(Cancel As Integer)
If IsNull(Me.PartNumber) Then
DoCmd.OpenForm "FRM_Inventory", , , , acFormAdd
Else
DoCmd.OpenForm "FRM_Inventory", , , "ItemNumber=" &
Me.PartNumber
End If
End Sub
What do you mean by "sometimes has characters that confuse the double
click
event"? Can you give an example?
Carl Rapson
Sometimes the "itemnumber" will have characters like "-" or "/" or
blank spaces. That seems to mess up the formula. If it was just a
standard number (12345) it doesn't cause any problems.
If your item number is a text value instead of numeric, you'll need to
put
quotes around it in the Where clause:
"ItemNumber='" & Me.PartNumber & "'"
The same thing exaggerated for clarity:
"ItemNumber= ' " & Me.PartNumber & " ' "
If there's a chance that the item number could also contain a single
quote,
you'll need to use Replace to "double-up" the single quotes:
"ItemNumber='" & Replace(Me.PartNumber,"'","''") & "'"
Again, exaggerated for clarity:
"ItemNumber= ' " & Replace(Me.PartNumber," ' "," ' ' ") & " ' "
Carl Rapson
Ok, now I stumbled onto another help site and it says something about
the field needing to be "numeric" or non/text. I tried to implement
the changes you suggested on a similar form but I'm still having
trouble. Here is what I have. The field "workordernum" will need to
be "text" to allow both alpha and/or numeric characters.
Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = " & Me.
[WorkOrderNum] & ""
End Sub

Thanks. That fixed the workordernum one. I'll try the same principle
on the first form later and let you know.

Ok, ran into a wrinkle. It works unless there is a space in the work
order number. Some of the work order numbers we get from our clients
have a space (3456 789). Do you know how to get around that?

 

[Pieter Wijnen]

Shouldn't make a difference if your calling form (Me.WorkOrderNum) matches
the stored value

Pieter

you need to enclose in ' or "

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = '" &
Me.WorkOrderNum.Value & "'"
End Sub

hth

Pieter

news:[email protected]...

On Oct 19, 9:14 am, "Carl Rapson" <[email protected]>
wrote:

news:[email protected]...

On Oct 12, 9:22 am, "Carl Rapson" <[email protected]>
wrote:

news:[email protected]...

I am using the following double click event to open another form
to
the record that matches the product id to the item number. The
problem is that the item number sometimes has characters that
confuse
the double click event. How do I preserve the event and
isolate the
item number so it doesn't hurt the code of the event?

Private Sub PartNumber_DblClick(Cancel As Integer)
If IsNull(Me.PartNumber) Then
DoCmd.OpenForm "FRM_Inventory", , , , acFormAdd
Else
DoCmd.OpenForm "FRM_Inventory", , , "ItemNumber=" &
Me.PartNumber
End If
End Sub

What do you mean by "sometimes has characters that confuse the
double
click
event"? Can you give an example?

Carl Rapson

Sometimes the "itemnumber" will have characters like "-" or "/" or
blank spaces. That seems to mess up the formula. If it was just
a
standard number (12345) it doesn't cause any problems.

If your item number is a text value instead of numeric, you'll need
to
put
quotes around it in the Where clause:

"ItemNumber='" & Me.PartNumber & "'"

The same thing exaggerated for clarity:

"ItemNumber= ' " & Me.PartNumber & " ' "

If there's a chance that the item number could also contain a single
quote,
you'll need to use Replace to "double-up" the single quotes:

"ItemNumber='" & Replace(Me.PartNumber,"'","''") & "'"

Again, exaggerated for clarity:

"ItemNumber= ' " & Replace(Me.PartNumber," ' "," ' ' ") & " ' "

Carl Rapson

Ok, now I stumbled onto another help site and it says something about
the field needing to be "numeric" or non/text. I tried to implement
the changes you suggested on a similar form but I'm still having
trouble. Here is what I have. The field "workordernum" will need to
be "text" to allow both alpha and/or numeric characters.

Private Sub WorkOrderNum_DblClick(Cancel As Integer)
DoCmd.OpenForm "FrmTicketsAll", , , "[workordernum] = " & Me.
[WorkOrderNum] & ""
End Sub

Thanks. That fixed the workordernum one. I'll try the same principle
on the first form later and let you know.

Ok, ran into a wrinkle. It works unless there is a space in the work
order number. Some of the work order numbers we get from our clients
have a space (3456 789). Do you know how to get around that?