Cascading Combo Boxes Row Source Issue

Shafique

New Member
Joined
Nov 23, 2018
Messages
42
I want the values in a combo box from another table's field Value. But when in query builder i select the desired Table's(TABLE1) Field without its ID(Primary Key) the combo box is not showing the value but a blank list.
as i add Table's ID(Primary Key) field the combo box show the value in its list but not DISTINCT
TABLE1
ID NAME CITY
1 ABC XYZ

I want Only NAME(ABC) field in my combo box in DISTINCT
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
What you're describing sounds like you're altering the number of combo rowsource fields without matching the number of combo columns in form design view. Try something like this in the combo rowsource property
SELECT DISTINCT ID, Name, City FROM table1;
For that, your combo needs 3 columns; the width of the first should be 0. Users should usually not see primary key values, especially if they're autonumbers.
Name is a reserved word and should not be used for any object. See
 

Shafique

New Member
Joined
Nov 23, 2018
Messages
42
What you're describing sounds like you're altering the number of combo rowsource fields without matching the number of combo columns in form design view. Try something like this in the combo rowsource property
SELECT DISTINCT ID, Name, City FROM table1;
For that, your combo needs 3 columns; the width of the first should be 0. Users should usually not see primary key values, especially if they're autonumbers.
Name is a reserved word and should not be used for any object. See
I am very grateful to you. Your ability to understand and resolve is very good.(y)
I did as you said(your combo needs 3 columns; the width of the first should be 0) and I benefited.
But I have another problem here that when i save the Form the data is not saved properly. my field's Values are text format. but in the table there are some number and when i close the form the values in table are changed. ( My Combo Boxes are Bounded ) and i use URDU language not ENGLISH.
 

Shafique

New Member
Joined
Nov 23, 2018
Messages
42
I am very grateful to you. Your ability to understand and resolve is very good.(y)
I did as you said(your combo needs 3 columns; the width of the first should be 0) and I benefited.
But I have another problem here that when i save the Form the data is not saved properly. my field's Values are text format. but in the table there are some number and when i close the form the values in table are changed. ( My Combo Boxes are Bounded ) and i use URDU language not ENGLISH. The Three Circled Combo boxes (ITEM NAME, ITEM TYPE, ITEM SIZE) are Cascading right to left in picture and also bounded To TABLE 2's Fields. And they show values form another table's field (Row Source)
 

Attachments

  • FORM 1.png
    FORM 1.png
    27.1 KB · Views: 4

Shafique

New Member
Joined
Nov 23, 2018
Messages
42

ADVERTISEMENT

After save the Next Record its miss some values e.g ITEM SIZE is blank. but i select the value and click save
 

Attachments

  • FORM 1 II.png
    FORM 1 II.png
    38 KB · Views: 3

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
My guess is that "some number" is the autonumber value from the rowsource table? If in the rowsource table "hat" is item 5 and you choose "hat" from the combo list you are saving 5 as the result? If your tables and relationships are properly designed, this is the way it should be. Here is a quick sample with a customer, items, orders and order items tables and how it is usually done. The PK (primary key) fields here would be autonumbers.
dbTables.jpg

Order 2 was purchased by customer 1 (Joe) on 02/01/2019 and had 3 items on the order; 2,3,4 (scarf, shoe, glove). By joining the correct fields in a query, that is the story you can tell. Therefore, it is often correct to store a number in a table. The alternative is to use meaningful data as your primary key. However, that can cause issues. If you decide shoe should have been "shoes" then you might have a problem because that value has to be changed everywhere you used it. If you use autonumber to relate records, you can change the value with no problem.

Also, if you bind a combo to a field, then when you alter the selected value and save the record, the value in the table will change so I do not understand what you are saying about that. Hopefully I understood your post. If not, I guess I wasted some time.
 

Shafique

New Member
Joined
Nov 23, 2018
Messages
42

ADVERTISEMENT

Aslamua Aliakum.
I learnt many thing from your guidance.
Here I want to add a Field in my order's Table or how it possible?
Questions.
1: I have an Orders Table with a Unique Order number. e.g. SD-10. in this order number i have many item Types e.g. Chair, Table, Door
2: I have a Sales Or Dispatch Table and also a Form. Where i enter a Order Number in a text box and one Item from this (SD-10) Order Number.
Now i want to create a Pending Order's Items Report That Shows only those item that are not Delivered Or not in Dispatch Table with the selected Order number (SD-10)
 

Attachments

  • Untitled.png
    Untitled.png
    47.7 KB · Views: 5
  • Untitled.png
    Untitled.png
    47.7 KB · Views: 5

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
Put your table into design view and add a row to the field list. You can right click to insert this new row (field) or you can add it to the end of the list. You can leave it there or you can drag it into place.

Create a query that shows the undelivered items on an order for a particular order number. I cannot read your data or field headings so it's hard to say exactly what to do. If your date field is the delivery date and this field is in the Order table and no date means no delivery, then for query criteria specify Is Null in the date field. Put SD-10 as criteria for the order number.

You could research 'parameter' queries to learn how to make this query more dynamic. When the query works, try using the report wizard to create a report from it.
 

Shafique

New Member
Joined
Nov 23, 2018
Messages
42
SOLVED......
You are my HERO.

I have a Group Of Three Steps To Define One Item In My Item Define Table.
1: ITEM TYPE e.g. Pipe
2: ITEM TYPE e.g. Plastic
3: ITEM SIZE e.g. 3"
I have Pipes in Iron, Steel and also in Different Sizes (Attachment = Untitled)
I Use these fields in my all tables( ITEM IN, ITEM OUT, ORDERS) Through Cascading Combo Boxes to Post an Item's Order (Attachment = Untitled2)
Here I want a Calculated Field or Tell me how it possible in my ORDERS Form or Table that at the Time of posting new order after updating the ITEM SIZE Combo It populated the Item Stock.
e.g. i have an Order of 30 Pieces (plastic pipe in 3") after the selecting of ITEM SIZE in orders's Form the Text Box shows some Value of (plastic pipe in 3")
 

Attachments

  • Untitled.png
    Untitled.png
    56.3 KB · Views: 5
  • Untitled2.png
    Untitled2.png
    22.8 KB · Views: 5

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
Take a look at the DSum function. It accepts criteria (in your case perhaps "steel" or 3") but it can be tricky to concatenate criteria when using this function. It sounds like you have an IN and an OUT field but one field is better: IN is positive and OUT is negative. In English DSum on your IN field could be described as "sum the IN field values for all records in tblMyTable Where [Item Type] = "Pipe". You would do this for the OUT field as well and subtract the two values. The entire expression can be the control source for your combo. If all the stock transactions were in the same field, then a simpler expression could be used. Rather than having to do 2 DSum calculations and a 3rd calculation to find the difference you simply DSum the table field. It will add all the positive and negative numbers for a particular product in one expression.

You should not be storing such calculations if that's what you plan to do. A calculated textbox cannot be bound to a table either; it would require code to store the calculation, which again, should not be done. Also, it's best to not have special characters or spaces in your object names (perhaps you don't really have something like "Item Type") but underscore is OK (Item_Type). Doing so will force you to use brackets [ ].
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top