Solved

Add dropdown to current program

Posted on 2011-03-08
11
186 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now