Using a form to update paramter query

S

Stephen_p

Registered User.
Local time
Today, 22:50
Joined
Nov 20, 2012
Messages
11
Im having serious problems getting my head around this:banghead:I want to pass a list box (which is called Pract_List) to a paramter query called testquery but for some reason it wont work. The line qdf.SQL = strSQL is comming up in the debugger could anyone help?


Private Sub Command_4_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("TESTQUERY")
For Each varItem In Me!Pract_List.ItemsSelected
strCriteria = strCriteria & "," & Me!Pract_List.ItemData(varItem) & ""
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, [Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], [Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], [Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], [Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov]" & _
"WHERE((([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE])=[Forms]![Test_Form]![Pract_List]));IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "TESTQUERY"
Set db = Nothing
Set qdf = Nothing
End Sub
 
From what I read I'm having a hard time finding an issue with the query other than you should really look into using table aliases. That table name is massive lol.

Does the query work directly?
 
You cannot pass a CONTROL to a Query as a Parameter.. What you need is to pass only the strCriteria.. so change your where part to..
Code: 
strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, " & _
            "[Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], " & _
            "[Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], " & _
            "[Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], " & _
            "[Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] " & _
            "WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN(" & strCriteria & "));"
 
Hey when i select a practice code. I basically want to run the query from a list box which will produce results on this selection, I had it as a single parameter box which the owner doesn’t think it is sufficient enough as it is time consuming. Do you know what would be wrong? Now no results appear :banghead:
 
I am sorry but I hardly understood what you were saying there.. Just in plain English explain, what is the data in the table, what you want to output.. For example..
I have a customer table with information like FirstName, LastName, City, Telephone.. I want to select all customers whose city is either Bristol, Bath, Exeter.
Click to expand...
 
In the list box there is alot of Practice codes which represents buildings in northern ireland each practice has a lot of assets assigned to them.

So from the list box i want to select practice codes which are in the same area and populate them into a table and then i can create a macro which will export it to excel

Is that a bit better?
 
Try this coding..
Code: 
Private Sub Command_4_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String, strSQL As String
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("TESTQUERY")
    
    For Each varItem In Me!Pract_List.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!Pract_List.ItemData(varItem) & "'"
    Next varItem
    
    If Len(strCriteria) = 0 Then
        MsgBox "You did not select anything." _
        , vbExclamation, "Nothing to find!"
        Exit Sub
    End If
    
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    
    strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, " & _
            "[Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], " & _
            "[Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], " & _
            "[Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], " & _
            "[Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] " & _
            "WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN(" & strCriteria & "));"
            
    qdf.SQL = strSQL
    DoCmd.OpenQuery "TESTQUERY"
    
    Set db = Nothing
    Set qdf = Nothing
End Sub
If this does not work could you use a Debug.Print statement and see what is placed inside strSQL??
 
The button now works but no query results will appeae, im a bit of a novice how would u do the print debug?

Thanks
Stephen
 
So did the code not run at all before?
Code: 
Private Sub Command_4_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String, strSQL As String
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("TESTQUERY")
    
    For Each varItem In Me!Pract_List.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!Pract_List.ItemData(varItem) & "'"
    Next varItem
    
    If Len(strCriteria) = 0 Then
        MsgBox "You did not select anything." _
        , vbExclamation, "Nothing to find!"
        Exit Sub
    End If
    
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    
    strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, " & _
            "[Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], " & _
            "[Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], " & _
            "[Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], " & _
            "[Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] " & _
            "WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN(" & strCriteria & "));"

    [B][COLOR=Red]Debug.Print strSQL[/COLOR][/B]
    
[B][COLOR=SeaGreen]   [/COLOR][COLOR=SeaGreen] 'On Error Resume Next
    'With CurrentDb
        '.QueryDefs.Delete ("TestQry")
        'Set qdfNew = .CreateQueryDef("TestQry", sqlText)
        '.Close
    'End With
    'DoCmd.OpenQuery ("TestQry")[/COLOR][/B]
    
    Set db = Nothing
    Set qdf = Nothing
End Sub
 
Yea will till you sent me that bit yesterday it just run up an error, now it runs but no results appear when i select and hit the command. If i select nothing an error message does appear saying i selected nothing so it works.

The print debug command sends this statement into the immediate box in visual basic,

SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, [Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], [Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], [Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], [Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN('4'));

I dont think anything else happens
 
I tried out the green bit of coding there but it didnt seem to work also :(
 
Stephen is Practice code Number type? Is it possible to upload a stripped down version of your DB?? preferably in mdb format??
 
Theres like 30,000 records in it, no practice code is text an example of a practice code is Z00004
 
I copied and pasted the results from the immediate window to a new query and it didnt work
 
You must log in or register to reply here.

Similar threads

C
Solved Position form to added record
Replies
8
Views
490
ClaraBarton
C
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
841
Minty
Minty
G
Help with unbound form to filter report
Replies
9
Views
1,006
gojets1721
G
N
Solved How to grab the last updated record in a table
Replies
10
Views
1,396
June7
June7
M
Populate Sub Form with similar to last main form record data
Replies
6
Views
1,395
MHART08
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom