Returning Single Scalar Value

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hi,

When the data in my combo cox changes, I need to execute a
query that returns a single scalar value. The method I am using is
shown below. It seems cumbersome. Is there some easier, less
cumbersome method of accomplishing the same thing?

Private Sub cboParticipant_Change()
Dim rs As ADODB.Recordset
Dim FacilityID As Integer
Dim FacilityName As String
Dim SQL As String
SQL = "SELECT FACILITY FROM PARTICIPANTS WHERE PARTICIPANTID = "
& CStr(cboParticipant.Value)
'MsgBox SQL

If DBConn.State = adStateClosed Then
DBConn.Open ConnStr
End If

'Assign to local variables.'

Set rs = DBConn.Execute(SQL)
With rs
If Not (rs.EOF And rs.BOF) Then
FacilityName = rs.Fields(0)
End If
End With

rs.Close
Set rs = Nothing
Me![Facility] = FacilityName
End Sub


Any Help Appreciated,
Joe
 
Joe,

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Open ConnStr

strSQL = ""SELECT Facility FROM Participants " & _
"WHERE ParticipantID = " & Me!cboParticipant
rs.Open strSQL, cn, adOpenStatic, adLockReadOnly
If Not (rs.BOF And rs.EOF) Then
Me!Facility = rs!Facility
End If

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Private Sub cboParticipant_Change()
Dim rs As ADODB.Recordset
Dim FacilityID As Integer
Dim FacilityName As String
Dim SQL As String
SQL = "SELECT FACILITY FROM PARTICIPANTS WHERE PARTICIPANTID = "
& CStr(cboParticipant.Value)
'MsgBox SQL

If DBConn.State = adStateClosed Then
DBConn.Open ConnStr
End If

'Assign to local variables.'

Set rs = DBConn.Execute(SQL)
With rs
If Not (rs.EOF And rs.BOF) Then
FacilityName = rs.Fields(0)
End If
End With

rs.Close
Set rs = Nothing
Me![Facility] = FacilityName
End Sub
Click to expand...

The Change event probably isn't appropriate: it fires at *every keystroke*,
when the Text value of the combo box "change"s. The AfterUpdate event fires
when a participant ID has been selected.

If the table PARTICIPANTS is linked to your database then one line is all you
need:

Private Sub cboParticipant_AfterUpdate)
Me!Facility = DLookUp("[Facility]", "[Participants]", "[ParticipantID] = " _
& Me!cboParticipant)
End Sub

Or perhaps no code is needed at all: you can set the Control Source of the
textbox Facility to this DLookUp expression.

If PARTICIPANTS is some remote table that will not ordinarily be available
then you may need to fiddle with the connect string... but why would you want
to do it that way? (You may well have a good reason that I don't know, so
don't take that as a slam!)

John W. Vinson [MVP]
 
Back
Top