Get results from a SQL statement

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

How do I get results from a SQL statement in VBA Code?
What I basically need from the code is how many instances
there are.

Here's my code that populates a list box from when a user
inputs a product code.

Private Sub cmbProductCode_AfterUpdate()

Dim strProduct As String, iPlant As Integer

If Me.OpenArgs = "PEPSAvg" Then
Me.txtLineCode.DefaultValue = Chr$(34) &
Me.txtLineCode & Chr$(34)
End If

If Not IsNull(Me.cmbProductCode) Then
strProduct = Me.cmbProductCode
iPlant = [Forms]![frmAnalysisTG]![cmbPlantCode]
Me.cmbPrintID.RowSource = "SELECT ActivePrint.ID,
ActivePrint.PrintName " _
& "FROM ActivePrint " _
& "WHERE ActivePrint.ProductCode = " & Chr$(34) &
strProduct & Chr$(34) & " " _
& "And ActivePrint.PlantCode = " & iPlant & " And
ActivePrint.IsInactive = 0 " _
& "ORDER BY ActivePrint.PrintName;"
Me.cmbPrintID.Requery
End If

End Sub
 
Eric said:
How do I get results from a SQL statement in VBA Code?
What I basically need from the code is how many instances
there are.

Here's my code that populates a list box from when a user
inputs a product code.

Private Sub cmbProductCode_AfterUpdate()

Dim strProduct As String, iPlant As Integer

If Me.OpenArgs = "PEPSAvg" Then
Me.txtLineCode.DefaultValue = Chr$(34) &
Me.txtLineCode & Chr$(34)
End If

If Not IsNull(Me.cmbProductCode) Then
strProduct = Me.cmbProductCode
iPlant = [Forms]![frmAnalysisTG]![cmbPlantCode]
Me.cmbPrintID.RowSource = "SELECT ActivePrint.ID,
ActivePrint.PrintName " _
& "FROM ActivePrint " _
& "WHERE ActivePrint.ProductCode = " & Chr$(34) &
strProduct & Chr$(34) & " " _
& "And ActivePrint.PlantCode = " & iPlant & " And
ActivePrint.IsInactive = 0 " _
& "ORDER BY ActivePrint.PrintName;"
Me.cmbPrintID.Requery
End If

End Sub
Click to expand...

Is cmbPrintID a list box or combo box (not that it really matters). By
the way, this line
Me.cmbPrintID.Requery
Click to expand...

is not needed, because changing the control's RowSource automatically
forces a requery. No need to do it twice.

Do I take it that you want to know how many items are now in the
cmbPrintID's list? The control has a ListCount property, so you can
just check cmbPrintID.ListCount :

If cmbPrintID.ListCount = 0 Then
' the rowsource returned no records.
End If
 
ERIC'S COMMENTS ARE IN CAPS...

Is cmbPrintID a list box or combo box (not that it really
matters). YES.

By the way, this line Me.cmbPrintID.Requery is not needed,
because changing the control's RowSource automatically
forces a requery. No need to do it twice. OK THANKS!

Do I take it that you want to know how many items are now
in the cmbPrintID's list? YES AGAIN! YOUR GOOD AT ASSUMING!

The control has a ListCount property, so you can
just check cmbPrintID.ListCount :

If cmbPrintID.ListCount = 0 Then
' the rowsource returned no records.
End If

THANKS A MILLION!!!!
 
Back
Top