Do I need to run a query in VBA after getting imported data to appear in a field?How?

[Ronald Mcdonald]

https://www.accessforums.net/showthr...n-Access-(2003)

This question is kind of based off the link above.

Background: I have an Excel sheet that I update each day which has a few entries a day about problems that occurred in operations and how many products were affected for each problem. I have no problem importing this tab into Access. I also ran a query to only show today's date, today's description of problems, and how many products were affected. What I'd like to do is have all of today's problems and these other fields to show up in an Access form once the data is imported.

Issue: I created a form to house fields "Date", "Description", "# Products Affected." I set the record source as the query that finds the problems that occured today. When I "run" the form, this information shows up fine, but only the first entry for that day. If there are 5 entries, only the first 1 would show up. But then, on the import/update button I made, I just get "#Deleted" in each of the fields.

Here's my code on the import button:

Private Sub Command11_Click()
Dim stDocName As String
Dim stLinkCriteria As String
        DoCmd.SetWarnings False
                
        CurrentDb.Execute "DELETE * from [File Name]"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "File name", "Location", True, "Cell Range"
                
        MsgBox ("Log - Updated")
        DoCmd.SetWarnings True
        
        stDocName = "Query1_TodaysErrors"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        

Exit_Command129_Click:
    Exit Sub
Err_Command129_Click:
    MsgBox Err.Description
    Resume Exit_Command129_Click
End Sub

Not sure if I posted too much, but didn't want to leave anything out! The program knows the query exists, hence why it posts the table after the message box, but am having trouble getting the fields in the query into the appropriate form fields once I hit the update button.



Appreciate any help. Let me know if you need more information. Thanks!

[R_Badger]

Stupid question but did you try setting your form property to continuous forms?

[Ronald Mcdonald]

Yup, continuous forms. I am a noob at Access so any questions on your end are more than welcomed if it solves my problem!

[R_Badger]

Forgot to mention before, the #Deleted is because you run the form, it loads the data in your table, you then click it, the delete query runs, deleting all the data in your table (including the line it just loaded)

When it opens the query does it show more records there (i.e what you would expect on the form)?

[Ronald Mcdonald]

Where about is the query being deleted? I only delete the old table so the new data doesn't stack below it when I import that. that's just to remove data to prevent double counting.

After the message box, when the query opens up, yes, all records do show up. Only problem is they don't land in the fields on the form

Should also mention that the way I get rid of these #deletes, I simply design the form and reselect the query. No characters have changed or anything, but Access seems to think it's a different one that I select. Weird.

[TG_W]

Try adding Me.Requery or Me.Refresh

[R_Badger]

Where about is the query being deleted?

You misread, you run a delete query, i.e a query that deletes data.

Should also mention that the way I get rid of these #deletes, I simply design the form and reselect the query. No characters have changed or anything, but Access seems to think it's a different one that I select. Weird.

It's not wierd, because it is loading the form again, it reloads the rowsource.

Try adding Me.Requery or Me.Refresh

Me.requery at the end of your vb will force the form to requery and have the same effect as if you had gone into design view and back to normal again.

The next best thing would be if you could upload a copy of your project for us to look.
(If you want to do this, be sure to remove any confidential data, put in some sample dummy data so we can get a look at how its trying to work. Then compact/repair, and zip if it is still too large to upload)

[Ronald Mcdonald]

I think I got it to work now. Mostly a combination of a few small and stupid things I didn't explain in the original post, plus everyone's responses. I understand a bit more now. Thanks!