Results 1 to 14 of 14
  1. #1
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37

    Importing Excel Data into Subform

    Hello,
    We have temperature data that is automatically collected. The data can only be downloaded into an excel format. I want to import this data into an access database. It would import it using a button on the form and bring it into a subform. I created a button and some code. Problem is, it imports the data into the table, but the linking field (LoggerID) is missing and the data is not shown in the subform. Any hints or suggestions. Attached is a picture showing relationship and form (with code on bottom) that I have built.



    Cheers
    Nick

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can you provide the Excel file - attach to post.
    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
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37

    Sample Data File

    Hi June,

    Thanks for the reply. Attached is a zip file of an example of our temperature data. It only contains 23 lines of data for simplicity and reduce size of file. Each logger download has 2927 lines of data and we deploy ~100 loggers each year.

    Cheers
    Nick

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The spreadsheet does not have the LoggerID data. If all of these records apply to the same logger, then add the column and populate the data to the spreadsheet before import. Or run an update on the newly imported records to add the LoggerID.
    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.

  5. #5
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37
    OK. So is there a way to save the LoggerID from the form I am on and insert that into the "Update to:" in an update query?

  6. #6
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37
    Say something along the lines of
    Saving LoggerID to a var
    Opening query
    Inserting value
    Running query
    Closing query
    Updating form to show imported data in subform

    (using VBA code)

    Thanks

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Code in some event. Maybe the one that performs the import:

    CurrentDb.Execute "UPDATE tablename SET LoggerID=" & Me.LoggerID & " WHERE LoggerID Is Null"

    If the above is in an event of the already open form/subform then also:
    Me.subformContainerName.Requery

    I always give subform containers a name different from the form they hold, like: ctrTempData
    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.

  8. #8
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37
    Good morning June,

    Thanks for the help and patience. Here is the code that I put in.

    Duluth_Streams_DB.Execute ("UPDATE Temperature SET LoggerID=" & Me.LoggerID & " WHERE LoggerID Is Null")
    Me.ctrTemp.Requery

    It is giving me an "object required" error. If I had more experience, I may be able to debug this myself. Fiddle with it a bunch and can't seem to to get it to work.

    Cheers
    Nick

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, use CurrentDb as shown. This is alias for the working project. VBA will understand it. No parentheses.

    CurrentDb.Execute "UPDATE Temperature SET LoggerID=" & Me.LoggerID & " WHERE LoggerID Is Null"
    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.

  10. #10
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37
    OK. So I entered the code:

    Private Sub ImportTemp_Click()
    DoCmd.RunCommand acCmdImportAttachExcel
    CurrentDb.Execute "UPDATE Temperature SET LoggerID=" & Me.LoggerID & " WHERE LoggerID Is Null"
    Me.ctrTemp.Requery
    End Sub


    It ran the import alright but then returned a "Syntax error in UPDATE statement"

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I was assuming LoggerID is number field. If it is text need apostrophe delimiters.
    CurrentDb.Execute "UPDATE Temperature SET LoggerID='" & Me.LoggerID & "' WHERE LoggerID Is Null"

    It would be best if date and time were in one field.
    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
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37
    LoggerID is an autonumber in the underlying main table and a number field in the underlying subform table. When I put in the single quotes. No errors come up but LoggerID is not filled in.

    Cheers
    Nick

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I see that now, the LoggerID must be number type. Odd, the apostrophes should have errored. I tested import to and update of table and it worked. I guess next step is to provide the project for analysis.
    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
    Nick F is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2011
    Posts
    37
    Hi June,

    I got it to work. Your code was correct. I had to add a save command line of code to save the table before loading data into the subform. Now it works. Thanks for the help and patience.

    Cheers
    Nick

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

Similar Threads

  1. Importing Data from Excel to Access
    By dkatorza in forum Import/Export Data
    Replies: 3
    Last Post: 11-05-2011, 09:17 AM
  2. importing data from an excel file
    By slimjen in forum Access
    Replies: 3
    Last Post: 09-21-2011, 12:38 PM
  3. Importing ever-changing Excel data
    By jtf1972 in forum Database Design
    Replies: 7
    Last Post: 08-05-2011, 11:24 AM
  4. Importing Data From Excel
    By king_bowzow in forum Import/Export Data
    Replies: 1
    Last Post: 09-11-2009, 02:26 AM
  5. iMPORTING DATA FROM EXCEL TO ACCESS
    By Rameez in forum Import/Export Data
    Replies: 7
    Last Post: 06-16-2009, 12:05 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