need to fill txtbox with first value matching sql results in table 1

[SiberBob]

Would someone please tell me how I can fill a text box with the first of multiple matches based on sql statement comparing multiple values and setting order on the field to be returned as alphabetical?

something similar to

txtWrecker.value = DLookup("Wrecker", "Wreckers", "[County] = [cmbCounty] AND [Size] = [cmbSize] AND [Area] = [cmbArea] AND [Rotate] = False ORDERBY [Wrecker]")

I want the user to select a value from 3 combo boxes on the current form (cmbCounty, cmbSize, cmbArea) and then return the first instance of Wrecker (sorted alphabetically) from tabled named Wreckers where the Rotate field is false.

I have read up on Dlookup, Select First, and Select Min but can't seem to get it tweaked just right. I think the Dlookup would be the right path, but I need some help...


Bob

 

[PHV]

Something like this ?
Me!txtWrecker.Value = DMin("Wrecker", "Wreckers", "[County]='" & Me!cmbCounty _
& "' AND [Size]='" & Me!cmbSize & "' AND [Area]='" & Me!cmbArea & "' AND [Rotate]=False")
If either County, Size or Area is defined as numeric then get rid of the single quotes surrounding the corresponding combo value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

If I understand correctly Bob, you want the first record, based on alphabetical sequence, not value.

If so, I don't believe any of the domain functions, offer an Order By clause.

All I can think of off hand, would be opening a recordset.
appropriately Ordered, & possibly using the find Method.

Sub FindADO()
Dim rec As New ADODB.Recordset

rec.Open "SELECT * FROM Wreckers County ='" & Me!cmbCounty _
  & "' AND [Size]='" & Me!cmbSize & "' AND [Area]='" & Me!cmbArea & "' AND [Rotate]=False ORDER By Wrecker", CurrentProject.Connection, adOpenStatic, adLockOptimistic

   txtWrecker = rec!Wrecker  'since the criteria is already restricted & sorted
   
rec.Close: Set rec = Nothing
End Sub

or alternately...

Sub FindADO()
Dim rec As New ADODB.Recordset

rec.Open "SELECT * FROM Wreckers  ORDER By Wrecker", CurrentProject.Connection, adOpenStatic, adLockOptimistic

rec.Find "County ='" & Me!cmbCounty _
  & "' AND [Size]='" & Me!cmbSize & "' AND [Area]='" & Me!cmbArea & "' AND [Rotate]=False"

   txtWrecker =  rec!Wrecker  
   
rec.Close: Set rec = Nothing
End Sub

Otherwise, I agree with PHV, if it's not an alphabetical sort you want, but a value.


Either way, good luck!

 

[SiberBob]

This worked wonderful (note that I added [] and Where around and between Wreckers & County and I have sinced changed the desired criteria for [Rotate])

Dim rec As New ADODB.Recordset

rec.Open "SELECT * FROM [Wreckers] WHERE " & _
    "[County] ='" & Me!cmbCounty & "' AND " & _
    "[Size]='" & Me!cmbSize & "' AND " & _
    "[Area]='" & Me!cmbArea & "' AND " & _
    "[Rotate]=True " & _
    "ORDER By Wrecker", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
   cmbWrecker = rec!WRECKER  'since the criteria is already restricted & sorted
    
rec.Close: Set rec = Nothing

I guess I am gonna have to learn about using recordsets... I've been reading about them in other posts but haven't gotten around to learning it yet

Bob

 

[SiberBob]

PHV:

your suggestion got me closer than I was...

But Zion 7's was more in line with what I needed.

Thanks for your help!