none
Performing an automated data entry in Access 2010 RRS feed

  • Question

  • Hello,

    I may have realized that it is a pain to perform data entry one record at a time.

    Is there a quicker way to perform an automated data entry task by using Access VBA?

    What I want to perform are the following steps:

    1. Declare record set, db as database, and Irec as long data type parameters.

    2. Write a For...Loop structure of the following pseudo coding algorithm:

        For IRec=0 to db.DataEntryFormOne.recordcount-1

              dbDataEntryFormTwo(JRec).value=dbDataEntryFormOne(IRec).value

             .MoveNext

        Next

    The example above is just a primary example of simply how it gets done by invoking a command button

    Click event.

    Are there any comments on this topic?

    JohnDBCTX

    Wednesday, August 5, 2015 9:47 AM

Answers

  • Your reference to "DataEntryFormOne" and "dbDataEntryFormTwo" leads me to wonder if you are thinking in terms of manipulating forms to create records, but if all you're doing is manipulating data -- creating records from other records -- it's much simpler to do that without involving forms at all.  Then, after the data manipulation is done, you can display or requery a form to show the results.

    I wouldn't be surprised if what you're trying to do could be accomplished by executing a single append query to generate and append all the desired records, without involving recordsets at all.  But I'd need more information about the tables involved and what you really want to accomplish.  Could you please post more details?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 5, 2015 12:17 PM
  • Hi JohnDBCTX,

    Your way is a common way to update the recordset which is descripted in the link below:
    # How to: Modify an Existing Record in a DAO Recordset
    https://msdn.microsoft.com/en-us/library/Bb243797(v=office.12).aspx

    What your recordset type, a table-type or dynaset-type? If your recordset is a table-type, I think you could update your table with update query, and then use Recordset.Requery to make sure that a Recordset contains the most recent data.
    # Create and run an update query
    https://support.office.com/en-us/article/Create-and-run-an-update-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514
    # Recordset.Requery Method
    https://msdn.microsoft.com/EN-US/library/office/ff821155.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, August 12, 2015 3:33 AM

All replies

  • So is it that you just want to copy records from one table to another?

    Wednesday, August 5, 2015 10:15 AM
  • Your reference to "DataEntryFormOne" and "dbDataEntryFormTwo" leads me to wonder if you are thinking in terms of manipulating forms to create records, but if all you're doing is manipulating data -- creating records from other records -- it's much simpler to do that without involving forms at all.  Then, after the data manipulation is done, you can display or requery a form to show the results.

    I wouldn't be surprised if what you're trying to do could be accomplished by executing a single append query to generate and append all the desired records, without involving recordsets at all.  But I'd need more information about the tables involved and what you really want to accomplish.  Could you please post more details?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 5, 2015 12:17 PM
  • Hi JohnDBCTX,

    Your way is a common way to update the recordset which is descripted in the link below:
    # How to: Modify an Existing Record in a DAO Recordset
    https://msdn.microsoft.com/en-us/library/Bb243797(v=office.12).aspx

    What your recordset type, a table-type or dynaset-type? If your recordset is a table-type, I think you could update your table with update query, and then use Recordset.Requery to make sure that a Recordset contains the most recent data.
    # Create and run an update query
    https://support.office.com/en-us/article/Create-and-run-an-update-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514
    # Recordset.Requery Method
    https://msdn.microsoft.com/EN-US/library/office/ff821155.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, August 12, 2015 3:33 AM