Combobox with multiple columns Results

I have a combobox with multiple columns. The first column is the bound column but it has a width of 0cm. The second column has a width greater than 0cm and thus is what is seen.

If I give this comboBox a value eg Me.cbo.value = "Text" it does not show up. I assume this is because it is adding it to the first column. But because "Text" is not in the list of items in the combobox there is nothing to show for the second column.

Thus is there a way to add a value directly to the second column which is not in the list of values for the comboBox.

Thanks guys in advance.

Is there a way to lookup information ina bound combo box based on either columns.

Here is a scenario I have a bound combobox with two columns Column(0), and Column(1) both visible. I want to search the combobox based on either value of both columns. In the words if I search ID it will lookup the value and if I search by name it will also lookup names.

Is there a sample VB script in the after update event of combo box that will accomplish that?


Much appreciated!

Hi everyone, I need some help (obviously!)

I have a simple database for entering sample requests.

In my sample request entry form (tblSampleOrders) I have a combobox that selects from a table with our sales reps information (name, phone & e-mail.)

What I can't seem to figure out seems like it should be very simple:
When I choose a given sales rep in the combo box, how can I copy the two additional fields in the table to two additional fields on the entry form?

i.e...I choose Sales Rep Norm from the combo box. This then copies the saved phone & e-mail for Norm to the Phone & E-mail fields on the Entry form. This information would be saved in the tblSampleOrders fields named RequestedByPhone & RequestedByEmail.

I found another post in this forum that seemed to be on the right track, but when I installed the code it did not work:

AfterUpdate_RequestedBy combo box
Me.SalesRepEmail = Me.RequestedBy.Column(1)(zerobased)
Me.SalesRepPhone = Me.RequestedBy.Column(2)(zerobased)

I appreciate any help you can offer!

Hi All,

I am trying to display multiple columns in my combo box once the selection has been made.

To elaborate.. I have a list of suppliers with all their relevant info in one table (supplerID, supplier name, city, address, phone number, etc). At this stage, some of my suppliers are the same company, but have different warehouses in different cities. They are listed as seperate suppliers as the billing is often different depending on which city it comes from.
So what I am wanting/hoping to do, is be able to display the supplers name AND city in the combo box on the form, once the user has made the choice.

I have no problem getting the combo box to function correctly with all the data I need in the drop down, and I have the combobox bound to the supplerID. The problem I am having is once a selection has been made, I can only get the combobox to display the suppliers name OR city, not both.


To make things easy for the users (plus it helps with the layout of my form), I would really like to be able to display both columns on the form without the need to add a second text box that updates off the combobox.


Thanks in advance for any help,
Lee.

Hi there, a somewhat complicated issue today based on an InventoryTable with multiple foreign Keys.

The basic structure is that a PurchaseOrder is processed and creates multiple items in the InventoryTable. These Items are later packed into Packages, and the packages are later packed into Containers. The structure ends up having one container filled with multiple packages, and each package containing multiple items.

In each Item record there are three foreign keys called PurchaseOrderID, PackageID and ContainerID, so that the record looks something like this:

ItemID – Item Description – PurchaseOrderID – PackageID

the PACKAGE table shows:

PackageID - ContainerID

I want to make a PACKAGE form that can select multiple existing inventory items and ultimately put the New PackageID # into those Item records under the PackageID column.

When i do this now with comboboxes in a subform, I can only create NEW records where the items are repeated in the InventoryTable with a PackageID, but without the PurchaseOrderID. I want to simply edit the old existing records from the Inventory Table.

Is this a pipedream in Access or is it possible? Perhaps a multi-select listbox that can highlight existing records with some code that will apply the PackageID?

Hi,

Here's my prob:
I've got a table with a unique Key (= serial nr)
These parts are in a project (= second column in table)

Now I would like to make a form (maybe with subform) where I can select a project in a combo and that all the parts (serials) within that project are shown.

Every serial has several data in it's record that has to be shown.

Hope my question is clearly written down to understand (see attach for example).

thx,
Vicar75.

Hi,

I'm goin' to explain my problem with an example..
I have 1 table Name with 1 column containing names, then I have another table with 1 column containing surnames.. and one final table containing the combinations of the 2 so 2 columns with a name and a surname combined per row.

I wanted to make this more user-friendly by making a form where I had 2 comboboxes with input, one for the name and one for the surname. If I then clicked the 'add' button it wouldn't only make a new record in table 1 and table 2 with the new input but also made a new record in table 3 saving the combination of the 2.

Is this possible, and if so.. how does it work?

thanks!
Ray

I have a bound CONTINUOUS FORM with 2 comboboxes on it.
A [company] combobox and a [company_member] combobox.

If you select a company it should update the rowsource of the [company_member] box to only show the members in that company.

CmboCompany: select company.companyName, company.companyID from company

CmboCompanyMember: select member.surname, member.memberID from member where member.companyID = forms![sales]![companyID]

the column width of column 1 in both boxes is 0 so that they show meaningful names and not ID numbers to the user.

Limit to list is set to true because access doesn't allow it to be false if you have multiple columns in your select statement.
The "On enter" event on the CmboCompanyMember says "CmboCompanyMember.requery" so that it always shows the records for the correct company.

You are probably thinking "This all sounds well implemented and should have no problems!"
Well ... here's the problem.

On a single form this works perfectly. On a continuous form we have a slight problem.

PROBLEM:
Lets say the continous form looks like this.

COMPANY MEMBER
row1 mcdonalds Jon
row2 Ceres Bob
row3 I&J Fred
row4 mcdonalds Frank

These 3 records are shown at the same time (being a continous form)
CmboCompanyMember, as a form object, can only contain 1 rowsource, yet on our screen we have 3 instances of CmboCompanyMember.
This means that all 3 instances have the same rowsource.

Therefore when the rowsource contains all the members from Ceres
(select member.surname, member.memberID from member where member.companyID = 2)

Bob and Fred become invalid records for the combobox.
ie: the limit to list causes access to go "Hey, Bob and Fred aren't in the list for this combobox, what the hell are they
doing on this form"
What then happens is the comboboxes all show blank values (although the underlying bound value still remains).
As you click into a box, it shows its correct value (because the rowsource is updated).
If you click into the row1 box, both row1 and row4 show their values because the rowsource is valid for both McDonalds records.

NB: If you give the 1st column in CmboCompanyMember a width then this whole problem doesnt happen.
Unfortunately you are then stuck looking at member no 154 trying to work out who he is so this is not really any good.

At the moment when I go into my form, almost all of the combo boxes are showing blanks even though they have values.

Workaround: I have used a dlookup text box (looking up the guys name) next to a combobox which shows you the member_id.
This works but the form becomes incredibly slow. Especially if you have a few of these on the same form.
I can't use that as my solution because it is just too slow.

Help ?
Please ?

I have searched this forum and FAQ's for cascading combobox and have not come across something to assist me with this version of cascading combo boxes. Any thoughts would be apreciated.

I have 2 combo boxes and 4 tables to deal with.

Combo boxes: cboQtyLocID and cboQtyLocation
Tables: LocationID, LocStored2C, LocStored3S, LocStored3R
LocationID has 3 fields
Each of the other tables has one field named [LocStored]

The first combo (cboQtyLocID) - (bound column is(1)) is populated with the following SQL:

	Code:
	SELECT LocationID.LocationID, LocationID.LocationName, LocationID.LocTableName
FROM LocationID
ORDER BY LocationID.LocationName;

What I would like to achieve is to have cboQtyLocation populated with data from the table associated with the selection from cboQtyLocID. The required table name can be obtained from LocationID.LocTableName.

I have not been able to work out the SQL for this yet.

I am pretty new to Access and I have a lot of questions. The first, and probably most basic is this:
I have created a combobox with multiple names. I would like to be able to have the user select multiple names to enter into the form. I have created a background table with the list of names and that is where the combobox pulls the names from. When I go to the "Forms view", the combo box will not allow me to select multiple names and it only displays the first column of the table. What do I need to do to be have the user be able to select multiple names and to have more than just the first column visible? Thanks

Hey there,

I'm struggling with the following challenge. I have a mainform with header information and a subform with details (datasheet view). Based on the value of a mainforms' combobox I would like to prepopulate multiple records of the subform so the user can add/modify the values for the 3rd column (please see attached image). Does anybody know how to do this?

Thanks so much! Attached Thumbnails   Reply With Quote 05-02-2011, 06:28 AM #2 TheSmileyCoder Novice Windows XP Access 2003 Join Date Apr 2011 Posts 15 In the afterUpdate Event of the combobox, you would need to add code to insert the values into the relevant table, and then requery the subform.

I have two comboboxes in access form. what i want is if i select "A" from combo1, I want to have one value to combo2 could it be "x" or "y" or "z" but of my choice.. After combo1 is selected "A", I want automatically pop, lets say, "y" or any value i want may be "z" at combo2 of the form. :-). Combo2 also has list of values (x,y,z) fetched from table.

Please note that both combos are fetched from tables that contains two columns (ID, name) and combos are fetched by binding first column and assigned column count 2; so that it shows the values rather than numbers. Moreover, the combos are allowed to store multiple values (defined in master table)

Appreciate for your help

Many thanks
Prad

I'm attempting to delete records from multipule tables with a single command button. However, I've so far been unsuccessful at getting it to work properly. My code doesn't throw any errors but it also doesn't actually delete the records either. I'm not posting the entire code simply because its repetative and is the same action w/ different TBLs and also deleted my comments to make the post a bit shorter but will provide them if needed. I'm stumped... I'm certain I'm overlooking something rather simple but can't put my finger on it. Thanks in advance

Background:
FRM_Main
-searchSSN (combobox thats populated from TBL_PRIMARY, 2 columns (Name, SSN) bound column2 returns SSN)
-Delete (cmd button to delete all records associated with SSN from searchSSN)

TBL_PRIMARY (contains personal information name, etc... primary key SSN)
TBL_PRIMARY_APFT (contains multipule records with the same SSN linked to TBL_PRIMARY by SSN)


	Code:
	Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then
  CurrentDb.Execute "DELETE * FROM TBL_PRIMARY_APFT WHERE SSN = " & Me.searchSSN, dbFailOnError
  CurrentDb.Execute "DELETE * FROM TBL_PRIMARY WHERE SSN = " & Me.searchSSN, dbFailOnError
 
  MsgBox "Record has been successfully removed"
  End If
Exit_Delete_Click:
Exit Sub
Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click
End Sub

Edit: FRM_Main is an unbound form that acts as a switch

Hi
Can anyone can help me with a search algorithm?....
I have a form with multiple controls (textboxes and comboboxes). In one combobox i have 3 values: legal entity, employee and and individual. Basing this values another subform appear for the search: for the person a search can be by its name or ss#; for the employee the search can be for by its contract number and name, etc.
I have a button "search". I need a search algorithm based on the values entered in text or comboboxes. The result of the searc will be displayed in another form containing as list with 6 columns containing data about files e.g a file for a employee can have: name,number,data,status,etc and this values are displayed basing on the values entered in those controls.

Hey everyone!

