Help with a Query running off of VBA

  • Thread starter SteelFire via AccessMonster.com
  • Start date
S

SteelFire via AccessMonster.com

I am trying to get my query to run off of VBA. As of now, I could not get it
to work so have made an SQL to run it. The SQL is at the bottom. The VBA that
I was running keeps bringing up the error 3011 that reads "The MS Office
Access database engine could not find the object" if strWhere is set as the
[FilterName] and error 3126 that reads "Invalid bracketing of the name" if it
is set as the [WhereCondition]. How would I go about making the changes
needed to make the VBA work for the query. The VBA reads:
=========================================================================
VBA-----
=========================================================================

Private Sub cmdOK_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

On Error GoTo Err_Handler

'Start of Body of Procedure***********************************
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "([tblPartList.CategoryName] = """ & Me.
cboCategory & """) AND "
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & "([CategoryType] = """ & Me.cboType & """) AND
"
End If

If Not IsNull(Me.txtSDate) Then
strWhere = strWhere & "([RevDate] >= " & Format(Me.txtSDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEDate) Then
strWhere = strWhere & "([RevDate] < " & Format(Me.txtEDate + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.cboDrawn) Then
strWhere = strWhere & "([DrawnBy] = """ & Me.cboDrawn & """) AND "
End If

If Not IsNull(Me.cboPart) Then
strWhere = strWhere & "([PartNum] = """ & Me.cboPart & """) AND "
End If

If Not IsNull(Me.cboJob) Then
strWhere = strWhere & "([JobNum] = """ & Me.cboJob & """) AND "
End If

If Not IsNull(Me.cboLocation) Then
DoCmd.OpenQuery "qryCustomers"
strWhere = strWhere & "([CustomerID] = SELECT DISTINCT qryCustomers.
CustomerID FROM qryCustomers ORDER BY qryCustomers.CustomerID) AND "
DoCmd.Close acQuery, "qryCustomers"
End If

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "([PartDescription] = ""*" & Me.txtDesc & "*"")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
DoCmd.Minimize
DoCmd.OpenForm "frmPartList", acNormal, , , strWhere
strWhere = ""
End If
'End of Body of Procedure***********************************

Exit_Handler:
Exit Sub
Err_Handler:
Call LogError(Err.Number, Err.Description, "cmdOK_Click from
Form_frmPartFinder")
Resume Exit_Handler
End Sub
=========================================================================
SQL-----
=========================================================================
SELECT tblPartTable.ID, tblPartTable.ProjectName, tblPartTable.
PartDescription, tblPartTable.PartNum, tblPartTable.JobNum, tblPartTable.
CategoryName, tblPartTable.CategoryType, tblPartTable.Rev, tblPartTable.
RevDate, tblPartTable.CustomerID, tblPartTable.FilePath, tblPartTable.
PartDrawingAtt, tblPartTable.Notes
FROM tblPartTable
WHERE (tblPartTable.CategoryName =[Forms]![frmPartFinder]![cboCategory] OR
[Forms]![frmPartFinder]![cboCategory] Is Null)

AND (tblPartTable.CategoryType =[Forms]![frmPartFinder]![cboType] OR [Forms]!
[frmPartFinder]![cboType] Is Null)

AND (tblPartTable.RevDate >=[Forms]![frmPartFinder]![txtSDate] OR [Forms]!
[frmPartFinder]![txtSDate] Is Null)

AND (tblPartTable.RevDate <[Forms]![frmPartFinder]![txtEDate] OR [Forms]!
[frmPartFinder]![txtEDate] Is Null)

AND (tblPartTable.DrawnBy=[Forms]![frmPartFinder]![cboDrawn] OR [Forms]!
[frmPartFinder]![cboDrawn] Is Null)

AND (tblPartTable.PartNum=[Forms]![frmPartFinder]![cboPart] OR [Forms]!
[frmPartFinder]![cboPart] Is Null)

AND (tblPartTable.JobNum =[Forms]![frmPartFinder]![cboJob] OR [Forms]!
[frmPartFinder]![cboJob] Is Null)

AND (tblPartTable.CustomerID = [Forms]![frmPartFinder]![cboCID] OR [Forms]!
[frmPartFinder]![cboCID] Is Null)

AND (tblPartTable.PartDescription Like "*" &[Forms]![frmPartFinder]![txtDesc]
& "*" OR [Forms]![frmPartFinder]![txtDesc] Is Null);
=========================================================================
 
D

Dirk Goldgar

SteelFire via AccessMonster.com said:
I am trying to get my query to run off of VBA. As of now, I could not get
it
to work so have made an SQL to run it. The SQL is at the bottom. The VBA
that
I was running keeps bringing up the error 3011 that reads "The MS Office
Access database engine could not find the object" if strWhere is set as
the
[FilterName] and error 3126 that reads "Invalid bracketing of the name" if
it
is set as the [WhereCondition]. How would I go about making the changes
needed to make the VBA work for the query. The VBA reads:
=========================================================================
VBA-----
=========================================================================

Private Sub cmdOK_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in
a JET query string.

On Error GoTo Err_Handler

'Start of Body of Procedure***********************************
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "([tblPartList.CategoryName] = """ & Me.
cboCategory & """) AND "
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & "([CategoryType] = """ & Me.cboType & """)
AND
"
End If

If Not IsNull(Me.txtSDate) Then
strWhere = strWhere & "([RevDate] >= " & Format(Me.txtSDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEDate) Then
strWhere = strWhere & "([RevDate] < " & Format(Me.txtEDate + 1,
conJetDate) & ") AND "
End If

If Not IsNull(Me.cboDrawn) Then
strWhere = strWhere & "([DrawnBy] = """ & Me.cboDrawn & """) AND "
End If

If Not IsNull(Me.cboPart) Then
strWhere = strWhere & "([PartNum] = """ & Me.cboPart & """) AND "
End If

If Not IsNull(Me.cboJob) Then
strWhere = strWhere & "([JobNum] = """ & Me.cboJob & """) AND "
End If

If Not IsNull(Me.cboLocation) Then
DoCmd.OpenQuery "qryCustomers"
strWhere = strWhere & "([CustomerID] = SELECT DISTINCT
qryCustomers.
CustomerID FROM qryCustomers ORDER BY qryCustomers.CustomerID) AND "
DoCmd.Close acQuery, "qryCustomers"
End If

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "([PartDescription] = ""*" & Me.txtDesc &
"*"")
AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
DoCmd.Minimize
DoCmd.OpenForm "frmPartList", acNormal, , , strWhere
strWhere = ""
End If
'End of Body of Procedure***********************************

Exit_Handler:
Exit Sub
Err_Handler:
Call LogError(Err.Number, Err.Description, "cmdOK_Click from
Form_frmPartFinder")
Resume Exit_Handler
End Sub
=========================================================================
SQL-----
=========================================================================
SELECT tblPartTable.ID, tblPartTable.ProjectName, tblPartTable.
PartDescription, tblPartTable.PartNum, tblPartTable.JobNum, tblPartTable.
CategoryName, tblPartTable.CategoryType, tblPartTable.Rev, tblPartTable.
RevDate, tblPartTable.CustomerID, tblPartTable.FilePath, tblPartTable.
PartDrawingAtt, tblPartTable.Notes
FROM tblPartTable
WHERE (tblPartTable.CategoryName =[Forms]![frmPartFinder]![cboCategory] OR
[Forms]![frmPartFinder]![cboCategory] Is Null)

AND (tblPartTable.CategoryType =[Forms]![frmPartFinder]![cboType] OR
[Forms]!
[frmPartFinder]![cboType] Is Null)

AND (tblPartTable.RevDate >=[Forms]![frmPartFinder]![txtSDate] OR [Forms]!
[frmPartFinder]![txtSDate] Is Null)

AND (tblPartTable.RevDate <[Forms]![frmPartFinder]![txtEDate] OR [Forms]!
[frmPartFinder]![txtEDate] Is Null)

AND (tblPartTable.DrawnBy=[Forms]![frmPartFinder]![cboDrawn] OR [Forms]!
[frmPartFinder]![cboDrawn] Is Null)

AND (tblPartTable.PartNum=[Forms]![frmPartFinder]![cboPart] OR [Forms]!
[frmPartFinder]![cboPart] Is Null)

AND (tblPartTable.JobNum =[Forms]![frmPartFinder]![cboJob] OR [Forms]!
[frmPartFinder]![cboJob] Is Null)

AND (tblPartTable.CustomerID = [Forms]![frmPartFinder]![cboCID] OR
[Forms]!
[frmPartFinder]![cboCID] Is Null)

AND (tblPartTable.PartDescription Like "*"
&[Forms]![frmPartFinder]![txtDesc]
& "*" OR [Forms]![frmPartFinder]![txtDesc] Is Null);
=========================================================================
Click to expand...


I see several problems here. The most obvious one is that this line:
DoCmd.OpenForm "frmPartList", acNormal, , , strWhere
Click to expand...

.... has your where-condition in the wrong place. It should be:

DoCmd.OpenForm "frmPartList", acNormal, , strWhere

But from what you write, I'm guessing that you may have had it there
already, and encountered a different problem, as your where-condition is
also not well-formed.

What are you trying to do with these lines:
If Not IsNull(Me.cboLocation) Then
DoCmd.OpenQuery "qryCustomers"
strWhere = strWhere & "([CustomerID] = SELECT DISTINCT
qryCustomers.
CustomerID FROM qryCustomers ORDER BY qryCustomers.CustomerID) AND "
DoCmd.Close acQuery, "qryCustomers"
End If
Click to expand...

? I think you are trying filter for just the customer or customers that are
returned by qryCustomers. You don't need to open that query for this, so
the DoCmd.OpenQuery and DoCmd.Close lines aren't needed there. Maybe this
is what you want:

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "([CustomerID] In (" & _
"SELECT DISTINCT CustomerID FROM qryCustomers" & _
)) AND "
End If

Also, in these lines:
If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "([PartDescription] = ""*" & Me.txtDesc &
"*"")
AND "
End If
Click to expand...

.... it looks like you want to use wild-card matching, in which case you need
to use the Like operator:

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "([PartDescription] Like ""*" & _
Me.txtDesc & "*"") AND "
End If
 
S

SteelFire via AccessMonster.com

Okay, the first problem you spotted was a typo. Must have typed it out wrong
or something. For the second one, I got rid of the open/close query and put
in the "In()" code. I also changed the '=' to a 'Like'. Now when I run it,
the query comes up, but none of the filtering was done. Any idea as to why?
My new VBA reads:

===============================================================================
Private Sub cmdOK_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in
a JET query string.

On Error GoTo Err_Handler

'Start of Body of Procedure***********************************
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "(tblPartTable.CategoryName = """ & Me.
cboCategory & """) AND "
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & "(tblPartTable.CategoryType = """ & Me.cboType
& """) AND "
End If

If Not IsNull(Me.txtSDate) Then
strWhere = strWhere & "(tblPartTable.RevDate >= " & Format(Me.
txtSDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEDate) Then
strWhere = strWhere & "(tblPartTable.RevDate < " & Format(Me.txtEDate
+ 1, conJetDate) & ") AND "
End If

If Not IsNull(Me.cboDrawn) Then
strWhere = strWhere & "(tblPartTable.DrawnBy = """ & Me.cboDrawn &
""") AND "
End If

If Not IsNull(Me.cboPart) Then
strWhere = strWhere & "(tblPartTable.PartNum = """ & Me.cboPart & """)
AND "
End If

If Not IsNull(Me.cboJob) Then
strWhere = strWhere & "(tblPartTable.JobNum = """ & Me.cboJob & """)
AND "
End If

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "(tblPartTable.CustomerID In (SELECT DISTINCT
CustomerID FROM qryCustomers)) AND"
End If

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "(tblPartTable.PartDescription Like ""*" & Me.
txtDesc & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
DoCmd.Minimize
DoCmd.OpenForm "frmPartList", acNormal, , strWhere
strWhere = ""
End If
'End of Body of Procedure***********************************

Exit_Handler:
Exit Sub
Err_Handler:
Call LogError(Err.Number, Err.Description, "cmdOK_Click from
Form_frmPartFinder")
Resume Exit_Handler
End Sub
===============================================================================

Dirk said:
I am trying to get my query to run off of VBA. As of now, I could not get
it
Click to expand...
[quoted text clipped - 127 lines]
& "*" OR [Forms]![frmPartFinder]![txtDesc] Is Null);
=========================================================================
Click to expand...

I see several problems here. The most obvious one is that this line:
DoCmd.OpenForm "frmPartList", acNormal, , , strWhere
Click to expand...

... has your where-condition in the wrong place. It should be:

DoCmd.OpenForm "frmPartList", acNormal, , strWhere

But from what you write, I'm guessing that you may have had it there
already, and encountered a different problem, as your where-condition is
also not well-formed.

What are you trying to do with these lines:
If Not IsNull(Me.cboLocation) Then
DoCmd.OpenQuery "qryCustomers"
Click to expand...
[quoted text clipped - 3 lines]
DoCmd.Close acQuery, "qryCustomers"
End If
Click to expand...

? I think you are trying filter for just the customer or customers that are
returned by qryCustomers. You don't need to open that query for this, so
the DoCmd.OpenQuery and DoCmd.Close lines aren't needed there. Maybe this
is what you want:

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "([CustomerID] In (" & _
"SELECT DISTINCT CustomerID FROM qryCustomers" & _
)) AND "
End If

Also, in these lines:
If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "([PartDescription] = ""*" & Me.txtDesc &
"*"")
AND "
End If
Click to expand...

... it looks like you want to use wild-card matching, in which case you need
to use the Like operator:

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "([PartDescription] Like ""*" & _
Me.txtDesc & "*"") AND "
End If
Click to expand...
 
D

Dirk Goldgar

SteelFire via AccessMonster.com said:
Okay, the first problem you spotted was a typo. Must have typed it out
wrong
or something. For the second one, I got rid of the open/close query and
put
in the "In()" code. I also changed the '=' to a 'Like'. Now when I run it,
the query comes up, but none of the filtering was done. Any idea as to
why?
My new VBA reads:

===============================================================================
Private Sub cmdOK_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to
append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in
a JET query string.

On Error GoTo Err_Handler

'Start of Body of Procedure***********************************
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & "(tblPartTable.CategoryName = """ & Me.
cboCategory & """) AND "
End If

If Not IsNull(Me.cboType) Then
strWhere = strWhere & "(tblPartTable.CategoryType = """ &
Me.cboType
& """) AND "
End If

If Not IsNull(Me.txtSDate) Then
strWhere = strWhere & "(tblPartTable.RevDate >= " & Format(Me.
txtSDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEDate) Then
strWhere = strWhere & "(tblPartTable.RevDate < " &
Format(Me.txtEDate
+ 1, conJetDate) & ") AND "
End If

If Not IsNull(Me.cboDrawn) Then
strWhere = strWhere & "(tblPartTable.DrawnBy = """ & Me.cboDrawn &
""") AND "
End If

If Not IsNull(Me.cboPart) Then
strWhere = strWhere & "(tblPartTable.PartNum = """ & Me.cboPart &
""")
AND "
End If

If Not IsNull(Me.cboJob) Then
strWhere = strWhere & "(tblPartTable.JobNum = """ & Me.cboJob &
""")
AND "
End If

If Not IsNull(Me.cboLocation) Then
strWhere = strWhere & "(tblPartTable.CustomerID In (SELECT DISTINCT
CustomerID FROM qryCustomers)) AND"
End If

If Not IsNull(Me.txtDesc) Then
strWhere = strWhere & "(tblPartTable.PartDescription Like ""*" &
Me.
txtDesc & "*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
DoCmd.Minimize
DoCmd.OpenForm "frmPartList", acNormal, , strWhere
strWhere = ""
End If
'End of Body of Procedure***********************************

Exit_Handler:
Exit Sub
Err_Handler:
Call LogError(Err.Number, Err.Description, "cmdOK_Click from
Form_frmPartFinder")
Resume Exit_Handler
End Sub
Click to expand...


The flaw isn't obvious to me. I suggest that you insert a statement,

Debug.Print strWhere

.... immediately before the DoCmd.OpenForm statement, to print the
where-condition to the Immediate window. Run the code, building a filter as
before, and then copy the where-condition and post it into a reply to this
message. Maybe we'll be able to see the problem.

If there's nothing wrong with the where-condition, then *maybe* there's code
in the Open event of frmPartList that overrides it. That's all I can think
of right now.
 
S

SteelFire via AccessMonster.com

I am not sure what I did, but things are working just fine. I think after a
few days of just playing with code it just happened to start working. I don't
recall, but I think there might have been something in the Open event of
frmPartList. But thanks for all of the help with the first posting.

Dirk said:
Okay, the first problem you spotted was a typo. Must have typed it out
wrong
Click to expand...
[quoted text clipped - 86 lines]
Resume Exit_Handler
End Sub
Click to expand...

The flaw isn't obvious to me. I suggest that you insert a statement,

Debug.Print strWhere

... immediately before the DoCmd.OpenForm statement, to print the
where-condition to the Immediate window. Run the code, building a filter as
before, and then copy the where-condition and post it into a reply to this
message. Maybe we'll be able to see the problem.

If there's nothing wrong with the where-condition, then *maybe* there's code
in the Open event of frmPartList that overrides it. That's all I can think
of right now.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need some help simplifying my query 2
Help with VBA with an unbound form 4
Message Box to Display a count of Records 3

Top