[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

How do I refer to the columns in an MSAccess continuous form?

MSAccess:  I have a form that lists columns from a table in a continuous form:
field1   field2   field3   field4   field5 ....  (can display up to 10000 records)
The user can request that a string contained in field2 be changed to "CCDD" and display the new string in field3 before actually committing it to the db via a cmdSubmit button.  My question is this:  I developed a pop up to enable the user to note the old and new string.  They close the popup via an OK button.  I then pass the old and new string back to the main form.  Please forgive me if this is a dumb question but how do I go through all the records on the form, look for the string in field2 and display the proposed change in field3.  I know how to do it in VB with a grid but I've never done it in an MSAccess form.  Thank you in advance.
0
CaroleTSullivan
Asked:
CaroleTSullivan
  • 4
  • 4
  • 4
2 Solutions
 
peter57rCommented:
You can't do this in Access in the way I think you expect to.
To display old and new values side by side the table that the form is based on must have both fields defined.
You can then run an update query to update field3 with the new value for all records where field2 matches the given 'old' value and refresh the form to display those updates.  But you will be updating the records (in field3) , not showing what the update will be.
You can then 'commit' the changes (to field2) by another update query and update field2 with field3 value. You will then, I assume, need a final update query to delete the values in field3.
0
 
CaroleTSullivanAuthor Commented:
peter57r:  But isn't there something that simulates selecting the record one at a time, checking the value to see if field2 is equal to the old and then setting field3 to the new value?  There must be because it would be like selecting the record, typing the new value in field3 and then on cmdOK_Click, actually commit the changes?  I know it would have to be through VBA code but I'm just wondering how do I traverse down the "grid" on the form.
0
 
peter57rCommented:
If you want it work one record at a time you might as well use Find & Replace from the menu.
That has all the functionality built-in.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ZuZuPetalsCommented:
I'm not sure how you could do this in a one-by-one fashion, but how about this...

In your form, can you have an unbound field that displays a value that is calculated?  Something like =IIF([Field2]="String you wanted to change", "CCDD",Null)... then you could filter the form to show only those records that have values in this unbound field, allowing you to review all the potential changes at once.  Then write them to Field3 when the user clicks, ok?
0
 
CaroleTSullivanAuthor Commented:
ZuZuPetals:  That is exactly what I want to do.  My question is this...how in VBA do I process all the records on the form?  What is the VBA syntax.  Is it something like...docmd.find........I just need someone to tell me how to do it.  I know in regular VB, the data is in a grid which I can process in a do loop.  Does MSAccess have the same capability when the data is displayed via a continuous form.  Is the form really a big data grid.  If so, how do I use it using VBA.....

Peter57r:  I was considering your approach too......find, filter, and then process and display the potential changes in field3.  But again.....how do I code that?  I tried doing exactly that and got syntax errors.  
0
 
peter57rCommented:
So you're not happy about using the built-in 'find and replace' then - that is all you are doing by the sound of it and as I said before it has the precise functionality you are describing.

it is possible to programmatically navigate your way through records in a form but it is not something that is commonly done in Access because of the built-in find/filter features.

0
 
peter57rCommented:
The other comment I have is that if the user wants to inspect/evaluate changes to records where field2="X" then really you should only be showing the user those records and no others. It just invites errors if you put other records on the screen as well.

0
 
ZuZuPetalsCommented:
Well, broadly speaking, there are several approaches to this depending on how you want the user interaction to be.  You could use a recordset object to iterate through records one at a time, but nothing you've said so far makes me believe you'd actually want to do it that way.

I can go into more details later tonight... not able to this minute...

A couple questions though... the value that you'd potentially be replacing with "CCDD" is it always the same?  Or does it differ from record to record?  Will you be soliciting the value to find from the user?  And in your current form, what is the underlaying recordset object?  Is it a query or a table?  If it is a table, make a query based on the table and bind the form to the query.  (Later, we'll put an expression field in the query and display it in the form (this will be the "preview" for the change that we will then write upon user approval)).  More later... (sorry)...
0
 
