How to Open Report to Current Record? (Access03)

[dohernan via AccessMonster.com]

When I use the "If, Then" below the Report/letter pops open, but has
information from the last record, alphabetically.
how do I get it to use the Current Record?

Thanks.

Private Sub Need_Auth__AfterUpdate()

If Me.[NeedAuth] = "y" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "AuthorizationNeededL", acPreview
End If

End Sub

 

[fredg]

When I use the "If, Then" below the Report/letter pops open, but has
information from the last record, alphabetically.
how do I get it to use the Current Record?

Thanks.

Private Sub Need_Auth__AfterUpdate()

If Me.[NeedAuth] = "y" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "AuthorizationNeededL", acPreview
End If

End Sub

If you wish to have the report print a specific record then you must
tell it to.
First create a report that displays all of the data you want to show.

Then¡K.

Your table should have a unique prime key field.
In my example it is named [RecordID].

The code the AfterUpdate Event:

If Me.[NeedAuth] = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview , , "[RecordID] = " &
[RecordID]
End If

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
OpenReport method
and also
Where Clause + Restrict data to a subset of records

 

[dohernan via AccessMonster.com]

RecordNum is type Auto Number, does that caus eissues?

I have a slightly different take-

Private Sub Need_Auth__AfterUpdate()

If Me.[NeedAuth] = "y" Then
If Me.Dirty Then Me.Dirty = False 'this will save the current record
DoCmd.OpenReport "AuthorizationNeededL", acPreview, , "RecordNum = " & Me.
RecordNum
End If

End Sub


However this isn't working, nothing happens when a "y" is put in.
Thanks again.

 

[fredg]

RecordNum is type Auto Number, does that caus eissues?

I have a slightly different take-

Private Sub Need_Auth__AfterUpdate()

If Me.[NeedAuth] = "y" Then
If Me.Dirty Then Me.Dirty = False 'this will save the current record
DoCmd.OpenReport "AuthorizationNeededL", acPreview, , "RecordNum = " & Me.
RecordNum
End If

End Sub

However this isn't working, nothing happens when a "y" is put in.
Thanks again.

An AutoNumber is a Number.
And why do you think
If Me.Dirty Then Me.Dirty = False 'this will save the current record
is better than
DoCmd.RunCommand acCmdSaveRecord?

Did you place a breakpoint in the code and step through it line by
line, seeing what value [NeedAuth] returns?
Are you sure just "y" is in the control and not " y" or "y "?

As a test, change the code to:

DoCmd.RunCommand acCmdSaveRecord 'This will save the record
MsgBox Me.[NeedAuth] & " " & Len(Me.[NeedAuth])
If Me.[NeedAuth] = "y" Then
DoCmd.OpenReport "AuthorizationNeededL", acPreview, , "RecordNum =
" & Me.RecordNum
End If

The MsgBox should return a "y 1" (without the quotes).
If it returns anything else than that is your problem.

 

[Gina Whipp]

Tip toeing in...

Is the field name *Need_Auth* OR *NeedAuth*?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

RecordNum is type Auto Number, does that caus eissues?

I have a slightly different take-

Private Sub Need_Auth__AfterUpdate()

If Me.[NeedAuth] = "y" Then
If Me.Dirty Then Me.Dirty = False 'this will save the current record
DoCmd.OpenReport "AuthorizationNeededL", acPreview, , "RecordNum = " &
Me.
RecordNum
End If

End Sub

However this isn't working, nothing happens when a "y" is put in.
Thanks again.

An AutoNumber is a Number.
And why do you think
If Me.Dirty Then Me.Dirty = False 'this will save the current record
is better than
DoCmd.RunCommand acCmdSaveRecord?

Did you place a breakpoint in the code and step through it line by
line, seeing what value [NeedAuth] returns?
Are you sure just "y" is in the control and not " y" or "y "?

As a test, change the code to:

DoCmd.RunCommand acCmdSaveRecord 'This will save the record
MsgBox Me.[NeedAuth] & " " & Len(Me.[NeedAuth])
If Me.[NeedAuth] = "y" Then
DoCmd.OpenReport "AuthorizationNeededL", acPreview, , "RecordNum =
" & Me.RecordNum
End If

The MsgBox should return a "y 1" (without the quotes).
If it returns anything else than that is your problem.

 

[dohernan via AccessMonster.com]

(I did have a Name issue, I had re-named things NeedAuth instead of Need_Auth)


Below code gives me a pop-up window. Compile Error, Syntax Error


+++++
Private Sub NeedAuth_AfterUpdate()

If Me.[NeedAuth] = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acPreview , , "[RecordNum] = " &
[RecordNum]
End If

End Sub

with the DoCmd.OpenReport line in Red, and the Private Sub needA in yellow
+++++

The test code did give me the y = 1 box, then another window poppoed up with
a Syntex error.


I also tried the code below and got a pop-up asking for Parameter Value
RecordNum

Private Sub NeedAuth_AfterUpdate()

If Me.[NeedAuth] = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acPreview, , "RecordNum = " & Me.
RecordNum
End If

End Sub

Thanks again

 

[Gina Whipp]

If RecordNum is numeric

DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]=" &
Me![RecordNum]

if RecordNum is text

DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]=' " &
Me![RecordNum] & " ' "

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[dohernan via AccessMonster.com]

Thanks, RecordNum, is an Auto-Generated #
The code below pops up a window asking for the value of RecordNum, then when
I leave it blank & click OK, it opens the Report with the current info filled
in.
How do I not get that extra pop-up?


+++++++++
Private Sub NeedAuth_AfterUpdate()

If Me.NeedAuth = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]=" &
Me![RecordNum]

End If

End Sub
+++++++++

The name field of the Report/Letter is coming up blank as well, any ideas?
Thanks very much.

=Forms!Personnel2009Form!LastName & ", " & Personnel2009Form![FirstName]

If RecordNum is numeric

DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]=" &
Me![RecordNum]

 

[Gina Whipp]

Is RecordNum in report AuthorizationNeededL AND on your current form? It
would need to be in both places.

Also just saw your other question... is FirstName and LastName on the form
and filed in? And are you printing the report from Forms!Personnel2009Form?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thanks, RecordNum, is an Auto-Generated #
The code below pops up a window asking for the value of RecordNum, then
when
I leave it blank & click OK, it opens the Report with the current info
filled
in.
How do I not get that extra pop-up?


+++++++++
Private Sub NeedAuth_AfterUpdate()

If Me.NeedAuth = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]="
&
Me![RecordNum]

End If

End Sub
+++++++++

The name field of the Report/Letter is coming up blank as well, any ideas?
Thanks very much.

=Forms!Personnel2009Form!LastName & ", " & Personnel2009Form![FirstName]

If RecordNum is numeric

DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]=" &
Me![RecordNum]

 

[dohernan via AccessMonster.com]

RecordNum isn't used in the Report/Letter I was only using it to get the Form
to open the Report at the Current Record. :/

The First name and Last Name are filled in on the Personnel before the user
get to the Authorization question.
Each new Record is added to the Personnel2009Table

When you do the "y" in the Personnel2009Form at the Need Authorization
question, I then force the Record to be saved and Generate the Report/Letter.

ATM nothing at all is happening when I put a "y". @@

I did notice in the Report/Letter that Filter is On and currently set to (
[RecordNum]=1364)
which is the very last record put in.


Private Sub NeedAuth_AfterUpdate()

If Me.NeedAuth = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]=" &
Me![RecordNum]

End If

End Sub

 

[dohernan via AccessMonster.com]

Minor update...

=Forms!Personnel2009Form!LastName & ", "

Gets me the last name and a comma.

 

[Gina Whipp]

dohernan ,

Okay, lets deal with this one at a time...

One...
RecordNum has to be on the form you calling the report from AND in the
report you are calling in order for that filter to work. Something has to
connect the Form and the Report. If you put it in the RecordSource of the
query which the report is based off of it doesn't have to *show* on the
Report.

Two...
If you are only getting the LastName where is the FirstName field? Is it
part of the combo box? Is it on the Form anywhere?

Now I am really confused why you storing the FirstName LastName in two
tables. You said after the *y* is filled in you save the name to another
table? Makes me wonder about your table set-up. The first sign of an
unnormalized database is duplicate data in the same or multiple tables. So
why are moving the Name from one table to the next?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

RecordNum isn't used in the Report/Letter I was only using it to get the
Form
to open the Report at the Current Record. :/

The First name and Last Name are filled in on the Personnel before the
user
get to the Authorization question.
Each new Record is added to the Personnel2009Table

When you do the "y" in the Personnel2009Form at the Need Authorization
question, I then force the Record to be saved and Generate the
Report/Letter.

ATM nothing at all is happening when I put a "y". @@

I did notice in the Report/Letter that Filter is On and currently set to (
[RecordNum]=1364)
which is the very last record put in.


Private Sub NeedAuth_AfterUpdate()

If Me.NeedAuth = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[RecordNum]="
&
Me![RecordNum]

End If

End Sub

Is RecordNum in report AuthorizationNeededL AND on your current form? It
would need to be in both places.

Also just saw your other question... is FirstName and LastName on the form
and filed in? And are you printing the report from
Forms!Personnel2009Form?

 

[dohernan via AccessMonster.com]

I renamed more things, and got it working with the code below-


Private Sub NeedAuth_AfterUpdate()

If Me.[NeedAuth] = "y" Then
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "AuthorizationNeededL", acViewPreview, , "[Record] = " & Me.
[Record]
End If


End Sub



... And I'm embarrassed, I had lots of simple errors, now the names work as
well-
=Forms!Personnel2009Form!LastName & ", " & Forms!Personnel2009Form!FirstName

 

[Gina Whipp]

While I am glad you got things working... I noticed you used a Reserved
Word for a field name, *Record*. You will want to fix as it will cause
issues. For a complete list of Reserved Words see...
http://allenbrowne.com/AppIssueBadWord.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm