concatenate child records

[JohnLute]

I've reviewed Duane Hookom's generic concatenation
http://www.rogersaccesslibrary.com/...neric Function To Concatenate Child Records'e

I'm really struggling with this. Here's my structure:

tblProfiles.txtProfileID is in a 1-Many relationship with
tblProfilesAllergens.txtProfileID.

Here's the SQL for the field I created:
Allergens: Concatenate("SELECT ProfilesAllergens FROM tblProfilesAllergens
WHERE txtProfileID =" & [tblProfilesAllergens].[txtProfileID] & " ORDER BY
ProfilesAllergens")

I'm not sure this is correct. Also, when I run it an error occurs "No value
given for one or more required parameters." The debugger points to this line
in the module:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

I'm really lost.

Any help would be greatly appreciated!!!

 

[Duane Hookom]

If txtProfileID is a text field, try:
Allergens: Concatenate("SELECT ProfilesAllergens FROM tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens")

tblProfilesAllergens should not be in the main query.

 

[JohnLute]

Thanks, Duane - that did the trick!

Now I'm stuck with my report field. I've made the control source
=[qryProfilesAllergens]![Allergens]

but this is returning an error.

???

 

[Duane Hookom]

A control source can't refer to fields in tables/queries outside of the
report's record source. If your report's record source is
[qryProfilesAllergens] then you should be able to set a control source to
[Allergens]

To grab a field value from a non-record source table/query, you can use code
or DLookup() or a subreport.

--
Duane Hookom
MS Access MVP

Thanks, Duane - that did the trick!

Now I'm stuck with my report field. I've made the control source
=[qryProfilesAllergens]![Allergens]

but this is returning an error.

???

--
www.Marzetti.com

If txtProfileID is a text field, try:
Allergens: Concatenate("SELECT ProfilesAllergens FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens")

tblProfilesAllergens should not be in the main query.

 

[JohnLute]

Thanks, Duane.

The concatenation works fine now but another problem has arisen. I've added
the query to the record source of a report and it's returning this error:

"The specified field '[tblProfiles.txtProfileID]' could refer to more than
one table listed in the FROM clause of your SQL statement."

Here's my SQL but I can't figure it out. Any help yo umight have would be
appreciated - thanks!

SELECT tblProfiles.*, tblFGPhysicalAttributes.UnitCount,
tblFGPhysicalAttributes.SubUnitCount, tblFGPhysicalAttributes.UnitSize,
tblFGPhysicalAttributes.UnitUOM, tblFGPhysicalAttributes.UnitDescription,
tblFGPhysicalAttributes.UnitComments, [GrossWeight] & " " & [GWUOM] AS [Gross
Weight], [NetWeight] & " " & [NWUOM] AS [Net Weight],
tblFGPhysicalAttributes.Ti, tblFGPhysicalAttributes.Hi,
tblFGPhysicalAttributes.AdditionalUnits,
tblFGPhysicalAttributes.UnitLoadStack, tblFGProcessing.FormulaNumber,
tblFGProcessing.DrumNumber, [StorageTime] & " " & [STIMEUOM] & " " &
[StorageCondition] AS Storage, qryProfilesAllergens.Allergens
FROM (((tblProfiles INNER JOIN tblFGPhysicalAttributes ON
tblProfiles.txtProfileID = tblFGPhysicalAttributes.txtProfileID) INNER JOIN
tblFGProcessing ON tblProfiles.txtProfileID = tblFGProcessing.txtProfileID)
LEFT JOIN qryProfilesAllergens ON tblProfiles.txtProfileID =
qryProfilesAllergens.txtProfileID) INNER JOIN tblProfilesStorage ON
tblProfiles.txtProfileID = tblProfilesStorage.txtProfileID;

--
www.Marzetti.com

A control source can't refer to fields in tables/queries outside of the
report's record source. If your report's record source is
[qryProfilesAllergens] then you should be able to set a control source to
[Allergens]

To grab a field value from a non-record source table/query, you can use code
or DLookup() or a subreport.

--
Duane Hookom
MS Access MVP

Thanks, Duane - that did the trick!

Now I'm stuck with my report field. I've made the control source
=[qryProfilesAllergens]![Allergens]

but this is returning an error.

???

--
www.Marzetti.com

If txtProfileID is a text field, try:
Allergens: Concatenate("SELECT ProfilesAllergens FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens")

tblProfilesAllergens should not be in the main query.

 

[Duane Hookom]

What is the SQL view of qryProfilesAllergens.

--
Duane Hookom
MS Access MVP

Thanks, Duane.

The concatenation works fine now but another problem has arisen. I've
added
the query to the record source of a report and it's returning this error:

"The specified field '[tblProfiles.txtProfileID]' could refer to more than
one table listed in the FROM clause of your SQL statement."

Here's my SQL but I can't figure it out. Any help yo umight have would be
appreciated - thanks!

SELECT tblProfiles.*, tblFGPhysicalAttributes.UnitCount,
tblFGPhysicalAttributes.SubUnitCount, tblFGPhysicalAttributes.UnitSize,
tblFGPhysicalAttributes.UnitUOM, tblFGPhysicalAttributes.UnitDescription,
tblFGPhysicalAttributes.UnitComments, [GrossWeight] & " " & [GWUOM] AS
[Gross
Weight], [NetWeight] & " " & [NWUOM] AS [Net Weight],
tblFGPhysicalAttributes.Ti, tblFGPhysicalAttributes.Hi,
tblFGPhysicalAttributes.AdditionalUnits,
tblFGPhysicalAttributes.UnitLoadStack, tblFGProcessing.FormulaNumber,
tblFGProcessing.DrumNumber, [StorageTime] & " " & [STIMEUOM] & " " &
[StorageCondition] AS Storage, qryProfilesAllergens.Allergens
FROM (((tblProfiles INNER JOIN tblFGPhysicalAttributes ON
tblProfiles.txtProfileID = tblFGPhysicalAttributes.txtProfileID) INNER
JOIN
tblFGProcessing ON tblProfiles.txtProfileID =
tblFGProcessing.txtProfileID)
LEFT JOIN qryProfilesAllergens ON tblProfiles.txtProfileID =
qryProfilesAllergens.txtProfileID) INNER JOIN tblProfilesStorage ON
tblProfiles.txtProfileID = tblProfilesStorage.txtProfileID;

--
www.Marzetti.com

A control source can't refer to fields in tables/queries outside of the
report's record source. If your report's record source is
[qryProfilesAllergens] then you should be able to set a control source to
[Allergens]

To grab a field value from a non-record source table/query, you can use
code
or DLookup() or a subreport.

--
Duane Hookom
MS Access MVP

Thanks, Duane - that did the trick!

Now I'm stuck with my report field. I've made the control source
=[qryProfilesAllergens]![Allergens]

but this is returning an error.

???

--
www.Marzetti.com


:

If txtProfileID is a text field, try:
Allergens: Concatenate("SELECT ProfilesAllergens FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens")

tblProfilesAllergens should not be in the main query.

 

[JohnLute]

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAllergens FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles INNER JOIN tblProfilesAllergens ON tblProfiles.txtProfileID
= tblProfilesAllergens.txtProfileID;

I know you said that tblProfilesAllergens shouldn't be in the query however
without it it was returning every record from tblProfiles without matching
records in tblProfilesAllergens. When I add tblProfilesAllergens to the SQL
the query returns only the records in tblProfiles with matching records in
tblProfilesAllergens.

I've removed tblProfilesAllergens from the query and the problem in the
report continues so I'm pretty sure that it being in the query isn't the
cause.

 

[Duane Hookom]

I would think your query would return multiple/duplicate txtProfileID
values. If so, this one should be better:

SELECT txtProfileID,
Concatenate("SELECT ProfilesAllergens
FROM tblProfilesAllergens
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles
WHERE txtProfileID IN (SELECT txtProfileID FROM tblProfilesAllergens);

I don't know why your second query would think there are more than one
[tblProfiles.txtProfileID].
--
Duane Hookom
MS Access MVP

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAllergens
FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles INNER JOIN tblProfilesAllergens ON
tblProfiles.txtProfileID
= tblProfilesAllergens.txtProfileID;

I know you said that tblProfilesAllergens shouldn't be in the query
however
without it it was returning every record from tblProfiles without matching
records in tblProfilesAllergens. When I add tblProfilesAllergens to the
SQL
the query returns only the records in tblProfiles with matching records in
tblProfilesAllergens.

I've removed tblProfilesAllergens from the query and the problem in the
report continues so I'm pretty sure that it being in the query isn't the
cause.

--
www.Marzetti.com

What is the SQL view of qryProfilesAllergens.

 

[JohnLute]

That was a nifty solution for the query, Duane! That did the trick however I
still have the other problem.

Very strange...

--
www.Marzetti.com

I would think your query would return multiple/duplicate txtProfileID
values. If so, this one should be better:

SELECT txtProfileID,
Concatenate("SELECT ProfilesAllergens
FROM tblProfilesAllergens
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles
WHERE txtProfileID IN (SELECT txtProfileID FROM tblProfilesAllergens);

I don't know why your second query would think there are more than one
[tblProfiles.txtProfileID].
--
Duane Hookom
MS Access MVP

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAllergens
FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles INNER JOIN tblProfilesAllergens ON
tblProfiles.txtProfileID
= tblProfilesAllergens.txtProfileID;

I know you said that tblProfilesAllergens shouldn't be in the query
however
without it it was returning every record from tblProfiles without matching
records in tblProfilesAllergens. When I add tblProfilesAllergens to the
SQL
the query returns only the records in tblProfiles with matching records in
tblProfilesAllergens.

I've removed tblProfilesAllergens from the query and the problem in the
report continues so I'm pretty sure that it being in the query isn't the
cause.

--
www.Marzetti.com

What is the SQL view of qryProfilesAllergens.

 

[Duane Hookom]

Is this still your problem:
"The specified field '[tblProfiles.txtProfileID]' could refer to more than
one table listed in the FROM clause of your SQL statement."


--
Duane Hookom
MS Access MVP

That was a nifty solution for the query, Duane! That did the trick however
I
still have the other problem.

Very strange...

--
www.Marzetti.com

I would think your query would return multiple/duplicate txtProfileID
values. If so, this one should be better:

SELECT txtProfileID,
Concatenate("SELECT ProfilesAllergens
FROM tblProfilesAllergens
WHERE txtProfileID =""" & [txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles
WHERE txtProfileID IN (SELECT txtProfileID FROM tblProfilesAllergens);

I don't know why your second query would think there are more than one
[tblProfiles.txtProfileID].
--
Duane Hookom
MS Access MVP

SELECT tblProfiles.txtProfileID, Concatenate("SELECT ProfilesAllergens
FROM
tblProfilesAllergens
WHERE txtProfileID =""" & [tblProfiles].[txtProfileID] & """ ORDER BY
ProfilesAllergens") AS Allergens
FROM tblProfiles INNER JOIN tblProfilesAllergens ON
tblProfiles.txtProfileID
= tblProfilesAllergens.txtProfileID;

I know you said that tblProfilesAllergens shouldn't be in the query
however
without it it was returning every record from tblProfiles without
matching
records in tblProfilesAllergens. When I add tblProfilesAllergens to the
SQL
the query returns only the records in tblProfiles with matching records
in
tblProfilesAllergens.

I've removed tblProfilesAllergens from the query and the problem in the
report continues so I'm pretty sure that it being in the query isn't
the
cause.

--
www.Marzetti.com


:

What is the SQL view of qryProfilesAllergens.

 

[JohnLute]

Yup. Really strange. I can't figure it out. I do have a filter in the report:

([tblProfiles.txtProfileID] = Forms![frmFinishedGoods].form![txtProfileID])

Could this be clashing...?

 

[Duane Hookom]

Well just take it out and see....
I never manually set the filter property. Using a Where clause in the
DoCmd.OpenReport method will set the filter property.

--
Duane Hookom
MS Access MVP

Yup. Really strange. I can't figure it out. I do have a filter in the
report:

([tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID])

Could this be clashing...?


--
www.Marzetti.com

Is this still your problem:
"The specified field '[tblProfiles.txtProfileID]' could refer to more
than
one table listed in the FROM clause of your SQL statement."

 

[JohnLute]

I would've taken it out however the report doesn't open without it and I
never considered a Where clause.

I understand this idea and where to put it but I'm still a novice at writing
code. Could you help with that?

--
www.Marzetti.com

Well just take it out and see....
I never manually set the filter property. Using a Where clause in the
DoCmd.OpenReport method will set the filter property.

--
Duane Hookom
MS Access MVP

Yup. Really strange. I can't figure it out. I do have a filter in the
report:

([tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID])

Could this be clashing...?


--
www.Marzetti.com

Is this still your problem:
"The specified field '[tblProfiles.txtProfileID]' could refer to more
than
one table listed in the FROM clause of your SQL statement."

 

[Duane Hookom]

I can't understand how removing a filter property value would cause a report
to not open.

Here is a post regarding the Where clause that I posted in another forum:
Create a form with two text boxes
Text box 1
Name: txtStartDate
Format: Short Date
Text box 2
Name: txtEndDate
Format: Short Date
Default Value: Date()

Use the Command Button wizard in the Tool Box to create a button that opens
your report. After the wizard finishes, right-click the button and choose
Build Event. Your code might look like the following. I have added some
lines as noted to use the text boxes:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
'ADDED NEXT 2 LINES
Dim strWhere as String
strWhere = "1=1 "

stDocName = "Catalog"
'ADDED NEXT 8 LINES
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [DateField]<=#" & _
Me.txtEndDate & "# "
End If
'ADDED 2 commas and strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


--
Duane Hookom
MS Access MVP

I would've taken it out however the report doesn't open without it and I
never considered a Where clause.

I understand this idea and where to put it but I'm still a novice at
writing
code. Could you help with that?

--
www.Marzetti.com

Well just take it out and see....
I never manually set the filter property. Using a Where clause in the
DoCmd.OpenReport method will set the filter property.

--
Duane Hookom
MS Access MVP

Yup. Really strange. I can't figure it out. I do have a filter in the
report:

([tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID])

Could this be clashing...?


--
www.Marzetti.com


:

Is this still your problem:
"The specified field '[tblProfiles.txtProfileID]' could refer to more
than
one table listed in the FROM clause of your SQL statement."

 

[JohnLute]

This is the Preview event of my current button. I developed this a couple
years ago with the help of another MVP:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String

DoCmd.OpenReport Me!cbSelectReport, acPreview, ,
"[tblProfiles.txtProfileID] = Forms![frmFinishedGoods].form![txtProfileID]"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

How should this be modified? I'm really getting lost here.

 

[Duane Hookom]

I would change the code to the following which assumes txtProfileID is text:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strWhere as String
strWhere = "[txtProfileID] = """ & _
Forms![frmFinishedGoods].form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

This is the Preview event of my current button. I developed this a couple
years ago with the help of another MVP:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String

DoCmd.OpenReport Me!cbSelectReport, acPreview, ,
"[tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID]"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

How should this be modified? I'm really getting lost here.

--
www.Marzetti.com

I can't understand how removing a filter property value would cause a
report
to not open.

Here is a post regarding the Where clause that I posted in another forum:
Create a form with two text boxes
Text box 1
Name: txtStartDate
Format: Short Date
Text box 2
Name: txtEndDate
Format: Short Date
Default Value: Date()

Use the Command Button wizard in the Tool Box to create a button that
opens
your report. After the wizard finishes, right-click the button and choose
Build Event. Your code might look like the following. I have added some
lines as noted to use the text boxes:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
'ADDED NEXT 2 LINES
Dim strWhere as String
strWhere = "1=1 "

stDocName = "Catalog"
'ADDED NEXT 8 LINES
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [DateField]<=#" & _
Me.txtEndDate & "# "
End If
'ADDED 2 commas and strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

 

[JohnLute]

Thanks for all your help, Duane! This works and I removed the filter property
value but the dreaded error continues!

"The specified field '[tblProfiles.txtProfileID]' could refer to more than
one table listed in the FROM clause of your SQL statement."

This is driving me nuts! Also, this is keeping me from publishing a revised
database that needs to go into systems today!

ARRRRRRRRRRRRRGGGGGGGGGGGGGHHHHHHHHHHHHHH!!!

--
www.Marzetti.com

I would change the code to the following which assumes txtProfileID is text:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strWhere as String
strWhere = "[txtProfileID] = """ & _
Forms![frmFinishedGoods].form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

This is the Preview event of my current button. I developed this a couple
years ago with the help of another MVP:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String

DoCmd.OpenReport Me!cbSelectReport, acPreview, ,
"[tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID]"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

How should this be modified? I'm really getting lost here.

--
www.Marzetti.com

I can't understand how removing a filter property value would cause a
report
to not open.

Here is a post regarding the Where clause that I posted in another forum:
Create a form with two text boxes
Text box 1
Name: txtStartDate
Format: Short Date
Text box 2
Name: txtEndDate
Format: Short Date
Default Value: Date()

Use the Command Button wizard in the Tool Box to create a button that
opens
your report. After the wizard finishes, right-click the button and choose
Build Event. Your code might look like the following. I have added some
lines as noted to use the text boxes:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
'ADDED NEXT 2 LINES
Dim strWhere as String
strWhere = "1=1 "

stDocName = "Catalog"
'ADDED NEXT 8 LINES
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [DateField]<=#" & _
Me.txtEndDate & "# "
End If
'ADDED 2 commas and strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

 

[Duane Hookom]

Check your sorting and grouping dialog.

If you can't find this, you may want to send me copy of your mdb. My email
will filter out any emails with large attachments so you would need to
contact me outside of the newsgroup prior to attempting to send me a file.
--
Duane Hookom
MS Access MVP

Thanks for all your help, Duane! This works and I removed the filter
property
value but the dreaded error continues!

"The specified field '[tblProfiles.txtProfileID]' could refer to more than
one table listed in the FROM clause of your SQL statement."

This is driving me nuts! Also, this is keeping me from publishing a
revised
database that needs to go into systems today!

ARRRRRRRRRRRRRGGGGGGGGGGGGGHHHHHHHHHHHHHH!!!

--
www.Marzetti.com

I would change the code to the following which assumes txtProfileID is
text:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strWhere as String
strWhere = "[txtProfileID] = """ & _
Forms![frmFinishedGoods].form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

This is the Preview event of my current button. I developed this a
couple
years ago with the help of another MVP:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String

DoCmd.OpenReport Me!cbSelectReport, acPreview, ,
"[tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID]"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

How should this be modified? I'm really getting lost here.

--
www.Marzetti.com


:

I can't understand how removing a filter property value would cause a
report
to not open.

Here is a post regarding the Where clause that I posted in another
forum:
Create a form with two text boxes
Text box 1
Name: txtStartDate
Format: Short Date
Text box 2
Name: txtEndDate
Format: Short Date
Default Value: Date()

Use the Command Button wizard in the Tool Box to create a button that
opens
your report. After the wizard finishes, right-click the button and
choose
Build Event. Your code might look like the following. I have added
some
lines as noted to use the text boxes:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
'ADDED NEXT 2 LINES
Dim strWhere as String
strWhere = "1=1 "

stDocName = "Catalog"
'ADDED NEXT 8 LINES
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [DateField]<=#" & _
Me.txtEndDate & "# "
End If
'ADDED 2 commas and strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

 

[JohnLute]

Sorting and grouping is empty. You've got mail!

--
www.Marzetti.com

Check your sorting and grouping dialog.

If you can't find this, you may want to send me copy of your mdb. My email
will filter out any emails with large attachments so you would need to
contact me outside of the newsgroup prior to attempting to send me a file.
--
Duane Hookom
MS Access MVP

Thanks for all your help, Duane! This works and I removed the filter
property
value but the dreaded error continues!

"The specified field '[tblProfiles.txtProfileID]' could refer to more than
one table listed in the FROM clause of your SQL statement."

This is driving me nuts! Also, this is keeping me from publishing a
revised
database that needs to go into systems today!

ARRRRRRRRRRRRRGGGGGGGGGGGGGHHHHHHHHHHHHHH!!!

--
www.Marzetti.com

I would change the code to the following which assumes txtProfileID is
text:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strWhere as String
strWhere = "[txtProfileID] = """ & _
Forms![frmFinishedGoods].form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

This is the Preview event of my current button. I developed this a
couple
years ago with the help of another MVP:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String

DoCmd.OpenReport Me!cbSelectReport, acPreview, ,
"[tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID]"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

How should this be modified? I'm really getting lost here.

--
www.Marzetti.com


:

I can't understand how removing a filter property value would cause a
report
to not open.

Here is a post regarding the Where clause that I posted in another
forum:
Create a form with two text boxes
Text box 1
Name: txtStartDate
Format: Short Date
Text box 2
Name: txtEndDate
Format: Short Date
Default Value: Date()

Use the Command Button wizard in the Tool Box to create a button that
opens
your report. After the wizard finishes, right-click the button and
choose
Build Event. Your code might look like the following. I have added
some
lines as noted to use the text boxes:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
'ADDED NEXT 2 LINES
Dim strWhere as String
strWhere = "1=1 "

stDocName = "Catalog"
'ADDED NEXT 8 LINES
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [DateField]<=#" & _
Me.txtEndDate & "# "
End If
'ADDED 2 commas and strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub

 

[Duane Hookom]

One issue I found was a text box bound to an expression containing
[tblProfiles.txtProfileID]. When I changed this to [txtProfileID] the error
message went away.

--
Duane Hookom
MS Access MVP

Sorting and grouping is empty. You've got mail!

--
www.Marzetti.com

Check your sorting and grouping dialog.

If you can't find this, you may want to send me copy of your mdb. My
email
will filter out any emails with large attachments so you would need to
contact me outside of the newsgroup prior to attempting to send me a
file.
--
Duane Hookom
MS Access MVP

Thanks for all your help, Duane! This works and I removed the filter
property
value but the dreaded error continues!

"The specified field '[tblProfiles.txtProfileID]' could refer to more
than
one table listed in the FROM clause of your SQL statement."

This is driving me nuts! Also, this is keeping me from publishing a
revised
database that needs to go into systems today!

ARRRRRRRRRRRRRGGGGGGGGGGGGGHHHHHHHHHHHHHH!!!

--
www.Marzetti.com


:

I would change the code to the following which assumes txtProfileID is
text:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String
Dim strWhere as String
strWhere = "[txtProfileID] = """ & _
Forms![frmFinishedGoods].form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

This is the Preview event of my current button. I developed this a
couple
years ago with the help of another MVP:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click

Dim stDocName As String

DoCmd.OpenReport Me!cbSelectReport, acPreview, ,
"[tblProfiles.txtProfileID] =
Forms![frmFinishedGoods].form![txtProfileID]"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

How should this be modified? I'm really getting lost here.

--
www.Marzetti.com


:

I can't understand how removing a filter property value would cause
a
report
to not open.

Here is a post regarding the Where clause that I posted in another
forum:
Create a form with two text boxes
Text box 1
Name: txtStartDate
Format: Short Date
Text box 2
Name: txtEndDate
Format: Short Date
Default Value: Date()

Use the Command Button wizard in the Tool Box to create a button
that
opens
your report. After the wizard finishes, right-click the button and
choose
Build Event. Your code might look like the following. I have added
some
lines as noted to use the text boxes:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim stDocName As String
'ADDED NEXT 2 LINES
Dim strWhere as String
strWhere = "1=1 "

stDocName = "Catalog"
'ADDED NEXT 8 LINES
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [DateField]>=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [DateField]<=#" & _
Me.txtEndDate & "# "
End If
'ADDED 2 commas and strWhere
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub