Hi there?
I have a PARAMETER QUERY for selecting a certain records on user request, works fine currently; But on every new table I have to set the parametric properties manually inorder for my users to use it. I want it be automatic, just after table selection there should be a way of setting a parameter without going to the design view or whatsoever. The tables can be selected from combobox.
How can I achieve this?
any hint please!
14 3104
If you're doing this in through form's combobox, then you'll need to change the query criteria pointed to the form's combo box ([Form]![<form name>]![<combo box name>])
If the above method is not understandable, please tell us more details on what have you done so far. Pasting code (with CODE TAG) would be better if you are not sure how its done properly
Thanks! What you have explained works even in my db, but in my db I have two tables set as example, the user of this DB will have their own table exported from external sources; After his export he want imediately start using the resources without calling somebody from remote area for assistance on change the query criteria pointed to the form's combo box ([Form]![<form name>]![<combo box name>]), it doent sound good.
What if we build this SQL (query criteria) in a form for whatever selection of a table from combobox.
I have a Combobox shows a list of TablenamesAndQueries in the db, (the queries doesnt have to have the Creteria). The selection criteria should be made in the forms through SQL or VBA.
How could this be achieved?
Please help
I'm not understanding quite well on what you tried to say...Can you tell me more about what is this external source? is it another access database table or some other database files? If so you can simply make a link table to that file source, so whenever he gets the updates he doesn't need to change things again again and again. But exclude sources such as SAP, as they do need manual export first somehow.
On the other hand with more VBA parts, you can predefine a set of SQL code, then simply ask an input of the new table name (if structure is the same), and put this name into the FROM clause to run the query at the end as a whole.
Yeah!
Its a kind of Access Tables from another DB.
Now how to create this linktable to that source? so whenever he gets the updates he doesn't need to change things again again and again
I might get lost if exposed alone to VBA-JUNGLE bymyself.
Please help in both cases!
compare to other programming languages such as c++, VBA is simply a small forest.
To create a linked table, I had replied that in your other question thread, see if it works.
The VBA part I mentioned requires to work with the link table, as soon you have the new table linked (with different names), then you have everything prepared in the SQL first except the FROM clause table name. Once the user puts in the new name of the table, everything will go automatically to finish the job.
That thread could work on exporting Exel2007.
The SQL and VBA are the ones looking for.
Let me be clear!
I have set Connection via ODBC Connection String as follows: - Sub openDB_DAO()
-
Dim db As DAO.Database
-
Dim dbName As String
-
Dim c As Container
-
Dim doc As Document
-
dbName = InputBox("Enter a name of an existing database:", "Database Name")
-
If dbName = "" Then Exit Sub
-
If Dir(dbName) = "" Then
-
MsgBox dbName & " was not found."
-
Exit Sub
-
End If
-
Set db = OpenDatabase(dbName)
-
With db
-
' list the names of the Container objects
-
For Each c In .Containers
-
Debug.Print c.Name & " container:" & _
-
c.Documents.Count
-
' list the document names in the specified Container
-
If c.Documents.Count > 0 Then
-
For Each doc In c.Documents
-
Debug.Print vbTab & doc.Name
-
Next doc
-
End If
-
Next c
-
.Close
-
End With
-
End Sub
Now how can I go around this to retrive tables from this Connections and set the parameters so that to link to a Criteria Query?
As from the other post we talked earlier, the processes you need is to create link table with Excel. So first I'm point out the thing you need to do, I'll be back to post some example codes later. - Ask user to give input of their excel file
- Check if this worksheet name exist in the link table, if yes remove it
- create a new link table with that user's excel file
- finish the job.
Yes! We are heading the same direction!
It can be database or just excel sheet, all have the same meaning for me!
If you give one I will finish the next
I'll just simply post the major part you need, you'll sort the rest which it won't be too difficult. Make sure you place them as a module in vba
1. So we're asking for an user input for a file. - Public strFilePath As String 'This will first from your user input things
-
-
Function XlsPath() As String
-
-
Dim dlg As FileDialog
-
Dim VrtSelected As Variant
-
-
'Set up the File Dialog for FILE picker
-
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
-
-
With dlg
-
-
'Allow user to make multiple selections in dialog box.
-
.AllowMultiSelect = False
-
-
'Set the title of the dialog box.
-
.Title = "Please Select Database File"
-
-
'Clear out the current filters, and add our own.
-
.Filters.Clear
-
.Filters.Add "Excel Workbooks 2007", "*.xlsx"
-
-
'Set location the dialog will show to database's root directory
-
.InitialFileName = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
-
.InitialView = msoFileDialogViewList
-
-
'Show the dialog box. If the .Show method returns True, the
-
'user picked at least one file. If the .Show method returns
-
'False, the user clicked Cancel.
-
If .Show = True Then
-
-
'Loop through each file selected and add it to the list box.
-
For Each VrtSelected In .SelectedItems
-
XlsPath = VrtSelected
-
Next
-
-
End If
-
-
End With
-
-
Set dlg = Nothing
-
-
End Function
From here, you'll need to think of some ways to extract the file name for step 2 from XlsPath
2. Check if the name exists already in the table list - Dim db As DAO.Database
-
Dim td As DAO.TableDef
-
Set db = CurrentDb
-
On Error Resume Next
-
Set td = db.TableDefs(strFileName)
-
If Err.Number = 0 Then
-
MsgBox "Table found."
-
db.TableDef.Delete strFileName
-
Err.Clear
-
Else
-
MsgBox "Table not found."
-
Err.Clear
-
End If
-
-
Set db = nothing
3. Create new linked table that user selected earlier - Dim db As Database
-
Dim linktbldef As TableDef
-
Dim sourceTable As String
-
-
sourceTable = tblName & "$" 'Get the worksheet name you want to link
-
-
Set db = CurrentDb
-
Set linktbldef = db.CreateTableDef("tmptable") 'create temporary table definition
-
linktbldef.Connect = "Excel 12.0;HDR=YES;IMEX=2;DATABASE=" & FilePath 'set the connection string for Excel 2007
-
linktbldef.SourceTableName = sourceTable 'attach the source table
-
db.TableDefs.Append linktbldef 'add the table definition to the group
-
db.TableDefs.Refresh 'refresh the tabledefinitions
-
-
linktbldef.Name = sourceTable 'rename the tmptable to original source table name
4. From here then on you'll on your own trying to get the stuffs working. Good luck.
If you run the first Code, it gives error and point to the dlg As FileDialog:
User defined-Type not Defined.
I couldnt go around that error!
Just forgot, you need to reference the Microsoft Office 12.0 Object Library
In the VBA menu, tool-->reference, then find office 12.0 object library
You also need other libraries in the reference to get all your stuffs work. Look around and see.
Well it works after setting the Microsoft Office 14 Object Library, but it just open the dialogbox with .*XLSX* file format but it cannot allow me to select the desired sheet in a Spreadsheet and yet nothing happens after Click-OK; Only dialogbox dissapears. Assume it opens Successfully!
My question is How to set the newly opened .xlsx document to my query criteria without entering them mannualy?
There are many more to complete, as I told you earlier those are the major parts, not 100% of the whole picture, and as for that it is then your responsible to figure it out. This is the furthest hint I can give you, unless you actually looking for someone to do the work for you.
If you look carefully to the codes, you'll notice why it just look for .xlsx files, you can then put other more excel extension files to it if you like.
And look clearly on how the function is defined, the function itself actually return the path of the file, this is where you work it out on how it can be store as well.
After that, it is another part of the code, which you will be read through the excel file, and list out the worksheets available in it or look for a specific one you like. Below will be the last bit of code about listing worksheets from the file, make yourself work around with the module function reading excel file. - Dim xl As Excel.Application
-
Dim xlsht As Excel.Worksheet
-
Dim xlwb As Excel.Workbook
-
-
Set xl = CreateObject("Excel.Application")
-
Set xlwb = GetObject(FilePath)
-
-
For Each xlsht In xlwb.Worksheets
-
'lstShts is a listbox item on a form
-
lstShts.AddItem (xlsht.Name)
-
Next xlsht
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Greg |
last post by:
I am trying to set a perameter for my query but keep getting an error: The
Microsoft Jet database engine does not reconize '' as a
valid field name or expression. The only difference from other...
|
by: Cory |
last post by:
When I run the subroutine testEmailContacts the msgbox says that there
is only 1 record. The sql for qyEmailContactsQyCard is below. There is
over 3000 records in the table "tbl:Contact". What am i...
|
by: carrionk |
last post by:
Hi,
I have created a Subform which SourceObject is a parameter query.
This is the Query:
Qry Name:80IsscomProduct
SELECT *
FROM Isscomp28
WHERE Like ;
|
by: biljana |
last post by:
Hi!
Could somebody help me to create simply parameter query?
I need query like this:
select from table_name;
column_name should be parameter from combo box or user can type column name.
|
by: Haas C |
last post by:
Hey all,
I created a Query (in Design View) which asks the user for an "As Of
Date" which would
then display relevant data. I put in the
Criteria Row of the Query Design for the Date field. Is...
| |
by: deveshparekh |
last post by:
I have created a query asking to calculate a third number using two fields in the query. However when I run the query I get a enter parameter query box asking me to enter information on one of the...
|
by: GladGad |
last post by:
I am not a real well versed query writer and therefore am having some problems figuring out how to write a parameter query where I want to input a partial name. I have tried a few different things,...
|
by: Kissi Asiedu |
last post by:
How could I create a parameter query that will return values that partially
or whole match to a user input. For instance; if a user inputs "stu", it
should return values that have "stu" in it. Like...
|
by: brat33 |
last post by:
I am trying to modify some code to create a mail merged label document within Word 2007, using a Access 2007 Parameter Field. My issue comes about when I cannot see the Parameter Query within the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |