create report based on input form!

[drive.hard-go.pro]

i have made this input form that contains 10 product code field and 10 product description fields. I want to enter a product code and immediately the box description next to it show the product's description. Also I have a discount combo box and a qty txt box. I have a create report button and want to use th inputted data to create my report!!! Is this possible?


The attachment is my input form....

Thank you

 

[ajetrumpet]

I want to enter a product code and immediately the box description next to it show the product's description.

You can do this with the column property. Here is Microsoft's article on the technique used...

http://support.microsoft.com/kb/319482/en-us

I have a create report button and want to use th inputted data to create my report!!! Is this possible?

Of course it is!! BUT, if this input form of yours contains 10 rows of independent controls, it will be a long process. Invoice creation is tough in general, and confusing. If you would like a shorter method, and possibly more efficient (especially for printing reports), check out the invoice form in the database I posted here.

If you would like to keep it the way it is, you will probably have to manually create your report to mirror the format of your input form. Thus, you will have the same amount of independent controls on your report that are on your form. As I mentioned before, this sure is a long process, but not very complicated to complete. For the report controls, you would only need one Sub...

Private Sub YourReport_Load()

  Me.Code1Control = Forms!YourForm!Code1Control
  Me.Description1Control = Forms!YourForm!Description1Control

    Etc, etc, etc.... (for every control on the Form!!)

End Sub

In the example I posted, a report can be printed that has a recordsource of the Invoice Form's Subform. This is basically a one-step process as soon as you get the invoice form and such set up. That's a bit easier than referencing a lot of controls on a Form.

 

[drive.hard-go.pro]

Need help

Thank you for your reply.

I have tried what you said but I can't get it to work... If you could attach a sample using 2 fields for each(product code, qty, discount) and the create report button, showing also the created report I would appreciate it much


Thank you

 

[ajetrumpet]

Here is short example of what I mean.

Features of the Form / Report

** The form's "Customer" controls populate the "category" controls (this is done the same way as the Microsoft article describes).

** The "discount" combo box is populated from a lookup table.

** All of the code is located with the Report's "OnLoad" Event.

** The purpose of the "For, Next" Loop is to hide any controls that are not populated (e.g. - Very useful for a form like yours, if a customer is only ordering say 5 different products). It would look kinda strange if they saw 5 extra lines of empty boxes on their invoice.

 

[drive.hard-go.pro]

that was helpful...!!!!!

thank you...
your sample helped me alot in most of the things I want to do in my database but I didn't manage to get the category show up when i choose a product code from the combo box...

If you could help me how to do it I would appreciate it even more!


Anyway thank you very much
Your help is appreciated...

 

[ajetrumpet]

Unfornately, that's about as far as I can go without seeing what you're doing.

There could be more than one reason why this is not happening, but just talking about it in the thread with no visual to reference is almost impossible, and frustrating for both sides of the table...

 

[drive.hard-go.pro]

finally succeed

i have managed to show the sdecription in my textbox but I want on my report to also show the price so that I can calculate total!!! is this possible?

thank you

 

[ajetrumpet]

but I want on my report to also show the price so that I can calculate total!!!

Show the price?? what do you mean?? Just the total??

Then put another box on it...

totalbox = control1 + control2, etc...

 

[drive.hard-go.pro]

price

no, i mean on my input form i want to see the price when i select a product from the product list, not only the category...

 

[ajetrumpet]

Then add a column for that control with the AfterUpdate of the Product box...

me.desc1 = me.product.column(1)
[color=red]me.price1 = me.product.column(2)[/color]

Don't forget to update the number of columns and column widths in the Product box's property list (AND the rowsource of it too, to select the price column from that table!!)

 

[drive.hard-go.pro]

problems again!

thank you for your quick responses...
i am sure you mean column count and not width! this problem has been fixed!


BUT(again) I have discovered that when I put also the price and then try to find the total in my report, I have to fill all the products in my invoice form or else the total is not presented!

any possible solutions will be much appreciated

once again thank you

 

[drive.hard-go.pro]

sample

here is attached your sample with the changes I made so that you can see what the problem is!

 

[ajetrumpet]

Complicated

Here is the form and report that does exactly what you want, but look at the amount of code that has to written just for 3 lines!!

I would really suggest you go about this another way. As a matter of fact, the sample database I referenced above with the link would be a wonderful adaptation for you with this, but if you're insistent on doing it this way, it WILL become complicated, and probably overwhelming to you.

There may be more efficient ways to write the code I have in the report's event, but the process itself is not efficient.

So, having said that, and any feelings I may hurt by it, here is the sample database that does what you need it to do.

I might mention too, that the sample DB is worth looking at, considering the fact that the same file was used for this thread as well...

 

[drive.hard-go.pro]

problem with access 2003

I have done what what you said and worked just fine in access 2007 but when i open it with the 2003 access, and try to preview the report it gets me to the debugger and tells me "you can't assign a value to this object!" for Me.amount1 = amount1

Is there a way to make it work on all access (2000, 2003,2007)?

 

[ajetrumpet]

That's kinda strange. I upload all files here in Access 2000 format. But I don't believe any of the event coding here changes between program versions. Try this one instead, I converted it specifically to the 2002-03 format. Maybe that will work. If it does, also try it with A07

 

[drive.hard-go.pro]

what changes did you make?
i cannot find them!

 

