Does MS Access have something similar to Excel's application.selection ?

I often do something like this:

I put Excel sourceSheet  cells A1:c5 onto clipboard, then paste them to the bottom of MS Access table tTarget
After the paste operation, MS Access highlights the 5 new rows at the bottom of the table.

I want to write a generic Access macro that will "fix" those 5 highlighted rows.

excel and word have application.selection
Outlook has activeexplorer.selection and activeinspector.selection.

Does MS Access 2010 have something similar that would let me manipulate the 5 rows that are selected?

Something like this:

sub FixExcelLineFeedsInAccess
   with application.ActiveTable.SelectedRows
      for each row in .rows
         for each field in row.fields
                 if instr(field,"$p") > 0 then
                      field = replace(field,"$p",vbcrlf)
                end if
 next
 next
end with
LVL 5
rberkeConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
Access is a relational database as I expect you to know.
A table has a primary key, and copying few records and pasting will not work for duplicate values.

Copying may be done according to certain criteria the copied records can be included in recordset and processed as required.
0
John TsioumprisSoftware & Systems EngineerCommented:
You can solve this problems with a lot of ways
here is one
Public Function SubStitute()
On Error Resume Next
    Dim rst As DAO.Recordset
    Dim fld As Field
    Set rst = CurrentDb.OpenRecordset("YourTable")
    With rst
        While Not .EOF
            
            For Each fld In .Fields
            .Edit
                fld = Replace(Nz(fld, ""), "$p",vbcrlf)
            .Update
            Next
            
            .MoveNext
        Wend
    End With
    Set rst = Nothing
End Function

Open in new window


The issue however as pointed by @hnasr is that you using Access and you are thinking Excel
Excel is a spreadsheet, Access is a database ...there is a huge gap between them as they are a total different  products with totally different philosophy.
To put it differently is like having a super sports car with hundreds of HPs and your putting stabilizers....
0
Gustav BrockCIOCommented:
That's quite easy using the selected records:

Function CleanSelectedRecords()
    Dim i As Long
    Dim F As Form
    Dim RS As Recordset

    ' Get the form and its recordset.
    Set F = Forms![Customers1]
    Set RS = F.RecordsetClone

    ' Move to the first record in the recordset.
    RS.MoveFirst

    ' Move to the first selected record.
    RS.Move F.SelTop - 1

    ' Enumerate the list of selected records presenting
    ' the CompanyName field in a message box.
    For i = 1 To F.SelHeight

        For Each Field In RS.Fields
            If InStr(Field.Value, "$p") > 0 Then
                RS.Edit 
                    Field.Value = Replace(Field.Value, "$p", vbCrLf)
                RS.Update
            End If
        Next

        RS.MoveNext
    Next

End Function

Open in new window

Of course, if you have many fields, the code can be optimised to do one update per record only.


Source: How to enumerate selected form records in Access
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
No, access doesn't really have the same info. You can get close to it, but its not a perfect solution. Its the kind I would say can work if its just you using it, and you know the limitations.
When you are opening a table directly, access is actually creating an using a form object for you. You can get that form, and use the selHeight to access how many records are selected. The code looks something like this:
   'Get a refeference to the active form.
   'Note: For some reason screen.activeForm fails
      Dim f As Form
      Set f = Screen.ActiveControl.Parent
   
   'Get a reference to the recordset
      Dim rs As Recordset
      Set rs = f.Recordset
   
   'Loop through the selection, updating records
   Debug.Print f.SelHeight
      Dim i As Long
      Dim records As Long
      records = f.SelHeight
      Do Until i = records
         With rs
            Debug.Print !someID
            .Edit
            !someText = "o"
            .Update
            .MoveNext
         End With
         i = i + 1
      Loop
      

Open in new window

Now one of the pitfalls I've discovered so far is that if you select the bottom record first, then drag up to select the rest it fails. The selection has to start with the top record you wanted selected. There might be other issues, its not really a "recommend approach".

Another more fool proof way of doing it, would be to create a form for that purpose, and set it to DataEntry=True. That way only new records can be added, so that when you paste, the records are "the only" records in the set. You might even code your logic in the beforeUpdate event. I'm not 100% sure how that fires when doing multiple inserts through copy-paste, but it seems worth a try.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
As hnasr mentioned in his initial comment, Access is a relational database and they work quite differently from a spreadsheet.  The Access interface does provide limited ability to do "cut and paste" operations but they are not intelligent and you may run int problems.  You would have fewer problems if you could use criteria to select a set of records (rather than point and click) and operate on the set using SQL.  The process sounds like it is needed because the table is not properly normalized.
0
Fabrice LambertFabrice LambertCommented:
Lord ! No no no !!

Selection object is already a mean troublemaker within MS Office products, we don't need it within MS Access
0
rberkeConsultantAuthor Commented:
Thank goodness someone understand my goal. The following does exactly what I want. As far as I can see there are no pitfalls.  Ideally I must remember to run it immediately after the paste.
If I forget, then I must select the records one at a time and run it.


Option Compare Database

Sub FixLastPaste()

' When copying records from excel to access this routine changes $p  to vbcrlf.

' Use it as follows:

' 1)  Select the excel rows you wish to copy.
' 2)  Run this excel macro  ExcelToAccess
'   Sub ExcelToAccess()
'       Dim S, ROW As Range, CELL As Range
'       For Each ROW In Selection.Rows
'       For Each CELL In ROW.Cells
'           S = S & Replace(CELL, chr(10), "$P$") & vbTab
'       Next
'       S = S & vbCrLf
'       Next
'       SetClipboard (S)  ' use your favorite software to set clipboard. Do NOT use dataobject which is unreliable.
'   End Sub


