ACCESS FEED: Re: [MS_AccessPros] Copy Down macro in MS Access?

Selasa, 08 Desember 2015

Re: [MS_AccessPros] Copy Down macro in MS Access?

Hi James,

this is something Excel is much better at ... here is code you can run in Excel. It starts in the column and row of the active cell (which, ideally, will have a value)

'~~~~~~~~~~~~~~~~~~~~~
Sub CopyDownWhenDifferent()
' runs in Excel
'151208 s4p

   Dim nRow As Long _
      , nRow1 As Long _
      , nRow2 As Long _
      , nCol As Long _
      , vValue As Variant

   'loop from the last filled cell in the current column up to the top
   'fill each empty cell with the value above it

   'assume loop is starting from the row of the active cell

   With ActiveCell
      nRow1 = .Row
      nCol = .Column
   End With

   With ActiveSheet
      nRow2 = .UsedRange.Rows.Count
   End With

   For nRow = nRow1 To nRow2
      With Cells(nRow, nCol)
         If .Value = "" Then 'if no value, write the last value saved
            If Len(vValue) > 0 Then
               .Value = vValue
            End If
         Else
            vValue = .Value 'save the value
         End If
      End With
   Next nRow

End Sub
'~~~~~~~~~~~~~~~~~~~~~

ideally there would be a MsgBox in the beginning to test vbOkCancel buttons in case the user wants to back out ~

alternately, if you want to do this in Access, convert Sheet references to a form and cell references to a field.

Instead of "For nRow = nRow1 To nRow2" for the loop, you would loop through records of a form recordset and look in a field. If no value is found,
rs.Edit
rs!fieldname = vValue
rs.Update

WHERE
rs is the form.recordset or RecordsetClone
fieldname is the field name in the recordset

warm regards,
crystal

~ have an awesome day ~

On 12/8/2015 5:39 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

James-

Need more details. If you tried to include graphics, they didn't come through.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Dec 8, 2015, at 10:10 AM, James Asante asanteza@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I need to copy down data in Access database field "SubModule" that contains data in various points as shown. Is there a macro or query that can be created to do that kind of job? Please help.

Regards,

James

SubModule

CRM

OpenText

ERP