RE: Saving Form

ok well the form is bound to a query where the only thing in it is the "in
stock" field from my second table. how do i specify that the number i enter
in the text box for the quantity is subtracted from the "in stock" and that
it matches only to the product i selected in my second drop box?

"Sprinks" wrote:

> Is your form bound to a table, that is, is its RecordSource property set to
> the name of a table or query? If it is a query, is it "updateable"? Some
> queries, for a variety of reasons cannot be updated. You can tell if it is
> by executing the query by itself and attempting to change data in it.
>
> If it is bound to a table or updateable query, are all of the form controls
> (textboxes, combo boxes, checkboxes, etc.) bound to fields of the
> RecordSource, that is, is their ControlSource property set to the names of
> each corresponding field?
>
> If the answer is Yes to all of the above, your data IS being saved to you
> table(s). You HAVE the computer record of the invoice that you want. You
> don't need to do anything further. If you want a printed copy for a paper
> file, either print the screen (File, Print), or if that loses too much of the
> formatting for your taste, define a report, and print it for the current
> record as I described in an earlier post.
>
> If the answer to any of the above is No, correct the deficiencies.
>
> Good luck.
>
> Sprinks
>
>
> "DistrautMan" wrote:
>
> > ill try all that out. the point of the entire database is that we have an
> > invoice in microsoft word aleady but what we need was 1: a table that stored
> > everything we need to know about our inventory ex. distributer, product, buy
> > price, sell price, quantity, reorder. then i had to duplicate the existing
> > invoice into access where i could now select the product from drop menus that
> > will display the prices and a command button that will total them, the only
> > place ive figured out how to do this is the form. but ive already
> > accomplished all that. the reason im using access is because i need the
> > invoice to tie into the inventory so that it automatically subtracts the "in
> > stock" value from the database, which i will attempt to do in a few minutes
> > from your explinations. but now i cant save like i need it do be saved. i
> > dont care which format i use as long as it saves in the same view as the
> > printed copy. i need two ways of storing invoices: one as a hard copy print
> > out to put in a drawer and another as a saved copy on the computer.
> >
> > "Sprinks" wrote:
> >
> > > My point of confusion is that you seem to be using an Access form simply as a
> > > replica of a paper form, or a Word form. Normally, Access forms are used to
> > > enter data into tables and/or display existing data. If your form is used in
> > > this way, each time you entered a new invoice, the data would be stored in
> > > tables. There would be no need to print an extra hard copy of each invoice
> > > because the invoice could be reviewed through your form at any time. You
> > > could, of course, print it if desired from a command button on the form that
> > > would execute a report for the current record.
> > >
> > > I should caution you that, unlike Excel or Word, learning Access,
> > > particularly from the design development side, is a significant learning
> > > curve. If you will have the responsibility of developing applications for
> > > your company, I strongly advise that you get some Access-specific training,
> > > and to pick up one or more good Access reference books. Trying to develop an
> > > application from ground zero is likely to be a very frustrating process.
> > >
> > > That said, an Update query is simply a type of query that changes data in
> > > one or more tables. In Access parlance, a query can be a Select query (the
> > > most common), Cross-Tab, Make-Table, Append, Update, or Delete. You can also
> > > use a query to summarize data from your tables, for example, the total amount
> > > sold to each customer last year.
> > >
> > > To experiment, start a new query based on some test tables with test data in
> > > them.
> > > Let's say you had one called InvoiceItems that stored each item's
> > > partnumber, order quantity, and unitprice, a primary key ID, and an
> > > InvoiceNumber that associates the items with the Invoice. Records would look
> > > like:
> > >
> > > InvoiceItemID InvoiceNumber PartNumber Qty UnitPrice
> > > ---------------- -------------------- -------------- ----------- -------------
> > > 45 11111 12345 12 65.00
> > > 46 11111 24654 10 75.00
> > >
> > > There is no need to store extended price because you can calculate in a
> > > query as a calculated field. Similarly, the part description is not needed
> > > because it can be obtained from the Products table through a join
> > > relationship on the PartNumber.
> > >
> > > Now, let's say you have an Inventory table that stores the partnumber and
> > > the current quantity on hand:
> > >
> > > PartNumber QtyOnHand
> > > ---------------- --------------
> > > 12345 650
> > > 24654 55
> > >
> > > Show the InvoiceItems and the Inventory tables. If Access does not provide
> > > a link between the two PartNumber fields, establish one by dragging one to
> > > the other. Then drag the InvoiceNumber field from InvoiceItems to the grid
> > > and the QtyOnHand field from the Inventory table. Execute the query by
> > > pressing the exclamation point icon. Note the quantity on hand for the items
> > > of one of the invoices you've entered into your test tables, and note the
> > > invoice number.
> > >
> > > Change to Update view by Query/Update. In the Criteria row of the
> > > InvoiceNumber enter the number you will use. In the Update To: row of the
> > > QtyOnHand field, type an expression that reduces the quantity on hand by the
> > > amount in the InvoiceItems Qty field:
> > >
> > > [Inventory].[QtyOnHand]-[InvoiceItems].[Qty]
> > >
> > > Execute the query, switch back to select view, and execute again. The
> > > QtyOnHand of each item on that invoice will have been reduced by the quantity
> > > entered on the invoice.
> > >
> > > To execute the query from a command button on the form, instead of manually
> > > entering the InvoiceNumber criteria as you just did, use a reference to a
> > > form control that has this information:
> > >
> > > Forms![YourForm]![YourInvoiceNumberControl]
> > >
> > > Use the wizard to create a command button that executes the query. To
> > > toggle the wizard on, choose View, Toolbox, and depress the wand and stars
> > > icon.
> > >
> > > To avoid deducting the same amount twice, add a Yes/No field to the Invoice
> > > table called something like InventoryAdjusted. Then change your command
> > > button's OnClick event procedure:
> > >
> > > ' Declarations here
> > >
> > > If Not Me![InventoryAdjusted] Then
> > > ' Wizard created code to execute the query
> > > Me![InventoryAdjusted] = True ' Set to false so it won't be run again
> > > End If
> > >
> > > ' Error-handling here
> > > "DistrautMan" wrote:
> > >
> > > Hope that helps.
> > > Sprinks
> > >
> > > > when i say saving i mean that aside from the printed hard copy for storage in
> > > > a cabinet or something i need another hard copy of each and every invoice i
> > > > make that i can save into a file called "customer's invoices". i want copies
> > > > of each one i make so that if ever needed we can go back on the computer and
> > > > pull one up to check that person's phone number and what they've ordered. and
> > > > btw i cant figure out the update query, i dont know how to make one because
> > > > ive been using access for like a week or two.
> > > >
> > > > "Sprinks" wrote:
> > > >
> > > > > Hi, DistrautMan.
> > > > >
> > > > > I'm not sure what you mean by "saving" it. If your form is bound to
> > > > > table(s), it already is saved. You can pull that record up at any time to
> > > > > look at the information or edit it if necessary. A hard copy can be printed
> > > > > at any time by printing a report that is based on a query that filters your
> > > > > recordset by the currently displayed record.
> > > > >
> > > > > As far as deducting quantity purchased from available inventory, I'm not an
> > > > > inventory expert, but there have been plenty of threads on this topic here
> > > > > that you can search. Basically, I believe you would run an update query on
> > > > > your inventory table.
> > > > >
> > > > > Hope that helps.
> > > > > Sprinks
> > > > >
> > > > > "DistrautMan" wrote:
> > > > >
> > > > > > ok well first how can i put my form fields like drop boxes on to a report if
> > > > > > i cant edit things on a report and second, each invoice contains, customer
> > > > > > name and number, and what they buy, i need to be able to save that so that
> > > > > > at anytime i can pull that information up and see what they bought. right
> > > > > > now i have a perfectly fine template in word but i need to make one in access
> > > > > > so that one, it tallies up totals automatically and i did that, and two it
> > > > > > automatically subtracts from the in stock value from my table when i put
> > > > > > something in the quantity text box for the product, and i havent figured that
> > > > > > out. but now ive run into the problem about being able to save it so it
> > > > > > looks like it does when i print it with all the information about the sale.
> > > > > >
> > > > > > "Sprinks" wrote:
> > > > > >
> > > > > > > DistrautMan,
> > > > > > >
> > > > > > > I forgot to mention that since you can recreate the report, or look at the
> > > > > > > record on a form at any time, why would you need to store a copy of it?
> > > > > > >
> > > > > > > Sprinks
> > > > > > >
> > > > > > > "Sprinks" wrote:
> > > > > > >
> > > > > > > > Hi, DistrautMan.
> > > > > > > >
> > > > > > > > I suggest you recreate the form's look in a report, and use a command button
> > > > > > > > on your form to output it in rich text format to a document. You can set the
> > > > > > > > filename in your command button procedure, or ask for a filename to be input
> > > > > > > > using the InputBox function.
> > > > > > > >
> > > > > > > > Base your report on a query that contains all necessary fields, and has
> > > > > > > > selection criteria referencing the currently displayed record:
> > > > > > > >
> > > > > > > > =Forms!YourFormName!YourPrimaryKey
> > > > > > > >
> > > > > > > > Dim stDocName As String
> > > > > > > > Dim stFilename As String
> > > > > > > >
> > > > > > > > stDocName = "YourReportName"
> > > > > > > > stFilename = "YourFileName" ' or use InputBox to get a filename
> > > > > > > > DoCmd.OutputTo acReport, stDocName, acFormatRTF, stFilename
> > > > > > > >
> > > > > > > > See VBA Help on the OutputTo method for further details.
> > > > > > > >
> > > > > > > > Hope that helps.
> > > > > > > > Sprinks
> > > > > > > >
> > > > > > > > "DistrautMan" wrote:
> > > > > > > >
> > > > > > > > > ok i have a form that i use as an invoice at work. i need to be able to
> > > > > > > > > export it out of access in a seperate folder called customer invoices. two
> > > > > > > > > problems: one it doesnt keep the layout format i have, it trys to convert it
> > > > > > > > > and it looks completely different, so i need to know how to keep it still
> > > > > > > > > looking like its a print preview, and two: how do you save it so it keeps the
> > > > > > > > > values i entered in the fields, such as text boxes and drop down menus. thanx
.

Loading