create a report from a form and subform.

[greengolfkid]

Hi there. I am not even sure of the terminology involved in most of this stuff as I am a novice and have just decided to create a database for my shop.

I have a form to input the data for a customer leaving a laptop,this consists of a primary key and some basic data so far.

Customer ID
Name
Address
Phone numbers
etc...

This form contains a subform where they may leave more than one laptop.

Laptop ID
Make
Model
Date booked in
faults
etc...

When I have filled in these forms I want to create a receipt to give the customer with this info on it but rearranged and including a company header and basic terms and conditions for them to sign so they understand what will happen when they leave it.

I honestly thought there would be a simple button to create this sort of thing, but it turns out it is a little more complicated(at least I cant find the simple button, lol).

I appreciate that this may not be the most simple/basic of tasks and may be something that a noob shouldn't try to get a handle on but I have just started to do this thing and don't really like to fall at the first hurdle. If anyone can possible give me a few pointers even a link to a tutorial to get me started it would be hugely appreciated, the internet is so vast and has so much info for every search that it can sometimes be too difficult to even search for the correct thing.

 

[Isskint]

GGK, welcome

Reports are, in essence, very simple to create. You are correct, there is a simple button to press. But that depends on what version of access you are working.

• So A2007 + A2010.
  • Method 1
    • Select the table (do not open it)
    • On the ribbon bar, select Create
    • Select Report
    • This will now build a basic report based on that table
  • Method 2
    • On the ribbon bar, select Create
    • Select Report Wizard
    • You can now choose the table and the fields to use
• Before A2007
  • Select the reports pane
  • Select Report Wizard
  • You can now choose the table and the fields to use

What you will end up with is a report that lists EVERYTHING. Don't worry, you can reduce the data shown.
Now you mentioned a subform and reports can also have sub reports. So you will need to create the subform first - that would be based on Laptop details table. You wont need logo's or warranty etc info on this. It just want to list the Laptop ID, Make, Model, Date booked in, faults etc - rather like a spreadsheet or Word table. With the subform created, create the main report - this will be based on the Customer details table. Once you have the basic report created, add the subreport (this works exactly the same as sub forms - a wizrd will guide you through linking the Main Reprot to the Sub Report) and then you can 'mess' around with logos, headers, T&Cs etc.

With that done you should have a report that shows every customer and for each customer the details of the laptops they have brought in.

Now add a command button to the MAIN FORM to print your report.

You will now need to edit the code or Macro that has been created to open the report. You need to add criteria to identify which record you want to print. the criteria will be on the lines of "((MainTableUniqueID)=" & Me!MainForms.UniqueID & ")". Basically whatever field in the table driving the main form. Again this will depend on Access version.

Perhaps you should look through this reply and see what you can achieve, then post back if you need further help. I have given you a lot to think about:banghead:, sorry

 

[greengolfkid]

Wonderful reply, thanks you so much. I have a busy day ahead but intend to attempt this tonight when I get home. I am eager to get to work on this and appreciate the apparent simplicity of your explanation. I will most certainly need more help but I'll let you know how I get on later.

 

[greengolfkid]

Ok, I hve got as far as,

"Now add a command button to the MAIN FORM to print your report."

which I have done, I added a button that created a print preview of the report rather than printing it (I guess I can change that later when I have a working preview, although I would probably prefer checking a preview before printing even if it is an unnecessary extra step for now.)

I think I am ready to attempt editing the macro as you suggest but when I click the "Database Tools" Tab and then the "visual basic" button (which I assume is the way to edit the macro) I am at a bit of a loss.

I wonder if it too much to ask if you could walk me through it please?

I see how simple it is to create a button and how many options there are and have experimented a little with searching for records by name etc. which is an awesome feature I intend to add as I have returning customers who can be found easily to save repeating information in the database. (I understand this may get a little complicated later but one step at a time for now eh?)

 

[Isskint]

That is good progress.

The first thing is to check what way the CommandButton wizard created the code. If you are in A2007/2010 then it would be a macro, otherwise VBA. Either way it is easier in VBA, so open the form in design mode, select the commandbutton, open the properties list and select EVENT Tab. If it says [Event Procedure] next to On Click then it is already VBA, click the elipses ... and you will be in the VBA editor at the code. If not, delete what is in there, and select [Event Procedure] from the dropdown and now you will be in the VBA editor at the code (although there will be no code).

The code should look something like this (add/change as necessary);

Private Sub CommandButtonName_Click()
On Error GoTo Err_CommandButtonName_Click
    Dim stDocName As String
 
    stDocName = "YourReportName"
    DoCmd.OpenReport stDocName, acNormal
Exit_CommandButtonName_Click:
    Exit Sub
Err_CommandButtonName_Click:
    MsgBox Err.Description
    Resume Exit_CommandButtonName_Click
    
End Sub

Now, what are the field names that link your Main form and Subform? I will assume Main form is TableMain & idField. So you need to add a criteria to the OpenReport method as follows.
"((idField)=" & Me.idField & ")". this goes at the end of the current OpenReport line (and note the extra comma);
DoCmd.OpenReport stDocName, acNormal,,"((idField)=" & Me.idField & ")"

This way, when the report is opened, the criteira tells it only to return data where idField in the report = idField on the Form.

 

[greengolfkid]

This is the bit I was dreading, all the coding I did in Uni all those years ago has totally deserted me. In fact it was so long ago it was all in COBOL and then a bit of Pascal which I am sure as similar but I dont remember any of it.

This is what I have:

Private Sub CommandButtonName_Click()
On Error GoTo Err_CommandButtonName_Click
Dim stDocName As String

stDocName = "customer"
DoCmd.OpenReport stDocName, acNormal,,"((idField)=" & Me.idField & ")"
Exit_CommandButtonName_Click:
Exit Sub
Err_CommandButtonName_Click:
MsgBox Err.Description
Resume Exit_CommandButtonName_Click

End Sub

I feel sure that I have the wrong end of the stick.

DoCmd.OpenReport stDocName, acNormal,,"((idField)=" & Me.idField & ")"

should that be:

DoCmd.OpenReport stDocName, acNormal,,"((Customer ID)=" & Me.Customer ID & ")"

there are a few relationships in the table that I didn't mention which aren't really important at the moment and can be ignored or removed if necessary, I cant post a picture yet but here are the table I have in full if it helps. (should've put them in to start really)

Table:

Customer

Customer ID
Name
Address
Postcode
Home Number
Mobile Number
Email Address

Laptop

Laptop ID
Make (I have a table with lots of brands and use a drop down box in the form)
Model
Date booked in
Problems (which I have a table with 21 records and use a dropdown box in the form)
Repairs Carried out (which is for use later in the process)
Customer ID (which is the relationship I think you asked for)
Date Collected (again for the receipt to be generated later)

I am so sorry to be so apparently useless, I am beginning to think I am trying to run before I can walk.

 

[greengolfkid]

perhaps you point me in the right direction for good form with naming fields too?

 

[Isskint]

Yep, replace my generic names with your actual field/control names, so DoCmd.OpenReport stDocName, acNormal,,"((Customer ID)=" & Me.Customer ID & ")" should be right - so ling as CustomerID is numeric. If it is Text then use single quotes around the variable, so "((Customer ID)='" & Me.Customer ID & "')"

For naming conventions have a look at this article http://www.access-programmers.co.uk/forums/showthread.php?t=156049&highlight=naming+conventions there is a good list and 2 good links. The basics are to avoid spaces and non Alphanumeric characters. Name things with a meaningful name and prefix it it to identify what type of data is stored in the object or what type of object it is. EG textboxes on forms and reports are usually prefixed tb EG tbUserName.