Open table from list

[mlrucci]

Stumped! I have a db this is importing 1,000s of rows from an excel spreadsheet. There are times that there are import errors with this import that causes the creation of an ImportError table. I would like to allow the end user to open the ImportError table to visualize the issue and then delete it without accessing all the other tables in the db. I have created a list that shows any active ImportError tables. How do I write code so that they can open the table, after review, then delete the table? All the code requires that I place the name of the table to open or delete. The qry below creates a list of ImportError tables.



SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "*ImportErrors") AND ((MSysObjects.Type)=1));

Can anyone help with code to open the selected table. I know the following isn't working and understand why, but do not know how to direct it to the selected table.

Private Sub List0_Click()
Dim strCriteria As String
strCriteria = "[Id] = " & Me.List0.Column(0)
DoCmd.OpenTable strCriteria
End Sub

Any help would be appreciated

[Micron]

Aren't you supposed to pass the table name as an argument, not its ID?

[mlrucci]

The only reason I am trying the ID is due to the table being on a list. Docmd.OpenTable requires a specific table. I am importing approx 50 excel spreadsheets, which could potentially create multiple ImportError tables. Need it to be specific to each import so that they can determine what adjustments in the spreadsheet that need to be made. I do not want them to access all the tables, only the ImportError tables

[Micron]

One of us isn't understanding. Or perhaps both.
You have to pass the table name. You have it in column(1), not (0)? My interpretation is that the specific one you want is picked from a listbox (or combo) but you're referencing the wrong column.

[orange]

This query will give you the tableName of the table(s) with import errors in field TblNameWithErrors

SELECT MSysObjects.Id, MSysObjects.Name
, MSysObjects.Flags
, MSysObjects.Type
, Left(MSysObjects.Name,Instr(MSysObjects.Name,"_Import")-1) as TblNameWithErrors
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "*ImportErrors") AND ((MSysObjects.Type)=1));

You can adjust how you use TblNameWithErrors (eg rowsource of Listbox). You may not need Flags or Type or ID.

If this isn't what you want, then please provide more info/example.

[mlrucci]

You are correct. My apologies, I am not explaining myself correctly and have attempted what you are asking by using the name of the table as strCriteria. I receive the following error. Error 7874 MCDB 1.0 Can't find the object [Name]=Recorded Loan Details $J:J_ImportErrors".

Private Sub List0_Click()
Dim strCriteria As String
strCriteria = "[Name] = " & Me.List0.Column(0)
    DoCmd.OpenTable strCriteria
End Sub

I also attempted using the table ID as strCriteria which result in error.

Private Sub List0_Click()
Dim strCriteria As String
strCriteria = "[Id] = " & Me.List0.Column(0)
     DoCmd.OpenTable strCriteria
End Sub

[mlrucci]

ImportError.accdb

Here is a simplified db if this helps

[mlrucci]

Orange, I used your code for the list qry. I continue to get the same error as above. It cannot see the table in either code.

SELECT Left(MSysObjects.Name,InStr(MSysObjects.Name,"_Import")-1) AS TblNameWithErrors, MSysObjects.Id, MSysObjects.Name, MSysObjects.Flags, MSysObjects.TypeFROM MSysObjects
WHERE (((MSysObjects.Name) Like "*ImportErrors") AND ((MSysObjects.Type)=1));
 or 
SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "*ImportErrors") AND ((MSysObjects.Type)=1));

I am using the VBA codes respectivly

Private Sub List0_Click()Dim strCriteria As String
strCriteria = "[TblNameWithErrors] = " & Me.List0.Column(0)
    DoCmd.OpenTable strCriteria
End Sub


or 

Private Sub List0_Click()
Dim strCriteria As String
strCriteria = "[Name] = " & Me.List0.Column(0)
    DoCmd.OpenTable strCriteria
End Sub

My apologies as I am novice and self taught with access. Not sure if I am understanding you when stating "You can adjust how you use TblNameWithErrors (eg rowsource of Listbox)" as I have the Listbox as a query in the rowsource.

[Micron]

I would say that it's you that's not following.

You are supposed to pass the name of the table - nothing else. So

DoCmd.OpenTable Me.List0.Column(0)

[mlrucci]

You are correct, it was ME not following. LOL Thank you very much for the help!!!! sometimes I make it hard for myself!! Appreciate the help greatly!

[Micron]

NP Glad you got it solved

[mlrucci]

Now that you have clarified the ability to open the tables requested, I am attempting to allow the end user to visualize the table for corrections and then delete the table once they have made the corrections in excel spreadsheet. Once the corrections are made, they will re-import from the spreadsheet. Because the end user will not have the tabs at top, they will not have the ability to close the table. I would like to open the table in a datasheet form to allow the end user to review in datasheet, close and delete the table. Ideas on how to create the recordsource for the form to be that of the selected table in my list? I do not want to create multiple forms. In other words, One form to open the selected table from the list. I am struggling with this ability. Included is a simple db with tbl, frm, qry

ImportError_v2.accdb

[Micron]

Before I look, why not
- double click on the list item and open the table
- review and when ready, click button to delete the selected item. But allow multi select in either case or just single?
- part of delete of table would include refreshing the db items if the navigation pane would ever be showing?
- also, refreshing the listbox would be part of the delete routine

Opening table in datasheet view provides control box (minimize/maximize and close button) so I don't get your reference to tabs. If you're going to open a table in datasheet view and show no tab, why do you need a form to show the table? Wouldn't the form just be used to list the tables and provide the interface to open/review/delete? If you must show the table on the form, no problem but you'd need a subform control as well.

[mlrucci]

ImportError_v3.accdbMicron, thank you for your response.

Before I look, why not
- double click on the list item and open the table- I have already set this in motion and have the code written to do this, then new issues arise that will need
- review and when ready, click button to delete the selected item. But allow multi select in either case or just single-​If I understand you correctly, after reviewing the data and making adjustments in the excel spreadsheet, you suggest clicking button to delete individual entries. Is that correct? First, I do not want them to select and delete a record. I need them to do the corrections in excel and then reimport. (Manual labor with different departments. Inefficient at this time, but working on that process) After reviewing the table and making adjustments in the excel spreadsheet, I want the end user to delete the table, not just the selected rows. These unnecessary tables will accumulate.
- part of delete of table would include refreshing the db items if the navigation pane would ever be showing?- navigation pane will not be showing
- also, refreshing the listbox would be part of the delete routine-Once everything is complete, they will click and delete the table.

Opening table in datasheet view provides control box (minimize/maximize and close button) so I don't get your reference to tab-This may be a settings issue for me. I went into options/Current Database/Application Options and disabled Display Document Tabs. This removed my Document tabs as expected. When I then go to the form and click on the list of ImportError tables, the table opens, as expected. The table opens, but I cannot close it. Below are my options. In addition, I would like to make this a popup (will make adjustments once the recordsource issue has been resolved), so need to make it a form and not a table.



I have made a few adjustments to the DB to help explain what I am looking to do. I hope this clarifies my crazy brain. Thanks in advance for your help!ImportError_v3.accdb

[June7]

Where does this Excel data come from? Are these 50 sheets extracts from some other app?