Periodic Data Import_Creating Form to Change Coded Path (1 Viewer)

[Rich_Lovina]

Hi there, Using both macros & code, now tired of all macros. I use a form (tied to code which defaults to C:\My Docs), but I would prefer a form to pop-up with previous week's filepath, as outlined here in my first converted macro Module, called GetNewData.

In other words, is there code which can create a form where I change this file path in this code, WITHOUT having to open this module each week?

There are five tables to import, file path being same throughout:

Function GetNewData()
On Error GoTo GetNewData_Err

DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\PUB\XS2925-07-2002.mdb", acTable, "Appointments", "PSAppointments", False
DoCmd.Echo False, "Importing PS Appts"
DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\PUB\XS2925-07-2002.mdb", acTable, "PromotionsNonAdVacs", "PSPromNon", False
DoCmd.Echo False, ""
DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\PUB\XS2925-07-2002.mdb", acTable, "SESEngagements", "PSSESEngagements", False
DoCmd.Echo False, "Importing PSEng"
DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\PUB\XS2925-07-2002.mdb", acTable, "SESPromotionsToAdVacs", "PSSESPromos", False
DoCmd.Echo False, "Importing PSSESPromos"
DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\PUB\XS2925-07-2002.mdb", acTable, "Transfers", "PSTransfers", False
DoCmd.Echo False, "LastRun"


GetNewData_Exit:
Exit Function

Many thanks in advance

 

[dynamictiger]

You could construct a form, with a field in it, called for example txtPath.

To change the code you would manually enter the new path into the text box, and click a button called addpath or something. In the onclick event you would run your code, modified as follows:

Dim strPath as String

strPath=Me.txtPath

DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, "Appointments", "PSAppointments", False
DoCmd.Echo False, "Importing PS Appts"

etc

Alternatively have a look at cmndlg32 ActiveX control this enables you to use the file dialog native to Windows.

 

[Rich_Lovina]

Method or Data Member Not Found?

Dear Dynamic

When I put the code in at the on double-click event for the text box which produces the new path, I get the above error command. I'm not sure why unless perhaps the code must operate off a separate command button?

Any suggestions?
Thanks in advance

 

[Rich_Lovina]

Well I cracked this one within 24 hrs!

Thanks Dynamic. I solved the error code by putting in the name "THeFileName" which was equivalent to Me.txtpath anyway.

The first test of data-input, previously running on 10 macros proved great results. But as I have to make six manual consultancy decisions with the data as it comes in, there are six command buttons, and they handle 22 queries beautifully.

....only one or two bugs left now, phew....

Incidentally you're not anywhere in WA government are you?