updating field value threw a combobox MDB VS ADP

[Ran BD]

hi guys
first I do apologize for the cross reference.

I've setup a combobox on a form and I would like the value to be updated in
the parent table if the value doesn't exist.
I've received a code from Allen brown which I'm using
the weird part is that the code produces error 3265 while being used on an
MDB file and error 91 while being used with ADP file.
does anyone has any idea ?

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.

Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK
Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If

Exit_Append2Table:
Set rst = Nothing
Exit Function

Err_Append2Table:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function
_________________________________________________________________________
Private Sub Combo2_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Combo2], NewData)
End Sub

 

[tom]

I have seen and used several different routines to help with this problem,
but all seem clunky and possibly unnecessary. If you want a combo rowsource
to be based on any entered value then why don't you base it on the table
where these valuse are stored, setting the Limit to List property of the
combo to No? To do this, set the combo's rowsource property to the column of
the table where the info is going (e.g. a reference data table is not
necessary). To remove duplicates Show Totals and use Group By, and set the
criteria field in the query grid to <>"" to remove blanks. If you need an
immediate update to the combo, then place a copy of the finished SQL in its
after update event, i.e. everytime you put a value into the combo, it adds
it to the list.

HTH.

Tom.

 

[Ran BD]

Tom that is great and the idea you suggested does work nevertheless as I am
in the process of learning I want to understand why the code I previously
posted didn't work.

Thanks

Ran

 

[DanK]

Why do people insist on having tons of lines of code when
one little line will do!

Enter the following in the AfterUpdate property!

Me![NameOfControl].Requery

email me if you need to!

 

[tom]

Because this doesn't always work! In any case, just who are you talking
about?