reference syntax for public functions

Posted on 2002-05-11
Medium Priority
Last Modified: 2008-02-01
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

Question by:mjustman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
LVL 12

Expert Comment

ID: 7003390
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?

LVL 12

Expert Comment

ID: 7003395
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?
LVL 12

Expert Comment

ID: 7003439
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)

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 7003474

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

Expert Comment

ID: 7003504
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.


Author Comment

ID: 7003511
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.


LVL 12

Expert Comment

ID: 7003516
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...

LVL 12

Expert Comment

ID: 7003525
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.

Accepted Solution

Bob Scriver earned 300 total points
ID: 7003559
I am getting into the end of your question but here is a reference that may help your situation:


Bob Scriver

Author Comment

ID: 7003563
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.

LVL 12

Expert Comment

ID: 7003649

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

Author Comment

ID: 7004032
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

LVL 12

Expert Comment

ID: 7004121
i dont understand....

did u accept the correct answer?

Expert Comment

by:Bob Scriver
ID: 7004400
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

Author Comment

ID: 7004582
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
LVL 12

Expert Comment

ID: 7004989
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...


Author Comment

ID: 7006428

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question