Importing Excel File to Access Form

Question

I have exhausted every resource I can find and before I determine my Access DB to be corrupt I wanted to post this question:

I am working on a tool that will import the data from an Excel form that is to be distributed to a couple hundred individuals. I have created an Access form in parallel and I am trying to create a macro that will auto-populate the Access form fields with the data from the Excel form.

Here is the code I am working with:

Sub Import_Click()
Dim objDialog As Object
Dim CEtarget As Form
Dim CEfile As String
Set CEtarget = Forms("Data Entry")
Set CEsource = Excel.Application
Set objDialog = Application.FileDialog(3)

With objDialog
    .AllowMultiSelect = False
    .Title = "Select Form"
    .filters.Clear
    .filters.Add "*.xlsm; *.xlsx; *.xls", "*.xlsm; *.xlsx; *.xls"
    .Show
    If .SelectedItems.Count = 0 Then
        Exit Sub
    Else
        CEfile = .SelectedItems(1)
        CEsource.Workbooks.Open (CEfile)
        CEsource.Visible = True

    End If

CEtarget.FirstName.Value = CEsource.Range("B14")
End Sub

The error occurs at the following string:

Set CEtarget = Forms("Data Entry")

I receive the following error:

Run-time Error '-2146500594 (800f000e)': Method 'Item' of object 'Forms' failed

Not sure what could be wrong. I did have the code working at one point, but somehow I started seeing that error message.

EDIT:I tested the code while the form was in Design View and it executed without any problems. The error prompt still persists while in Form and Layout View.

The error message is telling you that it can't find a form named "Data Entry" in your database. Double check your form's name. Edit: also make sure the form is open when you run the code. — Tmdean, Aug 22 '14 at 20:16
Thank you for the reply. The form is open when I run the code so I know that isn't the issue. I do agree that the error is due to the form not being recognized, but the name matches the form. I am thinking there is a name setting that needs to be modified in the form properties - I just don't know where to look in Access to find that setting. — KultRussell, Aug 23 '14 at 20:57
I tested this out and your syntax is fine (if the form is open). Are you 100% sure this is the line causing the error? Can you open a new project, import just form "Data Entry" and then try code with just two lines where you declare as forms and then set your form object? If it works fine in a new/empty project, then your corruption theory would have more weight. I'd also try opening form "Data Entry", saving it as "Data Entry2" then try that in your code. If form is corrupt you'll likely need to rebuild it from scratch. — pghcpa, Aug 25 '14 at 03:05
Also, suggest trying same set statement but refer to a new, blank form that you open first. I read this error can be caused by special characters in fieldnames on a form, so if it works with another form you know it's something with "Data Entry" specifically -- then can delete each field until you find which one is offending. — pghcpa, Aug 25 '14 at 03:36
pghcpa, thank you for the comments. Unless I have comlpletely missed the boat on using debug, I am 100% sure that I am being promted with an error at that line. I will give some of your suggestions a try and then probably just recreate the form. It seems like it would take more time to try and keep working this problem then to just create a new one. — KultRussell, Aug 25 '14 at 11:25
do you have any listboxes that are bound to a table? Item refers to an object in a collection, so Item is (most likely) a control on your form. — Sorceri, Aug 25 '14 at 21:01

score 1 · Answer 1 · answered Aug 27 '14 at 00:18

1

I never did figure out what the problem was, but I made a copy of the form and referenced that one instead. After that the code worked fine.

answered Aug 27 '14 at 00:18

KultRussell

11
2

I know this is old, but did your database read one file at time, forcing you to load each one separately? or did it read the whole 100 at the same time? – EmRoBeau Dec 20 '17 at 12:50

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

For future reference, decompiling the database will often fix this issue. Access will often get these "minor corruptions" when you're doing a lot of vba development. I think it has something to do with the way it stores the binaries. Be sure to also compact and repair after decompiling. That will remove any left over junk binaries. They cause the file size to bloat after a while.

edited May 23 '17 at 10:33

Community

1
1

answered Aug 27 '14 at 00:39

RubberDuck

11,933
4
50
95

Thank you for the response. I did try to decompile the data base and I compacted and repaired it when finished. It did not fix the issue, but I can't be sure if I followed the steps correctly. – KultRussell Aug 28 '14 at 01:47

Importing Excel File to Access Form

2 Answers2