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

Kamis, 10 Desember 2015

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

I said something wrong ... instead of using Ctrl-'... (I thought there was also a way with Ctrl-' ...)

In the formula for the first selected blank cell in Excel once blank cells have been selected in the column where you want to copy values down, enter = and then click one cell up to have Excel paste the cell reference (ie: =A2).

Now press Ctrl-Enter to make the formula go into all the selected cells. Excel will adjust the cell reference to be relative

This works because each time there is a blank value, the value above will be filled and copied because the rows are being executed in order.

warm regards,
crystal

~ have an awesome day ~

On 12/10/2015 12:37 PM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

excellent suggestion to change the DefaultValue on the AfterUpdate event of the control while data is getting entered.

It is not that Access cannot do what you want (because of course it can) ... just that Excel is a good tool to use to populate the data that Access needs. There is also a manual way to "copy down" :

highlight the section you want to fill

Goto special cells -- blank

type Ctrl'' (to copy from above)

and press Ctrl-Enter for it to go into all cells

warm regards,
crystal

~ have an awesome day ~

On 12/9/2015 12:54 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

Ah! Now that the discussion has expanded, I'm beginning to understand the problem. You can emulate some of the features of "copy down" in Access by setting the Default Value property of a control on a form every time a field gets changed. That will propagate the last saved value onto the "next" new record.

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 9, 2015, at 1:22 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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