[X]My Assistant

[ozop102]

Hello everyone, I have seemingly simple idea but I lack the right amount of loop knowledge to accomplish it.

I need to loop through all available import and export specifications and create a list box of each one that exists (Access.CurrentProject.ImportExportSpecifications(i).Name)

I then want the user to to be able to choose the desired export or import from a list. Then I can run DoCmd.RunSavedImportExport "Name" to execute.

Im not strong with loops and any help would be very much appreciated. I need help looping through to make the list and then putting that list into a window the user can select. Thanks again for everyone time.

-Mario
This post has been edited by ozop102: Aug 28 2019, 10:03 PM

[isladogs]

Details of all saved imports and exports are stored in two system tables: MSysIMEXSpecs and MSysIMEXColumns
So all you need to do is run a query on one or both tables to get what you need.

For example

CODE

SELECT MSysIMEXSpecs.SpecName, MSysIMEXSpecs.SpecType
FROM MSysIMEXSpecs;

Use this as your listbox row source.

NOTE
Like all system tables, these are hidden by default.
Handle with care as the records in these two tables are editable

[MadPiet]

Interesting... I did it the wrong way, but it worked:

Dim i As Integer

For i = 0 To Access.CurrentProject.ImportExportSpecifications.Count - 1
AddItemToEnd Me.lbxExportSpecs, Access.CurrentProject.ImportExportSpecifications(i).Name
Next i

[ozop102]

Ok, testing out both, I could not get any information to show from MSysIMEXSpecs, the query worked but was blank.

based on my database design (im launching the code from a switchboard) I had to make some changes and I used the loop idea to Frankenstein a working code. This is me reverse engineer alot of existing code so not sure if there are better ways of doing this. Also with the loop idea, AddItemToEnd was giving me an error.

The first function I put in was this:
------------------------------------------------------------------------------------------------------
Public Function InitiateSavedIE()

'<<<<<<<<<<<<<<<Part One(1) of Select Saved Import/Export>>>>>>>>>>>>>>

Dim ie As ImportExportSpecification
Dim arr() As Variant
Dim x As Long

DoCmd.OpenForm "SelectIE"

For Each ie In CurrentProject.ImportExportSpecifications
ReDim arr(x)
arr(x) = ie.Name
x = x + 1
Next

For x = LBound(arr) To UBound(arr)
Forms!SelectIE.ieChoice.AddItem arr(x)
Next x
End Function
------------------------------------------------------------------------------------------------------

I did it this way since I need to open the form as I start the function to be able to reference the listbox. Next I programmed the "Ok" button to run the next function.
------------------------------------------------------------------------------------------------------
Public Function SelectImportExport()

'<<<<<<<<<<<<<<<Part Two(2) of Select Saved Import/Export>>>>>>>>>>>>>>
Dim IEName As String

On Error GoTo ErrorHandler

If IsEmpty(Forms!SelectIE.ieChoice.Value) Then
GoTo ErrorHandler
Else
IEName = Forms!SelectIE.ieChoice.Value
End If

DoCmd.RunSavedImportExport IEName

MsgBox "File Import Complete"

Exit Function
ErrorHandler:
MsgBox "Error " & Err & ": " & Error(Err)

End Function
------------------------------------------------------------------------------------------------------

And finally I have it kinda working, it feels like I could do this alot better still.

[isladogs]

That's odd. Are there any saved exports/imports?
If so, open the MSysIMEXSpecs table itself and see if it contains any records of saved exports/imports
Similarly the MSysIMEXColumns table

[ADezii]

1. To be perfectly honest, your Code will never work as intended.
2. Redim will re-initialize the Array arr() and destroy any Data in it unless you use the Preserve Keyword. In your specific case, only the 'last' Import Specification will be listed.
3. Example and Output using Redim:
   
   CODE
   Dim varItm As Variant
   Dim intCtr As Integer
   Dim arr() As Variant
   Dim x As Long
   
   varItm = Array(12, 20, 30, 40)
   
   For intCtr = LBound(varItm) To UBound(varItm)
     ReDim arr(x)
     arr(x) = varItm(intCtr)
       x = x + 1
   Next
   
   For intCtr = LBound(arr) To UBound(arr)
     Debug.Print arr(intCtr)
   Next
   
   
   CODE
   Output: 40
4. Previous example modified using Redim Preserve which will maintain any existing Data within the arr() Array while re-initializing the Upper Bound:
   
   CODE
   Dim varItm As Variant
   Dim intCtr As Integer
   Dim arr() As Variant
   Dim x As Long
   
   varItm = Array(12, 20, 30, 40)
   
   For intCtr = LBound(varItm) To UBound(varItm)
     ReDim Preserve arr(intCtr)
     arr(intCtr) = varItm(intCtr)
   Next
   
   For intCtr = LBound(arr) To UBound(arr)
     Debug.Print arr(intCtr)
   Next
   
   
   CODE
   Output:
   12
   20
   30
   40
5. Hopefully, my explanation was clear enough.

This post has been edited by ADezii: Aug 29 2019, 07:26 AM

[ozop102]

Isladogs, I only had one saved import but it wouldnt show up at all in the specs nor the columns table. I do my work at home in a test database and the. i transfer the code to my work database later on. I tried again on my work database and it did show im, so Im not sure at all why it wasnt working for ke at home.

ADezii, you are 100% right! This was my first time using an array like this and didnt know about preserve. At work I added in the code and u were right. All blank enties and just the last one showed up! Preserve did the trick and its all good now! Thank you both for your help!

[Phil_cattivocara...]

You do not need array to populate the list box

CODE

Public Function InitiateSavedIE()
'<<<<<<<<<<<<<<<Part   One(1) of Select Saved   Import/Export>>>>>>>>>>>>>>
Dim ie As ImportExportSpecification
DoCmd.OpenForm "SelectIE"

For Each ie In CurrentProject.ImportExportSpecifications
      Forms!SelectIE.ieChoice.AddItem ie.Name
Next
End Function