Double Click to add

  • Thread starter Thread starter JNana
  • Start date Start date
J

JNana

I have a combo box on my order details form. If the information is not
listed in the combo box, I would like to be able to double click, have the
products form open and allow entry of new product without having to leave
the order details form.

I have this working on another field that is a drop down list and it works.
Will this not work with a combo box?

Thanks
 
A combo box is generally considered the same as "drop down list". If
something isn't working, you should provide more details about your attempts
and results.
 
Like Duane says there may be a bit wrong with your "drop down/combo"

To open the form and add a new record you could use the combos not on list
action, something like

Private Sub NameOfYourCombo_NotInList(NewData As String, Response As Integer)
Dim vbResponse
Dim Msg As String
Dim strFName As String
Dim strListType As String
strMsg = "The Product is not on the list!" & vbNewLine & vbNewLine &
"This product may need to be added , """ & NewData & """, is not " & "on the
list." & vbNewLine & vbNewLine & "Do you want to add this product """ &
NewData & """" & vbNewLine & "to the list of """ & strListType & """?"
vbResponse = MsgBox(strMsg, vbInformation + vbButtonName + vbYesNo, "Add
New List Value?")
If vbResponse = vbYes Then
DoCmd.OpenForm "Insert the name of the form here", acNormal, , acFormAdd

You could get rid of a load of the msg's but I have shown them to show where
they should go. But a box like this would be a little daft to look at, (too
many lines and a bit confusing) but it's up to you.

Also you would need to alter acFormAdd if you wanted to check other items on
the list whilst the form was open (prices, ect) but I don't really know whats
on your list can't be any more specific.
 
My apologies for not providing enough information. Following is the SQL for
my subform.

SELECT DISTINCTROW [Order Details].OrderDetailID, [Order Details].OrderID,
[Order Details].LineItem, [Order Details].ProductID, [Order
Details].SerialNum, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount, Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS [Line Total], [Order
Details].ProductCode, [HandlingPct]*Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS HandlingChg, [Order Details].Notes
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
ORDER BY [Order Details].LineItem;



Following is the information behind the double-click event for my combo box.

Private Sub cboProduct_DblClick(Cancel As Integer)
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![ProductID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub



This is the field (from the main form) that does work on double-click. I
copied this and inserted the VBA in the event procedure for my combo box. I
inserted cboProduct in place of ShippingMethodID, but it did not work for
me.

Private Sub ShippingMethodID_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 ShippingMethodID_DblClick(Cancel As Integer)
On Error GoTo Err_ShippingMethodID_DblClick
Dim lngShippingMethodID As Long

If IsNull(Me![ShippingMethodID]) Then
Me![ShippingMethodID].Text = ""
Else
lngShippingMethodID = Me![ShippingMethodID]
Me![ShippingMethodID] = Null
End If
DoCmd.OpenForm "Shipping Methods", , , , , acdialog, "GotoNew"
Me![ShippingMethodID].Requery
If lngShippingMethodID <> 0 Then Me![ShippingMethodID] =
lngShippingMethodID

Exit_ShippingMethodID_DblClick:
Exit Sub

Err_ShippingMethodID_DblClick:
MsgBox Err.Description
Resume Exit_ShippingMethodID_DblClick
End Sub
 
JNana said:
My apologies for not providing enough information. Following is the SQL for
my subform.

SELECT DISTINCTROW [Order Details].OrderDetailID, [Order Details].OrderID,
[Order Details].LineItem, [Order Details].ProductID, [Order
Details].SerialNum, [Order Details].Quantity, [Order Details].UnitPrice,
[Order Details].Discount, Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS [Line Total], [Order
Details].ProductCode, [HandlingPct]*Round([Quantity]*[Order
Details].UnitPrice*(1-[Discount]),2) AS HandlingChg, [Order Details].Notes
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID
ORDER BY [Order Details].LineItem;



Following is the information behind the double-click event for my combo box.

Private Sub cboProduct_DblClick(Cancel As Integer)
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![ProductID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub



This is the field (from the main form) that does work on double-click. I
copied this and inserted the VBA in the event procedure for my combo box. I
inserted cboProduct in place of ShippingMethodID, but it did not work for
me.

Private Sub ShippingMethodID_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 ShippingMethodID_DblClick(Cancel As Integer)
On Error GoTo Err_ShippingMethodID_DblClick
Dim lngShippingMethodID As Long

If IsNull(Me![ShippingMethodID]) Then
Me![ShippingMethodID].Text = ""
Else
lngShippingMethodID = Me![ShippingMethodID]
Me![ShippingMethodID] = Null
End If
DoCmd.OpenForm "Shipping Methods", , , , , acdialog, "GotoNew"
Me![ShippingMethodID].Requery
If lngShippingMethodID <> 0 Then Me![ShippingMethodID] =
lngShippingMethodID

Exit_ShippingMethodID_DblClick:
Exit Sub

Err_ShippingMethodID_DblClick:
MsgBox Err.Description
Resume Exit_ShippingMethodID_DblClick
End Sub
Click to expand...

you also need to change the "shipping methods" form to what form you are
using to add the new data to the table. ie: "cboProductEntryForm"
 
Hello

hopefully someone can help on this problem that I am having that is related
to this.

when i doubleclick to add a new record to the drop down list, it is not
moving me to a new record spot. why? i have the exact same code, except for
the field being used, as shown below.

Private Sub ShippingMethodID_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 ShippingMethodID_DblClick(Cancel As Integer)
On Error GoTo Err_ShippingMethodID_DblClick
Dim lngShippingMethodID As Long

If IsNull(Me![ShippingMethodID]) Then
Me![ShippingMethodID].Text = ""
Else
lngShippingMethodID = Me![ShippingMethodID]
Me![ShippingMethodID] = Null
End If
DoCmd.OpenForm "Shipping Methods", , , , , acdialog, "GotoNew"
Me![ShippingMethodID].Requery
If lngShippingMethodID <> 0 Then Me![ShippingMethodID] =
lngShippingMethodID

Exit_ShippingMethodID_DblClick:
Exit Sub

Err_ShippingMethodID_DblClick:
MsgBox Err.Description
Resume Exit_ShippingMethodID_DblClick
End Sub

i am assuming that the problem is something with the command:

DoCmd.OpenForm "Shipping Methods", , , , , acdialog, "GotoNew"

but what? i have the gotonew part on my line, is there another argument or
part to the DoCmd.OpenForm that is used to get it to move the current record
to a blank one?
 
By itself, that "GotoNew" does absolutely nothing. All you're doing is
passing a string as an argument to the form you're opening up. You need to
do something in the form that's being opened to read that argument, and take
some action.

In the Load event of form "Shipping Methods", put code like:

Private Sub Form_Load()
If IsNull(Me.OpenArgs) = False Then
If Me.OpenArgs = "GoToNew" Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
End If
End Sub
 
Hello

thanks, works great now!

Douglas J. Steele said:
By itself, that "GotoNew" does absolutely nothing. All you're doing is
passing a string as an argument to the form you're opening up. You need to
do something in the form that's being opened to read that argument, and take
some action.

In the Load event of form "Shipping Methods", put code like:

Private Sub Form_Load()
If IsNull(Me.OpenArgs) = False Then
If Me.OpenArgs = "GoToNew" Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
End If
End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


DawnTreader said:
Hello

hopefully someone can help on this problem that I am having that is
related
to this.

when i doubleclick to add a new record to the drop down list, it is not
moving me to a new record spot. why? i have the exact same code, except
for
the field being used, as shown below.

Private Sub ShippingMethodID_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 ShippingMethodID_DblClick(Cancel As Integer)
On Error GoTo Err_ShippingMethodID_DblClick
Dim lngShippingMethodID As Long

If IsNull(Me![ShippingMethodID]) Then
Me![ShippingMethodID].Text = ""
Else
lngShippingMethodID = Me![ShippingMethodID]
Me![ShippingMethodID] = Null
End If
DoCmd.OpenForm "Shipping Methods", , , , , acdialog, "GotoNew"
Me![ShippingMethodID].Requery
If lngShippingMethodID <> 0 Then Me![ShippingMethodID] =
lngShippingMethodID

Exit_ShippingMethodID_DblClick:
Exit Sub

Err_ShippingMethodID_DblClick:
MsgBox Err.Description
Resume Exit_ShippingMethodID_DblClick
End Sub

i am assuming that the problem is something with the command:

DoCmd.OpenForm "Shipping Methods", , , , , acdialog, "GotoNew"

but what? i have the gotonew part on my line, is there another argument or
part to the DoCmd.OpenForm that is used to get it to move the current
record
to a blank one?
Click to expand...
Click to expand...
 
Back
Top