Re: Dbl-Click to add Choice to Combo Box

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


"laser02910" wrote:

> 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?
>

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
--

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

.

Relevant Pages

  • Re: Requerying ComboBox in NotInList event
    ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... Dim lngBlankFound As Long ...
    (comp.databases.ms-access)
  • Re: Requerying ComboBox in NotInList event
    ... Any attempt to requery the combobox gives me an error that the field ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • RE: Help With "NotInList"
    ... account number, it lets me add it but the rest of the fields fill on the form ... Dim rs As DAO.Recordset ... Response As Integer) ... Set rst = Me.RecordsetClone ...
    (microsoft.public.access.formscoding)
  • disable default message (NotInList)
    ... my problem is the default access message still appear if i click NO ... Response As Integer) ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.forms)
  • Need Help | Rolling out new App. | Trouble with some computers.
    ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ... ' Data file not found in application folder - try to ask the user ...
    (microsoft.public.access.modulesdaovba)