Trying to pring report that looks exactly like form

[Brian]

Hello,
I've been reading and reading, and I've tried every suggestion I've
found, and I'm still having trouble.

I keep a database with 2 tables: Students and Classes
I have a Form that has a subform in it. The main form and subform are
based on different queries. The 2 queries pull from their own table.
Primary Key and Forrign Key = SSN. Form works great. Form is called
frmActiveEmployees.
What I want to do is print a form exactly the way I see it, minus a
few things. What I've done is, set up a blank report in design view.
The first thing I did was add a subform/subreport and pointed it to my
Form that I love so much. I named this report rptActiveEmployees.
From here, I was able to crop out some unwanted items on the subform.
I set up a command button from the frmActiveEmployees to print the
current displayed record to the report. The problem is the report
always shows the first person in the table. I'm trying to link the 2
on the SSN.
The text box on the form holding the SSN of the current record is
called txtSSN. All around my SSN's are stored as 9 digit numbers, but
I use an input mask for it. The problem may be that the form text box
for SSN is holding 11 digits, 2 extra for the dashes, but the table is
only saving the numbers.
Here is the current code I am using, that doesn't quite work:

Private Sub CmdPrint_Click()
On Error GoTo Err_Print_Click

Dim strWhere As String
Dim stDocName As String

strWhere = "[SSN]=" & "'" & Me![txtSSN] & "'"
stDocName = "rptActiveEmployees"
DoCmd.OpenReport stDocName, acPreview, , strWhere


Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub


Hope I gave enough detail. Thanks.
Brian

 

[Arvin Meyer]

Strip the dash out of the string:

Dim strTemp As String
strTemp = StripChar(Me.txtSSN,"-")

then use the strTemp variable in your code. Put the StripChar() function in
a standard module.

One other tip: Do not store SSNs in your database, unless it is secure and
anyone who can possibly have access to it is bonded. Since Access is not
100% secure (few databases are) you are taking a huge chance on being sued
by someone who is injured by your unnecessary use of a SSN.

Function StripChar(strIn As String, strChar As String) As String
'--------------------------------------------------------------------
' Name: StripChar
' Purpose: Strip out unwanted characters or spaces
' Inputs: strIn As String, strChar As String
' Returns: String
' Author: Arvin Meyer
' Date: February 09, 1999
' Comment: Strip any character or space from a string
'--------------------------------------------------------------------
On Error GoTo Err_StripChar

Dim i As Integer
Dim strTemp As String

StripChar = strIn

If strIn = "" Then Exit Function

For i = 1 To Len(strIn)
If Mid(strIn, i, 1) <> strChar Then
strTemp = strTemp & Mid(strIn, i, 1)
End If
Next i

StripChar = strTemp

Exit_StripChar:
Exit Function

Err_StripChar:
Select Case Err
Case 0
Case Else
MsgBox Err.Description
Resume Exit_StripChar
End Select

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hello,
I've been reading and reading, and I've tried every suggestion I've
found, and I'm still having trouble.

I keep a database with 2 tables: Students and Classes
I have a Form that has a subform in it. The main form and subform are
based on different queries. The 2 queries pull from their own table.
Primary Key and Forrign Key = SSN. Form works great. Form is called
frmActiveEmployees.
What I want to do is print a form exactly the way I see it, minus a
few things. What I've done is, set up a blank report in design view.
The first thing I did was add a subform/subreport and pointed it to my
Form that I love so much. I named this report rptActiveEmployees.
From here, I was able to crop out some unwanted items on the subform.
I set up a command button from the frmActiveEmployees to print the
current displayed record to the report. The problem is the report
always shows the first person in the table. I'm trying to link the 2
on the SSN.
The text box on the form holding the SSN of the current record is
called txtSSN. All around my SSN's are stored as 9 digit numbers, but
I use an input mask for it. The problem may be that the form text box
for SSN is holding 11 digits, 2 extra for the dashes, but the table is
only saving the numbers.
Here is the current code I am using, that doesn't quite work:

Private Sub CmdPrint_Click()
On Error GoTo Err_Print_Click

