UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Recordset ! Fieldname, Access 2010    
 
   
Pastor Del
post Oct 25 2017, 05:57 PM
Post#1



Posts: 329
Joined: 27-July 10



I need to replace all commas with semi-colons in a field the user enters in txtFieldName on a form. The code I've put together to do this works fine except for this line.
CODE
!strFieldName = strComma
I get an error "Item is not found in collection". Replacing this line with ![Mfr PN] = strComma works. How can i replace ![Mfr PN] with a variable?

CODE
Private Sub Command0_Click()

    If (Me.txtTableName <> "" Or Not IsNull(Me.txtTableName)) And Me.txtFieldName <> "" Or Not IsNull(Me.txtFieldName) Then
        
        'Load variables
        strTableName = Me.txtTableName
        strFieldName = Me.txtFieldName
        
    Else
        
        'User notification
        MsgBox "Enter name of table & field to be modified"
                
        'Exit
        Exit Sub
    
    End If
        
    'User notification
    If MsgBox("All commas in the selected field will be replaced with semi-colons.", vbOKCancel) = vbOK Then
        
        'Iterate through records
        With CurrentDb.OpenRecordset(strTableName)
            .MoveLast
            .MoveFirst
                Do
                    .Edit
                    
                    'Load variables
                    strRefDes = ![Ref Des]
                    strComma = Nz(DLookup("[" & strFieldName & "]", strTableName, ![Ref Des] = strRefDes))
                    
                    'Step through strComma
                    For intCount = 1 To Len(strComma)
                        
                        'Look for comma
                        If Mid(strComma, intCount, 1) = "," Then
                            
                            'Replace comma with semi-colon
                            strComma = (Left(strComma, intCount - 1)) & ";" & _
                                              (Right(strComma, Len(strComma) - intCount))
                                                    
                        End If
                    Next
                    
                    'Store result
                    !strFieldName = strComma
                    
                    .Update
                    
                    .MoveNext
                        
                Loop Until .EOF
        End With
    End If
End Sub
Go to the top of the page
 
cheekybuddha
post Oct 25 2017, 06:53 PM
Post#2


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


CODE
  Dim strField As String
' ...
  strField = YourFieldName
' ...
  .Fields(strFiledName) = strComma
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
moke123
post Oct 25 2017, 06:57 PM
Post#3



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



why not just use replace()?
Replace function
Go to the top of the page
 
cheekybuddha
post Oct 25 2017, 07:00 PM
Post#4


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Though it's much easier to replace your whole code with simply:
CODE
  Dim strSQL As String

  If Len(Me.txtTableName & vbnullString) > 0 AND Len(Me.txtFieldName & vbNullString) > 0 Then
    strSQL = "UPDATE " & Me.txtTableName & " SET " & Me.txtFieldName  & " = REPLACE(" & Me.txtFieldName & ", ',', ';');"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
  End If


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Pastor Del
post Oct 25 2017, 10:06 PM
Post#5



Posts: 329
Joined: 27-July 10



I appreciate all 3 of these responses. I can learn from each. Thanks.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 03:38 PM