Open Report from Command Button in Form

  • Thread starter Thread starter JennJenn
  • Start date Start date
J

JennJenn

I have tried several codes from the discussion group and I can not get my
report to open correctly, where is shows the current record only. It may be
because of the text box I am linking it to...it is my primary key in my table
and I have it sharing a relationship with other tables. In the text box it
says tblDivePlan.DiveID in the control source, is that why I am having
troubles?? Please help!!

JennJenn
 
Also I am getting an error message that says ... specified field '[DiveID]'
could refer to more than one table listed FROM clause of your SQL statement
....
 
See http://www.mvps.org/access/reports/rpt0002.htm for the basic concept

remeber in your WHERE clause if your DiveID is a number then it does no need
to be enclosed in '', but if it is a string (text) then it need to be
enclosed in ''.

So if we take the above mentioned example an modify it to your specific
case, assuming you did not rename the controls, your WHERE clause should look
like:

if DiveID is a number
strWhere = "[DiveID ]=" & me!DiveID
if DiveID is a string
strWhere = "[DiveID ]='" & me!DiveID & "'"

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.
 
In that case, you will need to specify the table name as well as the field name

[YourTableNameToFilterOn].[YourFieldName]
[].[DiveID]
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



JennJenn said:
Also I am getting an error message that says ... specified field '[DiveID]'
could refer to more than one table listed FROM clause of your SQL statement
...

JennJenn said:
I have tried several codes from the discussion group and I can not get my
report to open correctly, where is shows the current record only. It may be
because of the text box I am linking it to...it is my primary key in my table
and I have it sharing a relationship with other tables. In the text box it
says tblDivePlan.DiveID in the control source, is that why I am having
troubles?? Please help!!

JennJenn
Click to expand...
Click to expand...
 
Also I am getting an error message that says ... specified field '[DiveID]'
could refer to more than one table listed FROM clause of your SQL statement
Click to expand...

Tables with joins can be a ticking time bomb if you are creating SQL
dynamically and a field with the same name is added to the other
table. When I need to be extra careful I use the following function:

----'Begin Module Code----
Public Function IsFieldInTableDef(strFieldName As String, strTableName
As String, Optional strDatabase As String) As Boolean
Dim MyDB As DAO.Database
Dim tdf As TableDef
Dim fld As Field
Dim boolFound As Boolean

If strDatabase = "" Then
Set MyDB = CurrentDb
Else
Set MyDB = OpenDatabase(strDatabase, False, True)
End If
Set tdf = MyDB.TableDefs(strTableName)
boolFound = False
For Each fld In tdf.Fields
If fld.Name = strFieldName Then
boolFound = True
Exit For
End If
Next fld
IsFieldInTableDef = boolFound
'The following line is not absolutely required
Set tdf = Nothing
MyDB.Close
Set MyDB = Nothing
End Function
'----End Module Code----

Then I can determine whether or not to specify the table name as part
of the SQL string.

James A. Fortune
(e-mail address removed)
 
It worked, thank you both so much!!!!

Daniel Pineault said:
See http://www.mvps.org/access/reports/rpt0002.htm for the basic concept

remeber in your WHERE clause if your DiveID is a number then it does no need
to be enclosed in '', but if it is a string (text) then it need to be
enclosed in ''.

So if we take the above mentioned example an modify it to your specific
case, assuming you did not rename the controls, your WHERE clause should look
like:

if DiveID is a number
strWhere = "[DiveID ]=" & me!DiveID
if DiveID is a string
strWhere = "[DiveID ]='" & me!DiveID & "'"

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



JennJenn said:
I have tried several codes from the discussion group and I can not get my
report to open correctly, where is shows the current record only. It may be
because of the text box I am linking it to...it is my primary key in my table
and I have it sharing a relationship with other tables. In the text box it
says tblDivePlan.DiveID in the control source, is that why I am having
troubles?? Please help!!

JennJenn
Click to expand...
Click to expand...
 
Back
Top