Results 1 to 7 of 7
  1. #1
    KingByKing is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    3

    MS Access VBA Code for problem sample like this

    Hi hello, I have a problem to create a code in VBA that will be able to copy data from cell to cell below in given table.
    I showed what exactly I mean in the screenshoot below.
    Please help.




    Click image for larger version. 

Name:	screen.JPG 
Views:	14 
Size:	126.1 KB 
ID:	40003

    Best Regards

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Basic principle would be to use code to loop through the recordset. Depending on volumes could be quite slow

    think you will struggle since the only thing that connects the data to the date is its position. And in databases, position means nothing without an order so your recordset might not be in the order you see as displayed. Might be better to use Excel which makes use of relative positions

    Where does this data come from? If imported, suggest add an autonumber primary key field. Providing the order remains the same, then it will be possible to do with a query

    Note that Date is a reserved word (it means today and is also a datatype), using it as a field name is likely to cause problems.

    Also, you need to clarify what happens to the rows with just a date - are they to be removed or retained.

    Basic code would be something like

    Code:
    dim rs as dao.recordset
    dim tmpDate as date
    set rs=currentdb.openrecordset("Select * from Table1)
    while not rs.eof
        if isnull(rs!plant) then
          tmpDate=rs!Date
        else
          rs!date=tmpDate
          rs.update
        end if
        rs.movenext
    wend
    rs.close
    set rs=nothing
    it may be plant is not null but a zero length string in which case you will need to change the if line to

    if rs!plant="" then

    and as already mentioned, the order might not be what you expect - the above relies on the order being as displayed in your example

  3. #3
    KingByKing is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    3
    The data is handwritten and treated as a string of characters. I mainly mean how each of these dates can be copied like on screenshoot, thank you for the quick reply !!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It appears that you have experience with Excel when you reference "cells".
    And also that you are entering data directly into a table (guess on my part) -- a practice frowned upon/avoided by most.
    Data entry is normally done via a Form.

    A table contains/stores records.
    A record is made up of fields.
    Each record has identical structure.

    The "date", if handwritten, would be entered with each record.

    If using a form and some vba code, you could enter the date once for each group of records with that date value.
    You would need some logic to supply that date / update each record with that date value.

    However, more details on what you are trying to automate and what/how you will use the recorded data would be helpful to readers who are trying to offer focused advice.
    Good luck with your project.

  5. #5
    KingByKing is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    3
    Generally, the entire table is taken from the excel form as a string of characters entered by the user. The problem appeared at the automation level already in Access itself.
    My whole problem is editing the table in access using VBA, the program loads files via the VBA function, but also has to edit this file as I have presented.
    Generally, I mean copying the notes line by line and changing characters when the string of characters changes

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Based on your latest post, it appears you are overlooking or unfamiliar with some of the basics of database.
    The problem appeared at the automation level already in Access itself.
    Hmmm. Please give us some details on what you are doing, how you have done it and what exactly you have determined to be an automation level problem in Access itself.
    It may be an error but we need more info to help resolve your issue.

    Access (database) is built on a different underlying model than Excel (spreadsheet). Different concepts, different terminology and experience in one doesn't necessarily translate as a positive thing in the other.

    I suggest you tell us what you want and step by step of exactly what are doing.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    now totally confused.

    Suggest us step by step through your process, starting with the user handwriting into excel

    1. user enters data into excel in the format shown - is this always this format? i.e. separate columns, all one column
    2. data is copy/pasted//imported using import wizard//vba code transferspreadsheet to a temporary/permanent access table
    3. and clarify what 'changing characters when the string of characters changes' means

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

Similar Threads

  1. Sample Northwind database problem
    By George in forum Access
    Replies: 2
    Last Post: 02-27-2017, 10:30 AM
  2. Access Sample application
    By karthiccr in forum Tutorials
    Replies: 3
    Last Post: 10-27-2015, 05:04 PM
  3. Replies: 0
    Last Post: 10-07-2013, 09:37 AM
  4. Access 2010 - Error Code 438 Problem
    By Lexus350 in forum Access
    Replies: 5
    Last Post: 03-03-2011, 11:46 PM
  5. Interesting Query Problem (Sample Data Provided)
    By pinecrest515 in forum Queries
    Replies: 1
    Last Post: 02-07-2011, 03:27 PM

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