Double-click to add not working

  • Thread starter Thread starter jwr
  • Start date Start date
J

jwr

I have a subform that contains a combo box. ProductID is the record source.
Below is the code behind this field. I have another drop down list with
double click that does work and I do not see anything different.

Your assistance is appreciated.


Private Sub ProductID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acdialog, "GotoNew"
Me![ProductID].Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
 
When you say that it doesn't work, do you mean that the event isn't firing or
is there some other aspect of it that doesn't work. If you put a break on
your first line of code, does it stop execution there?

Barry
 
Sorry, I did not explain fully.

If my product is not in the drop down list, when I type the name, I get a
pop-up form that says "Double Click on this to add." Double clicking does
nothing. No errors, nothing.

I am not real knowledgeable in code. What do you mean, "If you put a break
on your first line of code, does it stop execution there?"

Thanks

Barry Gilbert said:
When you say that it doesn't work, do you mean that the event isn't firing
or
is there some other aspect of it that doesn't work. If you put a break on
your first line of code, does it stop execution there?

Barry

jwr said:
I have a subform that contains a combo box. ProductID is the record
source.
Below is the code behind this field. I have another drop down list with
double click that does work and I do not see anything different.

Your assistance is appreciated.


Private Sub ProductID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acdialog, "GotoNew"
Me![ProductID].Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
Click to expand...
Click to expand...
 
In your code, inside the ProductId_DblClick routine, put your cursor on the
line that says:
If IsNull(Me![ProductID]) Then

and click F9. This will change the line's color, meaning it has a break.
When (if) the code's execution reaches this line, it will stop. You can then
click F8 to step through each line of code to try to see where it's failing.

Barry


jwr said:
Sorry, I did not explain fully.

If my product is not in the drop down list, when I type the name, I get a
pop-up form that says "Double Click on this to add." Double clicking does
nothing. No errors, nothing.

I am not real knowledgeable in code. What do you mean, "If you put a break
on your first line of code, does it stop execution there?"

Thanks

Barry Gilbert said:
When you say that it doesn't work, do you mean that the event isn't firing
or
is there some other aspect of it that doesn't work. If you put a break on
your first line of code, does it stop execution there?

Barry

jwr said:
I have a subform that contains a combo box. ProductID is the record
source.
Below is the code behind this field. I have another drop down list with
double click that does work and I do not see anything different.

Your assistance is appreciated.


Private Sub ProductID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acdialog, "GotoNew"
Me![ProductID].Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I put in the break, tried to run the routine, got message box to double
click, but it is not working. This takes me to the Code. The line that
says:

If IsNull(Me![ProductID]) Then
is highlighted with yellow (over the color that it changed to with
F9).

I press F8, then the line that reads:
Me![ProductID].Text = ""
is highlighted yellow.

I press F8 again, and
MsgBox Err.Description
is highlighted yellow.

Now I do not know what I should do next.

Thank you.


Barry Gilbert said:
In your code, inside the ProductId_DblClick routine, put your cursor on
the
line that says:
If IsNull(Me![ProductID]) Then

and click F9. This will change the line's color, meaning it has a break.
When (if) the code's execution reaches this line, it will stop. You can
then
click F8 to step through each line of code to try to see where it's
failing.

Barry


jwr said:
Sorry, I did not explain fully.

If my product is not in the drop down list, when I type the name, I get a
pop-up form that says "Double Click on this to add." Double clicking
does
nothing. No errors, nothing.

I am not real knowledgeable in code. What do you mean, "If you put a
break
on your first line of code, does it stop execution there?"

Thanks

Barry Gilbert said:
When you say that it doesn't work, do you mean that the event isn't
firing
or
is there some other aspect of it that doesn't work. If you put a break
on
your first line of code, does it stop execution there?

Barry

:

I have a subform that contains a combo box. ProductID is the record
source.
Below is the code behind this field. I have another drop down list
with
double click that does work and I do not see anything different.

Your assistance is appreciated.


Private Sub ProductID_NotInList(NewData As String, Response As
Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acdialog, "GotoNew"
Me![ProductID].Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
Click to expand...
Click to expand...
Click to expand...
 
This tells us that the Me![ProductID].Text = "" statement is invalid. Try
removing the ".text" portion so that it reads:
Me![ProductId]=""

Barry

jwr said:
I put in the break, tried to run the routine, got message box to double
click, but it is not working. This takes me to the Code. The line that
says:

If IsNull(Me![ProductID]) Then
is highlighted with yellow (over the color that it changed to with
F9).

I press F8, then the line that reads:
Me![ProductID].Text = ""
is highlighted yellow.

I press F8 again, and
MsgBox Err.Description
is highlighted yellow.

Now I do not know what I should do next.

Thank you.


Barry Gilbert said:
In your code, inside the ProductId_DblClick routine, put your cursor on
the
line that says:
If IsNull(Me![ProductID]) Then

and click F9. This will change the line's color, meaning it has a break.
When (if) the code's execution reaches this line, it will stop. You can
then
click F8 to step through each line of code to try to see where it's
failing.

Barry


jwr said:
Sorry, I did not explain fully.

If my product is not in the drop down list, when I type the name, I get a
pop-up form that says "Double Click on this to add." Double clicking
does
nothing. No errors, nothing.

I am not real knowledgeable in code. What do you mean, "If you put a
break
on your first line of code, does it stop execution there?"

Thanks

When you say that it doesn't work, do you mean that the event isn't
firing
or
is there some other aspect of it that doesn't work. If you put a break
on
your first line of code, does it stop execution there?

Barry

:

I have a subform that contains a combo box. ProductID is the record
source.
Below is the code behind this field. I have another drop down list
with
double click that does work and I do not see anything different.

Your assistance is appreciated.


Private Sub ProductID_NotInList(NewData As String, Response As
Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acdialog, "GotoNew"
Me![ProductID].Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I removed "text". Still does not work. If I do make a selection from the
drop down list and attempt to continue past that field, error pops up "You
tried to assign the null value to a variable that is not a Variant data
type."


Barry Gilbert said:
This tells us that the Me![ProductID].Text = "" statement is invalid. Try
removing the ".text" portion so that it reads:
Me![ProductId]=""

Barry

jwr said:
I put in the break, tried to run the routine, got message box to double
click, but it is not working. This takes me to the Code. The line that
says:

If IsNull(Me![ProductID]) Then
is highlighted with yellow (over the color that it changed to with
F9).

I press F8, then the line that reads:
Me![ProductID].Text = ""
is highlighted yellow.

I press F8 again, and
MsgBox Err.Description
is highlighted yellow.

Now I do not know what I should do next.

Thank you.


Barry Gilbert said:
In your code, inside the ProductId_DblClick routine, put your cursor on
the
line that says:
If IsNull(Me![ProductID]) Then

and click F9. This will change the line's color, meaning it has a
break.
When (if) the code's execution reaches this line, it will stop. You can
then
click F8 to step through each line of code to try to see where it's
failing.

Barry


:

Sorry, I did not explain fully.

If my product is not in the drop down list, when I type the name, I
get a
pop-up form that says "Double Click on this to add." Double clicking
does
nothing. No errors, nothing.

I am not real knowledgeable in code. What do you mean, "If you put a
break
on your first line of code, does it stop execution there?"

Thanks

message
When you say that it doesn't work, do you mean that the event isn't
firing
or
is there some other aspect of it that doesn't work. If you put a
break
on
your first line of code, does it stop execution there?

Barry

:

I have a subform that contains a combo box. ProductID is the
record
source.
Below is the code behind this field. I have another drop down list
with
double click that does work and I do not see anything different.

Your assistance is appreciated.


Private Sub ProductID_NotInList(NewData As String, Response As
Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long

If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acdialog, "GotoNew"
Me![ProductID].Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID

Exit_ProductID_DblClick:
Exit Sub

Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
Click to expand...
Click to expand...
Click to expand...
 
If what you trying to do its to add a new item to the list then
why do you use double click event while you could do it using NotInList
event only?

ProductID the name of the combobox

Properties
ColumnCount=3
ColumnWidths=0cm;2,54cm;0cm
RowSource=SELECT tblProducts.ProductID, tblProducts.Product,
tblProducts.UnitPrice FROM tblProducts ORDER BY tblProducts.Product;
LimitToList=Yes.

Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("You want to add the new entry " & NewData & " to list?",
vbYesNo, "New Entry!") = vbYes Then
Response = acDataErrAdded

Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblProducts")
rst.AddNew
rst!Product = NewData
rst.Update
rst.Close
Else
Response = acDataErrDisplay

Me.ProductID.SetFocus
Me.ProductID = Null
End If
End Sub

Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
 
If you were to do the following, does your double click code work:

1. Open the form in runtime
2. Immediately (before entering a value and finding it out that it is not in
the list) double click the field.
 
No it does not work that way either.


Access101 said:
If you were to do the following, does your double click code work:

1. Open the form in runtime
2. Immediately (before entering a value and finding it out that it is not
in
the list) double click the field.



Svetlana said:
If what you trying to do its to add a new item to the list then
why do you use double click event while you could do it using NotInList
event only?

ProductID the name of the combobox

Properties
ColumnCount=3
ColumnWidths=0cm;2,54cm;0cm
RowSource=SELECT tblProducts.ProductID, tblProducts.Product,
tblProducts.UnitPrice FROM tblProducts ORDER BY tblProducts.Product;
LimitToList=Yes.

Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("You want to add the new entry " & NewData & " to list?",
vbYesNo, "New Entry!") = vbYes Then
Response = acDataErrAdded

Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblProducts")
rst.AddNew
rst!Product = NewData
rst.Update
rst.Close
Else
Response = acDataErrDisplay

Me.ProductID.SetFocus
Me.ProductID = Null
End If
End Sub

Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Click to expand...
Click to expand...
 
Just a thought....

If ProductID is a PK then it can't contain Null or "", it must have a value
os some sort.


jwr said:
No it does not work that way either.


Access101 said:
If you were to do the following, does your double click code work:

1. Open the form in runtime
2. Immediately (before entering a value and finding it out that it is not
in
the list) double click the field.



Svetlana said:
If what you trying to do its to add a new item to the list then
why do you use double click event while you could do it using NotInList
event only?

ProductID the name of the combobox

Properties
ColumnCount=3
ColumnWidths=0cm;2,54cm;0cm
RowSource=SELECT tblProducts.ProductID, tblProducts.Product,
tblProducts.UnitPrice FROM tblProducts ORDER BY tblProducts.Product;
LimitToList=Yes.

Private Sub ProductID_NotInList(NewData As String, Response As Integer)
If MsgBox("You want to add the new entry " & NewData & " to list?",
vbYesNo, "New Entry!") = vbYes Then
Response = acDataErrAdded

Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblProducts")
rst.AddNew
rst!Product = NewData
rst.Update
rst.Close
Else
Response = acDataErrDisplay

Me.ProductID.SetFocus
Me.ProductID = Null
End If
End Sub

Private Sub ProductID_AfterUpdate()
Me![UnitPrice] = Me![ProductID].Column(2)
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top