Solved module variables

[ClaraBarton]

I've had this code in my samples for a long time and it works great in a form module. Since I'd like to use it in several places I tried to make a module and now I can't get it to work. I can't get past line 10. I think the error may be frm. Is this the proper way to return a form name? ... EditCombo Me ...?

Private Sub cboUserName_DblClick(Cancel As Integer)
    EditCombo Me, "cboUserName", "UserName", "qryNames", "popNameInfo"
End Sub

Public Sub EditCombo(frm As Form, strCombo As String, strItem As String, _
        strDomain As String, strForm As String)

      Dim lngcbo As Long
      Dim strOpenArgs As String
                  'Assign Current Text held in cboUserName to string variable to be append to the OpenArgs
                  'If DCount("UserName", "qryNames", "UserName = '" & frm.cboUserName.Text & "'") = 0 Then strOpenArgs = frm.cboUserName.Text
10        If DCount(strItem, strDomain, strItem = frm.strCombo.Text) = 0 Then
20            strOpenArgs = "frm.strCombo.Text"
30        End If

 

[theDBguy]

Maybe try:

strItem & "='" & frm(strCombo).Text & "'"

 

[cheekybuddha]

Line 20 will likely need to be:

' ...
20            strOpenArgs = frm(strCombo).Text
' ...

 

[ClaraBarton]

It works! Always so amazing how much I don't know. Thank you both so much!

 

[cheekybuddha]

You don't show your whole code.

You could probably simplify your sub by passing the actual combobox object and getting rid of the form parameter altogether.

But perhaps you use frm further on in the code?

 

[ClaraBarton]

Option Compare Database
Option Explicit
Public lngInfoXchg As Integer
'ComboBoxUpdate in Access Samples
'frm = This Form (me)
'strCombo = This Combo (cboUserName)
'strItem = the text you typed in the combo
'strDomain = the table or query behind the combo
'strForm = the form used to add to the table or query
Public Sub EditCombo(frm As Form, strCombo As String, strItem As String, _
        strDomain As String, strForm As String)

      Dim lngcbo As Long
      Dim strOpenArgs As String
                  'Assign Current Text held in cboUserName to string variable to be append to the OpenArgs
10        If DCount(strItem, strDomain, strItem & "='" & frm(strCombo).Text & "'") = 0 Then   'strItem & "='" & frm(strCombo).Text & "'"
20            strOpenArgs = frm(strCombo).Text
30        End If
                  'The following code is the standard code used in many Access Templates to allow the addition
                  'of new records to the table feeding a combo box
                  'set the combo to a Zero Length Sting (ZLS) if it currently holds a Null Value
40            If IsNull(frm(strCombo)) Then
50                frm(strCombo).Text = ""
60            Else
                  'Records current value and set combo value to null
70                lngcbo = frm(strCombo)
80                frm(strCombo) = Null
90            End If
          
                  'Open the form through which new data will be added to the underlying table that feeds the ComboBox
                  'The OpenArgs portion of the DoCmd.OpenForm is used to pass the command to send the form to a New
                  'Record along with the New Record that is to be added to that table. The "#" character is used
                  'to denote the end of the instruction to move to the new record and the data. It is used in the
                  'On Load event of the from being called to idtentify if there is data being passed to
                  'it via the openArgs. If your data routinely ends with "#" it should be replaced with any other
                  'character that does not regularly terminate your data
                  'this change will need to be reflected in the On Load event of the form being called.
                  'NOTE: the form must be opened in acDialog mode to pause this code until the data entry form is closed
100           DoCmd.OpenForm strForm, , , , , acDialog, "New#" & strOpenArgs
                  
                  'Force the combo to requery once the form being used to add the new data has closed
110           frm(strCombo).Requery
              
                  'Check if a value has been previously recorded and return that value to the Combo if it has
120           If lngcbo <> 0 Then
130               frm(strCombo) = lngcbo
                  'Passing last value created on form popNameInfo
140           ElseIf lngInfoXchg <> 0 Then
150               frm(strCombo) = lngInfoXchg
160           End If
          
End Sub

I'm all for simplifying. And open for suggestions.

 

[Gasman]

Why don't you just open the form in add mode?

 

[ClaraBarton]

Because I don't know the text isn't there until it's been typed in... but I could... what would it save me?

 

[Josef P.]

Note:
EditCombo Me, "cboUserName", "UserName", "qryNames", "popNameInfo"

10        If DCount(strItem, strDomain, strItem & "='" & frm(strCombo).Text & "'") = 0 Then   'strItem & "='" & frm(strCombo).Text & "'"
20            strOpenArgs = frm(strCombo).Text
30        End If

Hopefully nobody has a username like that: abc' Or 'a'='a

=> strItem & "='" & replace(frm(strCombo).Text,"'", "''") & "'"

 

[The_Doc_Man]

It's hard to tell sometimes when looking at a pile of code, but this comment occurs because you are referring to the .Text property of a control. Be aware that .Text is not usable except while the control in question has focus. Once your control loses focus, .Text is not available. It acts like it ceases to exist, i.e. not a valid property. Since Access is not an Open Source product, we don't know where it actually goes, but its lifetime is limited.