Dbl-Click to add Choice to Combo Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box on an Order form to select Shipping Method. On double
click it opens the Shipping Methods form either to add if blank or edit if
filled in. This works fine but I want to be able to use the new value for
shipping method after I close the Shipping Method form. The code I'm using is:

' If no Method has been selected, open the form
' to add a record.
If IsNull(Me.ShippingMethod) Then
DoCmd.OpenForm "Shipping Methods", DataMode:=acFormAdd
Else
' Open the form to the selected record.
DoCmd.OpenForm "Shipping Methods", acFormAdd

End If

Any ideas?
 
Sorry Jeff, I'm not sure what you mean. I tried adding:
ShippingMrthod.Value = "NewData"
After the End If and I get runtime errors. The problem is that when I close
the Shipping method form the new value doesn't sow up in the drop down of my
combo box. It would be nice if the value I just entered was automatically
entered but just being able to see it in the list would be good too. Your
help is appreciated..
 
laser02910 said:
I have a combo box on an Order form to select Shipping Method. On double
click it opens the Shipping Methods form either to add if blank or edit if
filled in. This works fine but I want to be able to use the new value for
shipping method after I close the Shipping Method form. The code I'm using is:

' If no Method has been selected, open the form
' to add a record.
If IsNull(Me.ShippingMethod) Then
DoCmd.OpenForm "Shipping Methods", DataMode:=acFormAdd
Else
' Open the form to the selected record.
DoCmd.OpenForm "Shipping Methods", acFormAdd

End If

Any ideas?
Click to expand...

You need to use the "Not In List" event. Examples are at:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=NotInList.mdb

http://www.rogersaccesslibrary.com/download3.asp?SampleName=NotInListAlternatives.mdb


http://www.mvps.org/access/forms/frm0015.htm

HTH
 
Thanks Steve! I looked everywhere I knew for these types of examples. Now I
can look in more places. I like one of the examples in the NotInList.mdb but
I can't get it to work for me. The sample database works fine but when I use
the code I get a Run-time error #3134- "Syntax error in SET INTO statement"
My code:
Dim strsql As String, x As Integer
Dim LinkCriteria As String
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into Shipping Methods ([ShippingMethod]) values ('"
& NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ShippingMethod] = '" & Me!ShippingMethod.Text & "'"
DoCmd.OpenForm "Shipping Methods", , , LinkCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

It stops in the debugger at - CurrentDb.Execute strsql, dbFailOnError
If I exit the debugger I get the standard not in list message and I'm back
to square 1. Any ideas here?
 
laser02910 said:
Thanks Steve! I looked everywhere I knew for these types of examples. Now I
can look in more places. I like one of the examples in the NotInList.mdb but
I can't get it to work for me. The sample database works fine but when I use
the code I get a Run-time error #3134- "Syntax error in SET INTO statement"
My code:
Dim strsql As String, x As Integer
Dim LinkCriteria As String
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into Shipping Methods ([ShippingMethod]) values ('"
& NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ShippingMethod] = '" & Me!ShippingMethod.Text & "'"
DoCmd.OpenForm "Shipping Methods", , , LinkCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

It stops in the debugger at - CurrentDb.Execute strsql, dbFailOnError
If I exit the debugger I get the standard not in list message and I'm back
to square 1. Any ideas here?
Click to expand...

The only thing I see is that you used a space in the table name (bad
practice).
Add brackets around the table name:

'*** SNIP ***
strsql = "Insert Into [Shipping Methods] ...........

'***SNIP ***

Also, I would move "Response = acDataErrAdded" to be the next line after
"CurrentDb.Execute strsql, dbFailOnError".

Are you re-opening the form after adding the new value?? If so, that is not
necessary (IIRC). The Not-In-List event automatically requeries the combo box
so the new value will be available.

I was a little confused for a bit. It looks like you have a form named
"Shipping Methods", a table named "Shipping Methods" and a field named
"ShippingMethod"! How do you keep them straight? When you get time, look up
'Naming conventions' on the web or in the back of an Access reference manual.

(I've read many posts from MPVs about using a naming convention and sticking
with it. <g>)

