Hi just started working for a Company and I found they have tons of
databases but no structure or standard, so I would like to build a tool that will go thrue a list of databases and get all the table names, the items and type. Is there functions in vba that can get me a list of the tables in the database and the get all the items in the database ?
Thanks on any input
looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
Hi,
I wonder if there is a possibility to select a table from a database by using a sort of explorer window.
My problem is this:
I want to import a *.xls file in a table.
I want to be able to select a certain *.xls file from a list (so far no probs) en add it contents to a table selected in a browser window.
A table ofcourse who the exact field structure.
Anyone?
hi,
can anyone please tell wht's the query to check if database exists, if exists list all tables in database (in sqlserver) else raise error
Thanks in advance,
Deepthi
This is a somewhat complicated question to explain, so please bare with me.
I have a form with a ‘list box.’ The box is populated using a Table/Query Row Source Type method. The table I am grabbing the list from is “Department,” where it contains a list of my company’s departments(ie. Accounting, HR, Payroll, etc.).
Now, the only thing this table does not have is the word “ALL.” I need this word in the “List Box” because I want users to have the option to select ALL.
Is there anything in VBScript code wise or in Row Source that I can do to include this word?
I know I can manually go into the Department table to simply add the word, but this is not the point, because there is more to that(I have different list boxes that are grabbing lists from different tables, AND I have to refresh these tables twice a week). Please help.
Thank you.
Joe
I have a form where there are 2 list boxes: Part_List and acbPartList. acbPartList has multiselect enabled and what I want to do is be able to select multipler records in that list box and then press a button to add those selected records into the other list box. Here is my code for my button:
Private Sub addItemButton_Click()
Dim varItem As Variant
With Me.acbPartList_Existing
For Each varItem In .ItemsSelected
Me.Part_List.AddItem (varItem)
Next
End With
Me.Part_List.Requery
Me.Refresh
End Sub
I'm not sure if passing varItem is correct, but regardless, it isn't working because Part_List is based off of a query, its not a value list.
how to get the sum of column 2 of a list box total bags is in the second column, i only want the total of bags of the ones selected
I can get the sum of all the boxes but only want highlighted ones
Public Function SumListBox(sForm As String, _
sCtrl As String, iColumn As Integer) As Variant
Dim frm As Form
[Code]....
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
Hi,
I would like to be able to select multiple items from a dropdown list like we often see on web forms. The kind where you hold down the control key to select up to 5 items. Is this possible on an access form? If so, how would do we implement, and how is such data be stored?
Thanks!
~Bruce
I am using the following code in a textbox (CountryID) to show a country name (CoName) from a table (tblCountry). If the comboBox (cmbCoName) in current form (frmCountry) has no entry then CountryID remains blank, great. If I use the form to add a new entry then CountryID flashes because it does not recognize the entry. Can I use "or" to add another condition in the IIF statement so that it allows the "not in the combobox list" entry?
Code:
=IIf(IsNull([txtCoName]),"",DLookUp("CoName","tblCountry","Country_ID = " & [Forms]![frmCountry]![txtCoName]))
I have added a list box to a form with Single Selection mode on. Its purpose is to improve the interface. It contains items that represent all records. Clicking on the list box causes the form to jump to another record.
The problem is following: if I click on the List Box, it clears the selection and nothing is highlighted but it jumps to correct record. When I use standard record selection buttons, it highlights the correct items.
I read the index of selected item from .ListIndex property because Selected() does not work in a Single Selection mode. However, this is read-only property and I cannot use this to highlight the item back from VBA. But when I use Selected() it is not working. I mean when I click again on the same item it's selected. It's weird. I attached a simple database file with this problem.
The second problem is, when using standard record selection buttons, access iterates through all records and then jump to empty one. That is not like a new record. I don't know which event to use to control this situation. I would like to deselect all items, let user enter the data and re-query the List Box with a new record.
I have the following tables:
tblOrderDetailsKeyItemIdOrder QtyPO#001A204001002B204001003C304001
tblTransactionsKeyTransDateItemIDReceivedPO#0011/1/2014A1040010021/2/2014B2040010031/3/2014A54001
I also have a table that list the items and on that table the column ItemID is the key.I want to create a query that will list all items ordered on a PO# and how many have been received so far. With that I will calculate the outstanding qty. I want to see:
qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B202004001C30030
But all I see is this:
qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B20200
It is missing item C because I have not received any yet so there are no records in the Transactions table for it to select.
I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.
View 5 Replies View RelatedCan some one tell me why, on the attached database example, I can select multiple items from list boxes
"TipoCliente" and "FaixaEt" through "frmClientes" form but they are not passed forward to table "tblClientes" ?
Try to onpen attached database example and use form "fmrClientes" and select multiple items at
"TipoCliente" and "FaixaEt" list boxes by entering them with mouse click and Ctrl key pressed .
Select also single item from
"NivEns" Combobox and write anything on "Nome" and "Apelido" fields .
Go to the table "tblClientes" and you can see text fields "Nome" and "Apelido" and also the item "NivEns" from Combobox "NivEns" are all there but not the items you selected from "TipoCliente" and "FaixaEt" list boxes .
What is happenig ?
Lots of thanlks
Miguel
In appendix is .mdb file with this thema.
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery
Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
I want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.
Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.
Code:
Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer
For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)
[Code] ....
That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.
I want to limit the results in a query to the selected items on a list box - how do I do this?
View 4 Replies View RelatedI am trying to find out if there is a way to track the order in which items are selected from a list. I am a dabbler and any keyword searches that I can think of don't bring up what I am looking. So, here is what I am trying to do:
I have a table of symptoms with 3 fields (ID, Category, and Symptom).I have a combo box that will allows the user to pick a category (using select Distinct on category field). I then have a list box populated with all the symptoms that have a category of whatever the user selected. The problem I am having is that I need to somehow track the order in which the user selects symptoms and then save that order for future reference and to be printed on a report. The order is important because the most severe symptom needs to be listed first.
I have a list box on an unbound main form, which contains a rowsource consisting of files in a certain folder. The listbox is unbound
when I change an item in a subform, the listbox should update to show different items from the same folder.
Now it is updating correctly, so the rowsource appears to be correct, , but then the listbox behaves strangely - with the first item being sort of permanently selected - or at any rate - strange selection behaviour
out of interest, changed it to a combo box and it works correctly. so there must be some difference between the two?
After investigation, it might be this : [URL] ....
The appearance is similar to what is described in the thread.
although I have played with the strings to get them shorter without getting it work correctly. very strange
if I run the code to update the listbox from the subform, either directly, or by running as sub IN the main form, it produces this strange behaviour. If I run exactly the same code directly IN the main form, it seems to work properly.
I have a list box that allows multiple selections [Inventory]. I also have a combo box that has multiple selections [Shows].
Right now, user selects from list box and from a combo box and clicks a button. On button click, the items from the list box are associated with the PK from the combo and stored in a junction table. This allows me to quickly associate many inventory items to one show.
I realized that there I currently have no way to prevent duplicate Inventory+show records in the junction table besides having a composite key. This would be fine except no records get inserted into the junction table if there's a duplicate entry.
Ideally, I think that the user should select from the combo box [Shows]. This should narrow down what shows up in the list box [Inventory] in a way that Inventory items already associated with the show are not displayed.
If I have 10 Inventory items and Inventory items 1-5 are already associated with Show 1; after I select the combo box, the list box only displays Inventory items 6-10.
Here's the associated code
Option Compare Database
Option Explicit
Private Sub cmdAddRecords_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
[Code] ....
I have the code below and am trying to have a form allow Text Box to become visible based on selected items from a List box. Why I am getting the error listed?
Compile Error: Invalid Qualifier
Code:
Private Sub specific_opt_Click()
Dim users As Control
Dim ctrler As String
Dim xx As Long
If Me.specific_opt = True Then GoTo 169
[Code] ....
I have the below code behind a form so that a combo box will display a specific list of items based on the data in another combo box on my form.
I have two copies of this same form for two different departments. One of the forms works like a dream. However, when I copy that form, change the name, and update the code as pictured below, the form is asking for a parameter FROM MY ORIGINAL FORM and will not requery the combo box. I can't figure out why...there is no reference to the original form in my VBA as you can see below. I tried deleting the form and re-creating it, I tried deleting the code and re-typing it to no avail.
Private Sub cmboType_AfterUpdate()
Me.cmboAction.RowSource = "SELECT tblStatusList.Status FROM tblStatusList WHERE (((tblStatusList.Department)=[forms]![frmInquiryFraud]![cmboType]));"
End Sub
I have a list with 50 items in it, I would like to select some of these items by pasting in a coma separated list, is this possible?
View 12 Replies View RelatedI have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.
I have tried:
Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)
but when skipping the first item in the listbox it is still passed as into the textbox.
I have several comboboxes in my database from which users select values. However, in most cases, I do want to give them the option to add an item that does not appear while simultaneously updating the underlying table which stores those dropdown values. I have made use of the builtin "allow edit list items" feature of Access 2007 but the form always opens on the first record of the form on which they are to add the new item. Is there a way to specify that it should open in Data Entry view apart from using VBA?
View 4 Replies View RelatedI've been running into this problem in Access. I have records which have more than 255 fields. The easiest solution would have been to get a table with more than 255 fields, but I can't do this, so I've had to split up the tables. The next easiest solution would be if there is a way to link the tables--I have a data field "IntAccNum" identified as the primary key in each table--so that when a new row is formed in one, it is formed in the others. This wouldn't waste space, since any row in the first table would necessarily need a corresponding row in other tables. Is there a way to do this?
If not...
The problem I run into is when I populate a row in the demographic table and then try to fill a corresponding row in another table. I use a command button to for a macro to "OpenForm", and then try to "SetValue" of the appropriate field in the second table. This adds a new row in the second table. This does let me populate the correct row of the second table the first time I try for each row. However, after this initial population, when I switch back and forth between the first and second table, I get an error in access since obviously I can't keep adding a primary key with the same value in the second table.
Do I need to do some sort of query to see if that row exists in the second table already. Or is there some simple solution to what must be a very common situation that I am overlooking?