Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    3

    Unanswered: How to display unbound textbox in subform?

    Hello and Thank You in Advance for any help!

    I am using Access 2003.

    Local database only with one user.

    The tables at issue include tblMerchandise (MerchandiseID is PK), tblInventory (InventoryID is PK), tblPurchases (PurchaseID is PK). I also have tblSubMerchandise (MerchandiseDetailID is PK), tblPurchaseDetail (PurchaseDetailID is PK).

    The basic transaction I need to capture on the form involves a piece of Merchandise that is made from several inventory items. I need to display the total cost. The total cost includes the average cost of each of the inventory items added together plus the time spent times a fixed hourly rate.

    I have tried using a form & sub-form setup with the main form based on the Merchandise table and the subform (datasheet form) based on a query which includes the calculated expression for the average cost, a combobox for selecting the inventory item, and a quantity for each inventory item used and then a total of the avg cost x quantity products in the footer of the datasheet.

    The visible problem is with the quantity textbox having to be bound in the query. I cannot enter a value. I want it to be unbound, but then how do I get the selection as an available field for the subform? The quantity value is being stored in tblSubMerchandise.

    I hope this is clear. Thank you for your time.

  2. #2
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking

    Greets;

    The way that i would do this would be to create a form and bind that form to the query or table that hold all the data i wish to run my calcualtions on.

    (all this btw works just as efficiently in reports)

    once all the fields are dragged and dropped into my form (you can turn off thier visability if you dont want any visable)

    Create your unbound text box, and where it says unbound, or data source in properties for the text box, type the forumla you wish to run.

    eg =([total 1]+[total 2]+[total 3])*([Hourly Rate]/[hours])

    Thats just an example, to show you how to structure your formula correctly. If the formula needs to do multiple calculations, they need to be bracketed seperately, and if these multiple calculations are to be added/mulitplied/divided/subtracted form each other, these go outside the brackets.

    Hope this helps,

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  3. #3
    Join Date
    May 2006
    Posts
    3

    Question Thoughts on SQL statement (vs. Table) I used as bases for subform?

    Thank you for your insight Dan. I am a complete newb, helping out my Wife. My specific problem is that when I put Quantity from tblSubMerchandise as the Control Source for the text box called "txtQuantity" that initial value is then set as the control source for each of the inventory items. The same result if it is included in the query. How can I enter the data through the form interface into the table.

    This is the SQL statement from the query and subquery I tried to base the subform on.

    SELECT tblSubMerchandise.MerchandiseDetailID, qryAvgPrice.AvgOfUnitCost, qryAvgPrice.InventoryID
    FROM tblMerchandise INNER JOIN (tblSubMerchandise INNER JOIN qryAvgPrice ON tblSubMerchandise.InventoryID = qryAvgPrice.InventoryID) ON tblMerchandise.MerchandiseID = tblSubMerchandise.MerchandiseID
    GROUP BY tblSubMerchandise.MerchandiseDetailID, qryAvgPrice.AvgOfUnitCost, qryAvgPrice.InventoryID;

    The SQL from the qrAvgPrice is:

    SELECT tblInventory.InventoryID, Avg(tblInventory.UnitCost) AS AvgOfUnitCost
    FROM tblInventory
    GROUP BY tblInventory.InventoryID;

  4. #4
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    Just about to dash off to work, So, I'll have to be prompt.

    Your form really needs to run form the table that the form is designed for. Eg if inputting merchandise, the best thing would be to bind that form to the table merchandise and set it up around that.

    Often when creating a form for queries, its more to view data than to edit it or amend it, it can be done if there is only one table involved in the query, but when more queries/subqueries or multiple tables are used, it creates a conflict.

    for calculations, the text box is best left unbound. Without a control source. You can then run the calculation on both the reports (for invoices and statements) as well as the form. The calculations is not stored itself, however, the components are Eg cost and hourly rate.

    Feel free to post your db (as a zip) and ill take a look at it and see if i can come up with somehting for you.

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

  5. #5
    Join Date
    May 2006
    Posts
    3

    Cheers Mate!

    Dan-

    I really want to thank you for your time on this. If you ever find yourself in Arizona in the States, pints are on me. Cheers! I have attached a zip file of the main components of the Database. I hope I have included all that you may need.
    Last edited by ElusiveQuark; 05-10-06 at 13:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •