Solved

reference syntax for public functions

Posted on 2002-05-11
17
361 Views
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.

Thanks!

Marilyn Justman

0
Comment
Question by:mjustman
  • 9
  • 6
  • 2
17 Comments
 
LVL 12

Expert Comment

by:Paurths
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?

cheers
Ricky
0
 
LVL 12

Expert Comment

by:Paurths
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?
0
 
LVL 12

Expert Comment

by:Paurths
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)


0
 

Author Comment

by:mjustman
ID: 7003474
Paruths:

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?

thanks.

Marilyn Justman
0
 
LVL 12

Expert Comment

by:Paurths
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.

cheers
Ricky
0
 

Author Comment

by:mjustman
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.

Thanks.

mj
0
 
LVL 12

Expert Comment

by:Paurths
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...


0
 
LVL 12

Expert Comment

by:Paurths
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 3

Accepted Solution

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

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20297775

Bob Scriver
0
 

Author Comment

by:mjustman
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"
       rst.MoveLast
       Debug.Print "init reccount = ", rst.RecordCount
        rst.MoveLast
        rst.AddNew
      ' 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
        rst.Update
            Debug.Print rst.RecordCount
        rst.Close
    Set rst = Nothing
    DoCmd.OpenForm "tblmjtest", acNormal, "[tblmjtest].[name] = " & varnewdata
End Function


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

Marilyn
0
 
LVL 12

Expert Comment

by:Paurths
ID: 7003649
hi,

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.Close
        DoCmd.OpenForm strArgs
    Else
        DoCmd.Close
    End If
End Sub
0
 

Author Comment

by:mjustman
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
       Else
            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)
        CtlCurrentControl.Undo
        CtlCurrentControl.Requery
        no = no + 1  
        Debug.Print "no = "; no
        DoCmd.OpenForm "ftblmjtest", acNormal, , "name = " & quote & newdata & quote
    End If
End Function

0
 
LVL 12

Expert Comment

by:Paurths
ID: 7004121
i dont understand....
?

did u accept the correct answer?
0
 
LVL 3

Expert Comment

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

Author Comment

by:mjustman
ID: 7004582
Guys:  
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
0
 
LVL 12

Expert Comment

by:Paurths
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...

cheers
Ricky
0
 

Author Comment

by:mjustman
ID: 7006428
Ricky:

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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now