How do I select the first column of a multi-column list box (called "List1") for a query.
A single column list box works fine.
Code:
SELECT Tble_Employee.Emp_No, [forms]![attendee_form]![list1] AS SelectedCourse
FROM Tble_Employee;
I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.
All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.
Hi there,
I'm sure I'm not being dim, but I really can't figure how to do this.
I have a list box with two columns. One column (bound) is a name, the other is a unique ID. I need to transfer both the Name and the ID to a second list box (again, two columns).
I can get it to do this with single selections, but not multiple ones, and this is what I need.
Any ideas?
Cheers
I have a list box derived from a two column SQL select. When I try to use the user selected element I only get the first column data value.
In debugging I have tried ...
Ret = MsgBox(Me.List6.Value, vbOKCancel)
and...
Ret = MsgBox([Forms]![MeetingStatus]![List6], vbOKCancel)
Both of which return the selected row, first column value only.
I can find no help on how to return the content of the second column or the selection as a whole.
My form's 10 column combo-box (based on a query) won't break into text cells. Some of the columns contain numbers, some test, some phone #s. All columns show when I click the Combo-box in Form View. Access Help File said to put:
=Forms!Customers!CompanyName.Column(1) in the ControlSource for the text cell. Tried that and was returned #Name? for any of the fields in which I tried it.
My expression was:
=frmSpartanStudentBehaviorLog!Last_FirstUC.Column (3)
The other properties in combo-box are set correctly: ColumnCount=10, Column Widths are correct.
I also tried the expression:
=Last_FirstUC.Column(2) but got the same result #Name?
I'm baffled. As a very new user, I'll appreciate any assistance. Thanks.
I'm not familiar with listbox yet and i want to filter my form using it.
I have two separate listboxes which display "category" & "type" data from the same table.
Here is the situation i wanted for my listbox.
1. Select one data from "category" listbox.
2. Then it will automatically filter data from "type" listbox or it will list all related "type" data corresponding to "category" data.
3. Then select one data from "type" listbox and it will filter all related data on the form/sub-form.
Is it possible to do that way? Can i do it on multicolumn listbox instead of using 2 listbox?
:mad: :mad:
Hi folks,
I wonder how can I list all the column names for a given table using a single query? I know how to do it in SQL using system tables, but no idea how to do that in Access.
Thanks.
I am creating an absenteeism database which has the following tables:
tblEmp - PK - auto number, EmpId, First Name, Last name
tblFunction - PK - FID (autonumber) has departments
tblTL - PK TLID (autonumber) has list of team leaders
tblRelated - pK - Id (auto), EmpId, TLID, FId as long integeres
tblCodes - CodeId (auto), list of absenteeism codes like late, mia, etc
EmpLeave - pk - autonumber, empid, codeid, hours, date of absenteeism
I have a Qry_Master which just joins all the information together as it gets updated on a monthly basis
Now, I am trying to create a form where the user has option to select one or more tls and one or more codes and when they hit the button, it should come up with all emps that have those codes and report to the team leader selected.
In my form, I have made both my list boxes as multiselect and i have Qry_frm that is a parameter query but when i run the button nothing happens and i cant seem to figure it out. I have attached the sample database to this thread.
I have 7 multi-select list boxes, which I want the user to be able to select multiple items and have it filter a subform by what is selected. The subform will not be visible.
Here is the code I tried for filtering my subform (which is throwing an error when I call it).
Private Function MasterSearch()
On Error GoTo Error_MasterSearch
Dim StrgSQL As String
Dim WhereClause As String
StrgSQL = "SELECT * FROM MasterTbl"
[Code] ...
The above code was to test just 1 of the 7 listboxes. When I call it on click for the command button, it throws an error about the "Call MasterSearch".
i create a project for salary company , i have a list box and have 6 columns & i have 1 textbox , i work with code and a listbox is the query know i need the value of textbox is equal with the column 1 from listbox how to do it ? like when i run form need to be enter the code when entered the code in listbox is equaled with the textbox .another question , in the list box i have one column it is about salary i want the total of salary.
View 4 Replies View Relatedwhy =listboxname.column(0) in the control source of a textbox will give me the value "#name?" ? I've used it successfully on another form..but for some reason its not working for me.
View 6 Replies View RelatedAllen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
[Code] .....
I have a combo box with 5 columns. When I click it I want to display columns 1 to 4, together with the headings. But after making my selection, I want column 5 stored in the field.
How can I do that? Currently it is storing column 1.
Thanks,
Dave
How can I get a form to have multiple colums.
View 2 Replies View RelatedI have a reporting need that I believe (hope) is not unique
My customer needs to print out information about a customer that is compused of information specific to the customer (keyed on customer ID) and sets of information about their purchases in different categories (food, cars, clothes and pets). Each of these categories has different identifying characteristics. (These are represented by subReports on the report).
No problem printing out the report looking like this
-------------------------------------------------------------------
Customer Name - Mike Lester
Custoner Address - 101 Main St.,
AnyTown,CO 11111
Food
Beets 10 lbs $1.25 Green
Lettuce 1 lbs $ .78 Green
Bananas 20 lbs $ .33 Yellow
Cars
Mustang 1966 $23,888
VW 1955 $222
VW 2002 $23,000
Cadillac 2004 $44,999
Clothes
Belts 5
Shirts 9
Socks 11
Pants 5
Hats 1
Pets
Goldfish 2 Fish
Beagle 1 Dog
Great Dane 3 Dog
Cocateil 2 Bird
Macaw 1 Bird
------------------------------------------------------------------------
The problem is the customer wants the report to use the entire page. I tried positioning the subReports in two columns (left and right) but the problem is based on the number of items in each subReport the report ends up looking dumb and wasting paper (also it doesn't look very professional)
What I want to happen is the report to print as many subReports as it can on the left column (without leaving any hanging orphans) then start printing then next subreport at the beginning of the right column. See example below: (Sorry about the ~ Symbol being used to represent a space, for some reason the text box wouldn't save my formatting)
-------------------------------------------------------------------------
Customer Name - Mike Lester ~~~~~~~~Clothes
Custoner Address - 101 Main St., ~~~~~~~Belts 5
AnyTown,CO 11111 ~~~~~~~~~~~~~~~~~~~~~~~Shirts 9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Socks 11
Food ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Pants 5
Beets 10 lbs $1.25 Green~~~~~~~~~~~~~~~~Hats 1
Lettuce 1 lbs $ .78 Green
Bananas 20 lbs $ .33 Yellow ~~~~~~~~Pets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Goldfish 2 Fish
Cars ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Beagle 1 Dog
Mustang 1966 $23,888 ~~~~~~~~~~~~~~~~~~~Great Dane 3 Dog
VW 1955 $222 ~~~~~~~~~~~~~~~~~~~~~~~~~~~Cocateil 2 Bird
VW 2002 $23,000 ~~~~~~~~~~~~~~~~~~~~~~~~Macaw 1 Bird
Cadillac 2004 $44,999
------------------------------------------------------------------------
This is probably the longest question of the day. Does anyone have any
ideas or examples. I am hoping that this doesn't involve any massive VBA
or using Word. There are 200 other reports, and the customer doesn't currently use Word.
The entire application is 100% Access.
Hey buddays,
I'm not sure what the definition of this type of concatenation is, so I'm having trouble searching for it.
I have three tables - one is tblNames (with ID, first_name, last_name) with 25 records, and tblStates (with StateID, state_name, state_abbrev) with 50 records, and the requisite join table between them - tblNamesState (with autonumber and the two FKs), because some of the folks in tblNames can have locations in multiple states. Good enough.
But now it's output-to-spreadsheet time, and I want to run a query that will give me 25 records, and concatenate the location records in a single row. An example would be:
first_name | last_name | state_name
John | Johnson | AL, AK, TX, FL
Eddie | Edwards | MA, TX, FL
Sally | Smith | VT, WA, CA, TX
How do I concatenate records from a single column?
I have a query which select the records who have a certain criteria.
The result is something like this.
A 0012345
A 0032432
Note that the records that have this criteria will be smaller or equal than 2.
I want the query to give me this answer instead.
A0012345, A 0032343
(in the same field that is).
Is this possible, if so can someone help me. My email address is russcami@yahoo.com
Thanks
I wish to generate a new query consisting rows that I have selected in a multi colum list box. May I know if it is possible to work? I am totally lost now
View 10 Replies View RelatedI have combo boxes that contain attorney names and phone numbers. The attorney name is what is filled into the field when a selection is made from the combo box into the form. The phone number is just there for the occasions that the person managing the data in the database needs to call that attorney. Just a quicker way instead of having to go to another form to find the phone number. I created a query to combine the first and last name to one field (FullName) and this is where the combo box gets it's info.
When I run the query, the phone number is shown formatted as I want it to ((###)###-####), but in the combo box in the form, the phone number has no format to it. What I need to do to get the phone number to show up formatted in the combo box list?
I want to create the date from multiple columns to single colums. Just for example
table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013
Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013
Now i want to make one query, which can you the date as follows when we give criteria = apple
Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import
Means two dates from different table into one query column... One way in my mind to make one table for both tables.
Is there any way I can use data in the row as a column header ? I only have 1 row of data which i want to use as a header to I can link it to other tables/queries.
(Access 2007 query)
I cannot seem to find a way to upload several rows of data to one column in Access!I have about 9 columns already in the table which have 900 rows each. These include names, numbers, address etc.I was asked to add a column on next to the 9 columns with corresponding User ID such as 0093457FX (Which I really do not want to add manually).Every time I try to upload an excel file the data either goes below the current data from the 9 columns on the new column and does not match the current data set, or it does not show up at all.
View 4 Replies View RelatedI have two listboxes. One for Activities and the other for Organizations.
I set the Org listbox to be multi-select so I could run a loop on that listbox to join multiple Orgs to a single Activity. That part works well.
To do so, I am using the bound column (which is the pkey value) from the multi select listbox, and on the single select listbox I'm using the ListboxName.value to gather the pkey for the "1" side of this 1:Many series of inserts.
Now... I want to use one of the other-than-bound-column value from the multi-select listbox, but I don't know how. When setting a value I only know of the use of :
Code:
ListBoxOrganizations.ItemData(varItem)
and I don't know how to do the equivalent of
Code:
ListBoxOrganizations.ItemData(varItem's column(2).value)
yes, I just made that up, but I hope you get the point. Meaning, as the loop cruises the ItemsSelected on the multiselect I'd like to use other than the bound column when setting variables.
I've tried using the column property to then cruise to the proper record in the listbox, e.g. :
[code]
txtCollaborationDesc = "Collaborate " & ListBoxOrganizations.Column(2, varItem) & " with " & listboxActivities.Column(2)
[code]
But this seems to return the column(2) value of the first record loaded into the list box, as if the varItem piece is being ignored. I believe the loop is working properly, as when the inserts are happening correctly with the bound column of the multi-select list is correctly present as an Fkey in the resulting child records.
I just can't get any other column's value for some reason.
MS Access 2010 and this is an accdb.
So I have a list box that lists organizations. I recently changed the list box type to extended multi select. On the same form, I have a button that opens a new form where the user can input contacts for each organization. When the list box was not multi select, the expression [forms]![media]!
[List30] made the default value of one of the fields in my contact form the bound column from the selection in the list box. However, now that the list box is multi select, the contact input form does not seem to be able to get the value from the bound column in the list box. When multi select is turned on, is the bound column stored differently.
To even get the contact input button to work, I had to change the code from:
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contactsqry"
stLinkCriteria = "[Organization ID]=" & Me.List30
[Code] .....
HELP :confused:
Hi,
I need to copy 5 columns in an Access table into a single column in Excel. How can I do this?
Pictures To Help explain below:
http://uploads.savefile.com/users/uploads/1_154.jpg 261kb
http://uploads.savefile.com/users/uploads/2_154.jpg 192kb
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;
Crosstab query did the trick..