ZuZuPetalsCommented:
Sorry, just re-read and see that you are soliciting the value to find and the value to replace it with.  Disregard that part of my last post...
0
 
CaroleTSullivanAuthor Commented:
ZuZuPetals and Peter57r:  I appreciate what both of you are saying.  

Peter57r:  I do like the find and replace except I need to display the replace value BEFORE committing the changes to the database.  The nice thing about the replace is that the old/new string can be inbedded in the entire value and only the string needs to be changed.  I was looking for an easy way to display the proposed new value in field3.  

ZuZuPetals:  You are right on, but then again, you are confirming that there really is no easy way except by coding and that I can easily figure out.

thank you both again for your help and ideas!  Take care!
0
 
ZuZuPetalsCommented:
BTW, if you end up doing this via VBA, there doesn't seem to be a REPLACE function that can target just a whole word.  (so if replacing the word "the", the word therapy would also be altered).  It exists with Find / Replace from the GUI, but not from VBA.  (If I'm wrong on this, someone please chime in!).

So, you'll likely need to make a custom function for this.  I built this one earlier today and tested it (lightly) and it seems to do the trick.  It is case sensative and will consider "The," and "!The", to be the whole word "The", but nothing with another letter on either side (modify at will).

Here is the code if you'd like it:

Function ReplaceWord(ByVal Target As String, ByVal SearchWord As String, ByVal Replacement As String) As String
Dim I As Long
Dim T As Long
   
    I = 1
    T = 0
   
    'If Target is a single word that matches SearchWord.
    If SearchWord = Target Then
        'No replacement occurs.
        Target = Target
    Else
   
        'Loop through the Target string until all instances of the SearchWord have been found an replaced.
        Do Until InStr(I, Target, SearchWord, vbBinaryCompare) = 0
           
            'Target starts with SearchWord.
            If InStr(1, Target, SearchWord) = 1 Then
               
                'Check if following character qualifies.
                If Mid(Target, Len(SearchWord) + 1, 1) Like "[!a-zA-Z]" Then
                    'Replace the first word, update search start position and continue.
                    Target = Replacement & Mid(Target, Len(SearchWord) + 1, Len(Target))
                    I = Len(Replacement) + 2
                Else
                    'Update search start position and continue.
                    I = Len(SearchWord) + 2
                End If
               
            'Target ends with SearchWord.
            ElseIf InStr(I, Target, SearchWord, vbBinaryCompare) = Len(Target) - (Len(SearchWord) - 1) Then
                Target = Left(Target, InStr(I, Target, SearchWord, vbBinaryCompare) - 1) & _
                         Replace(Right(Target, Len(SearchWord)), SearchWord, Replacement, , , vbBinaryCompare)
                Exit Do
           
            'SearchWord is in the middle of the Target.
            Else
               
                'Check if preceeding and following characters qualify.
                If Mid(Target, InStr(I, Target, SearchWord, vbBinaryCompare) - 1, 1) Like "[!a-zA-Z]" And _
                   Mid(Target, InStr(I, Target, SearchWord, vbBinaryCompare) + Len(SearchWord), 1) Like "[!a-zA-Z]" Then
                   
                    'Update a temporary variable that will be used to update I.
                    T = InStr(I, Target, SearchWord, vbBinaryCompare)
                   
                    'Replace the middle word, update search start position and continue.
                    Target = Left(Target, InStr(I, Target, SearchWord, vbBinaryCompare) - 1) & _
                             Replace(Mid(Target, InStr(I, Target, SearchWord, vbBinaryCompare), Len(Target)), SearchWord, Replacement, , 1, vbBinaryCompare)
                    I = T + Len(SearchWord)
                Else
                    'Update search start position and continue.
                    I = InStr(I, Target, SearchWord, vbBinaryCompare) + Len(SearchWord) + 1
                End If
               
            End If
        Loop
    End If
   
    ReplaceWord = Target
   
End Function
0
 
CaroleTSullivanAuthor Commented:
ZuZuPetals:  Thank you.  Looks good!  This is what I decided to do and our code looks very similar.  Take care.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now