assign record source

  • Thread starter Thread starter GBA
  • Start date Start date
G

GBA

do not understand how to assign the report's record source as an SQL statement
or a string variable in VBA, assigned in Report_Open.

to date have only used an established query with a name in the query objects
area.

the user (non db technical) needs to derive a report based on a widely
flexible set of criteria. All fields will always show - which makes it
easier...but the criteria applied to those fields can vary widely. I can
give them a form with textboxes that will allow me to build an sql
statement...ironically on this part I am ok...building the sql statement.

my unclear points:
a. would I just be manipulating the 'Where' clause of the report open - or
am I creating a whole new record source for the report ?
b. if a new record source - can I / do I first open the recordset by itself
(i.e. trigger the query) in order to trouble shoot that before sourcing the
report on it...how?
c. how does one set the record source of the report to an sql statement
that is not existing in the queries objects area?...just type in the name in
the record source field??..or is it in the opening argument...
 
Since "all fields will always show" I would allow the user to set filtering
values in controls on your form. Then use this information to build a Where
Condition that can be used in the DoCmd.OpenReport method. The Where
Condition does not include "WHERE".

The basic code that I use resembles:
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
If Not IsNull(Me.cboProjID) Then 'numeric field
strWhere = strWhere & " AND [ProjID]=" & _
Me.cboProjID & " "
End If
If Not IsNull(Me.cboDept) Then 'text field
strWhere = strWhere & " AND [Dept]=""" & _
Me.cboDept & """ "
End If
' add more control references
DoCmd.OpenReport "rptYourReport", acViewPrevied, , strWhere
 
gotcha - thanks

I tend to think linear - and first is query as the record set/source - then
the report... so was thinking in terms of making the record set....



Duane Hookom said:
Since "all fields will always show" I would allow the user to set filtering
values in controls on your form. Then use this information to build a Where
Condition that can be used in the DoCmd.OpenReport method. The Where
Condition does not include "WHERE".

The basic code that I use resembles:
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
If Not IsNull(Me.cboProjID) Then 'numeric field
strWhere = strWhere & " AND [ProjID]=" & _
Me.cboProjID & " "
End If
If Not IsNull(Me.cboDept) Then 'text field
strWhere = strWhere & " AND [Dept]=""" & _
Me.cboDept & """ "
End If
' add more control references
DoCmd.OpenReport "rptYourReport", acViewPrevied, , strWhere



--
Duane Hookom
Microsoft Access MVP


GBA said:
do not understand how to assign the report's record source as an SQL statement
or a string variable in VBA, assigned in Report_Open.

to date have only used an established query with a name in the query objects
area.

the user (non db technical) needs to derive a report based on a widely
flexible set of criteria. All fields will always show - which makes it
easier...but the criteria applied to those fields can vary widely. I can
give them a form with textboxes that will allow me to build an sql
statement...ironically on this part I am ok...building the sql statement.

my unclear points:
a. would I just be manipulating the 'Where' clause of the report open - or
am I creating a whole new record source for the report ?
b. if a new record source - can I / do I first open the recordset by itself
(i.e. trigger the query) in order to trouble shoot that before sourcing the
report on it...how?
c. how does one set the record source of the report to an sql statement
that is not existing in the queries objects area?...just type in the name in
the record source field??..or is it in the opening argument...
Click to expand...
Click to expand...
 
in your syntax example is there a way to plug in the field name from a form
control?

i.e. [ProjID]

the report is going to have ~15 fields; and while they want to make a
criteria on any field they would only make a criteria on 2 or 3 at the same
time (AND)

so rather than build a form with 15 controls....can one build say just 6
controls each to hold 3 field names and 3 values... they enter the field
name (probably a list/combobox) then value

Field Value
Field Value
Field Value

have experimented some but no joy....I think the brackets are my stumbling
block....not a deal breaker just always interested....

Duane Hookom said:
Since "all fields will always show" I would allow the user to set filtering
values in controls on your form. Then use this information to build a Where
Condition that can be used in the DoCmd.OpenReport method. The Where
Condition does not include "WHERE".

The basic code that I use resembles:
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
If Not IsNull(Me.cboProjID) Then 'numeric field
strWhere = strWhere & " AND [ProjID]=" & _
Me.cboProjID & " "
End If
If Not IsNull(Me.cboDept) Then 'text field
strWhere = strWhere & " AND [Dept]=""" & _
Me.cboDept & """ "
End If
' add more control references
DoCmd.OpenReport "rptYourReport", acViewPrevied, , strWhere



--
Duane Hookom
Microsoft Access MVP


GBA said:
do not understand how to assign the report's record source as an SQL statement
or a string variable in VBA, assigned in Report_Open.

to date have only used an established query with a name in the query objects
area.

the user (non db technical) needs to derive a report based on a widely
flexible set of criteria. All fields will always show - which makes it
easier...but the criteria applied to those fields can vary widely. I can
give them a form with textboxes that will allow me to build an sql
statement...ironically on this part I am ok...building the sql statement.

my unclear points:
a. would I just be manipulating the 'Where' clause of the report open - or
am I creating a whole new record source for the report ?
b. if a new record source - can I / do I first open the recordset by itself
(i.e. trigger the query) in order to trouble shoot that before sourcing the
report on it...how?
c. how does one set the record source of the report to an sql statement
that is not existing in the queries objects area?...just type in the name in
the record source field??..or is it in the opening argument...
Click to expand...
Click to expand...
 
Assuming you have a combo box that selects a field name, you could use
something like:

If Not IsNull(Me.cboFieldName1) Then
strWhere = strWhere & " and [" & Me.cboFieldName1 & "] =" & _
Me.txtValue1 & " "
End If

--
Duane Hookom
Microsoft Access MVP


GBA said:
in your syntax example is there a way to plug in the field name from a form
control?

i.e. [ProjID]

the report is going to have ~15 fields; and while they want to make a
criteria on any field they would only make a criteria on 2 or 3 at the same
time (AND)

so rather than build a form with 15 controls....can one build say just 6
controls each to hold 3 field names and 3 values... they enter the field
name (probably a list/combobox) then value

Field Value
Field Value
Field Value

have experimented some but no joy....I think the brackets are my stumbling
block....not a deal breaker just always interested....

Duane Hookom said:
Since "all fields will always show" I would allow the user to set filtering
values in controls on your form. Then use this information to build a Where
Condition that can be used in the DoCmd.OpenReport method. The Where
Condition does not include "WHERE".

The basic code that I use resembles:
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
If Not IsNull(Me.cboProjID) Then 'numeric field
strWhere = strWhere & " AND [ProjID]=" & _
Me.cboProjID & " "
End If
If Not IsNull(Me.cboDept) Then 'text field
strWhere = strWhere & " AND [Dept]=""" & _
Me.cboDept & """ "
End If
' add more control references
DoCmd.OpenReport "rptYourReport", acViewPrevied, , strWhere



--
Duane Hookom
Microsoft Access MVP


GBA said:
do not understand how to assign the report's record source as an SQL statement
or a string variable in VBA, assigned in Report_Open.

to date have only used an established query with a name in the query objects
area.

the user (non db technical) needs to derive a report based on a widely
flexible set of criteria. All fields will always show - which makes it
easier...but the criteria applied to those fields can vary widely. I can
give them a form with textboxes that will allow me to build an sql
statement...ironically on this part I am ok...building the sql statement.

my unclear points:
a. would I just be manipulating the 'Where' clause of the report open - or
am I creating a whole new record source for the report ?
b. if a new record source - can I / do I first open the recordset by itself
(i.e. trigger the query) in order to trouble shoot that before sourcing the
report on it...how?
c. how does one set the record source of the report to an sql statement
that is not existing in the queries objects area?...just type in the name in
the record source field??..or is it in the opening argument...
Click to expand...
Click to expand...
Click to expand...
 
huh...well okay - thanks...that is syntax I couldn't quite figure out....
my experiments didn't have that combo of [ and " in the order you
show...makes sense....will give it a whirl.... Much Thanks.

Duane Hookom said:
Assuming you have a combo box that selects a field name, you could use
something like:

If Not IsNull(Me.cboFieldName1) Then
strWhere = strWhere & " and [" & Me.cboFieldName1 & "] =" & _
Me.txtValue1 & " "
End If

--
Duane Hookom
Microsoft Access MVP


GBA said:
in your syntax example is there a way to plug in the field name from a form
control?

i.e. [ProjID]

the report is going to have ~15 fields; and while they want to make a
criteria on any field they would only make a criteria on 2 or 3 at the same
time (AND)

so rather than build a form with 15 controls....can one build say just 6
controls each to hold 3 field names and 3 values... they enter the field
name (probably a list/combobox) then value

Field Value
Field Value
Field Value

have experimented some but no joy....I think the brackets are my stumbling
block....not a deal breaker just always interested....

Duane Hookom said:
Since "all fields will always show" I would allow the user to set filtering
values in controls on your form. Then use this information to build a Where
Condition that can be used in the DoCmd.OpenReport method. The Where
Condition does not include "WHERE".

The basic code that I use resembles:
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [DateField]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [DateField]<=#" & _
Me.txtEnd & "# "
End If
If Not IsNull(Me.cboProjID) Then 'numeric field
strWhere = strWhere & " AND [ProjID]=" & _
Me.cboProjID & " "
End If
If Not IsNull(Me.cboDept) Then 'text field
strWhere = strWhere & " AND [Dept]=""" & _
Me.cboDept & """ "
End If
' add more control references
DoCmd.OpenReport "rptYourReport", acViewPrevied, , strWhere



--
Duane Hookom
Microsoft Access MVP


:

do not understand how to assign the report's record source as an SQL statement
or a string variable in VBA, assigned in Report_Open.

to date have only used an established query with a name in the query objects
area.

the user (non db technical) needs to derive a report based on a widely
flexible set of criteria. All fields will always show - which makes it
easier...but the criteria applied to those fields can vary widely. I can
give them a form with textboxes that will allow me to build an sql
statement...ironically on this part I am ok...building the sql statement.

my unclear points:
a. would I just be manipulating the 'Where' clause of the report open - or
am I creating a whole new record source for the report ?
b. if a new record source - can I / do I first open the recordset by itself
(i.e. trigger the query) in order to trouble shoot that before sourcing the
report on it...how?
c. how does one set the record source of the report to an sql statement
that is not existing in the queries objects area?...just type in the name in
the record source field??..or is it in the opening argument...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top