HELP Compile Error Method or Data Member Not found

[Comic1]

Hi,
I am having trouble in setting up this form. There is a table that contains all vendor calls we have gotten. On the form for data entry I have a button that when clicked another form appears so that the user can select a date range and can have select one or more selections for the "Status" of each call. This is the code for that 2nd form. I also have a query that allows the date range selection & Status selection in the form. This will all be brought up in a report once selected. This is the coding that I get the Compile error message. The qdf.SQL = strSQL is what is highlighted with the error message. Any help would be greatly appreciated.

Private Sub OK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDefs
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Copy of Date Range Query")
' Loop through the selected items in the list box and build a text string
    If Me!Status.ItemsSelected.Count > 0 Then
        For Each varItem In Me!Status.ItemsSelected
            strCriteria = strCriteria & "Vendor Hotline Log.Status = " & Chr(34) _
                          & Me!Status.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
        strCriteria = "Vendor Hotline Log.Status Like '*'"
    End If
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM Vendor Hotline Log " & _
             "WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
    qdf.SQL = strSQL
' Open the query
    DoCmd.OpenQuery "Copy of Date Range Query"
' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
End Sub

 

[boblarson]

Remove the S

Dim qdf As DAO.QueryDefs

should be

Dim qdf As DAO.QueryDef

 

[Comic1]

Now I'm getting a new error. The expression On Click you entered as the event property setting produced the following error: Method or data member not found

 

[boblarson]

Now I'm getting a new error. The expression On Click you entered as the event property setting produced the following error: Method or data member not found

which line does it highlight now? (oh, and the removal of the S was ONLY in the declarations, not in the other part).

 

[Comic1]

It's giving me a Run Time error 3131, Syntax Error in FROM clause & it highlights the qdf.SQL = strSQL line

 

[boblarson]

Looks like you're missing a space at the red area:

& Me!Status.ItemData(varItem) & Chr(34) & "OR "

should be

& Me!Status.ItemData(varItem) & Chr(34) & " OR "

 

[Comic1]

It's still giving me the same error & highlights the same line

 

[boblarson]

Put in

Debug.Print strSQL

just after you assign the value to it and then copy that and post it here, please.

Oh, and the function again with the fixes I've already mentioned.

 

[Comic1]

Here it is

Private Sub OK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Copy of Date Range Query")
' Loop through the selected items in the list box and build a text string
If Me!Status.ItemsSelected.Count > 0 Then
For Each varItem In Me!Status.ItemsSelected
strCriteria = strCriteria & "Vendor Hotline Log.Status = " & Chr(34) _
& Me!Status.ItemData(varItem) & Chr(34) & " OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Vendor Hotline Log.Status Like '*'"
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Vendor Hotline Log " & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
Debug.Print strSQL
' Open the query
DoCmd.OpenQuery "Copy of Date Range Query"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

 

[spikepl]

Vendor Hotline Log -> [Vendor Hotline Log]

 

[Comic1]

Where does that go?

 

[boblarson]

Where does that go?

Where you are referring to a table or field with spaces. So you would need to include the square brackets around that table reference. But if that is the only table in the query that has that field then you don't need to include it you can just use

& "Status= " & Chr(34)....etc.

 

[Comic1]

It is almost there, I didn't need to use the brackets. When I click on the OK cmd button it still gives me the compile error 3131 Syntax error in FROM Clause & highlights the qdf.SQL = strSQL line but when I mouse over the StrSQL is is showing that I have selected both Closed & open status or if I only selected open or closed is shows that also. Am I missing something in the FROM clause is there punctuation I should have that I don't.

Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Vendor Hotline Log " &
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
Debug.Print strSQL
' Open the query
DoCmd.OpenQuery "Copy of Date Range Query"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

 

[boblarson]

Umm, yes you DO need square brackets. Access is parsing it and needs those to tell it you want all of those words to refer to a table. So you need:

strSQL = "SELECT * FROM [Vendor Hotline Log] " &
"WHERE " & strCriteria & ";"

Those brackets are CRUCIAL in Access.

 

[boblarson]

Oh, and as for status - nowhere are you limiting it to a single status:

strCriteria = strCriteria & "Vendor Hotline Log.Status = " & Chr(34) _
   & Me!Status.ItemData(varItem) & Chr(34) & " OR "

You would need

If Me!Status.ItemData(varItem) = "WhateverStatusYouWantHere" Then
   strCriteria = strCriteria & "Vendor Hotline Log.Status = " & Chr(34) _
& Me!Status.ItemData(varItem) & Chr(34) & " OR "
End If

 

[Comic1]

Re: HELP new syntax error

I am now getting a new error & I know it's something simple I'm just not seeing & it's driving me crazy. The error is missing operator in query expression 'Vendor Hotline Log.Status = "Closed"'. I think it might be in my query in the Status field I have this in Criteria

 In (([Vendor Hotline Log].[Status])='Open' Or ([Vendor Hotline Log].[Status])='Closed')

 

[boblarson]

You don't use IN and the OR together. You would use

[Vendor Hotline Log].[Status] IN('Open', 'Closed')

Or

[Vendor Hotline Log].[Status]='Open' Or [Vendor Hotline Log].[Status]='Closed'

(parens removed for easier reading - and usually the parens really aren't necessary but Access puts them there.