Find Record using a combo box on a form.

[Lou P]

Greetings.

I am trying to use a combo box on a form (frmItem from tblItem) to find records for viewing/editing. The information appears in the combo box and it finds the correct record, but the fields "ITYPEID" and the "MODELID" are displayed as numbers.

The way I set it up the "Find Record" Combo Box on the frmItem is:

tblItem.ITEMID = PrimaryKey autonumber from "tblItem"

tblItem.ITYPEID = ForiegnKey pulled from Table "tblItemType"
SELECT DISTINCTROW [tblItemType.ITYPEID], [tblItemType.ItemType] FROM [tblItemType] ORDER BY [tblItemType.ItemType];

tblItem.IMODELID = ForiegnKey pulled from Table "tblItemModel"
SELECT DISTINCTROW [tblItemModel.IMODELID], [tblItemModel.ItemModel] FROM [tblItemModel] ORDER BY [tblItemType.ItemType];

tblItem.ItemSerialNum

In the Combo Box for the Row Source is:
SELECT [tblItem].[ITEMID], [tblItem].[ITYPEID] &" / "& [tblItem].[IMODELID], [tblItem].[ItemSerialNum] FROM tblItem;

Row Source Type = Table/Query
Column Count = 3
Column Widths = 0";3";1"
Bound Column = 1

An EXAMPLE of what I get displayed in the "FInd Record" Combo Box is:

ITEM | SERIAL #
22 / 39 | 33491000
33 / 76 | 77622134 etc...

How do I get the "ItemType" & "ItemModel" to display vice their respective PrimaryKeys?

Thanks

 

[Brianwarnock]

but the fields "ITYPEID" and the "MODELID" are displayed as numbers.

What should they be?

Brian

 

[Lou P]

Sorry if I made it confusing! No...what I want is the actual "ItemType" and "Model" (text) to be displayed, or both if I have to.

You can't tell which Item Record you're selecting from the number alone.

Each value is pulled from their respective tables for each "Item" record.

tblItemType
ITYPEID - AutoNumber
ItemType - Text

tblItemModel
IMODELID - AutoNumber
ItemModel - Text

 

[Brianwarnock]

Then your combo query must select that data and your combo be designed to show it, Combos need only show what the user needs to see other columns needed for the operation can be hidden.
Thus a system may use partno. 1234567/wdg but the user only needs to see and select Widget.

Brian

 

[Lou P]

Correct. In my case I need to see the "ItemType" the "ItemModel" and the "ItemSerialNum" to be able to select the right record.

The question is how do I get the combination of information "displayed correctly" so the desired record can be selected from the combo box and then opened in the form.

I probably should rephrase the title...but not sure how to define it.

 

[azizsohail]

if you want 3 columns in your combo box. 1 hidden and 2 displayed. then you need to change the properties of your combo....
ColumnCount=3
Column Widths = 0cm;2cm;2cm (in this case i am hidding the frist value return by the query and the display the other 2 with a length of 2 cm each.

i hope u could be able to write the ur query ?

Regards

 

[Lou P]

My apologies if I haven't made it clear. The combo box I have works.
The problem I am having is with the query I guess...except when I create the query from the tblItem in the "Query Design":

tblItem.ITEMID
tblItem.ITYPEID
tblItem.IMODELID
tblItem.SerialNum

SELECT [tblItem].[ItemID], [tblItem].[ITYPEID] & " / " & [tblItem].[IMODELID] AS [ITEM / MODEL:], [tblItem].[ItemSerialNum] AS [SERIAL #] FROM tblItem ORDER BY [tblItem].[ITYPEID], [tblItem].[IMODELID], [tblItem].[ItemSerialNum];

It works fine and I get the desired results in the "Query Design".

EXAMPLE:

ITEM / MODEL: SERIAL #:
Tuner / CS-5020C 1TDZ011
Tuner / CS-5020C 1GMK201
Antenna / RP-22 1002
Antenna / RP-22 1003 etc...

But when I place the exact same query in the combo box it returns the ID number for Type & Model vice the actual names.

3 Columns, 1 Bound Column with Column Widths = 0";3";1"

ITEM / MODEL: SERIAL #:
2 / 4 1TDZ011
2 / 4 1GMK201
14 / 33 1002
14 / 33 1003



Of course this only occurs when I'm using a combo box as a "Find Record" capability within a form.

I've searched every book I have all of microsoft & by all accounts it should display the names so I'm not sure where the problem lies...

Lou P

 

[Brianwarnock]

I don't see where you select the text fields item type and item model, so how can they be shown.

Brian

 

[Lou P]

This is where it gets confusing, at least for me...

The values are in the two foreign key fields in the table "tblItem" - "ITYPEID" & "IMODELID" which are being pulled from their respective tables with the "ItemType" and "ItemModel" fields.

tblItem.ITYPEID = SELECT DISTINCTROW [tblItemType.ITYPEID], [tblItemType.ItemType] AS [ITEM TYPE:] FROM tblItemType ORDER BY [tblItemType.ItemType];
2 Columns, 1 Bound Column displays the value ItemType in the ITYPEID field in the table tblItem.

tblItem.IMODELID = SELECT DISTINCTROW [tblItemModel.IMODELID], [tblItemModel.ItemModel] FROM [tblItemModel] ORDER BY [tblItemType.ItemType];
2 Columns, 1 Bound Column displays the value ItemModel in the IMODELID field in the table tblItem.

I am trying to use those two foriegnkey fields from the tblItem to populate the "Find Record" function combo box.

When I create a query using the tblItem it gives me the desired results...just the ItemType & ItemModel

Yet using the exact same query in the combo box it only displays the ITYPEID & IMODELID respectivley without the ItemType or the ItemModel values.

Therein lies my problem...why does it work in the "Query Design" exactly as I wnat it to and not when it is run from the Combo Box?

 

[Lou P]

SELECT [tblItem].[ITEMID], [tblItem].[ITYPEID] &" / "& [tblItem].[IMODELID], [tblItem].[ItemSerialNum] FROM tblItem;

Why does it work exactly as I wanted it to in the "Query Design" not when the exact same query is run from the Combo Box?

 

[Brianwarnock]

And my problem is that i don't see how you can see a field that you don't SELECT, what you are doing is obviously outside my experience/expertise, perhaps you need to start a new thread, mentioning this one but hopefully arracting new contributors, tho' this has had over 100 visits.

BTW when ever I've been selecting on multiple fields I have used cascading combos to narrow the search. ie the first combo only showing Items and this is udsed to filter the second to show relevent models etc

Brian

 

[spikepl]

What is the column count of the combo
Which column is bound
What are the column widths

 

[Brianwarnock]

All of that info is given in Post #1.

I hope that you can help him I'm lost.

Brian

 

[spikepl]

#13 I did this on a Mac, that's why (*need some credible excuse for not reading*)

I agree with you. But obviously either we are missing something, or the db has corrupted itself in some weird fashion.

 

[spikepl]

Ah I get it.

You used Lookup fields in your table design, that's why it's confusing everybody. Get rid of the lookup fields in your tables, http://access.mvps.org/access/lookupfields.htm

For your combo you need to construct a query that has joins, so as to display the text for the relevant items instead of their ID's.

 

[Lou P]

Spike;

You're correct. I removed the lookups and did the query with joins. for just the ItemType and it works...

Thank you and Brian too for the help. Sorry I made it confusing, at my Access knowledge level I don't use the right terms or chasing the wrong problem.

Thanks again!

 

[Lou P]

Spike;

You're correct. I removed the lookups and did the query with joins. for just the ItemType and it works...

Thank you and Brian too for the help. Sorry I made it confusing, at my Access knowledge level I don't use the right terms or chasing the wrong problem.

Thanks again!

 

[Brianwarnock]

what you are doing is obviously outside my experience/expertise,



Brian

well I was correct as I have never used table lookups

Good catch by spike

Brian

 

[spikepl]

#18 Let's give credit where it's due: Your questions about how something seemingly not there was there, triggered me So it was a team effort.