Import Problem

[theseawulf]

Generally, is there any reason forms wouldn't be able to work with new imported data, or anything to allow Access 2010 to show new imported data in the database forms? My database imports my new data to a table just fine, but I'm having to restart Access for the form to begin pulling up the imported entries into the form.

Any help would be appreciated and tia

[CJ_London]

is there any reason forms wouldn't be able to work with new imported data

no

but I'm having to restart Access for the form to begin pulling up the imported entries into the form.

You haven't said what you are actually doing to import the data or whether or not the form is open but if the form is already open, you have to either close and reopen the form or requery it once the data has been imported to refresh the form recordset.

[theseawulf]

I'm trying to import new data and view it in a form rather than typing each entry one at a time. You've answered my question very well though thank you for your help!
Apparently based on your reply, a reason a form may not be able to work with new imported data is that it's open and must be closed to refresh the forms recordset.

If there is nothing that can be done to change this, importing data wouldn't be a solution for multiple users in my database.
I want to import data and have it be viewable in the form so as new data is entered it can be accessed by multiple people.

If anyone knows a quick way of doing this I'd be all ears. Maybe just not meant to work this way?

[Micron]

Depends on what this form is and how it's being used. It may be as simple as requerying the form after the user has performed some action or operation. Closing and reopening is not necessary. Importing has no bearing on this. If multiple users can be editing records, it is really the same resulting effect as importing new data.

[theseawulf]

I wish I could be more specific. I didn't design it unfortunately, I just enter data into it, so I don't know everything about how it works even. It has a form that I use to enter customer information onto a table(name, contact info, why they were interested in contacting us etc.)


Users of the database can use the same form to type in a customer name that they can find on a report after I've entered it. The form already knows how to pull up that customer data and they see it all as if I had just entered it into the form.


I do know the table the form uses where the data is stored. I've matched each field in the table to a php form field that goes into a sql database. I thought if I can export from sql to Access(just via .xls), I could import each customers basic info without having to type them all one at a time. The import to the table works perfectly. But it's just that form!


I don't know how to requery the form and have been closing it and reopening it to access the new imported data.
I haven't started doing it in a live copy of the database because users won't know to close and reopen the form to get to new entries on the table.


I will look around and see if I can find out how to requery the form since you said it could make this work without having to have everyone close and reopen the form after every import.


I feel a lot more optimistic than I did before thanks!



update: I googled about requerying Access forms and it told me to press SHIFT +F9. Seriously?? LOL

[theseawulf]

Will give this a try I think it might get me going thanks to you I appreciate it!
I knew it was something simple I just didn't know how to search for haha
Use code to requery data

You can also use code to requery data automatically. Suppose that you want to requery the data in a form whenever the form receives the focus. You could create a module and attach it to the form's On Got Focusevent, by using the following procedure:

1. Open the form that you want in Design view. If the property sheet is not displayed, press F4 to display it.
2. Make sure that the form itself is selected. Then, on the property sheet, click the Event tab.
3. On the Event tab, in the On Got Focus property, click the Build button .
4. In the Choose Builder dialog box, click Code Builder.
   A new module opens in the Visual Basic Editor.
5. Type Me.Requery, and then close the Visual Basic Editor.
6. Save the form.

[CJ_London]

you didn't read my full thread - I said

or requery it once the data has been imported

but looks like others have told you how to do this

[Micron]

Again, depending on what's going on, there are optional events that could or should be used to requery a form. GotFocus (for the form) is probably the worst you could use.
Create a form with no controls. Code a message box on its GotFocus event, save it, close it, open it. Your message code should fire. Now put one text box on it, save it, close it, reopen it. What happens?

Re-read my first 2 sentences in post #4. Having to employ key press combinations is not a good solution either. Surely there must be a user action, such as saving a record, that can be used as the trigger to requery?

[CJ_London]

if you are using say the transferspreadsheet function behind a button on the form to import the data, all you need is to add me.requery to the line immediately after the transferspreadsheet line.

[theseawulf]

No you were all exactly right. The instructions I found weren't exactly the solution and I know you guys don't know exactly what I'm looking at, but the form has a field that searches the table for the data. So I just went into the fields properties and added a macro on the "On Lost Focus" event. I just clicked on requery and saved the form. Now the form will find the new data after I've imported it.

It's worked perfect in two copies of my database the few times I've tried it.

I will add it to my live copy this weekend and hopefully be importing my data from my online web forms on Monday, rather than copying each customers data one by one from each individual email!(unless there's any issues I haven't discovered yet

This maybe ended up a pretty simple thing to do but hard for me to even ask about because I'm such a noob haha.
Because of everything you suggested I was able to figure out the right field and where to add that and I think it works! LOL

Thanks a lot I wouldn't have got on the right track without this thread!!