Populate table from query based form

[Wind54Surfer]

Hi all,

I have a "frmOrder" made up of 2 subforms with customer information and 1
calculated field.
Made up an "frminvoice" based in a query (based on the 2 subforms) to be
able to auto-fill some information)
I want to be able to populate a table (tblInvoice) with automatically filled
info + added descriptive information.
Is there a way to do this?
I have little experience with Access and whatever I try fails.
I hope I explained properly.

Thanks in advance.

 

[PJFry]

Need a bit more information. What have you tried that failed?

Generally speaking, the purpose of a form is to put data into a table. Are
there other tables in the database than tblInvoice? Are they providing or
storing any of the data you are using in your forms? Is the calculated field
one that you are trying to save in the table?

Let's start there.

PJ

 

[John W. Vinson]

Hi all,

I have a "frmOrder" made up of 2 subforms with customer information and 1
calculated field.
Made up an "frminvoice" based in a query (based on the 2 subforms) to be
able to auto-fill some information)
I want to be able to populate a table (tblInvoice) with automatically filled
info + added descriptive information.
Is there a way to do this?
I have little experience with Access and whatever I try fails.
I hope I explained properly.

Thanks in advance.

What are the Recordsources of the three forms? What are your tables, what
fields do they contain, and how are they related? Are you trying to store
information redundantly from the two subforms into the invoice table? If so,
DON'T - that's not how relational databases work!

 

[Wind54Surfer]

Thanks for your help

The "frmOrder" has the info of customer orders (name, PO#, and the
calculated field in a subform is "txtAmountOwing" (Order price - Deposit).

Up to now when I click in button Invoice (in "frmOrder") "frmInvoice" opens
(whose record source is "tblInvoice") and I had to manually enter the info;
PO#, "txtAmountOwing" and enter a description of services rendered, and it
all goes to "tblInvoice".

I want to automatically be able to see the PO# and "txtAmountOwing" (to
avoid mistakes) in this form when it opens.
=========================================================================

I tried making a query (based in the subforms of "frmOrder") and a
variations of this involving "tblInvoice" but it doesn't work.
==========================================================================
The value of the calculated field will be saved in "txtInvoice" to match
payment later as well as PO# and description of the Invoice.
=========================================================================

Hope this help, please let me know.

And Thanks again

 

[PJFry]

That should be enough.

First let’s look at how to do what you want and then we can talk about a
better way to do it.

Open frmInvoice and under Properities – Events go to the OnLoad event and
choose Code Builder.

Use this code:
Private Sub Form_Load()

Me.PO# = Forms!frmOrder!PO#
Me.txtAmountOwing = Forms!frmOrder!txtAmountOwing

End Sub

You have to make sure that frmOrder is open for this to work. This method
can produce some errors, so be careful.

Now let’s look at a better way to do this.

To John’s point, if you are storing the same data in two tables, then you
are defeating the purpose of a relational database. Unless PO# is the
primary way you relate the information in your two tables, then it should
only show up in either tblInvoice or tblOrder, not both. If it’s the way you
relate the data, consider using an Autonumber in the future. I prefer to use
a key that is difficult to mess up.

Don’t use the # character in your field names. 90% of the time you won’t
have an issue, but there are times using that character will foul things up.
Use PONum or PO_Num instead.

Now where should store txtAmountOwing? No where. If you know that A+B=C,
then you only need to store two of those three values. Have txtAmountOwing
calculate on the form or report you are using at the time. In this case, it
looks like you want to store txtAmountOwing in both tables and storing the
same calculated data in multiple tables is a double whammy.

Hope this helps!
PJ

 

[Wind54Surfer]

THANK YOU!

I have been struggling forever with this.
Forgot what a great community this is!

By the way is OrderID not PO# (that is what I call it in paper)

And the txtAmountOwing value is not stored in any table, except as
txtAmountInvoiced in tblInvoiced

Thanks again so much!