Create a Report from current record on a form

[accessnewbie8944]

Hello, I am totally new to the Access world. I need help!!!!
I have created a form with all the fields that I need. Now I would like to take specific fields and generate a report that I will export to excel and email. Unfortunately I cannot change the process, just try to go around it. So here is where I am at.

1) I can create a report but it pulls up all my records. I just want a button that will take my current record and generate the report in the format that I have already chosen.
I have made a query, that didn't work.
I used the Report Wizard and it does all the records, not just the one that was populating the form when launched.
SiteID is my Primary Key
The rest of the tables fields are as follows.
Site Type
Business Type
Note
BWV size
Is there a way to do this without the code. I have learned recently that my naming convention is off, so I think that is why I am having a such a hard time trying to use the VB script. Please help.

 

[Alansidman]

Look at this example that I created a while ago that should help you.

Look at the code behind the Print Button

 

[accessnewbie8944]

Any way you could explain it for what I have.

 

[pr2-eugin]

Hello there.. Welcome to AWF..

Am sorry but I seem to not understand what you are saying here..

1) I can create a report but it pulls up all my records. I just want a button that will take my current record and generate the report in the format that I have already chosen.....

Good.. But you failed to tell the condition that it needs to filter upon..

You also mentioned a Form involved here.. does that form act as a criteria filter to the Query you have designed?

Could you please another shot at explaing your problem.. Maybe throwing out some examples?

 

[parkerjallen]

I believe what he wants, because I am in the process of making something similar, is a button to generate a report based on the criteria of the currently selected record.

 

[accessnewbie8944]

Ok sorry for the confusion. Let me try to better explain.
I have a form. On this form I have allot of fields. Lets say 40. I used the access report wizard and only selected 10 fields from my form. The result was showing me all the fields I wanted but for every single record.
What I want to do is create a button on my form. That button will generate a report based on only that data that is on that form at that time. So if lets say I have record A1 and I press the button and the report takes out all the items that I specified. Then I go to record A2 and do the same thing.

 

[accessnewbie8944]

When i say ever single record, I meant ALL records in my table.

 

[accessnewbie8944]

Lets say I have
A=Primary key
B
C
D
E
F
G
H
This is my whole table. I only want to create a report that displays A,B, D, F but I want to do it from the form that I am currently on. So while in my from I press the button and A, B, D,F all show the text that I had up on my form, just that record.

 

[Wayne]

Hopefully, you already know how to create a button, and place it where you want on the form. With the form in design view, and the properties window open on the "Events" tab, click on the button to highlight it, click on the line for "ONClick" event in the properties window. There is a little button with 3 dots on it on the right. Click on it, and select Macro Builder on the Builder form.

On the Macro Design View screen, in the column named "Action", click on the drop down box, ansd select "Open Report". At the bottom of the screen, on the line that syas "Report Nme", select the name of the report you want to print. On the line named "View", select "Print Preview". On the line that named "Where Condition", put the following:

[YourReportFieldName] = Forms![YourFormName]![YourReportFieldNameThatMatchesYourForm]

Window Mode should be set at Normal. Then save your macro (and name it something easy to remember).

Save your form. Re-open it, select a record and try it. The report should open in preview mode, to the current record on your screen. If it's what you want, select "Print" and you're done.

 

[Wayne]

Sorry! I goofed! That should read:

[YourReportFieldName] = [Forms]![YourFormName]![YourFormFieldThatMatchesYourReportFieldName]

I kinda had that one backwards! Sorry. This will work.

 

[accessnewbie8944]

IT WORKS......IT WORKS!!!!!! Thanks. Now step two. When I need it to keep its format but stay in excel. When I email the Excel spreadsheet off to somebody else, they make thier additions to the excel spreadsheet. When exporting it though, it automatically reverts the form to more of a table looking excel sheet, completely ignoring the format that I made in the Report. What now?

 

[Wayne]

Great. I'm glad it worked out for you. I'm sorry but my experience is all in Access, not Excel. I wouldn't be able to help you there. Anybody else got any ideas?

 

[accessnewbie8944]

Does anybody know how to take my report and to populate an existing excel template with the same record that is on the report?

 

[Samantha]

I have some code that takes the information on my form and transfers it to an Excel template. It does not link any information back into Access. I then save the template in a file location on my hard drive. It may be what you are looking for.

Private Sub cmdMergeXLbttn_Click()
On Error GoTo Error_Handler
'Declare a variable named MySheetPath as string
Dim MySheetPath As String
'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xltx"
'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strServiceAddress As String
Dim ctl As Control
Dim varItem As Variant
'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = GetObject(MySheetPath)
'Make sure everything is visible
XL.Visible = True
XLBook.Windows(1).Visible = True
'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)
'Build service address string from multi list box
'For Each varItem In Me.ServiceAddress.ItemsSelected
'strServiceAddress = strServiceAddress & "'" & Me.ServiceAddress.ItemsSelected(varItem) & ", "
'Use this line if your value is text
'strServiceAddress = strServiceAddress & "" & ctl.ItemData(varItem) & "',"
'Next varItem
'trim trailing comma
'strServiceAddress = Left(strServiceAddress, Len(strServiceAddress) - 2)
XLSheet.Range("DateGen") = Date
XLSheet.Range("ProjectNumber") = JobNumber
XLSheet.Range("CompanyAdd") = [Company] & vbCrLf & ([sfrmContacts].[Form]![Address]) & vbCrLf & ([sfrmContacts].[Form]![City]) & ", " & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
XLSheet.Range("Contact") = ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
XLSheet.Range("Phone") = ([sfrmContacts].[Form]![Phone])
XLSheet.Range("Fax") = ([sfrmContacts].[Form]![BusinessFax])
XLSheet.Range("ProjectDescription") = ProjectDescription
XLSheet.Range("ServiceAdd") = Me!ServiceAddress.Value '-returned only first selection
XLSheet.Range("City") = City
XLSheet.Range("State") = State
XLSheet.Range("ProjectType") = ([sfrmProjectType].[Form]![ProjectType])
 
MsgBox "Your Project Information Sheet is Ready." & vbCrLf & "Please re-name your document and save to the job file.", vbOKOnly, "Successful"
Exit_Procedure:
Exit Sub
Error_Handler:
MsgBox "An error has occurred in this application." & " Please contact your technical support with the following information:" & vbCrLf & vbCrLf & "Error Number" & " " & Err.Number & ", " & Err.Description, Buttons:=vbCritical
 
Resume Exit_Procedure
 
End Sub

You do have to set up the bookmarks in your template by renaming the cells. Highlight the cell you want the information to go to and click on the location box "A1" and simply rename it. I think also the template has to be in the same folder with your database.