Listbox displaying only numbers

[rayape]

Hello All -

I have two tables AssetList and AssetInfo. Both tables have AssetID (field) as Primary Key. AssetList has all the names of Assets. AssetInfo has details for each asset (like serial number, model number and so on). AssetIDs are joined to form a relationship.

I have a listbox in which I need two columns of information to be displayed. 1. Asset (stored in AssetList table) and 2. Serial Number (stored in AsserInfo table).

When I try to list both tables in my rowsource and select Asset and Serial Number, all is see is numbers for Asset. I see the serial numbers just fine though.

Please see the image to see what I am seeing.

What do I need to do to fix it?

Thank you.

 

[Isaac]

all is see is numbers for Asset. I see the serial numbers just fine though.

is this a typo? it doesn't make sense.
and your screenshot looks exactly like what you said you wanted to see ... i'm confused.

 

[Gasman]

Table lookup?

 

[rayape]

@Uncle Gizmo - please see the pictures. I don't know how to explain technically, so I am providing pictures of what I have. Thanks.

 

[rayape]

@ pisorsisaac@gmail.co

I would like to see the names of Asset in the first column. For ex: Machine A, Thermometer B, Pan C
However, all I see is for ex: 22, 1, 13 that corresponds to the Asset from the AssetList table.

Thanks

 

[Deleted member 147267]

When I try to list both tables in my rowsource

Me confuzzed too. But you cannot set a row source using the names of multiple queries AFAIK. You'd use a query based on multiple tables.

 

[Isaac]

I would like to see the names of Asset in the first column. For ex: Machine A, Thermometer B, Pan C
However, all I see is for ex: 22, 1, 13 that corresponds to the Asset from the AssetList table.

What is your Rowsource property value on the Listbox?

 

[rayape]

@ Uncle Gizmo
I wanted to keep the AssetList and AssetInfo tables separate.
Reason: One Asset, for ex: thermometer. I could have 10 thermometers with 10 different serial numbers.
So I created AssetID field and joined them as I showed in the picture.

 

[rayape]

@ pisorsisaac@gmail.co

SELECT AssetInfo.AssetID, AssetInfo.Asset, AssetInfo.SerialNumber FROM AssetInfo;

 

[Isaac]

Do you have your ColumnCount property set correctly on the Listbox, with appropriate column widths to show all the information?

Second question ... does that query , if run as a query, show correctly?

 

[rayape]

Do you have your ColumnCount property set correctly on the Listbox, with appropriate column widths to show all the information?
Yes

Second question ... does that query , if run as a query, show correctly?
Yes

 

[plog]

You got the memo about using autonumbers for primary keys, but missed their purpose. Primary keys are what you use to link tables. You are not doing that, you have connected AssetInfo and AssetList by a non-primary key field ([Asset]). That is wrong. However...

The AssetList table doesn't need to exist. Tables with just 1 real field (autonumbers are not real data) should just have that 1 piece of data go into the table its foreign to. Additionally, it sounds as if AssetInfo to AssetList is a 1-1 relationship which is also a no-no.

I'm really concerned about the other tables/relationships in your database. Can you complete the Relationship tool for all tables, exapand to show all fields and then post a complete image of it?

 

[rayape]

You got the memo about using autonumbers for primary keys, but missed their purpose. Primary keys are what you use to link tables. You are not doing that, you have connected AssetInfo and AssetList by a non-primary key field ([Asset]). That is wrong. However...

The AssetList table doesn't need to exist. Tables with just 1 real field (autonumbers are not real data) should just have that 1 piece of data go into the table its foreign to. Additionally, it sounds as if AssetInfo to AssetList is a 1-1 relationship which is also a no-no.

I'm really concerned about the other tables/relationships in your database. Can you complete the Relationship tool for all tables, exapand to show all fields and then post a complete image of it?

 

[Isaac]

Do you have your ColumnCount property set correctly on the Listbox, with appropriate column widths to show all the information?
Yes

Second question ... does that query , if run as a query, show correctly?
Yes

If the Listbox's Rowsource-query returns exactly what you want, and if you have that query specified as the Listbox's Rowsource correctly, then the remaining possibilities are that you do not have the ColumnCount property correct, or do not have the listbox's column width OR overall width set correctly.

Not detracting from any of the good advice you are getting simultaneously about tables.

 

[rayape]

If the Listbox's Rowsource-query returns exactly what you want, and if you have that query specified as the Listbox's Rowsource correctly, then the remaining possibilities are that you do not have the ColumnCount property correct, or do not have the listbox's column width OR overall width set correctly.

Not detracting from any of the good advice you are getting simultaneously about tables.

This is what I see.

 

[plog]

So, you did make that same mistake with MaintenanceType and MaintenanceRecord and you have 2 other tables that don't need to exist (Status and Manufacturer). You honestly just need 2 tables for this, AssetInfo and MaintennceRecord.

You get ride of AssetList by moving its data into AssetInfo and the initial issue you posted about is probably moot.

 

[Isaac]

This is what I see

Well, that's different than what you first posted you saw.

 

[rayape]

Well, that's different than what you first posted you saw.

Originally, I only had Columns 2 and 3. I had omitted the first column. Since your post, I wanted to make sure the ColumnCount property was set correct. The updated image shows all 3 columns. That's it.

Thank you

 

[Deleted member 147267]

I suggest you forget about the listbox issue. You have bigger problems with tables design, which when fixed, will likely make your list box unusable again.

 

[rayape]

I suggest you forget about the listbox issue. You have bigger problems with tables design, which when fixed, will likely make your list box unusable again.

I am learning about it through all you learned folks. Please give me some pointers to fix the whole thing.
Thank you all.