?
Solved

Using List boxes to query a form will only show info for criteria not the rest of the fields. Error Name!? for other fields

Posted on 2013-11-01
9
Medium Priority
?
371 Views
Last Modified: 2013-11-17
This code allows a user to select criteria as many as they need In 3  List Boxes. Then that information is suppose to populate a Sub form.

The data that is selected shows the criteria that is selected but doesnt show the information for the rest of the fields in the subform. Where and how do I put the code to join the forms to work together and show all the data for the subform?

Below is the code the tables were linked so I had to change them.

 Dim sf As SubForm
    Dim MODKitStr As String, DIVStr As String, BdeStr As String
    Dim SQLStr As String
    Dim ListCounter As Integer
    Dim AnItem As Variant
     
    For Each AnItem In Me.lstMODKit.ItemsSelected
        If Not IsNull(AnItem) Then
            MODKitStr = MODKitStr & "'" & Me.lstMODKit.ItemData(AnItem) & "',"
        End If
    Next AnItem
    If Len(MODKitStr) = 0 Then
        Call noselection("A")
        Exit Sub
    Else
        MODKitStr = Left(MODKitStr, Len(MODKitStr) - 1)
    End If
     
    For Each AnItem In Me.lstDIV.ItemsSelected
        If Not IsNull(AnItem) Then
            DIVStr = DIVStr & "'" & Me.lstDIV.ItemData(AnItem) & "',"
        End If
    Next AnItem
    If Len(DIVStr) = 0 Then
        Call noselection("B")
        Exit Sub
    Else
        DIVStr = Left(DIVStr, Len(DIVStr) - 1)
    End If
       
    For Each AnItem In Me.lstBde.ItemsSelected
        If Not IsNull(AnItem) Then
            BdeStr = BdeStr & "'" & Me.lstBde.ItemData(AnItem) & "',"
        End If
    Next AnItem
    If Len(BdeStr) = 0 Then
        Call noselection("C")
        Exit Sub
    Else
        BdeStr = Left(BdeStr, Len(BdeStr) - 1)
    End If
    SQLStr = "SELECT tblMODHistory.Bde,tblMODHistory.DIV,tblMODHistory.[MOD Kit]   " & _
    "FROM qryMODHistory " & _
    "WHERE qryMODHistory.Bde IN(" & BdeStr & ") and qryMODHistory.DIV IN(" & DIVStr & ") and qryMODHistory.[MOD Kit] IN(" & MODKitStr & ")"
    Set sf = Me.[qryMODHistory_Crosstab subform]
    Debug.Print SQLStr
    sf.Controls.Parent.RecordSource = SQLStr
    sf.Requery

   End Sub
Private Sub noselection(LType As String)
    Dim msg As String
    Select Case LType
    Case Is = "A"
        msg = "No MOD Kits "
    Case Is = "B"
        msg = "No DIvision "
    Case Is = "C"
        msg = "No Brigade "
    Case Else
        msg = "Error : Do not know list box type :"
        End Select

    Me.[qryMODHistory_Crosstab subform].Controls.Parent.RecordSource = "SELECT qryMODHistory.Bde  ,qryMODHistory.DIV, qryMODHistory.[MOD Kit] FROM qryMODHistory WHERE qryMODHistory.[MOD Kit]  = '£$^'"
    Me.[qryMODHistory_Crosstab subform].Requery
    msg = msg & "have been selected" & vbCrLf & "Record was not updated"
    MsgBox msg
End Sub
 


Private Sub lstMODKit_AfterUpdate()
    Call DoubleMultiSelect_Click
End Sub
 
 
Private Sub lstDIV_AfterUpdate()
    Call DoubleMultiSelect_Click
End Sub

Private Sub lstBde_AfterUpdate()
    Call DoubleMultiSelect_Click
End Sub


exampleHelpwithListSearch.zip
0
Comment
Question by:gigifarrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39617463
test this,

note that --some of the columns in your query are not valid...
HelpwithListSearchRev.accdb
0
 

Author Comment

by:gigifarrow
ID: 39617647
Thank you for your time and help. I tried that rev database and Im still getting #Name?.

Can you tell what do you mean by some of my columns are not valid? What is making them not valid?
0
 

Author Comment

by:gigifarrow
ID: 39617657
Can you tell me what you changed on the example database please? Thanks in advance.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39617881
open the one that you uploaded and you will see the error when you open the form.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39617886
<Can you tell me what you changed on the example database please? Thanks in advance. >

open the form vba module,

look for the lines i commented,
the change is the line below/above the commented lines
0
 

Author Comment

by:gigifarrow
ID: 39621485
I saw the changes you made and I Incorporated them in my database. However, I am still getting the error #NAME?

You see the form is a crosstab query. The columns are in a field called Model. So instead of showing these fields under model, in the query it is shown as a individual column.


How do I make the Crosstabform work with the selections?

example
0
 

Author Comment

by:gigifarrow
ID: 39621681
I dont understand why you changed it from Sub Form to form. It stills is showing the same error messages.
0
 

Accepted Solution

by:
gigifarrow earned 0 total points
ID: 39641684
I will give myself credit because I figured it out on my own. The problem was I needed to make the query from the same table.
0
 

Author Closing Comment

by:gigifarrow
ID: 39654299
I figured out the answer to my own question.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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