Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8

    Importing data into table from a form

    I have a database that contains purchase order records (JobID, PONumber, description, budget), every so often I get a spreadsheet of invoices that contain InvoiceID, JobID, PONumber, Description, Cost. What I would like is to have a form with instructions at the top, and a subform to a temp/invoices table, the worker pastes the information into the sub-form, clicks a button and the database attempts to input the information into the PO table, once done it will say in the right most column if it was 'Successful', 'Invoice already exists', 'PONumber not found', or 'JobID not found'. The user can then edit the info if need be and then try again, or click a 'Clear data' button to remove the information.



    I'm looking for advice on how to approach this. I normally use queries etc to upload data etc, but have never created this kind of method for other users.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to copy a group of cells in Excel and paste them into a record in form? Form must be in Datasheet View. User must first select the entire New Record row in the form then paste. Depends on Excel columns in the same order as the table fields. And cannot have autonumber field in the subform, unless it is the last field in table design.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by June7 View Post
    You want to copy a group of cells in Excel and paste them into a record in form? Form must be in Datasheet View. User must first select the entire New Record row in the form then paste. Depends on Excel columns in the same order as the table fields. And cannot have autonumber field in the subform, unless it is the last field in table design.

    Copying and pasting from Excel to Access I am familiar with, it's how can I get the data from the subform datasheet into the PO Table and validate the data that is what i need guidance on.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Easiest may be to paste directly into the PO table. If the PONumber field is set to Index No Duplicates, the record will not commit to table.

    Otherwise, maybe just:

    CurrentDb.Execute "INSERT INTO POTable SELECT * FROM TempPO"

    If PONumber already in table, records will not insert. However, autonumber field can interfere with that simple SQL. Review https://www.w3schools.com/SQL/sql_in...nto_select.asp


    If you want to notify user whether or not record(s) committed, that will be more complicated.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by June7 View Post
    Easiest may be to paste directly into the PO table. If the PONumber field is set to Index No Duplicates, the record will not commit to table.

    Otherwise, maybe just:

    CurrentDb.Execute "INSERT INTO POTable SELECT * FROM TempPO"


    If PONumber already in table, records will not insert. However, autonumber field can interfere with that simple SQL. Review https://www.w3schools.com/SQL/sql_in...nto_select.asp


    If you want to notify user whether or not record(s) committed, that will be more complicated.

    I don't want to paste directly into the PO table, I need it done from the form along with the user getting notification of the result of each line of invoice.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Create a dummy table for info read from excel.

    When data import is initiated, a script is run:
    1. For case for some reason the dummy table is not empty, the script deletes all records from dummy table;
    2.a The script reads all data from Excel into dummy table;
    3.a The user edits/adds info/does whatever either in all record displayed in form, or the script displays info record-wise in form for user. When single record was displayed, repeat;
    2.b The script reads a record from Excel into dummy table;
    3.b Like in variant a, but for single record only. Continue cycle until until all records are imported;
    4. Records in dummy table are inserted into PO table. All records in dummy table are deleted.

  8. #8
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by ArviLaanemets View Post
    Create a dummy table for info read from excel.

    When data import is initiated, a script is run:
    1. For case for some reason the dummy table is not empty, the script deletes all records from dummy table;
    2.a The script reads all data from Excel into dummy table;
    3.a The user edits/adds info/does whatever either in all record displayed in form, or the script displays info record-wise in form for user. When single record was displayed, repeat;
    2.b The script reads a record from Excel into dummy table;
    3.b Like in variant a, but for single record only. Continue cycle until until all records are imported;
    4. Records in dummy table are inserted into PO table. All records in dummy table are deleted.

    I would like it done on a form with a subform where the data is pasted in from an Excel sheet. I'm look for VBA code that would insert the data from the subform into the POtable providing certain criteria were met, e.g the PO number exists, the invoice hasn;t already been paid etc.

  9. #9
    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
    Have you considered importing to a temp table, then validating/verifying the data values? It would seem prudent to analyze/vet the incoming data before final processing.

  10. #10
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by orange View Post
    Have you considered importing to a temp table, then validating/verifying the data values? It would seem prudent to analyze/vet the incoming data before final processing.

    How would this work?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is user copying and pasting only 1 record? What exactly needs to be validated? Only whether PONumber already exists? Maybe something like:
    Code:
    If IsNull(DLookup("PONumber", "POTable", "PONumber='" & Me.PONumber & "'")) Then
        CurrentDb.Execute "INSERT INTO POTable SELECT * FROM TempPO"
    Else
        MsgBox "PONumber already in POTable."
    End If
    If you want to import and validate a batch of records, there are many examples of code to automate this process. Do some research and attempt code. When you have specific issue, post a question.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by June7 View Post
    Is user copying and pasting only 1 record? What exactly needs to be validated? Only whether PONumber already exists? Maybe something like:
    Code:
    If IsNull(DLookup("PONumber", "POTable", "PONumber='" & Me.PONumber & "'") Then
        CurrentDb.Execute "INSERT INTO POTable SELECT * FROM TempPO"
    Else
        MsgBox "PONumber already in POTable."
    End If
    If you want to import and validate a batch of records, there are many examples of code to automate this process. Do some research and attempt code. When you have specific issue, post a question.

    Yes I want to import and validate a batch of records. I have had a serious search but much of the examples out there are for how to import from Excel or a CSV file, I have struggled to find anything about how to import and validate from a subform datasheet, that't the bit I am really stuck with. Say I had a frmImport, with subfrmImportData in add mode, how do I access the data in subfrmImportData and cycle through the records against the validation I want?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    RecordsetClone is one way. I always name subform container different from the object it holds, like ctrDetails. Code behind button on main form:

    Code:
    Private Sub btnImport_Click()
    If Me.ctrDetails.Form.Dirty Then Me.ctrDetails.Form.Dirty = False 'make sure records are committed to table
    With Me.ctrDetails.Form.RecordsetClone
        While Not .EOF
            If IsNull(DLookup("PONumber", "POTable", "PONumber='" & .PONumber & "'")) Then
                CurrentDb.Execute "INSERT INTO POTable SELECT * FROM TempPO WHERE PONumber='" & .PONumber & "'"
                MsgBox "New PO added."
            Else
                MsgBox "PO already exists"
            End If
            .MoveNext
        Wend
    End With
    End Sub
    This requires the subform container hold a form. If the container holds a table or query, RecordsetClone will not work and you will have to open a recordset object that pulls directly from table.

    Code can also physically move to each record of form. This movement will be visible to user.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Jimbola is offline Novice
    Windows 8 Access 2007
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by June7 View Post
    RecordsetClone is one way. I always name subform container different from the object it holds, like ctrDetails. Code behind button on main form:

    Code:
    Private Sub btnImport_Click()
    If Me.ctrDetails.Form.Dirty Then Me.ctrDetails.Form.Dirty = False 'make sure records are committed to table
    With Me.ctrDetails.Form.RecordsetClone
        While Not .EOF
            If IsNull(DLookup("PONumber", "POTable", "PONumber='" & .PONumber & "'")) Then
                CurrentDb.Execute "INSERT INTO POTable SELECT * FROM TempPO WHERE PONumber='" & .PONumber & "'"
                MsgBox "New PO added."
            Else
                MsgBox "PO already exists"
            End If
            .MoveNext
        Wend
    End With
    End Sub
    This requires the subform container hold a form. If the container holds a table or query, RecordsetClone will not work and you will have to open a recordset object that pulls directly from table.

    Code can also physically move to each record of form. This movement will be visible to user.

    Thank you for your help. I have had a look and I sort of get it, two questions;
    Is ctrDetails the name of the subform in your instance?
    What is TempPO? How does info get into it for it to to be accessed.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    ctrDetails is name of Subform/Subreport container control that holds your form. As I said, I name the container different from the object it holds.

    TempPO represents your temporary table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing table data to autopopulate a form
    By voughtce in forum Access
    Replies: 4
    Last Post: 03-08-2018, 11:48 PM
  2. Replies: 9
    Last Post: 12-15-2017, 08:31 PM
  3. Replies: 4
    Last Post: 10-09-2017, 03:17 PM
  4. conditionally importing table data from sql
    By vinayak36 in forum Access
    Replies: 7
    Last Post: 07-21-2017, 07:21 AM
  5. Importing data from Pivot Table?
    By jstoler in forum Import/Export Data
    Replies: 22
    Last Post: 07-11-2013, 06:58 AM

Tags for this Thread

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