SQL code return value and set to variable.

[penfold1992]

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String
Set dbs = CurrentDb
Dim StatusInt As Integer
 
SQLstr = "SELECT [Status] FROM [Packages] WHERE [Packages].[Package]='" & _
         Me.Workpackagecmb.Value & "'"
Set rs = dbs.OpenRecordset(SQLstr)
 
If IsNull(rs) Then
    TrendValue = 0
    Exit Function
End If

I created this about 1 hour ago but my laptop crashed and didnt save... now for some reason i cant figure out how to solve this meaningless problem...

So, I open a record set and rs is now loaded with the record I want,

how do I assign the value of "Status" as a vba variable. when I try
StatusInt = rs I get the "Type Mismatch" error...

its annoying because this is a simple answer, I know it is because i didnt have this problem before -.-

 

[pr2-eugin]

Why are you troubling with Recordset if the return value is going to be a single value.. Try DLookUp,

statusInt = Nz(DLookUp("[Status], "[Packages], "[Package] = '" & Me.Workpackagecmb & "'"), 0)

The reason for your error is because The Select statement in result to the OpenRecordset, would return (not surprisingly) a recordset - by general meaning a set of records.. So when you try to assign a Set of records to an Integer it does not like it.. The long way to go about doing is..

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String
Set dbs = CurrentDb
Dim StatusInt As Integer
 
SQLstr = "SELECT [Status] FROM [Packages] WHERE [Packages].[Package]='" & _
         Me.Workpackagecmb.Value & "'"
Set rs = dbs.OpenRecordset(SQLstr)

If rs.RecordCount > 0 Then
    StatusInt = rs.Fields(0)
Else
    StatusInt = 0
End If
Set rs = Nothing

 

[penfold1992]

because I was told not to get into the habit of using DLookUp.

this works fine:

SQLstr = "SELECT * FROM [Packages] WHERE [Packages].[Package]='" & _
         Me.Workpackagecmb.Value & "'"
Set rs = dbs.OpenRecordset(SQLstr)
StatusInt = rs![Status]

however its now just on principle... I had this working before just fine and now its grinding my gears.

 

[DavidAtWork]

Declare your variable as the same data type as that of the field being returned in your recordset. What sort of data type is 'Status'?
Dim StatusInt as String/Integer/Boolean

The you can assign the value to your variable using:
StatusInt = rs.Fields("Status")
or StatusInt = rs.Fields(0) if it's the only field

If you're only ever getting the status for one package, you could just use DLookup

David

 

[pr2-eugin]

Using any Domain function in excess is not advisable.. Using them sparingly/occasionally is/will not a problem.. They should mainly be avoided in Form Events.. Like Open/Load/Current/Close etc.. If you only want one Field from the table for the given condition, after update of a combobox you can use DLookUp..

But as I said, you can always use Recordset's long way.. Make sure you close them and also add an If condition to differentiate between 'No match' and 'Empty value'.