Results 1 to 7 of 7
  1. #1
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43

    Fill vacant cells in an access table with the data from the next above cell programmatically

    In my access table certain rows in a field are vacant. I want to Fill this vacant cells with the data of the just above cell. If a cell has data no change in the cell. But if a cell has no data the data in the next above cell will be copied to that cell and so on till all the cell have filled with data. Is there any way to achieve it programmatically. If any one help me with a code for the same it will be a great help for me.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    depends if you want to do it field by field , or run an update query using the keys....

    Code:
    Public Sub FillBlankRecs()
    Dim rst
    Dim vVal, vKey, vPrevKey
    Dim vName, vAddr, vCity, vSt
    
    
    DoCmd.SetWarnings False
    Set rst = CurrentDb.OpenRecordset("qsMyQuery")
    With rst
       While Not .EOF
            vKey = .Fields("Key").Value
            vName = .Fields("Name").Value
          
        'either copy the previous record to this record
            If IsNull(vName ) Then
                sSql = "UPDATE tData, tData AS tData_1 SET tData.Mo = [tData_1].[mo], tData.MoName = [tData_1].[MoName], tData.FiscalOrder = [tData_1].[FiscalOrder] WHERE (((tData.MoNum)=" & vKey & ") AND ((tData_1.MoNum)=" & vPrevKey & "));"
                DoCmd.RunSQL sSql
            End If
            
        'or do it FIELD by FIELD
            If IsNull(vVal) Then
               .Fields("Name").Value = vName
               .Fields("Addr").Value = vAddr
               .Fields("City").Value = vCity
               .Fields("ST").Value = vSt
               .Update
            End If
            
            vName = .Fields("Name").Value
            vAddr = .Fields("Addr").Value
            vCity = .Fields("City").Value
            vSt = .Fields("ST").Value
            
            vPrevKey = vKey
           .MoveNext
       Wend
    End With
    
    
    Set rst = Nothing
    DoCmd.SetWarnings True
    MsgBox "done"
    End Sub

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Access has no idea about what "cell" is above or below another one.
    It might know that a record is before or after another one, but that can also change based on using a sort order, or changing an index or key field.

    You appear to be trying to use the table like a spreadsheet - which it is not.

    If your records have an identifier or a time stamp and you can determine the order by that, then your request can be carried out with a either some code or possibly a clever query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    Thank you for the reply.I have only a little knowledge in VBA. With a regret to note that my present knowledge in VBA is not enough to use the code you send. I wish to improve it soon.But at present I want to complete the database I now going on as early as possible for the sake of my office . Hence I will be

    very much obliged to you if you send a specific code or update query with the following fields. My table has 3 fields 1st Auto Number named as "ID", 2nd named as "Clist" and 3rd "F1". The second field is long integer and 3rd Text. I want to update the blank records in the "Clist" field only. My table name is "tblClist". Thank You.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You didn't provide data nor a dB, so I had to create a table and a data set.

    I strongly advise you to do this on a COPY of your database until you are satisfied that the code is doing what you want!!

    And you should study the code to see what it does.

    Create a module and paste in the following:
    Code:
    Option Compare Database
    Option Explicit
    
    Public Sub UpdateFields()
        Dim d As DAO.Database
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim tmp As Long
    
        Set d = CurrentDb
    
        'create a recordset (query) on tblClist
        sSQL = "SELECT ID, Clist,F1 "
        sSQL = sSQL & " FROM tblClist"
        sSQL = sSQL & " Order By ID"
    
        Set r = d.OpenRecordset(sSQL)
    
        'check to ensure there are records
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
    
            'first record must have a value in Clist
            If Len(r("Clist")) > 0 Then
                tmp = r("Clist")  'get values from first record "Clist"
                r.MoveNext
                Do While Not r.EOF
                    'check to see if there is a value in the Clist
                    If Len(r("Clist") & "") = 0 Then
                        'no value in Clist, so add value
                        r.Edit
                        r("Clist") = tmp
                        r.Update
                    Else
                        'get the current value
                        tmp = r("Clist")
                    End If
                    r.MoveNext
                Loop
            End If
        End If
    
        r.Close
        Set r = Nothing
        Set d = Nothing
        MsgBox "Done"
    End Sub
    Click somewhere in the procedure, then press the "F5" key to execute the code.
    When "Done", check to see that the data is like you want.

    REMEMBER, I SAID TO TRY THIS ON A COPY OF YOUR dB!!!

    When you are satisfied all is OK, paste the code in a module in the "real" dB and execute the code.





    This code has a tail light guarantee - the code is guaranteed as long as you can see my tail lights....
    I accept NO responsibility if the data gets messed up. I warned you!!!
    It is not warranted to be free of defects.....

    It is only guaranteed and warranted to be free of defects ONLY on the 2nd Tuesday of the week in a month that has 3 Blue Moons...

  6. #6
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    Thank You for sending the code. The code received after correcting certain field name and table name put in a module and run the code. "Runtime Error 3131 , Syntax error in From clause" is displayed.
    Set r = d.OpenRecordset(sSQL) is seen selected.

    Your code used in my db is as follows. Please look into the code and gave me an advice to correct the code.
    Thank you once again for your good mind in helping me.


    Option Compare Database
    Option Explicit
    Public Sub UpdateFields()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim tmp As Long
    Set d = CurrentDb
    'create a recordset (query) on tblCL-CaseNo
    sSQL = "SELECT ListID, listNo,F1 "
    sSQL = sSQL & " FROM tblCL-CaseNo"
    sSQL = sSQL & " Order By ListID"
    Set r = d.OpenRecordset(sSQL)
    'check to ensure there are records
    If Not r.BOF And Not r.EOF Then
    r.MoveLast
    r.MoveFirst
    'first record must have a value in ListNo
    If Len(r("ListNo")) > 0 Then
    tmp = r("ListNo") 'get values from first record "ListNo"
    r.MoveNext
    Do While Not r.EOF
    'check to see if there is a value in the Clist
    If Len(r("ListNo") & "") = 0 Then
    'no value in ListNo, so add value
    r.Edit
    r("ListNo") = tmp
    r.Update
    Else
    'get the current value
    tmp = r("ListNo")
    End If
    r.MoveNext
    Loop
    End If
    End If

    r.Close
    Set r = Nothing
    Set d = Nothing
    MsgBox "Done"
    End SubOption Compare Database
    Option Explicit
    Public Sub UpdateFields()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim tmp As Long
    Set d = CurrentDb
    'create a recordset (query) on tblCL-CaseNo
    sSQL = "SELECT ListID, listNo,F1 "
    sSQL = sSQL & " FROM tblCL-CaseNo"
    sSQL = sSQL & " Order By ListID"
    Set r = d.OpenRecordset(sSQL)
    'check to ensure there are records
    If Not r.BOF And Not r.EOF Then
    r.MoveLast
    r.MoveFirst
    'first record must have a value in ListNo
    If Len(r("ListNo")) > 0 Then
    tmp = r("ListNo") 'get values from first record "ListNo"
    r.MoveNext
    Do While Not r.EOF
    'check to see if there is a value in the Clist
    If Len(r("ListNo") & "") = 0 Then
    'no value in ListNo, so add value
    r.Edit
    r("ListNo") = tmp
    r.Update
    Else
    'get the current value
    tmp = r("ListNo")
    End If
    r.MoveNext
    Loop
    End If
    End If

    r.Close
    Set r = Nothing
    Set d = Nothing
    MsgBox "Done"
    End Sub

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The error is because of the improper naming of your table.

    You posted
    My table name is "tblClist"
    Apparently, you named the table "tblCL-CaseNo".

    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names.


    Your choices are:
    1) rename the table to "tblCL_CaseNo" or "tblCLCaseNo" or "tblClist". Then change the code....

    2) put brackets around the name: "[tblCL-CaseNo]". The code would look like
    Code:
    sSQL = sSQL & " FROM [tblCL-CaseNo]"

    Number 1 is the best option: rename the table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 01-24-2018, 02:44 PM
  2. Replies: 12
    Last Post: 07-07-2015, 08:12 PM
  3. Replies: 5
    Last Post: 05-09-2013, 06:47 PM
  4. Merging Data from 3 Cells to One Cell
    By Upon66 in forum Queries
    Replies: 5
    Last Post: 08-11-2012, 11:04 AM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums