locked
Fill Down in Column? RRS feed

  • Question

  • I’m wondering if there is a way to get Access to fill down records, if there are a bunch of nulls in my data set.  I know this is not supposed to be used like Excel; I’m just trying to do a small project.  I know how to do this in SQL Server, but I have no idea if it can be done in Access.

    In my sample data, I want to fill in CUSIPs, for ID 2602.  There is no CUSIP for 2603 & 2604, so I don’t want to do anything for those.  For 2623, I want to fill down again.  Can this be done in Access?  Maybe a Dlookup?  Maybe an Update Query or Make Table Query?  Maybe a Function?


    Thanks everyone.

     


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.



    • Edited by ryguy72 Friday, June 12, 2015 7:29 PM
    Friday, June 12, 2015 7:27 PM

Answers

  • Thanks.  I got another idea from here.

    http://www.mrexcel.com/forum/microsoft-access/333243-simulating-fill-down-access.html

    Hi ryquy,

    I misunderstood your "fill down". I assume you mean to take value of the field from the previous record, when it is Null in the current record. Sometimes it is hard to fully understand a not native language.

    Good that you already found a solution. Else I could dig up a comparable functionality that I made long time ago. At that time I also made a routine to copy the value of a field from the previous record in a form.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Friday, June 12, 2015 8:48 PM
  • On second thought, can you post your solution?  I thought I had it working, but then realized I was using an Inner Join, not a Left/Right Join, like I need to use.

    Hi ryguy,

    This is about the routine that I use, but I have isolated it from my systematics.

    Sub Fill_in() Dim cur_set As Recordset Dim last_value As Variant active_sql = "SELECT ID, CUSIP FROM ..." Do While (Active_set(cur_set, busy4)) SELECT CASE cur_set!ID
    CASE 2603, 2604
    CASE ELSE If (cur_set!CUSIP) > "") Then last_value = cur_set!CUSIP Else cur_set.Edit cur_set!CUSIP = last_value cur_set.Update End If
    END SELECT Loop End Sub

    Active_set takes as input the global active_sql, which holds the sql-string for the update. All error handling is included within this function.

    busy4 describes the kind of progress information to be displayed.

    You have to modify the routine for use in your environment, but the process should be clear.

    I have assumed that a new ID always has a "first" value, else you have to take precautions for that.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Saturday, June 13, 2015 8:32 AM
  • ryguy,

    You should probably stop playing around with trying to do nifty things that Excel can do and just get the task accomplished.  An update query would serve you adequately.  However, here is a solution for you to get what you want.

    1. Using the Form Wizard, create a datasheet form using a desired query or table.
    2. Open the form in design view.
    3. In the form's properties>Event>Key Preview, change the value to Yes.
    4. On the Design tab, select View Code.
    5. Copy and paste the following code into the module.
    6. Compile the module.  On the menu bar, select Debug>Compile
    7. Save the code changes. Using the keyboard, press [Ctrl]+[S].
    8. Close the VBA editor and return to the form.  On the menu bar, select File>Close and return to MS Access.
    9. Switch the Form's view to datasheet.  Using the keyboard, type [Alt]+[V], [S].

    Test your new capability: 

    1. Using the mouse, place the cursor at the top of the recordset in the column CUSIP.
    2. Select the column field for the following three records.  Using the keyboard, Press and hold [Shift], then tap the [Down Arrow] three times.
    3. Fill the current value down to the last selected field. Using the keyboard, type [Alt]+[Down Arrow].  An input box will be displayed.  If the first field selected has a value in it, the input box will display that value by default.  You have the option to accept that value, or you can enter something different.
    4. Populate the selected fields.  If you are satisfied with the value in the input box and are ready to proceed with the update, click OK.  To abort the update, click Cancel.

    And there you have it...

    Please be sure to mark your post as answered if this or any other solution satisfies your need.

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
        
        Select Case KeyCode & Shift
        Case vbKeyDown & 4
            FillDown
        Case Else
            Exit Sub
        End Select
        KeyCode = 0
        Shift = 0
        
    End Sub
    
    Private Sub FillDown()
        
        Dim rst1 As Recordset
        Dim strInput As String
        Dim lngCount As Long
        
        Set rst1 = Me.RecordsetClone
        
        With rst1
            .MoveLast
            .Bookmark = Me.Bookmark
            If (Not .EOF) Then
                strInput = InputBox("Enter fill value:", "Fill Down", Nz(Me.ActiveControl.Value, ""))
                If (strInput <> "") Then
                    For lngCount = 1 To Me.SelHeight
                        If (.AbsolutePosition > -1) Then
                            .Edit
                            .Fields(Me.ActiveControl.ControlSource) = strInput
                            .Update
                            If (.AbsolutePosition < .RecordCount) Then
                                .MoveNext
                            Else
                                Exit For
                            End If
                        Else
                            Exit For
                        End If
                    Next
                End If
            End If
        End With
        
        Set rst1 = Nothing
        
    End Sub



    • Edited by RunningManHD Monday, June 15, 2015 10:35 PM
    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 10:14 PM
  • In my sample data, I want to fill in CUSIPs, for ID 2602.  There is no CUSIP for 2603 & 2604, so I don’t want to do anything for those.  For 2623, I want to fill down again.  Can this be done in Access?  Maybe a Dlookup?  Maybe an Update Query or Make Table Query?  Maybe a Function?

    Hi ryguy,

    You could do this with the next instruction, in general form:

        CurrentDb.Execute "UPDATE <table> SET CUSIP = <value> WHERE ID = 2602 AND CUSIP IS NULL"

    According to your example this would work for ID = 2602. For ID = 2623 there are 2 (or more?) possibilities to fill in. How can the program distinguish between the different possible values?

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Friday, June 12, 2015 7:53 PM
  • In order to do something like that, you need to be working in a form, not a query.  Your form can be a datasheet to look just like a resulting query.  You would want to make a parent form and your datasheet would be a subform of the parent.  In the parent form you can use a button to collect the necessary information you want from the user and then run an update query based on your defined criteria.

    • Edited by RunningManHD Monday, June 15, 2015 3:54 PM
    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 3:52 PM
  • I thought that would call the Sub, but it's prompting me for a parameter, and no matter what I pass in I then thing I passed in is returned in the Query, and the Sub is never called. 

    Do you have any idea what I'm doing wrong?  It's not really apparent, to me, what's happening here.

    Hi ryguy,

    Make a command button on a form, in the Click event you then run: Fill_in.

    For 'sql_active' you use the sql-expression of your query. Of course, for customization of the process, you can use parameters in the Fill_in routine.

    Then use the debugger to see what happens, after you click the button.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 7:08 PM
  • Option Compare Database
    
    Private Sub Fill_In_Click()
    
      Dim cur_set As Recordset
      Dim last_value As Variant
      
      active_sql = "SELECT tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "FROM (tbl_Inventory_Debt_and_Swap_WSS_only_csv LEFT JOIN tbl_SKFR_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_SKFR_csv.Number) LEFT JOIN tbl_CUSIP_Mapping_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_CUSIP_Mapping_csv.[Transaction Number]" & _
      "GROUP BY tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "HAVING (((tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID]) Is Not Null));"
      Do While (Active_set(cur_set, busy4))
        Select Case cur_set!ID
        Case 2603, 2604
        Case Else
          If (qry_Package_ID!CUSIP <> "") Then
            last_value = cur_set!CUSIP
          Else
            cur_set.Edit
            cur_set!CUSIP = last_value
            cur_set.Update
          End If
        End Select
      Loop
      
    End Sub

    The text 'Active_set' is highlighted.

    How would that 2nd Form know about the 1st Form?

    Hi ryguy,

    Instead of "mine" Active_set, you must use "your" looping through the set, for instance:

        Set cur_set = CurrentDb.OpenRecordset (active_sql)
        Do While (Not cur_set.EOF)
            ...
            ...
            cur_set.MoveNext
        Loop

    including error handling, etc.

    The form is only used for the Click event. The RecordSet is in principle independent of the 2nd and 1st Form, because in the sql-specification you have already given the relation between different records through a join of the (underlying) tables.

     PS. In the above code you still have named:  qry_Package_ID!CUSIP. This probably has to be: cur_set!CUSIP.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 8:51 PM

