returning more than one row

[Access2000_JS1]

The snipet below returns only a single record. I want all the records. I am not sure how to get the results from the query or the syntax.

This is what I want:

Select location from client_loc
where clientID = FORMS!job.ClientId


Thanks
---------------------------------

Private Sub ClientId_AfterUpdate()
GetValidLocations
End Sub


Private Sub GetValidLocations()

Dim varName As Variant ' declare a variable for the name

' Check if either combo box is null
If (IsNull(Me.ClientId)) Then
varName = ""
Else
' Look up the title
varName = DLookup("[Location]", "client_loc", "[clientID] = FORMS!job.ClientId")
End If

If (varName = "" Or IsNull(varName)) Then
Me.location = "(None found)"
' Otherwise, put name into the text box
Else
Me.location = varName
End If

End Sub

 

[boblarson]

I believe you want

"Select location from client_loc " & _
"where clientID =" &  FORMS!job.ClientId & ";"

 

[Access2000_JS1]

Yes, that's the syntax, but how do I get the results into varName?

 

[ShaneMan]

Hey JS,

I believe DLookup will only return the first record it finds. If your looking for multiple records then you'll need to use SQL or recordsets or at least something like that. What is 'Me.Location' ? Is it a cboBox? If it is, then you could use SQL to set it's RowSource and get what your trying to accomplish.

HTH,
Shane

 

[Access2000_JS1]

the syntax will be wrong but I am sure you can see what I want to achieve. Some help with the syntax will be much appreciated


Private Sub ClientId_AfterUpdate()

Dim varName As Variant // to store the results
Dim sqlstr As String

' Check if combo box is null
If (IsNull(Me.ClientId)) Then
varName = ""
Else
rem find the values

rem this statement will return all client locations that belong to a client
sqlstr = "Select location from client_loc where clientID =" & Forms!job.ClientId

rem put the results into varName
varName = DoCmd.RunSQL sqlstr

End If

If (varName = "" Or IsNull(varName)) Then
Me.location = "(None found)"
else
' Otherwise, point it at the combo 'location' in the form 'job'
Me.location = varName
End If

End Sub

 

[ShaneMan]

Hey JS,

I think I see what your trying to do. Reworked your code so try this:

Private Sub ClientId_AfterUpdate()

Dim stSQL As String

' Check if combo box is null
If (IsNull(Me.ClientId)) Then
Msgbox "Client ID Must Be Filled Out"
Me.ClientID.SetFocus
Exit Sub
Else
stSQL = "SELECT location FROM client_loc WHERE clientID =" & Forms! job.ClientId
End If

If Len(stSQL) = 0 Then
Msgbox "No Matching Records"
'or whatever you what to do here if no records match clientID in client_loc
Else
' Otherwise, point it at the combo 'location' in the form 'job'
Me.location.RowSource = stSQL
End If

End Sub

Hope I got it right!

HTH,
Shane

 

[Access2000_JS1]

Thanks Shane. I just added a quote around the string

stSQL = "SELECT location FROM client_loc WHERE clientID = '" & Forms!job.ClientId + "'"

and I got the required result.

Cheers.