CODEWITHSUNDEEP
ms-accessvba
Oba Mandley
Oba Mandley

Reputation: 1

Create a query from a button on a form

I have a form with a button on it. I want the button to create a query from a table (which the form populates)

I made the button, went to the code builder

Private sub button123_on click()

End sub

I've looked up queries in DOA but i cant figure it out, or even know if that is what im supposed to be using. I just need to know what comes after private sub

If statements? Dim stuff? doCmd? šŸ¤·šŸ¾ā€ā™‚ļø

Im just looking for the basic layout

Do i build the query elsewhere and then put a command to run it for the button? It has to be in VBA because i need to select the TOP variable# of records. The TOP changes so i cant do it in sql.

After some research this is my code

Private Sub Command487_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Dim varX    As Variant
Set qdef = db.CreateQueryDef("MyQuery")
Application.RefreshDatabaseWindow
varX = DLookup("[Quantity1]", "tblFilledRequests", "[OrderID] = [Forms]![frmFilledRequests]![OrderID]")

strSQL = "SELECT TOP varX tblFilledRequests.OrderID, tblFilledRequests.RequestFillDate, tblFilledRequests.Issuer, tblFilledRequests.Unit, tblFilledRequests.ContactNumber, tblFilledRequests.CommonName1, tblFilledRequests.Quantity1, tblFilledRequests.CommonName2, tblFilledRequests.Quantity2, tblWeapons.IssueCount, tblWeapons.StockNumber, tblWeapons.SerialNumber, tblWeapons.Status " _
& "FROM tblWeapons INNER JOIN tblFilledRequests ON tblWeapons.WeaponID = tblFilledRequests.CommonName1 " _
& "WHERE (((tblFilledRequests.OrderID)=Forms!frmFilledRequests!OrderID) And ((tblWeapons.Status)=""AVAILABLE"")) " _
& "ORDER BY tblWeapons.IssueCount, tblWeapons.StockNumber;"

qdf.SQL = strSQL
DoCmd.OpenQuery "MyQuery"
qdf.Close
Set qdef = Nothing
Set db = Nothing

End Sub

I get a blank query and i get an error message qdf.SQL object variable or with block variable not set

Upvotes: 0

Views: 531

Answers (3)

Oba Mandley
Oba Mandley

Reputation: 1

Private Sub Command490_Click()
   Dim db As DAO.Database
   Set db = CurrentDb
   Dim qdf As DAO.QueryDef
   Dim strSQL As String
   Dim limit As Integer
   limit = Me.Quantity1.Value

   On Error Resume Next
   DoCmd.DeleteObject acQuery, "testQry"
   On Error GoTo 0

   strSQL = "SELECT TOP " & limit & " tblFilledRequests.OrderID, 
   tblFilledRequests.RequestFillDate, tblFilledRequests.Issuer, 
   tblFilledRequests.Unit, tblFilledRequests.ContactNumber, 
   tblFilledRequests.CommonName1, tblFilledRequests.Quantity1, 
   tblFilledRequests.CommonName2, tblFilledRequests.Quantity2, tblWeapons.IssueCount, 
   tblWeapons.StockNumber, tblWeapons.SerialNumber, tblWeapons.Status " & vbCrLf & _
   "FROM tblWeapons INNER JOIN tblFilledRequests ON tblWeapons.WeaponID = 
   tblFilledRequests.CommonName1 " & vbCrLf & _
   "WHERE (((tblFilledRequests.OrderID)=[Forms]![frmFilledRequests]![OrderID]) AND 
   ((tblWeapons.Status)=""AVAILABLE"")) " & vbCrLf & _
   "ORDER BY tblWeapons.IssueCount, tblWeapons.StockNumber;"

   Set qdf = db.CreateQueryDef("testQry", strSQL)

   DoCmd.OpenQuery ("testQry")
End Sub

It all works beautifully. Thank you

Upvotes: 0

Oba Mandley
Oba Mandley

Reputation: 1

thank you for the advice, Grimlor.

no, i am not trying to display the results of the query on the current form. I actually need to display them in an array on another form, but that is a little down the road.

i need to write the query in VBA

for my TOP I used the DLookup function. and defined varX as a variable

varX = DLookup("[Quantity1]", "tblFilledRequests", "[OrderID] = [Forms]!
[frmFilledRequests]![OrderID]")

i will take what i can from your answer

Upvotes: 0

Grimlor
Grimlor

Reputation: 21

I'm going to assume that you want to execute that query and display the results in an element on your form, for example, a listbox.

I've made a form with:

  • A textbox named txtTopX that will allow the user to input the amount of records they want to retrieve.
  • A button that will execute the query and show the results (btnQuery)
  • A listbox that will display the results. I've named it lstResults and set the Column Count property to the amount of columns my query will return. (in this example, 3)

The code on the form is this:

Private Sub btnQuery_Click()
Dim limit As Integer

'Determine the TOP X limit
If IsNumeric(Me.txtTopX.Value) Then
    limit = Me.txtTopX.Value
Else
    limit = 5 'Some default value incase the input from the textbox is not a number
End If

'Set the rowsource of the listbox to the query's results
lstResults.RowSource = "SELECT TOP " & limit & " col1, col2, col3 FROM Table1 ORDER BY ID DESC"
End Sub

The table in this example is Table1 and has 4 columns: ID, col1, col2 and col3. In my query I'm only showing 3 columns and using the ID column to sort my records on. (as you're planning to use TOP X, I think you want to show the TOP X most recent records)

Upvotes: 0

Related Questions