Setting a variable value using a SELECT query

[benkingery]

I have a variable I need to populate with a value and in turn populate a field on a form with that variable value.

The value I want to set the variable to is the result of this query:

SELECT TOP 1 WarehouseLocation.LocationCode FROM WarehouseLocation WHERE (WarehouseLocation.CurrentUnits < WarehouseLocation.MaxUnits) AND (LocationCode Like 'LT*') ORDER BY LocationCode

What i currently have is this:

Dim SelectedLocationSQL As String
Dim SelectedLocation As String

SelectedLocationSQL = "SELECT TOP 1 WarehouseLocation.LocationCode FROM WarehouseLocation WHERE (WarehouseLocation.CurrentUnits < WarehouseLocation.MaxUnits) AND (LocationCode Like 'LT*') ORDER BY LocationCode"
SelectedLocation = CurrentProject.Connection.Execute("SelectedLocationSQL").Fields(0).Value

Me.Location = SelectedLocation

I'm struggling with this. I'm new to this type of coding. Anyone offer some solution?

Thanks in advance.

 

[pbaldy]

More like:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "Your SQL string"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Me.Location = rs!LocationCode 
  
  set rs = nothing
  set db = nothing

 

[benkingery]

That was it! Thank you. I figured it had something to do with a record set. Whenever I get to these types of things I get out of my league.

Thank you

 

[pbaldy]

Happy to help!