Type Mismatch in Combo Box

[uaguy3005]

I have a form (frmSortDataEntry) updating a table (tblSortData). The combo box for part number gets its values from a query of the other tables, but it gives me a type mismatch error because the query gives me text data and I'm assigning it to a numerical field. Summary of the tables below and attached the DB for any help, but I'm trying to get PartNum to show up in combo box to populate tblSortData.PartsOnSortID. If you can open the attached database, I'm trying to get Combo30 to display PartNum for only parts that are active on sort.

tblParts


---------
PK - PartID
PartNum
PartDesc

tblPartsOnSort
-------
PK - PartsOnSortID
FK - PartID
StartDate
EndDate

tblSortData
---------
PK - SortDataID
FK - PartsOnSortID
ContainerNum
QtyInspected

The query I was using just looks at parts that have a startdate before today and enddate either null or after today. Any help would be appreciated!

Thanks!

[ItsMe]

So, it seems like you want to store the PartID from tblParts in the PartID field of tblPartsOnSort via the combo.

You were using the combo to try and store PartNum from tblParts.

So you need to change the SQL in your query. It does not seem you need to actually include the date fields in the Select statement. So I did not include those. You just need to retrieve the PartID and the PartNum. Of course I kept the Date fileds as Criteria in the WHERE statement. Paste the following into SQL view of your query object.

SELECT tblParts.PartID, tblParts.PartNum
FROM tblParts INNER JOIN tblPartsOnSort ON tblParts.PartID = tblPartsOnSort.PartID
WHERE (((tblPartsOnSort.StartDate)<=Date()) AND ((tblPartsOnSort.EndDate) Is Null)) OR (((tblPartsOnSort.EndDate)>=Date()))
ORDER BY tblParts.PartNum;

And then you need to go to design view of your form and change a couple properties of your combo.
Column Count: 2
Column Widths: 0, 2

This will show the PartNum to your User and use the PartID as the .Value property of the Combo.

[ssanfu]

So you need to either
1) change PartsOnSortID in tblSortData to a text field

or

2) add the PartsOnSortID to qryActiveSortParts.


If you use #2 (I would), add PartsOnSortID to qryActiveSortParts as the first column.
In the combo box properties FORMAT tab, set
Column count to 2
Column widths to 0, 1

Save the form and try

[ItsMe]

Steve, I am going to buy you typing lessons for Christmas so you can touch type and speed things up.

[uaguy3005]

Excellent! That worked, I just changed SELECT to SELECT DISTINCT to make sure I didn't show other records with the same part number.

Thanks for the help!

[ItsMe]

Excellent! That worked, I just changed SELECT to SELECT DISTINCT to make sure I didn't show other records with the same part number.

Thanks for the help!

May I ask how you came up with the idea to use SELECT DISTINCT? I think I need a nap.

[ssanfu]

Steve, I am going to buy you typing lessons for Christmas so you can touch type and speed things up.

Now that's funny!
(I do get sidetracked with that 4 letter word - "Work". And there is the Curly syndrome - "I'm trying to think, but nothing is happening! Yuk,yuk)

I didn't even look at the values returned in the combo box.
I was looking at the relationship window, the combo box, the combo box query, and then modified the combo box.

But thanks...