All replies

  • In my sample data, I want to fill in CUSIPs, for ID 2602.  There is no CUSIP for 2603 & 2604, so I don’t want to do anything for those.  For 2623, I want to fill down again.  Can this be done in Access?  Maybe a Dlookup?  Maybe an Update Query or Make Table Query?  Maybe a Function?

    Hi ryguy,

    You could do this with the next instruction, in general form:

        CurrentDb.Execute "UPDATE <table> SET CUSIP = <value> WHERE ID = 2602 AND CUSIP IS NULL"

    According to your example this would work for ID = 2602. For ID = 2623 there are 2 (or more?) possibilities to fill in. How can the program distinguish between the different possible values?

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Friday, June 12, 2015 7:53 PM
  • Thanks.  I got another idea from here.

    http://www.mrexcel.com/forum/microsoft-access/333243-simulating-fill-down-access.html


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, June 12, 2015 8:18 PM
  • Thanks.  I got another idea from here.

    http://www.mrexcel.com/forum/microsoft-access/333243-simulating-fill-down-access.html

    Hi ryquy,

    I misunderstood your "fill down". I assume you mean to take value of the field from the previous record, when it is Null in the current record. Sometimes it is hard to fully understand a not native language.

    Good that you already found a solution. Else I could dig up a comparable functionality that I made long time ago. At that time I also made a routine to copy the value of a field from the previous record in a form.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Friday, June 12, 2015 8:48 PM
  • On second thought, can you post your solution?  I thought I had it working, but then realized I was using an Inner Join, not a Left/Right Join, like I need to use.

    Thanks!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, June 13, 2015 12:40 AM
  • On second thought, can you post your solution?  I thought I had it working, but then realized I was using an Inner Join, not a Left/Right Join, like I need to use.

    Hi ryguy,

    This is about the routine that I use, but I have isolated it from my systematics.

    Sub Fill_in() Dim cur_set As Recordset Dim last_value As Variant active_sql = "SELECT ID, CUSIP FROM ..." Do While (Active_set(cur_set, busy4)) SELECT CASE cur_set!ID
    CASE 2603, 2604
    CASE ELSE If (cur_set!CUSIP) > "") Then last_value = cur_set!CUSIP Else cur_set.Edit cur_set!CUSIP = last_value cur_set.Update End If
    END SELECT Loop End Sub

    Active_set takes as input the global active_sql, which holds the sql-string for the update. All error handling is included within this function.

    busy4 describes the kind of progress information to be displayed.

    You have to modify the routine for use in your environment, but the process should be clear.

    I have assumed that a new ID always has a "first" value, else you have to take precautions for that.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Saturday, June 13, 2015 8:32 AM
  • Thanks for this.  I'm just using it for the first time now.  I passed my entire SQL script into the 'active_sql' string.

    Now, he Query is named 'qry_Package_ID', so I'm using this.

    If (qry_Package_ID!CUSIP <> "") Then

    Now, back in the Query Design Grid, I'm passing in this:

    CUSIP: [Fill_in]

    I thought that would call the Sub, but it's prompting me for a parameter, and no matter what I pass in I then thing I passed in is returned in the Query, and the Sub is never called. 

    Do you have any idea what I'm doing wrong?  It's not really apparent, to me, what's happening here.




    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, June 15, 2015 3:23 PM
  • In order to do something like that, you need to be working in a form, not a query.  Your form can be a datasheet to look just like a resulting query.  You would want to make a parent form and your datasheet would be a subform of the parent.  In the parent form you can use a button to collect the necessary information you want from the user and then run an update query based on your defined criteria.

    • Edited by RunningManHD Monday, June 15, 2015 3:54 PM
    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 3:52 PM
  • Ok, that makes sense.  I setup a Form that looks like a DataSheetView; here's an image.

    The CUS: Fill_In([CUSIP]) is throwing the following error.
    Undefined function 'Fill_In' expression.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, June 15, 2015 4:38 PM
  • I thought that would call the Sub, but it's prompting me for a parameter, and no matter what I pass in I then thing I passed in is returned in the Query, and the Sub is never called. 

    Do you have any idea what I'm doing wrong?  It's not really apparent, to me, what's happening here.

    Hi ryguy,

    Make a command button on a form, in the Click event you then run: Fill_in.

    For 'sql_active' you use the sql-expression of your query. Of course, for customization of the process, you can use parameters in the Fill_in routine.

    Then use the debugger to see what happens, after you click the button.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 7:08 PM
  • When I click the button, it ways Sub or Function not defined.

    Option Compare Database
    
    Private Sub Fill_In_Click()
    
      Dim cur_set As Recordset
      Dim last_value As Variant
      
      active_sql = "SELECT tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "FROM (tbl_Inventory_Debt_and_Swap_WSS_only_csv LEFT JOIN tbl_SKFR_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_SKFR_csv.Number) LEFT JOIN tbl_CUSIP_Mapping_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_CUSIP_Mapping_csv.[Transaction Number]" & _
      "GROUP BY tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "HAVING (((tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID]) Is Not Null));"
      Do While (Active_set(cur_set, busy4))
        Select Case cur_set!ID
        Case 2603, 2604
        Case Else
          If (qry_Package_ID!CUSIP <> "") Then
            last_value = cur_set!CUSIP
          Else
            cur_set.Edit
            cur_set!CUSIP = last_value
            cur_set.Update
          End If
        End Select
      Loop
      
    End Sub

    The text 'Active_set' is highlighted.

    How would that 2nd Form know about the 1st Form?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Monday, June 15, 2015 8:23 PM
    Monday, June 15, 2015 8:20 PM
  • Option Compare Database
    
    Private Sub Fill_In_Click()
    
      Dim cur_set As Recordset
      Dim last_value As Variant
      
      active_sql = "SELECT tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "FROM (tbl_Inventory_Debt_and_Swap_WSS_only_csv LEFT JOIN tbl_SKFR_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_SKFR_csv.Number) LEFT JOIN tbl_CUSIP_Mapping_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_CUSIP_Mapping_csv.[Transaction Number]" & _
      "GROUP BY tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "HAVING (((tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID]) Is Not Null));"
      Do While (Active_set(cur_set, busy4))
        Select Case cur_set!ID
        Case 2603, 2604
        Case Else
          If (qry_Package_ID!CUSIP <> "") Then
            last_value = cur_set!CUSIP
          Else
            cur_set.Edit
            cur_set!CUSIP = last_value
            cur_set.Update
          End If
        End Select
      Loop
      
    End Sub

    The text 'Active_set' is highlighted.

    How would that 2nd Form know about the 1st Form?

    Hi ryguy,

    Instead of "mine" Active_set, you must use "your" looping through the set, for instance:

        Set cur_set = CurrentDb.OpenRecordset (active_sql)
        Do While (Not cur_set.EOF)
            ...
            ...
            cur_set.MoveNext
        Loop

    including error handling, etc.

    The form is only used for the Click event. The RecordSet is in principle independent of the 2nd and 1st Form, because in the sql-specification you have already given the relation between different records through a join of the (underlying) tables.

     PS. In the above code you still have named:  qry_Package_ID!CUSIP. This probably has to be: cur_set!CUSIP.

    Imb.

    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 8:51 PM
  • ryguy,

    You should probably stop playing around with trying to do nifty things that Excel can do and just get the task accomplished.  An update query would serve you adequately.  However, here is a solution for you to get what you want.

    1. Using the Form Wizard, create a datasheet form using a desired query or table.
    2. Open the form in design view.
    3. In the form's properties>Event>Key Preview, change the value to Yes.
    4. On the Design tab, select View Code.
    5. Copy and paste the following code into the module.
    6. Compile the module.  On the menu bar, select Debug>Compile
    7. Save the code changes. Using the keyboard, press [Ctrl]+[S].
    8. Close the VBA editor and return to the form.  On the menu bar, select File>Close and return to MS Access.
    9. Switch the Form's view to datasheet.  Using the keyboard, type [Alt]+[V], [S].

    Test your new capability: 

    1. Using the mouse, place the cursor at the top of the recordset in the column CUSIP.
    2. Select the column field for the following three records.  Using the keyboard, Press and hold [Shift], then tap the [Down Arrow] three times.
    3. Fill the current value down to the last selected field. Using the keyboard, type [Alt]+[Down Arrow].  An input box will be displayed.  If the first field selected has a value in it, the input box will display that value by default.  You have the option to accept that value, or you can enter something different.
    4. Populate the selected fields.  If you are satisfied with the value in the input box and are ready to proceed with the update, click OK.  To abort the update, click Cancel.

    And there you have it...

    Please be sure to mark your post as answered if this or any other solution satisfies your need.

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
        
        Select Case KeyCode & Shift
        Case vbKeyDown & 4
            FillDown
        Case Else
            Exit Sub
        End Select
        KeyCode = 0
        Shift = 0
        
    End Sub
    
    Private Sub FillDown()
        
        Dim rst1 As Recordset
        Dim strInput As String
        Dim lngCount As Long
        
        Set rst1 = Me.RecordsetClone
        
        With rst1
            .MoveLast
            .Bookmark = Me.Bookmark
            If (Not .EOF) Then
                strInput = InputBox("Enter fill value:", "Fill Down", Nz(Me.ActiveControl.Value, ""))
                If (strInput <> "") Then
                    For lngCount = 1 To Me.SelHeight
                        If (.AbsolutePosition > -1) Then
                            .Edit
                            .Fields(Me.ActiveControl.ControlSource) = strInput
                            .Update
                            If (.AbsolutePosition < .RecordCount) Then
                                .MoveNext
                            Else
                                Exit For
                            End If
                        Else
                            Exit For
                        End If
                    Next
                End If
            End If
        End With
        
        Set rst1 = Nothing
        
    End Sub



    • Edited by RunningManHD Monday, June 15, 2015 10:35 PM
    • Marked as answer by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Monday, June 15, 2015 10:14 PM
  •   
      active_sql = "SELECT tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "FROM (tbl_Inventory_Debt_and_Swap_WSS_only_csv LEFT JOIN tbl_SKFR_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_SKFR_csv.Number) LEFT JOIN tbl_CUSIP_Mapping_csv ON tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Transaction Number] = tbl_CUSIP_Mapping_csv.[Transaction Number]" & _
      "GROUP BY tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID], tbl_SKFR_csv.Number, tbl_CUSIP_Mapping_csv.CUSIP, tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Instrument Class], tbl_SKFR_csv.Portfolio, tbl_SKFR_csv.Currency, tbl_SKFR_csv.[Market Value], tbl_SKFR_csv.[Accrued Interest], tbl_SKFR_csv.Transactiontype" & _
      "HAVING (((tbl_Inventory_Debt_and_Swap_WSS_only_csv.[Package ID]) Is Not Null));"
    

    Hi ryguy,

    The above only works in an updatable recordset. So you must use a "simple" query, without GROUP and HAVING.

    Imb.

    Tuesday, June 16, 2015 6:09 AM
  • I just tested one more concept which worked for me as well.

    #1)  Create a Make Table Query

    Name the table 'tbl_Package_ID' and have a Field named 'CUSIP'.  The CUSIP field has the nulls.

    #2)  Run the VBA script below.

    Private Sub Command1_Click()
    
    Dim DB As DAO.Database
    Dim Rst As DAO.Recordset
    Dim NullReplace As String
    Set DB = CurrentDb()
    Set Rst = DB.OpenRecordset("tbl_Package_ID")
    Do Until Rst.EOF
    If IsNull(Rst!CUSIP) = True Then
    Rst.Edit
    Rst!CUSIP = NullReplace
    Rst.Update
    Else
    NullReplace = Nz(Rst!CUSIP, "")
    End If
    Rst.MoveNext
    Loop
    Rst.Close
    Set Rst = Nothing
    Set DB = Nothing
    
    End Sub
    Thanks for the help everyone.



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Tuesday, June 16, 2015 2:58 PM
    Tuesday, June 16, 2015 2:57 PM
  • I just tested one more concept which worked for me as well.

    #1)  Create a Make Table Query

    Name the table 'tbl_Package_ID' and have a Field named 'CUSIP'.  The CUSIP field has the nulls.

    #2)  Run the VBA script below.

    Private Sub Command1_Click()
    
    Dim DB As DAO.Database
    Dim Rst As DAO.Recordset
    Dim NullReplace As String
    Set DB = CurrentDb()
    Set Rst = DB.OpenRecordset("tbl_Package_ID")
    Do Until Rst.EOF
    If IsNull(Rst!CUSIP) = True Then
    Rst.Edit
    Rst!CUSIP = NullReplace
    Rst.Update
    Else
    NullReplace = Nz(Rst!CUSIP, "")
    End If
    Rst.MoveNext
    Loop
    Rst.Close
    Set Rst = Nothing
    Set DB = Nothing
    
    End Sub
    Thanks for the help everyone.



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    Hi ryguy72,

    How can I apply this only if ID field (or Field1 in my sample) is non-blank?

    Field1 Field2
    | 28/01/2015


    | 28/01/2015


    | 28/01/2015


    |
    | 28/01/2015
    |
    | 28/01/2015


    | 29/01/2015


    | 30/01/2015
    |
    | 30/01/2015




    • Edited by navafolk Wednesday, July 22, 2015 10:52 AM
    Wednesday, July 22, 2015 10:48 AM
  • After working on this project for about a month now, I've learned so much.  I'd say, use an Update Query to do what you want.  I can't tell you the exact logic because I don't know your actual data set, but check out this small SQL sample.

    http://www.dbforums.com/showthread.php?1609140-Only-update-fields-with-null-values-update-query-or-VBA


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, July 25, 2015 9:16 PM
  • I know this is an old post but it still helped me out heaps Thanks RunningManHD!

    I have tweaked the code a little however, to overcome a bug where if the user selected from the bottom up it would still fill down from the first (bottom) field/row. I also modified it to allow a blank entry as I needed to be able to blank out the selected values.

    Modified code:

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
        
        Select Case KeyCode & Shift
        Case vbKeyDown & 4
            FillDown
        Case Else
            Exit Sub
        End Select
        KeyCode = 0
        Shift = 0
        
    End Sub
    
    Private Sub FillDown()
        
        Dim rst1 As Recordset
        Dim strInput As String
        Dim lngCount As Long
        
        Set rst1 = Me.RecordsetClone
        
        With rst1
            .MoveFirst
            .Move Me.SelTop - 1
            If (Not .EOF) Then
                strInput = InputBox("Enter fill value:", "Fill Down", Nz(Me.ActiveControl.Value, ""))
                For lngCount = 1 To Me.SelHeight
                    If (.AbsolutePosition > -1) Then
                        .Edit
                        .Fields(Me.ActiveControl.ControlSource) = strInput
                        .Update
                        If (.AbsolutePosition < .RecordCount) Then
                            .MoveNext
                        Else
                            Exit For
                        End If
                    Else
                        Exit For
                    End If
                Next
            End If
        End With
        
        Set rst1 = Nothing
        
    End Sub
    

    Friday, April 12, 2019 3:26 AM