Creating a "global" notinlist function

[Ceebaby via AccessMonster.com]

Hi Folks

This has been puzzling me. I frequently use the same code over and over again
on various combo boxes stored within my database on their notinlist event.

Heres what I use

Private Sub cmbType_NotInList(NewData As String, Response As Integer)

Dim rst As DAO.Recordset

If MsgBox(NewData & "... not in list, do you want to add it?", vbOKCancel,
"DIS87") = vbOK Then
Set rst = CurrentDb.OpenRecordset("QRY ListEnforcement") -

With rst
.AddNew
.Fields("Enforcement") = NewData
.Update
.CLOSE
End With
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Works like a charm'

However, I would like to create a global/public proceedure/function for the
notinlist code above where I could just insert the names of the tables for
the recordset and fields names which the combo list will update. Basically I
am trying to streammline the code within each form. For instance, I have a
form that has 5 combo boxes and each combo box has the above code for the
notinlist event.

I have had a bash with

Public Sub Getlist(rstType,NewFieldType as string)

Dim rst As DAO.Recordset

If MsgBox(NewData & "... not in list, do you want to add it?", vbOKCancel,
"DIS87") = vbOK Then
Set rst = CurrentDb.OpenRecordset(rstType)

With rst
.AddNew
.Fields(NewFieldType) = NewData
.Update
.CLOSE
End With
Set rst = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If


then would call it on the notinlist event

call getList

rstType ="Some Table"
NewfieldType ="Some table fieldname" field to be updated in the combo box

This does not work - i think I may be at least at the starting line here if
nothing else

Is this possible at all? or should I just resign myself to repeating the same
code over and over again for each combo. I hope this makes sense.

As ever any help or guidance is always much appreciated.
Cheers
Ceebaby
London

 

[Graham Mandeno]

Hi Ceebaby

Yes, you can do this, but you must call it from inside the combo box's own
NotInList procedure and you must pass the NewData and Response arguments to
it, as well as any other parameters such as the question to ask, the table
to update or the form to open to add a new record. It's a good idea to pass
the combo box as an argument as well.

Actually, the way I do it is not to pass the Response argument, but to
return it from the function, like this:

Public Function GlobalNotInList(NewData As String, _
cbo As ComboBox, _
... other args) As Integer
.....
GlobalNotInList = acDataErrAdded
End Function

Then:

Private Sub MyCombo_NotInList( NewData As string, Response as integer)
Response = GlobalNotInList( NewData, MyCombo, ... )
End Sub

 

[Albert D. Kallal]

Here is my global code:

In the combo after update, we go:

Private Sub cboVenuType_NotInList(NewData As String, Response As Integer)

Call comboAdd("not in Venue types list", "tblVenuType", "VenuType",
NewData, Response)

End Sub

The combo add code is:

Public Sub comboAdd(strPrompt As String, _
strTable As String, _
strField As String, _
strData As String, _
Response As Integer)

Dim strSql As String

If MsgBox(strData & " " & strPrompt & ", add?", _
vbYesNo + vbQuestion, "Add to list?") = vbYes Then
strSql = "insert into " & strTable & " (" & strField & ") values('" &
strData & "')"
CurrentDb.Execute strSql
Response = acDataErrAdded
End If

 

[Ceebaby via AccessMonster.com]

Thank you very much gentlemen for your suggestions and responses.

I have tried to work this out for ages.

This certainly makes for streamlined code.

Have a good day to both of you.

Kind regards
Ceebaby
London