DLookUp Sorting

[Cschmitt]

I have a form that uses DLookUp as a control source. I would like to be able to sort alphabetically. I'm not that savvy at the in's and outs of Access. Any suggestions. Something to ponder thru the weekend I guess.



Just in case, here is the control source created by my glorious predecessor:

=DLookUp("[vendorname]","[apvend]","[vendorkey] = ' " & [vendorkey] & " ' ")

[ItsMe]

That looks like an expression within a control not a form's RecordSource. If your Dlookup is retrieving multiple records you will need to assign it to a Variant type and I am Not sure how you would go about sorting from there. I am sure it is possible but there would be a better way than using Dlookup. Besides, I will guess what you have there is retrieving a single record and it is displaying the value in a textbox.

[Micron]

DLookup will only return one value from a table field or set of records. The first one found that satisfies the criteria will be returned, regardless of how many there are. If none are found, the function returns NULL. So there is no such thing as a sort on a domain lookup. If you need more than one value to be returned to a control (combo box or list box) you need a sql statement.

[June7]

I presume the records you want to sort are the form's, because sorting the lookup table makes no sense. What method do you want to use to sort? Try the sort tool on the ribbon or right click shortcut menu.

A better method of returning the vendor name is to have a multi-column combobox that includes the venderkey and vendorname fields in its RowSource. Then expression in textbox can reference the columns by their index. Index begins with 0.

=[comboboxname].[Column](1)

Another option is to include the lookup table in the form's RecordSource, not INNER JOIN. Bind textboxes to the associated data from apvend and set them Locked Yes, TabStop No so they can't be edited.

[Cschmitt]

It's been a few weeks since I was able to get back into this. The database isn't my full time job =(. I would upload the DB but the boss is not happy with that so I'll try to explain as best I can:

subFrmPurchaseOrder_Bought has 4 main fields (Vendor Name, Amt, QTY, and Request Date). These are all tied together using the part number. How this is I can’t tell, but when I am in the record for that part number I click on the reports button it brings up another form with the subFrm above. Part number, Amt, QTY, and Request date are all coming from table POLIN (an SQL table that comes from Sage). Vendor Name comes from APVEND (another SQL table that comes from Sage) using the above code in the Control Source box. After that I have no clue as to what to do.

June7, when a user brings up the reports form, they can click on the Amt, QTY and request date and it will give the right click or ribbon option to sort. When they click on the Vendor Name, it does not give the option. They would like the same functionality in the vendor name (right click in the vendor name and sort alphabetically).

Thank you all for the help. Hopefully I can work something out.

[June7]

What do you mean by 'reports form' - just a form that lists some records?

The vendor is currently a combobox?

Did you try what I suggested?