Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add dropdown to current program

Posted on 2011-03-08
11
Medium Priority
?
193 Views
Last Modified: 2012-05-11
I have added a drop down that selects values from a separate table to filter search results.  I can't see how to fit the drop down into the current procedure.  Also the drop down does not need to be selected every time - just when the user wants to.  Any help is appreciated.
Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim vItem As Variant
   
   ' loop through selected field names
   For Each vItem In Me.lstFieldList.ItemsSelected
      SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
  Next vItem
   
   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [F442audt]"
   SQL = SQL & " Where [D_Date] Between [Begin Date] and [End Date]"
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryF442audt")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryF442audt"
 

End Sub

Open in new window

0
Comment
Question by:JohnMac328
  • 6
  • 5
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35070935
test this


Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim vItem As Variant
   
   ' loop through selected field names
   For Each vItem In Me.lstFieldList.ItemsSelected
      SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
  Next vItem
   
   'check if drop down has value selected
   if me.combo0 & ""<>"" then
      SQL=SQL " And [FieldName]= " & me.combo0
      ' if the fieldName is Text data type, use this
   '  SQL=SQL " And [FieldName]= '" & me.combo0 & "'"
   end if

   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [F442audt]"
   SQL = SQL & " Where [D_Date] Between [Begin Date] and [End Date]"
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryF442audt")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryF442audt"
 

End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35070942
correction

  'check if drop down has value selected
   if me.combo0 & ""<>"" then
      SQL=SQL & " And [FieldName]= " & me.combo0
      ' if the fieldName is Text data type, use this
   '  SQL=SQL & " And [FieldName]= '" & me.combo0 & "'"
   end if
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35071043
sorry, if you intend to add the value selected from the combo as field name in the query, use this
other wise give an explanation how do you want to use the value from the drop down box..
Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim vItem As Variant
   
   ' loop through selected field names
   For Each vItem In Me.lstFieldList.ItemsSelected
      SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
  Next vItem
   
   'check if drop down has value selected
   if me.combo0 & ""<>"" then
      SQL=SQL & ",[" & me.combo0 & "]"
   end if

   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [F442audt]"
   SQL = SQL & " Where [D_Date] Between [Begin Date] and [End Date]"
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryF442audt")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryF442audt"
 

End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:JohnMac328
ID: 35071275
I suppose they would want to see the value in the result query.  When I select the drop-down value I get this screen shot.
Example.jpg
0
 

Author Comment

by:JohnMac328
ID: 35071281
The values are in a separate table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35071378
first create a query showing the fields you want to show in the result.
copy the SQL view and post here..
0
 

Author Comment

by:JohnMac328
ID: 35071595
Here is an example of a possible select.  It is filtering on EDV and that value is in the main table under C_ACCOUNT.  I was putting the combo box values in a seperate table so they would easily be selectable.
SELECT F442audt.D_DATE, F442audt.C_ACCOUNT, F442audt.[C_RECORDTYPE(4)]
FROM F442audt
WHERE (((F442audt.C_ACCOUNT)="EDV"));

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35071623
what is the rowsource of the combo box?
0
 

Author Comment

by:JohnMac328
ID: 35071639
When I am trying to pull the value from the accounts table it is

SELECT Accounts.C_ACCOUNT FROM Accounts;
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35071743
try this


Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String, sWhere as string
   Dim vItem As Variant
   
   ' loop through selected field names
   For Each vItem In Me.lstFieldList.ItemsSelected
      SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
  Next vItem
   
 

   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [F442audt]"
   SQL = SQL & " Where [D_Date] Between [Begin Date] and [End Date]"
 
  'check if drop down has value selected
   if me.combo0 & ""<>"" then
      sWhere= "[C_ACCOUNT]='" & me.combo0 & "'"
      SQL=SQL & " And " & sWhere
   end if

  ' save query with new SQL statement

   Set qDef = CurrentDb.QueryDefs("qryF442audt")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryF442audt"
 

End Sub

Open in new window

0
 

Author Closing Comment

by:JohnMac328
ID: 35071791
That's it - Thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question