How to write a function that does query? (1 Viewer)

[emsadoon]

I have a query string in the following code. I want to put the entire code into a function so I can use it somewhere else as well.

Private Sub Form_Load()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordset
Dim strQuery As String
Dim db As Database
        strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining Units] " + _
        "FROM (OrderingT INNER JOIN PackingSlipT ON OrderingT.Order_ID = PackingSlipT.Order_ID) INNER JOIN ProductT ON PackingSlipT.PackingSlip_ID = ProductT.PackingSlip_ID " + _
        "WHERE (PackingSlipT.PackingSlip_ID = " & Form.Tag & ") " + _
        "GROUP BY OrderingT.Order_ID, OrderingT.UnitsRequested , OrderingT.OrderStatus;"
        
 Set db = CurrentDb
 Set rstTest = db.OpenRecordset(strQuery)
 
 If Not rstTest.EOF Then
    If rstTest![The Remaining Units] = 0 Then
     
        MsgBox "This order was filled"
     
    
    Else
    
    'If the remaining units is not equal to zero once the form is opened , then the record additions are allowed.
    
     Me.AllowAdditions = True
     
     AddRecord.Enabled = True
     
     
    End If
    
 End If
   
    
  'To read the packing slip number of the product form into a Tag
  
     If InStr(Me.Filter, "=") > 0 Then
        If IsNumeric(Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "="))) Then
            Me.Tag = (Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "=")))
        End If
    End If
        
End Sub

 

[sxschech]

Is this what you were looking to do?

Private Sub Form_Load()
   "Run the code that used to be here without having it listed here"
End Sub

If so, then it would look something like this:

Private Sub Form_Load()
    Call AllowAdditionQuery
End Sub

You would then create a new module in the modules section of the database window. You may also need to change the Me's to variables and pass the form name to them. I haven't tried this code to see if it works, but may be a way to do it.

Sub AllowAdditionQuery()
'To enable the AllowAddition property once the form is opened
Dim dbs As DAO.Database
Dim rstTest As DAO.Recordset
Dim strQuery As String
Dim db As Database
        strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining Units] " + _
        "FROM (OrderingT INNER JOIN PackingSlipT ON OrderingT.Order_ID = PackingSlipT.Order_ID) INNER JOIN ProductT ON PackingSlipT.PackingSlip_ID = ProductT.PackingSlip_ID " + _
        "WHERE (PackingSlipT.PackingSlip_ID = " & Form.Tag & ") " + _
        "GROUP BY OrderingT.Order_ID, OrderingT.UnitsRequested , OrderingT.OrderStatus;"
        
 Set db = CurrentDb
 Set rstTest = db.OpenRecordset(strQuery)
 
 If Not rstTest.EOF Then
    If rstTest![The Remaining Units] = 0 Then
     
        MsgBox "This order was filled"
     
    
    Else
    
    'If the remaining units is not equal to zero once the form is opened , then the record additions are allowed.
    
     Me.AllowAdditions = True
     
     AddRecord.Enabled = True
     
     
    End If
    
 End If
   
    
  'To read the packing slip number of the product form into a Tag
  
     If InStr(Me.Filter, "=") > 0 Then
        If IsNumeric(Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "="))) Then
            Me.Tag = (Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "=")))
        End If
    End If
        
End Sub

 

[emsadoon]

Thanks. I just need the query itself be in the function. I have defined my function below:

Public Function RemainingUnitsQuery(rstTest As Integer) As Integer

    Dim strQuery As String
    Dim dbs As DAO.Database
    Dim rstTest As DAO.Recordset
    Dim db As Database
            
            strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining Units] " + _
            "FROM (OrderingT INNER JOIN PackingSlipT ON OrderingT.Order_ID = PackingSlipT.Order_ID) INNER JOIN ProductT ON PackingSlipT.PackingSlip_ID = ProductT.PackingSlip_ID " + _
            "WHERE (PackingSlipT.PackingSlip_ID = " & Form.Tag & ") " + _
            "GROUP BY OrderingT.Order_ID, OrderingT.UnitsRequested , OrderingT.OrderStatus;"
            
     Set db = CurrentDb
     Set rstTest = db.OpenRecordset(strQuery)
     
    RemainingUnitsQuery = rstTest
     
End Function

And I want to use it in an event like below:

Private Sub Form_Load()
    'To enable the AllowAddition property once the form is opened
    
    
    RemainingUnitQuery (rstTest)
    
     If Not rstTest.EOF Then
     
        If rstTest![The Remaining Units] = 0 Then
         
            MsgBox "This order was filled"
         
        
        Else
        
        'If the remaining units is not equal to zero once the form is opened , then the record additions are allowed.
        
         Me.AllowAdditions = True
         
         AddRecord.Enabled = True
         
         
        End If
        
     End If

Can you please tell me what I should change from the code above.

 

[Mihail]

A lot of words with no meaning.
Exactly, in human language, what you are trying to do ?

 

[gblack]

Do you have to have a function to do this?

Why not create a query def?

That way there's an object you can refer to after you've set your query.

(optional) Create a query and give it a name...

Then do something like this:

Dim strQuery as String

DoCmd.SetWarnings False

Set qrySearch = CurrentDb.QueryDefs("New_Query_Name")

strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining Units] " + _
            "FROM (OrderingT INNER JOIN PackingSlipT ON OrderingT.Order_ID = PackingSlipT.Order_ID) INNER JOIN ProductT ON PackingSlipT.PackingSlip_ID = ProductT.PackingSlip_ID " + _
            "WHERE (PackingSlipT.PackingSlip_ID = " & Form.Tag & ") " + _
            "GROUP BY OrderingT.Order_ID, OrderingT.UnitsRequested , OrderingT.OrderStatus;"


qrySearch.Sql = strQuery
qrySearch.Close

DoCmd.SetWarnings True

Now your New Query Object is set to the above SQL code...

Any other time you need to reference it you'd just do something like:

strQuery = "SELECT * FROM New_Query_NAME;"

 

[sxschech]

Sounds like maybe you would need to use dlookup rather than the sql string. This is a possible idea:

1. Save the sql as a regular query without the where portion.

2. Add a text box on your form and put the dlookup in the Control Source of the properties window. Something like below:

=DLookUp("[The Remaining Units]","queryName","PackingSlip_ID = '" & WHERE CRITERIA & "'")

3. In your On Load event, refer to the textbox and evaluate the result.

If me.txtTest = 0 Then ...