BuildCriteria Method in VBA

G

Guest

Hello,

I am trying to use the buildcriteria method for a query to use when opening
a form. Please see code below.

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
DoCmd.OpenForm "Location Selection for VE and RFE"
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
DoCmd.OpenForm "Forecast Input"

Where "ComboBU" is a dropdown box on the main menu, and Forms]![Location
Selection for VE and RFE]![Combo0]" is a dropdown box on the form "Location
Selection for VE and RFE".

My question is this: How do I incorporate "Str Criteria" into the query
that the form Forecast Input needs for the criteria?

Please let me know if I'm not being clear.

Thank you,
MN
 
G

Guest

DoCmd.OpenForm "Forecast Input",,,strCriteria

Hint: after you type this: DoCmd.OpenForm "Forecast Input", insert a comma,
and you will receive help tips for the arguments for the OpenForm method. The
current argument's help tip is bold, and will advance to the next as you
insert the commas.
 
G

Guest

Well how does the form know, when opening, that strCriteria is being used for
the JDE Bus Unit Field? Do I need to put something in the query?

I tried doing that and the form opened with no filter...

Thanks

Brian said:
DoCmd.OpenForm "Forecast Input",,,strCriteria

Hint: after you type this: DoCmd.OpenForm "Forecast Input", insert a comma,
and you will receive help tips for the arguments for the OpenForm method. The
current argument's help tip is bold, and will advance to the next as you
insert the commas.

MacNut2004 said:
Hello,

I am trying to use the buildcriteria method for a query to use when opening
a form. Please see code below.

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
DoCmd.OpenForm "Location Selection for VE and RFE"
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
DoCmd.OpenForm "Forecast Input"

Where "ComboBU" is a dropdown box on the main menu, and Forms]![Location
Selection for VE and RFE]![Combo0]" is a dropdown box on the form "Location
Selection for VE and RFE".

My question is this: How do I incorporate "Str Criteria" into the query
that the form Forecast Input needs for the criteria?

Please let me know if I'm not being clear.

Thank you,
MN
Click to expand...
Click to expand...
 
G

Guest

Ensure that "JDE Bus Unit" is included as a field in the query. The fourth
argument, where we placed strCriteria, simply adds a WHERE clause to the
query. In fact, if you open your form using the button, then switch to design
view and look at the form's properties -> Data tab -> Filter, you should see
the results of your strCriteria.

Try the code below and let me know what you see in the message box. It
should display the strCriteria, which should look something like this: [JDE
Bus Unit] = 15

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
MsgBox "strCriteria = " & strCriteria
DoCmd.OpenForm "Location Selection for VE and RFE",,,strCriteria
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
MsgBox "strCriteria = " & strCriteria
DoCmd.OpenForm "Forecast Input",,,strCriteria
End If

MacNut2004 said:
Well how does the form know, when opening, that strCriteria is being used for
the JDE Bus Unit Field? Do I need to put something in the query?

I tried doing that and the form opened with no filter...

Thanks

Brian said:
DoCmd.OpenForm "Forecast Input",,,strCriteria

Hint: after you type this: DoCmd.OpenForm "Forecast Input", insert a comma,
and you will receive help tips for the arguments for the OpenForm method. The
current argument's help tip is bold, and will advance to the next as you
insert the commas.

MacNut2004 said:
Hello,

I am trying to use the buildcriteria method for a query to use when opening
a form. Please see code below.

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
DoCmd.OpenForm "Location Selection for VE and RFE"
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
DoCmd.OpenForm "Forecast Input"

Where "ComboBU" is a dropdown box on the main menu, and Forms]![Location
Selection for VE and RFE]![Combo0]" is a dropdown box on the form "Location
Selection for VE and RFE".

My question is this: How do I incorporate "Str Criteria" into the query
that the form Forecast Input needs for the criteria?

Please let me know if I'm not being clear.

Thank you,
MN
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

thanks!

ok....this is what I see:

strCriteria = JDE Bus Unit = [Forms]![Main]![ComboBU]

Brian said:
Ensure that "JDE Bus Unit" is included as a field in the query. The fourth
argument, where we placed strCriteria, simply adds a WHERE clause to the
query. In fact, if you open your form using the button, then switch to design
view and look at the form's properties -> Data tab -> Filter, you should see
the results of your strCriteria.

Try the code below and let me know what you see in the message box. It
should display the strCriteria, which should look something like this: [JDE
Bus Unit] = 15

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
MsgBox "strCriteria = " & strCriteria
DoCmd.OpenForm "Location Selection for VE and RFE",,,strCriteria
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
MsgBox "strCriteria = " & strCriteria
DoCmd.OpenForm "Forecast Input",,,strCriteria
End If