Dim strWhere As String
Dim stDocName As String

strWhere = "[SSN]=" & "'" & Me![txtSSN] & "'"
stDocName = "rptActiveEmployees"
DoCmd.OpenReport stDocName, acPreview, , strWhere


Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub


Hope I gave enough detail. Thanks.
Brian

 

[Brian]

Thanks for the follow up. I tried it, but it didn't work. To me, it
seems that I wouldn't need to stip anything, or even need
strWhere = "[SSN]=" & "'" & Me![txtSSN] & "'"
in my command. I mean, the report is nothing but a subform pointing
to the Form, so it looks the same. So, both the form and the report
are identical, I shouldn't have to worry about stripping or telling
the computer that SSN=txtSSN.
What I did end up doing, was make a report based on a query, the same
query that frmActiveEmployees is based on. When I made a new button
from the form, I used the wizard to point it at the new report, and it
asked me what field to link them on. It worked fine. I had to make a
subform on the new report to show the classes as well as the personal
information. That part was easy too. So, in the end I'll get what I
needed. I was just asked to make a print out exactly the way it
looked on the form. My form was too wide which is why I wanted to
crop out some of the stuff on the far right which caused it to print a
2nd unnecessary page.
Thanks again.
Brian

Strip the dash out of the string:

Dim strTemp As String
strTemp = StripChar(Me.txtSSN,"-")

then use the strTemp variable in your code. Put the StripChar() function in
a standard module.

One other tip: Do not store SSNs in your database, unless it is secure and
anyone who can possibly have access to it is bonded. Since Access is not
100% secure (few databases are) you are taking a huge chance on being sued
by someone who is injured by your unnecessary use of a SSN.

Function StripChar(strIn As String, strChar As String) As String
'--------------------------------------------------------------------
' Name: StripChar
' Purpose: Strip out unwanted characters or spaces
' Inputs: strIn As String, strChar As String
' Returns: String
' Author: Arvin Meyer
' Date: February 09, 1999
' Comment: Strip any character or space from a string
'--------------------------------------------------------------------
On Error GoTo Err_StripChar

Dim i As Integer
Dim strTemp As String

StripChar = strIn

If strIn = "" Then Exit Function

For i = 1 To Len(strIn)
If Mid(strIn, i, 1) <> strChar Then
strTemp = strTemp & Mid(strIn, i, 1)
End If
Next i

StripChar = strTemp

Exit_StripChar:
Exit Function

Err_StripChar:
Select Case Err
Case 0
Case Else
MsgBox Err.Description
Resume Exit_StripChar
End Select

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hello,
I've been reading and reading, and I've tried every suggestion I've
found, and I'm still having trouble.

I keep a database with 2 tables: Students and Classes
I have a Form that has a subform in it. The main form and subform are
based on different queries. The 2 queries pull from their own table.
Primary Key and Forrign Key = SSN. Form works great. Form is called
frmActiveEmployees.
What I want to do is print a form exactly the way I see it, minus a
few things. What I've done is, set up a blank report in design view.
The first thing I did was add a subform/subreport and pointed it to my
Form that I love so much. I named this report rptActiveEmployees.
From here, I was able to crop out some unwanted items on the subform.
I set up a command button from the frmActiveEmployees to print the
current displayed record to the report. The problem is the report
always shows the first person in the table. I'm trying to link the 2
on the SSN.
The text box on the form holding the SSN of the current record is
called txtSSN. All around my SSN's are stored as 9 digit numbers, but
I use an input mask for it. The problem may be that the form text box
for SSN is holding 11 digits, 2 extra for the dashes, but the table is
only saving the numbers.
Here is the current code I am using, that doesn't quite work:

Private Sub CmdPrint_Click()
On Error GoTo Err_Print_Click

Dim strWhere As String
Dim stDocName As String

strWhere = "[SSN]=" & "'" & Me![txtSSN] & "'"
stDocName = "rptActiveEmployees"
DoCmd.OpenReport stDocName, acPreview, , strWhere


Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub


Hope I gave enough detail. Thanks.
Brian