Cascading Combo Boxes Row Source Issue

[Shafique]

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

 

[Micron]

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

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

www.allenbrowne.com

 

[Shafique]

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

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

www.allenbrowne.com

I am very grateful to you. Your ability to understand and resolve is very good.
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]

I am very grateful to you. Your ability to understand and resolve is very good.
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)

 

[Shafique]

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

 

[Micron]

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.

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]

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)

 

[Micron]

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]

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")

 

[Micron]

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 [ ].