I am stuck on an issue with my db. I am trying to run a query with data from multiple tables based on a date selection in a combobox. When entering the date on the criteria design (#9/16/2008# for example) the query runs fine. However when trying to run the same query with the combobox selection I get the following error: "The expression is typed incorrectly, or is too complicated to be evaluated. For example..."

Not too sure what to do. I have checked that the bound column of the cbo is the date, checked that data types match, etc. and have gotten nowhere. The criteria in the query is [Forms]![frmLaunch]![cboDate] under IssueDate.

Thanks for the feedback!

Jason.

P.S. One thing that might be possible. The field name IssueDate is on two tables identically (not linked) in the query.

My apologies if this is answered elsewhere.

Access 2007

Tables: tblNewspapers, tblConventionReg

I have a form for entering/editing new registrations for a newspaper convention. All of the relevant information for each regional newspapers is in tblNewspapers, and all of the registrations are going into the tblConventionReg.

Since there are multiple registrants from each newspaper, I have a combobox on the form that executes a query and retrieves the newspaperID, newspapername, mailingaddress1, mailingaddress1, city, state, zip, email, fax, phone

The information for each individual is entered manually.

The problem I am having is filling each of the newspaper specific fields with data after selecting the newspaper name from the combobox and then adding that data to the record in the tblConventionReg.

For example, if I put:

=[NewspaperSelect].[Column](4) NewspaperSelect is the name of the combobox and the data retrieved from a sql query in column 4 is the city

into the text field for city, the appropriate data shows up just fine no problem. It is also reflected in the datasheet view for the form. However, the data does NOT show up in the table. If I change the control source for the field to "City" (the name of the appropriate column in the table) then it will only show data that I enter in manually.

What I need to know, is how I get data to populate the field on the form after selecting a newspaper from the combobox (again, this executes a query and retrieves all the data I need and lists the newspaper name), and then pass that through to the table.

Ideas? Suggestions?

Form name=Comic_Filter
it has two comboboxes the 1st is "titleName" and the second is "artistName". Both drop down boxes have a list of titles and artist in them. I would like the user to be able to first select the title he wants then select the artist that worked on that title, click the command Button and tada the "Comic_List" report i created pops up related only to the two inputs from the titleName and artistName but lists all information that is in the tblComics (Related to just that Title and Artist). The following is my code for the command button and my tables and queries and SQL code for my quieres.(PS I am not a coder this is for a database I am designing in school, I have not been taught code just a dumb class on code design. So this is what i have figured out on my own with the help of a Visual Basic 6.0 book which is not the same as .net .When this is run as of now if I select values in the titleName combo box and select a value in the artistName combo box and press the command button the report WILL apear but will not have any of the feilds filled in , or if you like there is no data. BUT if I open the form and leave both the titleName combo Box and the artistName combo box blank and pree the command button the REPORT will open with ALL of data shown, 81 pages worth. SO it works almost but there is a bug somewhere or I could be doing it toally wrong. Any help would be awesome!
--------------------------------------------------------------------------
Private Sub Command10_Click()
'On Error Go To Err_Command10_Click
Dim DocName As String
Dim stFilter As String
Dim lgtitlename As String
Dim lgartistname As String
strFilter = "1=1"
If Not IsNull(Me.titleName) Then
strFilter = strFilter & " And [titleName] = "" & Me.titleName & "" "
End If
If Not IsNull(Me.artistName) Then
strFilter = strFilter & " And [artistName]="" & Me.artistName & "" "
End If

stDocName = "Comic_List"
DoCmd.OpenReport stDocName, acPreview, , strFilter
Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
End Sub
------------------------------------------------------------
Here is the qry's i have with the respective SQL code:
------------------------------------------------------------
qryArtist
SELECT tblArtist.artistName, tblTitle.titleName
FROM tblTitle INNER JOIN (tblPublisher INNER JOIN (tblArtist INNER JOIN tblComics ON tblArtist.Artist_ID = tblComics.Artist_ID) ON tblPublisher.Publisher_ID = tblComics.Publisher_ID) ON tblTitle.Title_ID = tblComics.Title_ID;

qryTitle
SELECT tblTitle.titleName
FROM tblTitle;

qryComics
SELECT tblTitle.titleName, tblComics.Edition, tblComics.Volume, tblArtist.artistName, tblWriter.writerName, tblPublisher.publisherName, tblComics.[Publish Date], tblComics.[Purchase Price], tblComics.[Market Value]
FROM tblWriter INNER JOIN (tblTitle INNER JOIN (tblPublisher INNER JOIN (tblArtist INNER JOIN tblComics ON tblArtist.Artist_ID = tblComics.Artist_ID) ON tblPublisher.Publisher_ID = tblComics.Publisher_ID) ON tblTitle.Title_ID = tblComics.Title_ID) ON tblWriter.Writer_ID = tblComics.Writer_ID;
------------------------------------------------------------
Here is a list of my tables with the feilds in them
------------------------------------------------------------
tblComics
Comics_ID (Priamry Key)
Title_ID (Foreign Key)
Publisher_ID (foreign Key)
Artist_ID (Foreign Key)
Writer_ID (Foreighn Key)
Edition
Volume
Publish Date
purchasePrice
marketValue
Description
picture1

tblArtist
artist_ID (Primary Key)
artistName

tblTitle
title_ID (primary Key)

tblpublisher
publisher_ID (priamry Key)
publisherName

tblWriter
writer_ID (Primary Key)
writerName

All Primary Keys are autonumber
------------------------------------------------------------
this is what i have in my row source for the artist combo box

SELECT [tblArtist].[Artist_ID], [tblArtist].[artistName] FROM tblArtist ORDER BY [artistName];

and like wise for the title combo box

SELECT [tblTitle].[Title_ID], [tblTitle].[titleName] FROM tblTitle ORDER BY [titleName];

My bound column for both of them is set to 1
and yes my primary keys for both tbltitle and tblartist are set to autonumber
--------------------------------------------------------------------------
And this is my my Record Source for the actual (report) Comic_List:
--------------------------------------------------------------------------
SELECT tblComics.Comics_ID, tblTitle.titleName, tblComics.Edition, tblPublisher.publisherName, tblComics.[Purchase Price], tblComics.[Market Value], tblComics.Description, tblComics.[Publish Date], tblArtist.artistName, tblWriter.writerName, tblComics.Volume, tblComics.picture1 FROM tblWriter INNER JOIN (tblArtist INNER JOIN (tblPublisher INNER JOIN (tblTitle INNER JOIN tblComics ON tblTitle.Title_ID=tblComics.Title_ID) ON tblPublisher.Publisher_ID=tblComics.Publisher_ID) ON tblArtist.Artist_ID=tblComics.Artist_ID) ON tblWriter.Writer_ID=tblComics.Writer_ID;
----------------------------------------------------------------
there are multiple values in both combo box's in case that makes a difference. There are 55 title names in the tbltitle and 12 values in the tblartist.

And agin I would like a form that has two combo box'es on it one labeled titleName and the second labeled artistName. I want the drop down on the comboboxes to display the actuall names not the ID number and after selecting two choices from the combo box'es to push the cmdButton which will in turn open a report that I already have made, which will display all the data from qryComics for only the selected titleName and artistName that we chose.Complicated isnt it. Again any help would be great. Thanks
J

heya there,

i don't know if 'joined' table is what i have, but i have tblMuscles and tblGroups and then a tblMusclesInGroups.

the only thing i have in the tbluscleInGroups is the MuscleID and GroupID, and i have a form where i can add groups (via a subform whose control source is tblMuscleInGroups) to a muscle when i edit muscle info.

I have done this so that multiple muscles can belong to multiple groups and via versa (e.g., the trapezius can belong to the shoulder and back, but the back and shoulder also have other muscles as part of it)

this is part of a massage database.

now here is my problem, which is, at this stage not a programming problem but a logistical barrier... (i've never been good at strategy):

i have it all setup such that the user is able to add/delete/edit muscles and groups in tblMuscles and tblGroups and their combinations in tblMusclesInGroups (via forms).

now, i used to have (in an older version of the ddb) the muscle groups display in a combo box on a frmMassageData, where the user would enter the data of individual massage sessions. the problem now lies with adding the function to the user to be able to edit groups...

i understand that i can possibly make these groups display by applying the SELECT DISTINCT proprty to the tblMusclesInGroups in the control source, but here is where i become unstuck....

...in my old ddb, i had static musclegroups, which were organised via individual queries for each group. user could not change groups. thus, when i made the combobox to select groups, i fired events using


	Code:
	Select Case cmbMuscleRegion

    Case "Head"
        lstMuscleByGroup.RowSource = "qryMusclesHead"
        lstMuscleByGroup.StatusBarText = "Muscles of the head"
        imgMuscleRegion.Picture = cmbMuscleRegion.Column(2)
        txtMassageService.ControlSource = "MassageServiceHead"
        txtMassageService.StatusBarText = "Services performed on head muscles"
        txtAreaGuide.Value = "This text goes with head image"
   
    Case "Neck"
        lstMuscleByGroup.RowSource = "qryMusclesNeck"
        lstMuscleByGroup.StatusBarText = "Muscles of the neck"
        imgMuscleRegion.Picture = cmbMuscleRegion.Column(2)
        txtMassageService.ControlSource = "MassageServiceNeck"
        txtMassageService.StatusBarText = "Services performed on neck muscles"
        txtAreaGuide.Value = "This text goes with neck image"
    
    Case "Shoulder"
        lstMuscleByGroup.RowSource = "qryMusclesShoulder"
        lstMuscleByGroup.StatusBarText = "Muscles of the shoulder"
        imgMuscleRegion.Picture = cmbMuscleRegion.Column(2)
        txtMassageService.ControlSource = "MassageServiceShoulder"
        txtMassageService.StatusBarText = "Services performed on shoulder muscles"
        txtAreaGuide.Value = "This text goes with shoulder image"
    
End Select

and so on for all the groups. the idea is that, when user selects group from combobox, a list of associated muscles pops into a listbox, which the user can double click to make the name of that muscle drop into a table memo field (which is distinct to the group for that massage session on that individual). This field is then what makes it to the report (which i haven't deisgned yet)... when this field has data, it displays the text, plus the associated group image (from a file path field in tblGroups).

but how can i possibly make this work if i am letting the user add and delete groups? i was thinking to make generic thing - can i do some sort of loop based on ID? like, for each DISTINCT ID item, create a field in the MassageData table to store group massage info?

i know this is a lot fo information to get your head around, and i may be doing everything 'the hard way', but i'm not a programmer by career, just by hobby (and only since recently)....

can anyone see a way out of my connundrum?
cheers,
agnieszka.

Hello,
I have a form with a combobox (combo0) where a sample id can be selected from the drop down menu.

In a list box I have the following code:

SELECT DIVERS_pt2.sample_id, DIVERS_pt2.[1/(maxi/summe)]
AS 1/(max species count/total count)], SHANN_pt2.proz
FROM DIVERS_pt2, SHANN_pt2
WHERE (((DIVERS_pt2.sample_id)=[forms]![NANDATSAMPLEID].
[combo0])
AND ((SHANN_pt2.sample_id)=[forms]![NANDATSAMPLEID].[combo0]));

I want to show the two types of species diversity ("1/max species count/total count", AND "proz"), however, for some sample ID's I have a value for the "1/max species/total count" but not for the "proz" one. Right now if I select a sample that does not have a Shannon Diversity value ("proz") no record shows up in my listbox, even though a value still exists for "1/max species/total count"

Does anybody have any suggestions on how I could alter my code so that a record will always show up as long as one of the two diversity values exist??

Thank you very much!

I am building a spec database for everything we produce. My spec database has a few different tables for different types of data:
- tblProductInfo
ItemNumber
Description
MasterCase

- tblpackaging
PackageID
Description
Pallet Configuration

- frmSpecification
ItemNumber
Description
MasterCase (this is a combo box with a listing of descriptions from tblpackaging)
Unbound text box - MastercaseID
Unbound text box - Pallet configuration

There are a few more, but that's the idea.
So I have a form and a report that I am using to pull data from each one of these tables to build a final specification. The form can be used to build a new product specification and uses combo boxes to add data from the different tables.
So lets say I have a product "x" that needs a master case "y". I would open the form, look for product "x" and use the combo box that sources from tblPackaging to select Master Case "y". Easy enough.
The problem I'm running into is that most tables have more than one piece of data that needs to show up on the form. For example, If I use Master Case "y", There is an assigned corresponding ID number and a pallet configuration that needs to be on the specification as well. the Id number and pallet configuration are stored in the tblPackaging I'm just struggling to get it to show up on the form.
So what I've done is in tblProduct info, There is a field called MasterCase. It uses a query that pulls values from the table: tblPackaging and displays 3 columns if information, Mastercase, ID, and palletconfiguration. Then on my form, I select the master case through a combobox and would like to have my selection automatically populate additional text boxes with ID and pallet configuration. I was able to accomplish this by editing the control source in unbound text boxes as so:

	Code:
	=[MasterCase].[column](1)

I got everything setup and it was working great and then I saved my database and left for the night and when I came back in this morning I opened my form and got the following Error:
Quote: The expression on current you entered as the event property settting produced the following erro: Return without gosub. After pushing OK I raelized that every one of the text boxes with edited control sources now say #NAME in them and no longer perform their desired function. I deleted all of the affected text boxes on the form and recreated them again and it worked again so I decided to test it to see if closing the database would cause the problem again. And it did. So for some reason my attempt to set up this auto populating feature only works until the database is closed and then everything gets messed up for some reason. I'm wondering if there is way for me to fix this or if I'm going about this in a much too complicated way and maybe there is an easier way to do this?


Not finding an answer? Try a Google search.