Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / April 2008

Tip: Looking for answers? Try searching our database.

Populate table from  query based form

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Wind54Surfer - 30 Apr 2008 05:01 GMT
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 - 30 Apr 2008 06:25 GMT
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  

> Hi all,
>
[quoted text clipped - 9 lines]
>
> Thanks in advance.
Wind54Surfer - 30 Apr 2008 14:16 GMT
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

> Need a bit more information.  What have you tried that failed?  
>
[quoted text clipped - 20 lines]
> >
> > Thanks in advance.
PJFry - 30 Apr 2008 15:59 GMT
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

> Thanks for your help
>
[quoted text clipped - 45 lines]
> > >
> > > Thanks in advance.
Wind54Surfer - 01 May 2008 00:05 GMT
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!

> That should be enough.
>
[quoted text clipped - 86 lines]
> > > >
> > > > Thanks in advance.
John W. Vinson - 30 Apr 2008 06:40 GMT
>Hi all,
>
[quoted text clipped - 9 lines]
>
>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!
Signature


            John W. Vinson [MVP]

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.