[ajetrumpet]

What changes did I make?? I didn't make ANY. From my last post, I simply uploaded a new file in 2002-03 format because you were saying you had compatibility issues.

What do you mean by this....???

I have done what what you said and worked just fine in access 2007 but when i open it with the 2003 access, and try to preview the report it gets me to the debugger and tells me "you can't assign a value to this object!" for Me.amount1 = amount1

Do you mean that you've changed the structure to look like my sample in the "sample databases" section of the forum???

And...this error: "you can't assign a value to this object!" makes me think that the control you are trying to set a value for already has a controlsource assigned to it. I believe that is a common error when trying to do something like that.

 

[drive.hard-go.pro]

issues

my report is presented in the right way when opened on access 2007 but on other access the debugger shows that message!

 

[ajetrumpet]

Have you changed the coding in anyway?? If you haven't, corruption of the database is possible. I have had messages like this in the past, and in order to eliminate it, I had to import all of my objects into a new file. You might try to that as well and see if it fixes the problem.

Also, there was a few lines of redundant coding from the initial file I posted. I don't think this will clear an error message, but it's worth a try, for reasons that might pertinent, but reasons I cannot explain very well. Open Access 2003, and replace the first subroutine with the second one....

Private Sub Report_Load()

Dim amount1, amount2, amount3

    amount1 = IIf(IsNull(Forms!invoiceform!qty1), "0", Forms!invoiceform!qty1 * Forms!invoiceform!txtprice1)
    amount2 = IIf(IsNull(Forms!invoiceform!qty2), "0", Forms!invoiceform!qty2 * Forms!invoiceform!txtprice2)
    amount3 = IIf(IsNull(Forms!invoiceform!qty3), "0", Forms!invoiceform!qty3 * Forms!invoiceform!txtprice3)

  Me.id = Forms!invoiceform!id

  Me.code1 = Forms!invoiceform!code1
    Me.code2 = Forms!invoiceform!code2
      Me.code3 = Forms!invoiceform!code3
      
  Me.cat1 = Forms!invoiceform!cat1
    Me.cat2 = Forms!invoiceform!cat2
      Me.cat3 = Forms!invoiceform!cat3
      
  Me.qty1 = Forms!invoiceform!qty1
    Me.qty2 = Forms!invoiceform!qty2
      Me.qty3 = Forms!invoiceform!qty3

  Me.disc1 = Forms!invoiceform!disc1
    Me.disc2 = Forms!invoiceform!disc2
      Me.disc3 = Forms!invoiceform!disc3
      
  Me.unitp1 = Forms!invoiceform!txtprice1
    Me.unitp2 = Forms!invoiceform!txtprice2
      Me.unitp3 = Forms!invoiceform!txtprice3

  Me.amount1 = amount1
    Me.amount2 = amount2
      Me.amount3 = amount3

  Me.txttotal = amount1 + amount2 + amount3
  
  Me.amount1 = IIf(Me.amount1 = 0, Null, Me.amount1)
  Me.amount2 = IIf(Me.amount2 = 0, Null, Me.amount2)
  Me.amount3 = IIf(Me.amount3 = 0, Null, Me.amount3)
  
  Dim c As Control

  For Each c In Me.Controls
    If IsNull(c) Then
      c.BorderStyle = Transparent
    End If
  Next

End Sub

Change to this...

Private Sub Report_Load()

    Me.amount1 = IIf(IsNull(Forms!invoiceform!qty1), "0", Forms!invoiceform!qty1 * Forms!invoiceform!txtprice1)
    Me.amount2 = IIf(IsNull(Forms!invoiceform!qty2), "0", Forms!invoiceform!qty2 * Forms!invoiceform!txtprice2)
    Me.amount3 = IIf(IsNull(Forms!invoiceform!qty3), "0", Forms!invoiceform!qty3 * Forms!invoiceform!txtprice3)

  Me.id = Forms!invoiceform!id

  Me.code1 = Forms!invoiceform!code1
    Me.code2 = Forms!invoiceform!code2
      Me.code3 = Forms!invoiceform!code3
      
  Me.cat1 = Forms!invoiceform!cat1
    Me.cat2 = Forms!invoiceform!cat2
      Me.cat3 = Forms!invoiceform!cat3
      
  Me.qty1 = Forms!invoiceform!qty1
    Me.qty2 = Forms!invoiceform!qty2
      Me.qty3 = Forms!invoiceform!qty3

  Me.disc1 = Forms!invoiceform!disc1
    Me.disc2 = Forms!invoiceform!disc2
      Me.disc3 = Forms!invoiceform!disc3
      
  Me.unitp1 = Forms!invoiceform!txtprice1
    Me.unitp2 = Forms!invoiceform!txtprice2
      Me.unitp3 = Forms!invoiceform!txtprice3

  Me.txttotal = Me.amount1 + Me.amount2 + Me.amount3
  
  Me.amount1 = IIf(Me.amount1 = 0, Null, Me.amount1)
  Me.amount2 = IIf(Me.amount2 = 0, Null, Me.amount2)
  Me.amount3 = IIf(Me.amount3 = 0, Null, Me.amount3)
  
  Dim c As Control

  For Each c In Me.Controls
    If IsNull(c) Then
      c.BorderStyle = Transparent
    End If
  Next

End Sub

It might not do anything, but it's worth a try.