reference syntax for public functions

I've got an app with LOTS of combo boxes, which all need not in list coding.  For most of them I want to
1-grab the newdata and throw it into a table using a recordset
2-then open a form on that table for addition of more data that the user probably has available at the same moment.
3-after he closes the form, requery and return him to his original forms with no error messages.

I can do all that, of course, locally. But I'd  like to build a generic notinlist function tha would work
-initially, for all combo boxes on one form, all tables
-ultimately, from a separate module for ALL forms, all tables.

I can't seem to get the references right to create the broader function. It's going to have to receive the tablename (to feed to the recordset definition), and the fieldname for the recordset to accept newdata into. And: as long as my function has "notinlist" in its name (?), it seems to know enough about what it's going to do to insist on having newdata and response passed to it...tho I won't have them to pass until the local not-in-list event occurs.

Did I make that clear? At least, as I'm figuring it, when the local/actual cbo box's  notinlist event occurs (so that code's gotta be there, and it'll have the newdata and response variables), I immediately want to call the public function, adding table name and fieldname ... and probably, finally, a formname open!)

Surely somebody's done this???????? either at the form or the global level.

For what it's worth, all data involved in the combo boxes is string data.


Marilyn Justman

Who is Participating?
Bob ScriverConnect With a Mentor Commented:
I am getting into the end of your question but here is a reference that may help your situation:

Bob Scriver
hi Marilyn,

seems to me u explained it all.
U did analyse it good, now u need to code it.

if u can code the 'local' stuf, u surely are able to code the global stuf.

Exactly what part do u need assistance with?

just a quick example of the sub (without the coding)

Public Sub psb_NotInList(strTable as String, varNewData as Variant, strFormToOpen as String, strCallingForm as String)

u could also use a function, and return a long
give the long a value if success, and another value if no success.
Locally u can then receive that long, when calling the function, and see if the function failed or was successfull.

this what u mean?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

btw, ofcourse u also need the fieldname...

Public Sub psb_NotInList(strTable as String, strField as string, varNewData as Variant, strFormToOpen as String, strCallingForm as String)

mjustmanAuthor Commented:

Why do I need calling form?

You totally missed the issue with the field/control to write in the recordset.  That changes, depending on the original cbo's source and its the syntax for that, both in the statement that CALLS the function, AND in the "rst!fieldname =" statement in the function itself that's choking the compiler.  Does that variable have to be a string, and should it be double-or-triple-quoted, so that it's still the same text when it's finally evaluated for execution?

Have you got a sample that you've built?  I can't find one in any of my books/CDs?


Marilyn Justman
hi Marilyn

the 'strCallingForm': u need this one b/c u will be opening another form to let the user input data.
When this form is closed u need to know what form to return to. Let access itself not take care of that, u might see some strange results... another way, without passing the name of the calling form, is to use an API to retrieve what window had last focus. This can be build into the sub/function.

If u pass the name of the table, then i suggest u also pass the name of the field into which data needs to be appended. I would not pass "rst!fieldname=", since u will be appending using a sql-statement (or even open a recordset-object) in the sub/function.

Once the function knows the tablename and fieldname, the  fieldtype can be retrieved from the tabledef-object, so its quite simple to produce the proper syntax for the appending.

i have never build anything like this, b/c i never had the need to do this.
When i let my user select from a combobox, or listbox, and the item is not there, i provide a button next to the combo/listbox that will open a form into which the user can input the new data.
On opening this form u 'tell' this will appendonly, and pass openingargs, so the closing button knows what form to 'reopen' on closing.

What u want to create can be done, but i did not think about it very deeply, but i assume there might be several things that go wrong, so it is imperative to trap any error that might occur whithout scaring the user away from the pc.

mjustmanAuthor Commented:
I've got it making the rst, but it chokes on executing the line:

rst!strfieldtochange = varnewdata

Does some of that need to be quoted (double-quoted?)?, &'d?  etc.


u need to know what data is passed ...
u either retrieve the fieldtype of the field in the table and use the convert-functions (i would use this scenario)

or test for several types
if IsNumeric(varnewdata) then
   varnewdata = CInt(varnewdata)  <--> but, it might be long also....

if isdata(varnewdata) th...

last one should be 'if isdate...)

or u could add another variable in which u define the data-type that is passed.

strDType as string

and would be passed like this:
Public Sub psb_NotInList(strTable as String, strField as string, varNewData as Variant, strDType as string,  strFormToOpen
as String, strCallingForm as String)

psg_NotInList "YourTableName", "Field1", "newdata", "NUMERIC", "form1", "form2"

in your sub u can then use a Select Case to build your sql-string.
mjustmanAuthor Commented:
That's all very interesting, but not to the point.

At this juncture, I have another function which actually constructs the correct line, but calling that function within my initial notinlist function  doesn't make it executable.  Here's the entire code (with a bunch of bad tries still in place):

Option Compare Database
Option Explicit
Public varnewdata As Variant
Public strfieldtochange As String
Public varrstline

Private Sub name_NotInList(NewData As String, Response As Integer)
       ' MsgBox "calling function"
        mjnotinlist "tblmjtest", NewData, "name"
       ' Response = acDataErrContinue
End Sub

Public Function rstline(strfieldtochange, varnewdata)
        rstline = ("rst!" & strfieldtochange & " = """ & varnewdata & """")
        varrstline = rstline
        Debug.Print rstline, varrstline
End Function

Public Function mjnotinlist(strTable As String, varnewdata As Variant, strfieldtochange As String)
    Const quote = """"
    Dim db As Database
    Dim rst As Recordset
    Dim rstline As String

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strTable, dbOpenDynaset)
       MsgBox "made rst"
       Debug.Print "init reccount = ", rst.RecordCount
      ' varrstline
      ' Call rstline(strfieldtochange, varnewdata)
  '    rst!name = varnewdata
  '    Debug.Print rst!name
      Dim varfield As Variant
      varfield = strfieldtochange
      Debug.Print varfield
       rst!varfield = varnewdata
            Debug.Print rst!varfield.Value
            Debug.Print rst!name
            Debug.Print rst!varfield.Value
            Debug.Print rst.RecordCount
    Set rst = Nothing
    DoCmd.OpenForm "tblmjtest", acNormal, "[tblmjtest].[name] = " & varnewdata
End Function

I'm still hanging, and it's still on the fieldname.


here is an example i rapidly put together.

In the form where the combobox is:
Private Sub cboYourBox_NotInList(NewData As String, Response As Integer)
    'MsgBox "calling function"
    psb_NotInList "tbl1", "Field1", NewData, "frmEnterData", Me.Name
    Response = acDataErrContinue

End Sub

In a new module:
Public Sub psb_NotInList(strTable As String, strField As String, varNewData As Variant, strFormToOpen As String, strCallingForm As String)
Dim dtType As Integer

'First we get the datatype for the field
dtType = RetType(strField, strTable)

'Now we now the datatype, we can build our string, AND insert the data
Select Case dtType
    Case 4, 5 'NUMERIC
        CurrentDb.Execute "Insert Into " & strTable & " (" & strField & ") Values (" & varNewData & ");"
    Case 8 'DATE
        CurrentDb.Execute "Insert Into " & strTable & " (" & strField & ") Values (#" & varNewData & "#);"
    Case 10 'TEXT
        CurrentDb.Execute "Insert Into " & strTable & " (" & strField & ") Values ('" & varNewData & "');"
End Select
    'The record has been added, now opening the form
    DoCmd.OpenForm strFormToOpen, acNormal, strField = varNewData, , , , strCallingForm

End Sub

'This function will return the datatype
Function RetType(strFieldName As String, strTableName As String)

    RetType = CurrentDb.TableDefs(strTableName).Fields(strFieldName).Type

End Function

in the form where the data is entered:
Dim strArgs As String

Private Sub cmdClose_Click()
    If Me.OpenArgs <> "" Then
        strArgs = Me.OpenArgs
        DoCmd.OpenForm strArgs
    End If
End Sub
mjustmanAuthor Commented:
Yes, this citation and some book research finally did it. Since my problem was a little more complicated than that one, but he solved problems I forgot to ask about, I'll also post mine code here.

Note that the problem is all about preserving (or recreating) the quotes that have to be around string variables through their various evaluations.  In my book, answers were under "Using Variables in Strings".  

In this code, there's some stuff that's UNnecessary.  It's there because (blush) I kept writing acdataerrcontinue instead of acdataerradded !

Here's the code:

Option Compare Database
Option Explicit

Public strfieldtochange As String    
Public no As Integer    'probably NOT nec

Private Sub name_NotInList(newdata As String, response As Integer)
      no = 0   'this and the if loop prob'ly NOT nec.
       If no > 0 Then
            response = acDataErrAdded
            Exit Sub
            mjnotinlist "tblmjtest", "name", newdata, "name"
            response = acDataErrAdded
       End If
 End Sub

Function mjnotinlist(strTable, strfieldtochange, newdata, response)
    Const quote = """"   'there are other solutions. I liked this one, as being clearest. Note strings below
    Dim CtlCurrentControl As control
    Set CtlCurrentControl = Screen.ActiveControl
    Dim msg As String
    msg = "Not in list. Want to add (Y/N) ?"
    Dim SSQL As String
    SSQL = "insert into " & strTable & " (" & strfieldtochange & ") VALUES (" & quote & newdata & quote & "); "
            Debug.Print SSQL
    If MsgBox(msg, vbYesNo) = vbYes Then
        response = acDataErrAdded
        CurrentDb.Execute (SSQL)
        no = no + 1  
        Debug.Print "no = "; no
        DoCmd.OpenForm "ftblmjtest", acNormal, , "name = " & quote & newdata & quote
    End If
End Function

i dont understand....

did u accept the correct answer?
Bob ScriverCommented:
I was a little surprised with this one also.  I just threw in a link to passing parameters to a module from a NotInList situation.  There certainly was a lot of extra effort by you guys on this one.  I didn't expect that to be the answer.  MJ should review her actions to see what really helped her find a solution to her Question.

Bob Scriver
mjustmanAuthor Commented:
The citation provided by scriverb provided a nice, concise model.  I stole his idea of using a SQL statement, and got rid of lines of rst-related code, including the ugly one where the target fieldname has to be plopped into an assignment.  But I didn't like his  mixed-single-and-double-quote solution to the most perplexing problem (because Access "ripsoff" quotes when it evaluates  strings, even (?) when passed byref, you essentially have to restore those quotes to the final strings where it needs them... AND it won't compile odd numbers of double-quotes)  -- so I used my book's  idea of setting a constant to 4 doublequotes, and using that explicitly.  It shows me what the problem was, while solving it.  Thus, I can isolate THOSE quotes problems from any OTHER quotes problems I happen to have.

His code is simpler because he's always adding his newdata to the same fields in the same table -- I wanted mine to be so generic that it would serve for all fields in all tables that I might (and do) need it for, throughout the app.  

It turned out that I didn't need any of those if statements, or the no counter.  That was 'cuz I made the stupid error I told you about earlier.  Stripped, the code for the local cbo's notinlist event boils down to the call to the global module ... I don't think I need the response there, either...I found another typo (it's in the code I posted)

It also turned out that, because a) in many cases, the  controlname in the editing form is NOT identical to the fieldname in the table, I'd have had to  add yet another parameter to the  function to get it to open the forms correctly, and b) I already had the docmd.openform line in all but one of the cbo box notinlist events, I just took that part OUT of the global module altogether and restored it to the calling code. Makes a total of 2 lines.

But I also finally  DID make the constant global (dimensioned at the top of  "module1").  That way, I can use it throughout, including in those OpenForm lines, with newdata.

Hope this helps you, and that the code is of  value to someone further down the line.

Marilyn Justman
lol, lol , lol

ok, i now realise it is very hard, for some, to even begin to understand the purpose of a dll.

B/C, Marilyn, part of what u are asking, or asked for, is a dll.
Some function where u pass the proper arguments, and let the dll do the work for u.
This means u, once, go extreme lenghts, but in the future it pays of...

The functions/subs i provided will deal with this,
i am sorry u do not yet fully see the thoughts and work that went into this,

i spent quite some time, relatively, producing the code i provided,

and, i see u used some of it,
only to see my effort NOT rewarded....
what can i say....

alto' i am glad Bob does see it...

mjustmanAuthor Commented:

I'm sorry, but I am not at that level where I can even contemplate writing dlls; if I were, I would still only consider such a move if it were necessary, as opposed to convenient.

Access does what I needed to do ... as you saw, with a minimal bit of coding.

I appreciate your efforts.

However, if you had done the research  thru the EE archives that Bob did in the first place, you would have produced my answer immediately, and with no other creative work on your part.

Thanks again.

Marilyn Justman
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.