My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 8 Joined: 28-August 19 ![]() | 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 |
![]() Post#2 | |
![]() UtterAccess VIP Posts: 1,883 Joined: 4-June 18 From: Somerset, UK ![]() | 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 -------------------- |
![]() Post#3 | |
Posts: 3,364 Joined: 27-February 09 ![]() | 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 |
![]() Post#4 | |
Posts: 8 Joined: 28-August 19 ![]() | 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. |
![]() Post#5 | |
![]() UtterAccess VIP Posts: 1,883 Joined: 4-June 18 From: Somerset, UK ![]() | 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 -------------------- |
![]() Post#6 | |
![]() Posts: 2,700 Joined: 4-February 07 From: USA, Florida, Delray Beach ![]() |
This post has been edited by ADezii: Aug 29 2019, 07:26 AM |
![]() Post#7 | |
Posts: 8 Joined: 28-August 19 ![]() | 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! |
![]() Post#8 | |
Posts: 368 Joined: 2-April 18 ![]() | 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 -------------------- Please forgive in advance my horrible English. |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 9th December 2019 - 07:23 AM |