MacNut2004 said:
Well how does the form know, when opening, that strCriteria is being used for
the JDE Bus Unit Field? Do I need to put something in the query?

I tried doing that and the form opened with no filter...

Thanks

Brian said:
DoCmd.OpenForm "Forecast Input",,,strCriteria

Hint: after you type this: DoCmd.OpenForm "Forecast Input", insert a comma,
and you will receive help tips for the arguments for the OpenForm method. The
current argument's help tip is bold, and will advance to the next as you
insert the commas.

:

Hello,

I am trying to use the buildcriteria method for a query to use when opening
a form. Please see code below.

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
DoCmd.OpenForm "Location Selection for VE and RFE"
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
DoCmd.OpenForm "Forecast Input"

Where "ComboBU" is a dropdown box on the main menu, and Forms]![Location
Selection for VE and RFE]![Combo0]" is a dropdown box on the form "Location
Selection for VE and RFE".

My question is this: How do I incorporate "Str Criteria" into the query
that the form Forecast Input needs for the criteria?

Please let me know if I'm not being clear.

Thank you,
MN
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I also then get a run-time error of 3075. "Syntax error (missing oeprator)
in query expression 'JDE Bus Unit = [Forms]![Main]![ComboBU]'

Brian said:
Ensure that "JDE Bus Unit" is included as a field in the query. The fourth
argument, where we placed strCriteria, simply adds a WHERE clause to the
query. In fact, if you open your form using the button, then switch to design
view and look at the form's properties -> Data tab -> Filter, you should see
the results of your strCriteria.

Try the code below and let me know what you see in the message box. It
should display the strCriteria, which should look something like this: [JDE
Bus Unit] = 15

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
MsgBox "strCriteria = " & strCriteria
DoCmd.OpenForm "Location Selection for VE and RFE",,,strCriteria
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
MsgBox "strCriteria = " & strCriteria
DoCmd.OpenForm "Forecast Input",,,strCriteria
End If

MacNut2004 said:
Well how does the form know, when opening, that strCriteria is being used for
the JDE Bus Unit Field? Do I need to put something in the query?

I tried doing that and the form opened with no filter...

Thanks

Brian said:
DoCmd.OpenForm "Forecast Input",,,strCriteria

Hint: after you type this: DoCmd.OpenForm "Forecast Input", insert a comma,
and you will receive help tips for the arguments for the OpenForm method. The
current argument's help tip is bold, and will advance to the next as you
insert the commas.

:

Hello,

I am trying to use the buildcriteria method for a query to use when opening
a form. Please see code below.

Dim strCriteria As String
DoCmd.Minimize
If IsNull(Me![ComboBU].Value) Then
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger, "[Forms]![Location
Selection for VE and RFE]![Combo0]")
DoCmd.OpenForm "Location Selection for VE and RFE"
Else
strCriteria = BuildCriteria("JDE Bus Unit", dbInteger,
"[Forms]![Main]![ComboBU]")
DoCmd.OpenForm "Forecast Input"

Where "ComboBU" is a dropdown box on the main menu, and Forms]![Location
Selection for VE and RFE]![Combo0]" is a dropdown box on the form "Location
Selection for VE and RFE".

My question is this: How do I incorporate "Str Criteria" into the query
that the form Forecast Input needs for the criteria?

Please let me know if I'm not being clear.

Thank you,
MN
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

In
MacNut2004 said:
I also then get a run-time error of 3075. "Syntax error (missing
oeprator) in query expression 'JDE Bus Unit =
[Forms]![Main]![ComboBU]'
Click to expand...

Since your field name has spaces in it, enclose it in square brackets
when calling BuildCriteria:

strCriteria = BuildCriteria( _
"[JDE Bus Unit]", _
dbInteger, _
"[Forms]![Main]![ComboBU]")
 
G

Guest

GREAT! Works well.

Thanks to you and Brian!! :)

Dirk Goldgar said:
In
MacNut2004 said:
I also then get a run-time error of 3075. "Syntax error (missing
oeprator) in query expression 'JDE Bus Unit =
[Forms]![Main]![ComboBU]'
Click to expand...

Since your field name has spaces in it, enclose it in square brackets
when calling BuildCriteria:

strCriteria = BuildCriteria( _
"[JDE Bus Unit]", _
dbInteger, _
"[Forms]![Main]![ComboBU]")

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
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

Problem with Update Query 5
Multiple Criteria in Listbox 3
Syntax to refer to subform in BuildCriteria statement - Access 2k 1
BuildCriteria works in Northwind, but not in my db 8
Using a combo box to display info in a subform 5
Populate query in a subform based on selection from combobox in main form 0
Eliminate prompt in requery of subdatasheet in vba 6
access 2002 vba and exportXML method 2

Top