SQL Statements??

[natemclain]

I was trying to figure out how to make a Row Source so that my users could select a name instead of the custID on a table. I got the names to show up in the combo box but when you select one I get a error because it is now trying to put text in a number field. And I can't change the field type because of my relationships. Can someone explaint o me how this is done. I have looked at the NorthWind DB and saw how they are doing it but I don't understand the SQL Statement. Can someone explain this statement to me??
SELECT DISTINCTROW [ShipperID], [CompanyName] FROM Shippers ORDER BY [CompanyName];

TIA

natemclain

 

[Aivars]

1. Create unbound combobox (name cboFindCust) with rowSource which is based on your form recordset - first column may be custID, the second one - name or other, e.g.:

Select custID, custName From Customers;

2. Put code like following into combobox AfterUpdate procedure:

-------------------------------------
private sub cboFindCust_AfterUpdate()
dim rst as recordset

set rst=me.recordsetclone
'Use identifiers ' for text or # for date type field
rst.findfirst "custID=" & me.cboFindCust
if not rst.nomatch then
me.bookmark=rst.bookmark
end if
rst.close
set rst=nothing
end sub
----------------------------

Aivars

 

[jfischer]

If you use a Bound combo box, change the order of your fields in RowSource to:

SELECT DISTINCTROW [CompanyName], [ShipperID] FROM Shippers ORDER BY [CompanyName];

Then change the BoundColumn property of your combo box to 2, and change the TextAlign property to Left.

This will put the numeric value in the Bound field, and will display the alpha value in the combo box.

 

[BoxHead]

Nate, if ShipperID is your primary key, use the combobox wizard. Only select CompanyName to appear in your combobox. Don't select the ShipperID. Access will bring along the primary key as a column that is not shown.

Then you can go to the RowSource and click the build button. It will take you the QBE window so you can see how your SQL differs.

Good luck.

BoxHead

 

[Craig0201]

Would add to JFischer's post that it's probably best to hide the primary key altogher. Can be done using column widths.

HTH

Craig