By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,501 Members | 831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,501 IT Pros & Developers. It's quick & easy.

NotInList DblClick ERROR

P: n/a
Hi i have the following code in a CustomerID field if the user enters a
notinlist customer then they should dbl click to open the customer form
However this error occurs
"You tried to assign a null value to a varaible that is not a variant
type"

My code
Dim lngCustomerID As Long

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

Exit_CustomerID_DblClick:
Exit Sub

Where have i gone wrong???
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 26 Jan 2004 04:59:08 GMT, David Deacon wrote:
Hi i have the following code in a CustomerID field if the user enters a
notinlist customer then they should dbl click to open the customer form
However this error occurs
"You tried to assign a null value to a varaible that is not a variant
type"

My code
Dim lngCustomerID As Long

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

Exit_CustomerID_DblClick:
Exit Sub

Where have i gone wrong???
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Have you tried stepping through to determine at what point the error
occurs? If not, it may not this piece of code that is causing it.
Also, is your example with in an event? Which one?

--
Mike Storr
veraccess.com
Nov 12 '05 #2

P: n/a
On 26 Jan 2004 04:59:08 GMT, David Deacon
<da**********@bigpond.com.au> wrote:

If the CustomerID field in the table is set to not allow nulls, the
line:
Me![CustomerID] = Null
may fail.

-Tom.

Hi i have the following code in a CustomerID field if the user enters a
notinlist customer then they should dbl click to open the customer form
However this error occurs
"You tried to assign a null value to a varaible that is not a variant
type"

My code
Dim lngCustomerID As Long

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

Exit_CustomerID_DblClick:
Exit Sub

Where have i gone wrong???
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 12 '05 #3

P: n/a
DD
Hi Mike
the code fails here
Else
lngCustomerID = Me![CustomerID]

This is a combobox, where you look up the the customerName it is the customerID
I am a bit lost on this
DD

Mike Storr <st******@sympatico.ca> wrote in message news:<a1****************************@40tude.net>.. . On 26 Jan 2004 04:59:08 GMT, David Deacon wrote:
Hi i have the following code in a CustomerID field if the user enters a
notinlist customer then they should dbl click to open the customer form
However this error occurs
"You tried to assign a null value to a varaible that is not a variant
type"

My code
Dim lngCustomerID As Long

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

Exit_CustomerID_DblClick:
Exit Sub

Where have i gone wrong???
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Have you tried stepping through to determine at what point the error
occurs? If not, it may not this piece of code that is causing it.
Also, is your example with in an event? Which one?

Nov 12 '05 #4

P: n/a
On 26 Jan 2004 12:30:02 -0800, DD wrote:

See below.
Hi Mike
the code fails here
Else
lngCustomerID = Me![CustomerID]


This is a combobox, where you look up the the customerName it is the customerID
I am a bit lost on this
DD

Mike Storr <st******@sympatico.ca> wrote in message news:<a1****************************@40tude.net>.. .
On 26 Jan 2004 04:59:08 GMT, David Deacon wrote:
Hi i have the following code in a CustomerID field if the user enters a
notinlist customer then they should dbl click to open the customer form
However this error occurs
"You tried to assign a null value to a varaible that is not a variant
type"

My code
Dim lngCustomerID As Long

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

Exit_CustomerID_DblClick:
Exit Sub

Where have i gone wrong???
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Have you tried stepping through to determine at what point the error
occurs? If not, it may not this piece of code that is causing it.
Also, is your example with in an event? Which one?


Is this being done in the NotInList or some other event that occurs prior
to the controls updating? If so, I think the problem is that the combobox
does not yet have a Value. I think it is not null because someone type
something into it, but a value has not yet been assigned to the bound
column because it does not yet exist.
If this is the NotInList event, then all the checking for nulls you are
doing is not really necessary, as something had to be entered in order for
the event to fire. Perhaps all you need to do is confirm that it is not an
empty string, and pass the new value (NewData argument) along to
frmCustomer using the OpenArgs portion of the DoCmd.OpenForm.

--
Mike Storr
veraccess.com
Nov 12 '05 #5

P: n/a
DD
Hi Mike

thanks for your input
I followed your idea of getting rid of the If Not IsNull and used
openArgs, all working great
thanks
DD
Mike Storr <st******@sympatico.ca> wrote in message news:<1f******************************@40tude.net> ...
On 26 Jan 2004 12:30:02 -0800, DD wrote:

See below.
Hi Mike
the code fails here
Else
lngCustomerID = Me![CustomerID]


This is a combobox, where you look up the the customerName it is the customerID
I am a bit lost on this
DD

Mike Storr <st******@sympatico.ca> wrote in message news:<a1****************************@40tude.net>.. .
On 26 Jan 2004 04:59:08 GMT, David Deacon wrote:

Hi i have the following code in a CustomerID field if the user enters a
notinlist customer then they should dbl click to open the customer form
However this error occurs
"You tried to assign a null value to a varaible that is not a variant
type"

My code
Dim lngCustomerID As Long

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

Exit_CustomerID_DblClick:
Exit Sub

Where have i gone wrong???
DD

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Have you tried stepping through to determine at what point the error
occurs? If not, it may not this piece of code that is causing it.
Also, is your example with in an event? Which one?


Is this being done in the NotInList or some other event that occurs prior
to the controls updating? If so, I think the problem is that the combobox
does not yet have a Value. I think it is not null because someone type
something into it, but a value has not yet been assigned to the bound
column because it does not yet exist.
If this is the NotInList event, then all the checking for nulls you are
doing is not really necessary, as something had to be entered in order for
the event to fire. Perhaps all you need to do is confirm that it is not an
empty string, and pass the new value (NewData argument) along to
frmCustomer using the OpenArgs portion of the DoCmd.OpenForm.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.