DblClick "Type Mismatch" error

[JohnLute]

I'm getting a "Type Mismatch" error dialog box when I execute the DblClick
event procedure below. I've reviewed it over and over again and I can't see
the problem.

Any help would be greatly appreciated!

Private Sub ShipUnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_ShipUnitDescription_DblClick
Dim lngShipUnitDescription As Long

If IsNull(Me![ShipUnitDescription]) Then
Me![ShipUnitDescription].Text = ""
Else
lngShipUnitDescription = Me![ShipUnitDescription]
Me![ShipUnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![ShipUnitDescription].Requery
If lngShipUnitDescription <> 0 Then Me![ShipUnitDescription] =
lngShipUnitDescription

Exit_ShipUnitDescription_DblClick:
Exit Sub

Err_ShipUnitDescription_DblClick:
MsgBox Err.Description
Resume Exit_ShipUnitDescription_DblClick
End Sub

 

[Klatuu]

It would be easier to determine the problem if we knew on which line the
error is occuring. Two things may be causing it:

First possibility
Change this line:
Me![ShipUnitDescription].Text = ""
To
Me![ShipUnitDescription] = ""

The Text property is only valid when the control has the focus. VBA does
not see the Text propert the same way VB does.

Second Possibility
Is lngShipUnitDesscription a numeric field? One would normally expect
something with "Dessscription" in the name to be text.

If lngShipUnitDescription <> 0 Then Me![ShipUnitDescription] =
lngShipUnitDescription

 

[JohnLute]

Thanks!

I don't know which line is creating the error. I don't get prompted for the
debugger. I just get the dialog box that states "Type mismatch."

The field is text however, I use the same exact coding for other text fields
and have no issues. Just this field. very puzzling.

I changed this line:
Me![ShipUnitDescription].Text = ""
To
Me![ShipUnitDescription] = ""

Didn't help. Same dialog box appears.

--
www.Marzetti.com

It would be easier to determine the problem if we knew on which line the
error is occuring. Two things may be causing it:

First possibility
Change this line:
Me![ShipUnitDescription].Text = ""
To
Me![ShipUnitDescription] = ""

The Text property is only valid when the control has the focus. VBA does
not see the Text propert the same way VB does.

Second Possibility
Is lngShipUnitDesscription a numeric field? One would normally expect
something with "Dessscription" in the name to be text.

If lngShipUnitDescription <> 0 Then Me![ShipUnitDescription] =
lngShipUnitDescription

I'm getting a "Type Mismatch" error dialog box when I execute the DblClick
event procedure below. I've reviewed it over and over again and I can't see
the problem.

Any help would be greatly appreciated!

Private Sub ShipUnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_ShipUnitDescription_DblClick
Dim lngShipUnitDescription As Long

If IsNull(Me![ShipUnitDescription]) Then
Me![ShipUnitDescription].Text = ""
Else
lngShipUnitDescription = Me![ShipUnitDescription]
Me![ShipUnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![ShipUnitDescription].Requery
If lngShipUnitDescription <> 0 Then Me![ShipUnitDescription] =
lngShipUnitDescription

Exit_ShipUnitDescription_DblClick:
Exit Sub

Err_ShipUnitDescription_DblClick:
MsgBox Err.Description
Resume Exit_ShipUnitDescription_DblClick
End Sub

 

[Klatuu]

You can get into debug mode so you can see where the error occurs.
Open the code module where this code is, position your cursor on the first
executable line of code and press F9.
Now do the double click to start the code. The VB Editor will open up with
the line you selected highlighted. Step through the code 1 line at a time
until the error occurs.

Thanks!

I don't know which line is creating the error. I don't get prompted for the
debugger. I just get the dialog box that states "Type mismatch."

The field is text however, I use the same exact coding for other text fields
and have no issues. Just this field. very puzzling.

I changed this line:
Me![ShipUnitDescription].Text = ""
To
Me![ShipUnitDescription] = ""

Didn't help. Same dialog box appears.

--
www.Marzetti.com

It would be easier to determine the problem if we knew on which line the
error is occuring. Two things may be causing it:

First possibility
Change this line:
Me![ShipUnitDescription].Text = ""
To
Me![ShipUnitDescription] = ""

The Text property is only valid when the control has the focus. VBA does
not see the Text propert the same way VB does.

Second Possibility
Is lngShipUnitDesscription a numeric field? One would normally expect
something with "Dessscription" in the name to be text.

If lngShipUnitDescription <> 0 Then Me![ShipUnitDescription] =
lngShipUnitDescription

I'm getting a "Type Mismatch" error dialog box when I execute the DblClick
event procedure below. I've reviewed it over and over again and I can't see
the problem.

Any help would be greatly appreciated!

Private Sub ShipUnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_ShipUnitDescription_DblClick
Dim lngShipUnitDescription As Long

If IsNull(Me![ShipUnitDescription]) Then
Me![ShipUnitDescription].Text = ""
Else
lngShipUnitDescription = Me![ShipUnitDescription]
Me![ShipUnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![ShipUnitDescription].Requery
If lngShipUnitDescription <> 0 Then Me![ShipUnitDescription] =
lngShipUnitDescription

Exit_ShipUnitDescription_DblClick:
Exit Sub

Err_ShipUnitDescription_DblClick:
MsgBox Err.Description
Resume Exit_ShipUnitDescription_DblClick
End Sub

 

[JohnLute]

Slick! I never knew about that! I placed a ">" before the 3 lines that are in
question:

Private Sub UnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_UnitDescription_DblClick

Dim lngUnitDescription As Long

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![UnitDescription].Requery

Exit_UnitDescription_DblClick:

Exit Sub

Err_UnitDescription_DblClick:

MsgBox Err.Description
Resume Exit_UnitDescription_DblClick
End Sub

Again, this coding works fine for other text fields. Very strange.

 

[Klatuu]

I don't understand what you mean by "lines that are in question".
They are not executable lines and will not cause an error.
Start your trace on this line:
If IsNull(Me![UnitDescription]) Then

Slick! I never knew about that! I placed a ">" before the 3 lines that are in
question:

Private Sub UnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_UnitDescription_DblClick

Dim lngUnitDescription As Long

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![UnitDescription].Requery

Exit_UnitDescription_DblClick:

Exit Sub

Err_UnitDescription_DblClick:

MsgBox Err.Description
Resume Exit_UnitDescription_DblClick
End Sub

Again, this coding works fine for other text fields. Very strange.

--
www.Marzetti.com

You can get into debug mode so you can see where the error occurs.
Open the code module where this code is, position your cursor on the first
executable line of code and press F9.
Now do the double click to start the code. The VB Editor will open up with
the line you selected highlighted. Step through the code 1 line at a time
until the error occurs.

 

[JohnLute]

I see now.

OK - when the field is null then the DblClick executes correctly. When the
field has a value then I get the "Type mismatch" dialog.

--
www.Marzetti.com

I don't understand what you mean by "lines that are in question".
They are not executable lines and will not cause an error.
Start your trace on this line:
If IsNull(Me![UnitDescription]) Then

Slick! I never knew about that! I placed a ">" before the 3 lines that are in
question:

Private Sub UnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_UnitDescription_DblClick

Dim lngUnitDescription As Long

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![UnitDescription].Requery

Exit_UnitDescription_DblClick:

Exit Sub

Err_UnitDescription_DblClick:

MsgBox Err.Description
Resume Exit_UnitDescription_DblClick
End Sub

Again, this coding works fine for other text fields. Very strange.

--
www.Marzetti.com

You can get into debug mode so you can see where the error occurs.
Open the code module where this code is, position your cursor on the first
executable line of code and press F9.
Now do the double click to start the code. The VB Editor will open up with
the line you selected highlighted. Step through the code 1 line at a time
until the error occurs.

 

[Klatuu]

Makes sense, I should have seen it. What is happening when
Me![UnitDescription] contains Null, you are trying to assign Null to
lngUnitDescription. Only a variant data type can be Null. You should retype
lngUnitDescription as Variant.

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If

I see now.

OK - when the field is null then the DblClick executes correctly. When the
field has a value then I get the "Type mismatch" dialog.

--
www.Marzetti.com

I don't understand what you mean by "lines that are in question".
They are not executable lines and will not cause an error.
Start your trace on this line:
If IsNull(Me![UnitDescription]) Then

Slick! I never knew about that! I placed a ">" before the 3 lines that are in
question:

Private Sub UnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_UnitDescription_DblClick
Dim lngUnitDescription As Long

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![UnitDescription].Requery
If lngUnitDescription <> 0 Then Me![UnitDescription] = lngUnitDescription

Exit_UnitDescription_DblClick:
Exit Sub

Err_UnitDescription_DblClick:
MsgBox Err.Description
Resume Exit_UnitDescription_DblClick
End Sub

Again, this coding works fine for other text fields. Very strange.

--
www.Marzetti.com


:

You can get into debug mode so you can see where the error occurs.
Open the code module where this code is, position your cursor on the first
executable line of code and press F9.
Now do the double click to start the code. The VB Editor will open up with
the line you selected highlighted. Step through the code 1 line at a time
until the error occurs.

 

[JohnLute]

Sorry - I'm lost there. What do you mean by "variant?"

Thanks!

--
www.Marzetti.com

Makes sense, I should have seen it. What is happening when
Me![UnitDescription] contains Null, you are trying to assign Null to
lngUnitDescription. Only a variant data type can be Null. You should retype
lngUnitDescription as Variant.

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If

I see now.

OK - when the field is null then the DblClick executes correctly. When the
field has a value then I get the "Type mismatch" dialog.

--
www.Marzetti.com

I don't understand what you mean by "lines that are in question".
They are not executable lines and will not cause an error.
Start your trace on this line:
If IsNull(Me![UnitDescription]) Then
:

Slick! I never knew about that! I placed a ">" before the 3 lines that are in
question:

Private Sub UnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_UnitDescription_DblClick
Dim lngUnitDescription As Long

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![UnitDescription].Requery
If lngUnitDescription <> 0 Then Me![UnitDescription] = lngUnitDescription

Exit_UnitDescription_DblClick:
Exit Sub

Err_UnitDescription_DblClick:
MsgBox Err.Description
Resume Exit_UnitDescription_DblClick
End Sub

Again, this coding works fine for other text fields. Very strange.

--
www.Marzetti.com


:

You can get into debug mode so you can see where the error occurs.
Open the code module where this code is, position your cursor on the first
executable line of code and press F9.
Now do the double click to start the code. The VB Editor will open up with
the line you selected highlighted. Step through the code 1 line at a time
until the error occurs.

 

[Klatuu]

Open your VB Editor, click on Help, and type Variant Data Type in the search
box. You will find complete information on it there.

Sorry - I'm lost there. What do you mean by "variant?"

Thanks!

--
www.Marzetti.com

Makes sense, I should have seen it. What is happening when
Me![UnitDescription] contains Null, you are trying to assign Null to
lngUnitDescription. Only a variant data type can be Null. You should retype
lngUnitDescription as Variant.

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If

I see now.

OK - when the field is null then the DblClick executes correctly. When the
field has a value then I get the "Type mismatch" dialog.

--
www.Marzetti.com


:

I don't understand what you mean by "lines that are in question".
They are not executable lines and will not cause an error.
Start your trace on this line:
If IsNull(Me![UnitDescription]) Then
:

Slick! I never knew about that! I placed a ">" before the 3 lines that are in
question:

Private Sub UnitDescription_DblClick(Cancel As Integer)
On Error GoTo Err_UnitDescription_DblClick
Dim lngUnitDescription As Long

If IsNull(Me![UnitDescription]) Then
Me![UnitDescription] = ""
Else
lngUnitDescription = Me![UnitDescription]
Me![UnitDescription] = Null
End If
DoCmd.OpenForm "frmFGUnitDescriptions", , , , , acDialog, "GotoNew"
Me![UnitDescription].Requery
If lngUnitDescription <> 0 Then Me![UnitDescription] = lngUnitDescription

Exit_UnitDescription_DblClick:
Exit Sub

Err_UnitDescription_DblClick:
MsgBox Err.Description
Resume Exit_UnitDescription_DblClick
End Sub

Again, this coding works fine for other text fields. Very strange.

--
www.Marzetti.com


:

You can get into debug mode so you can see where the error occurs.
Open the code module where this code is, position your cursor on the first
executable line of code and press F9.
Now do the double click to start the code. The VB Editor will open up with
the line you selected highlighted. Step through the code 1 line at a time
until the error occurs.

 

[JohnLute]

Thanks! Because the field was a text data type with a size of 255 I had to
change the "lng" variant in the code to "dbl."

Thanks for your help!

 

[Klatuu]

I am glad you got it working, but you really are misusing data types.

 

[JohnLute]

The text field is properly text. So I suppose I'm potentially misusing code...?

 

[Klatuu]

No, you code looks pretty good - nice style, good indenting, easy to read.
The problem is data types.
Try to be consistent with the types of data and variables you are using.
VBA will force conversion for you when it can, but sometimes it doesn't come
out like you expect because VBA is making a "best guess".

I suggest you do some reading on variable types and data types. Please
understand I am only trying to make your life easier.

 

[Mark]

I know this is an old thread but im having the exact same problem as this and
just found it from a search. i know nothing about code and am building my
first database. i need help please. ive got the following code for DblClick

Private Sub ChemicalID_DblClick(Cancel As Integer)
On Error GoTo Err_ChemicalID_DblClick
Dim lngChemicalID As Long

If IsNull(Me![ChemicalID]) Then
Me![ChemicalID].Text = ""
Else
lngChemicalID = Me![ChemicalID]
Me![ChemicalID] = Null
End If
DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"
Me![ChemicalID].Requery
If lngChemicalID <> 0 Then Me![ChemicalID] = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

My problem is that if the ChemicalID field is empty the dblclick works, but
if there is a value in there i get the "Type Mismatch" error. can anyone
tell me simply which line needs changing and to what? the previous user
seemed to solve it by changing lng to dbl, but that doesnt seem to work for
me.

help greatly appreciated

 

[Klatuu]

It would be good to know what kind of control ChemicalID is and what kind of
data you may expect it to contain. Also, if you can identify the line where
the error occurs, it would be helpful.

I can't quite figure out what it is you are trying to do. If you could
please describe the purpose of this, it would be helpful.

Private Sub ChemicalID_DblClick(Cancel As Integer)
Dim lngChemicalID As Long

On Error GoTo Err_ChemicalID_DblClick

If IsNull(Me.ChemicalID) Then
Me.ChemicalID = "" 'You don't need to use the Text property in VBA
Else 'It is only valid when the control
has the focus
lngChemicalID = Me.ChemicalID
Me.ChemicalID = Null
End If

DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"

Me.ChemicalID.Requery 'Why?

If lngChemicalID <> 0 Then Me.ChemicalID = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

 

[Mark]

Hi, thanks for the reply. ChemicalId is a text field on a main form that
holds the name of a chemical. if the chemical that the user types in is not
on the list they get a not on list error and a msg telling them to dblclick
to open the chemicals form so they can put in a new one. The problem is that
only works if the field is blank. So people start typing something and if
anythign is selected they then cant open the form because of the type
mismatch error. they have to delete whats in the field, click on another
field and then come back to the now empty ChemicalId and then do the doubl
click.

does that help?

It would be good to know what kind of control ChemicalID is and what kind of
data you may expect it to contain. Also, if you can identify the line where
the error occurs, it would be helpful.

I can't quite figure out what it is you are trying to do. If you could
please describe the purpose of this, it would be helpful.

Private Sub ChemicalID_DblClick(Cancel As Integer)
Dim lngChemicalID As Long

On Error GoTo Err_ChemicalID_DblClick

If IsNull(Me.ChemicalID) Then
Me.ChemicalID = "" 'You don't need to use the Text property in VBA
Else 'It is only valid when the control
has the focus
lngChemicalID = Me.ChemicalID
Me.ChemicalID = Null
End If

DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"

Me.ChemicalID.Requery 'Why?

If lngChemicalID <> 0 Then Me.ChemicalID = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

I know this is an old thread but im having the exact same problem as this and
just found it from a search. i know nothing about code and am building my
first database. i need help please. ive got the following code for DblClick

Private Sub ChemicalID_DblClick(Cancel As Integer)
On Error GoTo Err_ChemicalID_DblClick
Dim lngChemicalID As Long

If IsNull(Me![ChemicalID]) Then
Me![ChemicalID].Text = ""
Else
lngChemicalID = Me![ChemicalID]
Me![ChemicalID] = Null
End If
DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"
Me![ChemicalID].Requery
If lngChemicalID <> 0 Then Me![ChemicalID] = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

My problem is that if the ChemicalID field is empty the dblclick works, but
if there is a value in there i get the "Type Mismatch" error. can anyone
tell me simply which line needs changing and to what? the previous user
seemed to solve it by changing lng to dbl, but that doesnt seem to work for
me.

help greatly appreciated

 

[Klatuu]

You are actually going about it a very hard way. One of the problems is that
when you double click, the click event will fire first, because it did detect
a click.

Lookups like this are commonly handled with a combo box. That way, you can
take the appropriate action in the After Update event when the user selects a
item from the list and in the Not In List event when the user has entered
something not in the combo's rowsource.

You can use a plain text box, but the better place to handle this would be
in the Before Update event. Thay way, if the use decides not to create a new
record, you can cancel the update and allow them to reenter.

Hi, thanks for the reply. ChemicalId is a text field on a main form that
holds the name of a chemical. if the chemical that the user types in is not
on the list they get a not on list error and a msg telling them to dblclick
to open the chemicals form so they can put in a new one. The problem is that
only works if the field is blank. So people start typing something and if
anythign is selected they then cant open the form because of the type
mismatch error. they have to delete whats in the field, click on another
field and then come back to the now empty ChemicalId and then do the doubl
click.

does that help?

It would be good to know what kind of control ChemicalID is and what kind of
data you may expect it to contain. Also, if you can identify the line where
the error occurs, it would be helpful.

I can't quite figure out what it is you are trying to do. If you could
please describe the purpose of this, it would be helpful.

Private Sub ChemicalID_DblClick(Cancel As Integer)
Dim lngChemicalID As Long

On Error GoTo Err_ChemicalID_DblClick

If IsNull(Me.ChemicalID) Then
Me.ChemicalID = "" 'You don't need to use the Text property in VBA
Else 'It is only valid when the control
has the focus
lngChemicalID = Me.ChemicalID
Me.ChemicalID = Null
End If

DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"

Me.ChemicalID.Requery 'Why?

If lngChemicalID <> 0 Then Me.ChemicalID = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

I know this is an old thread but im having the exact same problem as this and
just found it from a search. i know nothing about code and am building my
first database. i need help please. ive got the following code for DblClick

Private Sub ChemicalID_DblClick(Cancel As Integer)
On Error GoTo Err_ChemicalID_DblClick
Dim lngChemicalID As Long

If IsNull(Me![ChemicalID]) Then
Me![ChemicalID].Text = ""
Else
lngChemicalID = Me![ChemicalID]
Me![ChemicalID] = Null
End If
DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"
Me![ChemicalID].Requery
If lngChemicalID <> 0 Then Me![ChemicalID] = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

My problem is that if the ChemicalID field is empty the dblclick works, but
if there is a value in there i get the "Type Mismatch" error. can anyone
tell me simply which line needs changing and to what? the previous user
seemed to solve it by changing lng to dbl, but that doesnt seem to work for
me.

help greatly appreciated

 

[Mark]

im new to access and not really sure what you are describing. i am using a
combo for the chemicalid on the main form, but what i want is the user to be
able to open the chemicals form from there in order to be able to enter a new
chemical. i thought the way to do that was to give them a "double click to
enter" message when what they type is not already in the combobox. but as i
say it only works if that combo is empty, not once they have started to type
something in there. is there no way to change that code so that the double
click will work regardless of whether theres anything in the chemicalid combo
or not? something to do with that null statement?

You are actually going about it a very hard way. One of the problems is that
when you double click, the click event will fire first, because it did detect
a click.

Lookups like this are commonly handled with a combo box. That way, you can
take the appropriate action in the After Update event when the user selects a
item from the list and in the Not In List event when the user has entered
something not in the combo's rowsource.

You can use a plain text box, but the better place to handle this would be
in the Before Update event. Thay way, if the use decides not to create a new
record, you can cancel the update and allow them to reenter.

Hi, thanks for the reply. ChemicalId is a text field on a main form that
holds the name of a chemical. if the chemical that the user types in is not
on the list they get a not on list error and a msg telling them to dblclick
to open the chemicals form so they can put in a new one. The problem is that
only works if the field is blank. So people start typing something and if
anythign is selected they then cant open the form because of the type
mismatch error. they have to delete whats in the field, click on another
field and then come back to the now empty ChemicalId and then do the doubl
click.

does that help?

It would be good to know what kind of control ChemicalID is and what kind of
data you may expect it to contain. Also, if you can identify the line where
the error occurs, it would be helpful.

I can't quite figure out what it is you are trying to do. If you could
please describe the purpose of this, it would be helpful.

Private Sub ChemicalID_DblClick(Cancel As Integer)
Dim lngChemicalID As Long

On Error GoTo Err_ChemicalID_DblClick

If IsNull(Me.ChemicalID) Then
Me.ChemicalID = "" 'You don't need to use the Text property in VBA
Else 'It is only valid when the control
has the focus
lngChemicalID = Me.ChemicalID
Me.ChemicalID = Null
End If

DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"

Me.ChemicalID.Requery 'Why?

If lngChemicalID <> 0 Then Me.ChemicalID = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub


:

I know this is an old thread but im having the exact same problem as this and
just found it from a search. i know nothing about code and am building my
first database. i need help please. ive got the following code for DblClick

Private Sub ChemicalID_DblClick(Cancel As Integer)
On Error GoTo Err_ChemicalID_DblClick
Dim lngChemicalID As Long

If IsNull(Me![ChemicalID]) Then
Me![ChemicalID].Text = ""
Else
lngChemicalID = Me![ChemicalID]
Me![ChemicalID] = Null
End If
DoCmd.OpenForm "chemicals", , , , , acDialog, "GotoNew"
Me![ChemicalID].Requery
If lngChemicalID <> 0 Then Me![ChemicalID] = lngChemicalID

Exit_ChemicalID_DblClick:
Exit Sub

Err_ChemicalID_DblClick:
MsgBox Err.Description
Resume Exit_ChemicalID_DblClick
End Sub

My problem is that if the ChemicalID field is empty the dblclick works, but
if there is a value in there i get the "Type Mismatch" error. can anyone
tell me simply which line needs changing and to what? the previous user
seemed to solve it by changing lng to dbl, but that doesnt seem to work for
me.

help greatly appreciated