' 3) Switch back to access and select the empty row at bottom of desired table.
' 4) run the FixLastPaste macro.  (I use the vba immediate window, to run it, but others might prefer other methods.)



' if any field in the recently pasted text contains  "$p", it will become vbcrlf.


  'Get a refeference to the active form.
   'Note: For some reason screen.activeForm fails
      Dim f As Form
      Set f = Screen.ActiveControl.parent
   
   'Get a reference to the recordset
      Dim rs As Recordset
      Set rs = f.Recordset
   
   'Loop through the selection, updating records
   Debug.Print f.SelHeight
      Dim i As Long
      Dim records As Long
      records = f.SelHeight
      Do Until i = records
         With rs
            'Debug.Print !someID
            .Edit
            changed = False
            For Each fld In rs.Fields
                
                oldtext = rs(fld.Name)
                
                newtext = Replace(oldtext, "$p", vbCrLf)
                
                If oldtext <> newnext Then
                    changed = True
                    rs(fld.Name) = newtext
                End If
            Next
            If changed Then
                .Update
            End If
            .MoveNext
         End With
         i = i + 1
      Loop
      
End Sub

Open in new window

1
rberkeConsultantAuthor Commented:
The other posted solutions would work, but they were not generic.  They would need to be modified depending on which table was pasted.

The Anders Ebro solution is perfect and never needs to be modified.
0
rberkeConsultantAuthor Commented:
WARNING for future researchers.
My  ExcelToAccess macro puts cell. value onto the clipboard.  You might want to change it to be cell.value2, or maybe cell.text.

The difference is subtle.
If a cell contains 1,234.567890 and is formatted as currency with 8 decimal places then
cell.text has $1,234.567890
cell.value has 1234.56789
cell.value2 has 1234.5679    ("Currency" is automatically truncated to 4 decimal points in many Excel processes.)

cell. value is 1.2346 if the cell is formatted as currency.  routine would put 1.2346 on the clipboard
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Interesting. I never knew about that distinction.  Guess I will have to read up on Value2
0
rberkeConsultantAuthor Commented:
<<Guess I will have to read up on Value2 ""

Good luck with that. It can be confusing.  Excel always treats numeric cells as double precision while  vba unqualified references to  a cell  will default to cell.value  which rounds currency cells to 4 digits in the mantissa.

For instance put =1/3 in cells a1:a10 and sum(a1:a10) in a11.  a11 now has 3.33333333  which proves that speadsheets usually ignore the rule that currency is supposed to be limited to 4 digits in the mantissa.

But, create the following vba program shows that vba honors the currency rules.
Dim s As Double
For Each cell In [A1:A10].Cells
s = s + cell
Debug.Print s
Next
MsgBox s  & application.sum([a1:a11]) ' S contains 3.3330


So, if you want vba to be consistent with spreadsheets, you should always code < s + cell.value2 > instead of the "prettier"  < s + cell >.

As a good programmer I should always use cell.value2.  But, as a lazy programmer I usually use s + cell.  For my kind of programming, 99.9% of the time it doesn't matter. Thank goodness I am not a structural engineer !!
0
Gustav BrockCIOCommented:
It isn't that difficult. Value returns the formatted (displayed) value, while Value2 returns the "raw" data.

The discussion on when an how Excel should round numbers is old and never-ending.
If you need to sum the rounded values, do round them first, then sum the rounded values.
0
rberkeConsultantAuthor Commented:
No, it is not difficult, but it is easy to get it wrong.  I often get things wrong myself, so I am not criticizing Gustav here: Nonetheless, he  just now got it wrong.

Actually  .text returns a string with the formatted displayed value.

 .Value returns the CONVERTED raw data. It is automatically converted to a specific type depending on the cell's formatting.
So, typename(selection.value) might return one of the following.
1 Date  (when cell is formatted as Date or Time AND the cell contains a number)
2 Currency (when cell is formatted as Currency or Accounting AND the cell contains a number)
3 Double if cell contains a number other than Currency or Date
4 empty if cell is really empty.  (But a zero length string will not return empty.  They often occur when pasting internet values into excel)
5 String if none of the above

.value2 is much simpler, numbers always get turned into Double (Never currency or Date.)

  --I often get things wrong myself.  For instance today I was totally surprised that Accounting format returns Currency. I always thought it returned Double.  We all learn something every day.

rberke
0
Gustav BrockCIOCommented:
Forgot about Text for a moment …
If you enter 0.33333 and apply format of Currency (four decimals max):

Value2: 0.33333  as vbDouble
Value : 0.3333   as vbCurrency
Text  : 0,33 kr. as vbText (displayed localised format)

Open in new window

0
rberkeConsultantAuthor Commented:
Yes, that above correct, but incomplete.    For instance  the following is more complete

If you enter 0.33333 and apply format of Currency or Accounting (four decimals max):
Value2: 0.33333  as vbDouble  (this is result no matter how you set decimals max)
Value : 0.3333   as vbCurrency  (this is result no matter how you set decimals max)
Text  : 0,33 kr. as vbText (displayed localised format)(this result depends on decimals max)

Open in new window



So my point still stands: value, Value2 and Text are not difficult but they are not simple either.  The only way it can be made simpler is by leaving out some details.

Anyway, we are way off topic on this closed question, so I suggest we end this discussion.

Bob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.