I found a text file that I had put several different Not-In-List code
snippets. Here is another way to add values - uses DAO - so you need a
reference to Microsoft DAO 3.6 Object Library. I tried to modify it to fit
your case.... (untested - but it should work):

'*** begin code ***
'Suppress the default error message.
Response = acDataErrContinue

' Prompt user to verify if they wish to add a new value.
If MsgBox("Do you want to add this value to the list?", vbYesNo) = vbYes
Then

' Set Response argument to indicate that data is being added.
'Open a recordset of the [Shipping Methods] Table.
Dim db As Database
Dim rst As Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "Select * From [Shipping Methods]"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'Add a new Shipping Method with the value
'that is stored in the variable NewData.
rst.AddNew
rst![ShippingMethod] = NewData
rst.Update

'Inform the combo box that the desired item has been added to the list.
Response = acDataErrAdded

rst.Close 'Close the recordset
Set rst = Nothing
Set db = Nothing
End If
'*** end code ****


HTH
 
Thank you Steve! I'm still new to a lot of Access' features, especially code.
I'm using some examples from Northwind.mdb, Inventory.mdb, etc... some of
these examples have the same type of "naming" problems. You are quite right
that I have a hard time keeping track of some things and I would go back and
change the names if it were that easy. I will definitely use better practices
in my next project. The code I was using just would not work for some reason
so, I used yours. It worked better than the example I was using. I didn’t
have to make any changes at all. You have been a great help! Thanks again!

SteveS said:
laser02910 said:
Thanks Steve! I looked everywhere I knew for these types of examples. Now I
can look in more places. I like one of the examples in the NotInList.mdb but
I can't get it to work for me. The sample database works fine but when I use
the code I get a Run-time error #3134- "Syntax error in SET INTO statement"
My code:
Dim strsql As String, x As Integer
Dim LinkCriteria As String
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into Shipping Methods ([ShippingMethod]) values ('"
& NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
LinkCriteria = "[ShippingMethod] = '" & Me!ShippingMethod.Text & "'"
DoCmd.OpenForm "Shipping Methods", , , LinkCriteria

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

It stops in the debugger at - CurrentDb.Execute strsql, dbFailOnError
If I exit the debugger I get the standard not in list message and I'm back
to square 1. Any ideas here?
Click to expand...

The only thing I see is that you used a space in the table name (bad
practice).
Add brackets around the table name:

'*** SNIP ***
strsql = "Insert Into [Shipping Methods] ...........

'***SNIP ***

Also, I would move "Response = acDataErrAdded" to be the next line after
"CurrentDb.Execute strsql, dbFailOnError".

Are you re-opening the form after adding the new value?? If so, that is not
necessary (IIRC). The Not-In-List event automatically requeries the combo box
so the new value will be available.

I was a little confused for a bit. It looks like you have a form named
"Shipping Methods", a table named "Shipping Methods" and a field named
"ShippingMethod"! How do you keep them straight? When you get time, look up
'Naming conventions' on the web or in the back of an Access reference manual.

(I've read many posts from MPVs about using a naming convention and sticking
with it. <g>)

I found a text file that I had put several different Not-In-List code
snippets. Here is another way to add values - uses DAO - so you need a
reference to Microsoft DAO 3.6 Object Library. I tried to modify it to fit
your case.... (untested - but it should work):

'*** begin code ***
'Suppress the default error message.
Response = acDataErrContinue

' Prompt user to verify if they wish to add a new value.
If MsgBox("Do you want to add this value to the list?", vbYesNo) = vbYes
Then

' Set Response argument to indicate that data is being added.
'Open a recordset of the [Shipping Methods] Table.
Dim db As Database
Dim rst As Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "Select * From [Shipping Methods]"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

'Add a new Shipping Method with the value
'that is stored in the variable NewData.
rst.AddNew
rst![ShippingMethod] = NewData
rst.Update

'Inform the combo box that the desired item has been added to the list.
Response = acDataErrAdded

rst.Close 'Close the recordset
Set rst = Nothing
Set db = Nothing
End If
'*** end code ****


HTH
Click to expand...
 
Back
Top