Full Version: Simple doubt about subform
UtterAccess Forums > Microsoft® Access > Access Forms
yahya263
Hi
I am very new to access. Therefore my doubt may appear silly. I have a data base where I use a form to update the data in the table. I use a combo box to move between the records. But I cannot add any new records in this case. Is there any way that I can update the existing records as well as add new records once I use a combo box?
Secondly, now I am using a sub form to add new record in the main form. When I click on any record in the main form through the combo box, I could see two records in the sub form. I think the second record appears by default. Is there any way to make some of the fields (which needs updating) in this default record as blank?
strive4peace
Hi Vahya,
ost likely the reason you cannot add records is because your form is based on more than one tabl;e and there is a parent/child relationship -- you cannot create a parent and a child at the same time, the parent must come first wink.gif
"now I am using a sub form to add new record in the main form"
no, that is the wrong approach. the main form should be based on just one table and if you want records in a related table, then a subform can be used.
Some rather basic use of relationships -- Mainform/Subform
http://www.utteraccess.com/forums/showflat...;Number=1551226
for more information on the basics of Access, read this:
Access Basics
http://www.utteraccess.com/forums/showflat...;Number=1220772
30-page Word document on that covers essentials in Access
yahya263
Hi Crystal,
Thank you for the reply.Please note that my form is based on just one table only. Also I can update all records which is already available in the table by scrolling it with the combo box (used foe the ID field). But my issue is when I want to add a new record which is not available in the table.
Therefore, I am using the sub form to update the records in the table which in turn will update the form also
strive4peace
Hi Vahya,
heck properties on your main form -- especially this one:
AllowAdditions
there must be something different between the subform and the main form ...
yahya263
Sorry Crystal,
could not find the AllowAdditions anywhere in properties. I could see only AllowAutocorrect.
strive4peace
read the Access Basics document wink.gif ... you will find the answe in the section on Properties and Methods ... AllowAdditions is a Form property
yahya263
Thank you Crystal
Ofound it in Form properties. Allow Additions is "yes' there. I think my question is not clear for you.I am attaching a sample file so that you can help me.
In the attached db, I am using the pur form to update the purchase details in the table. I have a combo box for the field product ID (Say in the range 6001 to 6500 now). If I want to update any Pur/Sales details of any products in the range 6001 to 6500, I can do it by just navigating through the combo box in the form. However, if I want to add a new product ID say 6501 and its details, I can't do that since there is a combo box which is "bound" (is it the correct term?) to the existing table.
So the method I am using is to update the table through a sub form. Hope it is clear now.
Secondly since I may have more instances of buying and selling, can u suggest a better way to add the data instead of using buy1, buy2,........sell1,sell2........etc each time?
adnanmohd
according to me there should be three tables.
roduct (id, name)
Pur (purID,productID (FK), date, qty, rate)
Sale (saleID,productID (FK), date, qty, rate)
this will help you add as many sale or purchase as you need for any product.
Regards,
Mohammad
ace
You can't add a new record using the Pur form as it is now
because there is no control on the form that is bound to
ProductID. Since ProductID is your primary key and because it
is not an autonumber field you need to be able to enter a value
for it.

The combo box is not an issue in that problem. The combo box
is just a method of navigating the records. It has a record source,
but it isn't bound to a field.

Bottom line is that you need to add a control to your form that
is bound to ProductID if you want to be able to add new records.




Edited by: ace on Sun Feb 10 15:49:32 EST 2008.
yahya263
Thank you all
Mr Ace, Can you tell me how to add a control so that I can add new records?
Mr Mohammed, How can we link the three tables mentioned in your solution? Can you please do it for me?
Sorry guys, I am really new to Access.
Regards
Yahya
ace
Can you tell me how to add a control so that I can add new records?
The easiest way is to drag the productID field down from the fields list
on the menu bar onto the form while in design view.
Mohammed is right about about your table structure though. It really
is not correct like it is.
yahya263
Thank you Mr. Ace, It was a simple but effective solution. Now I can use the combo box for adding details of existing records and the product ID field for creating new records.
How I need your help for the second part, that is for the table structure. I may have a lot of instances of buying and selling. I don't want to add one field in the table each time. (Like Buy1 Date, Buy2 Rate, ..........Sell12, Sell2 etc...........)
strive4peace
you need a transactions table and the repeating fields need to come out of the main table
ransactions
- TranID, autonumber
- ProductID, Long, FK to Main Table (which should be called Products)
- TranTypeID, Long, FK to TranTypes
- TranDate, Date
- Qty, number (Integer or Long if it will be whole)
- Rate, currency (I would be inclined to call this Amount)
TranTypes
- TranTypeID, autonumber
- TranType, text

records would be, for instance:
1, "Purchase"
2, "Sale"

If you would kindly read the Access Basics document, you would learn valuable information -- it also covers normalizing your data.
yahya263
Hi
I have attached a wrong file with the last post. Please find the right one now.
yahya263
The issue is when I try to create a relationship between the "TranTypeID" in the Transactions Table with that of the TranType table, it says "no unique index found for the referenced field of the primary table" What went wrong?
strive4peace
TranTypeID needs to be designated as the Primary Key of the TranType table
on't use spaces in field names
ProductID
ProductName
TranDate
yahya263
Thank you. It was great and working fine.I have entered all the data. Now I want to have the following details
.Total quantity of each product purchased.
2.Total qty sold of each product .
3.Qty remaining for each product.
4.Average buy price of each product.
5.Average sale price of each product.
6.Total investment.
7.Individual and total Profit/Loss and its %
8.Total buy price of remaining products
Which is the simple and effective way of getting these results?
Thank you once again
Yahya
strive4peace
Hi Yahya,

please post your current database and, for each of the calculations you want, indicate where the source information comes from -- tablename(s) and field name(s)
yahya263
Hi
The database is attached. All the results should come from the Products table or Transactions query (Converted to transactions form now).
Regards
Yahya
strive4peace
Hi Yahya,

you will have to specify the fieldnames for each calculation. Your products table only has ProductID and ProductName so any numeric calculations will need to be from fields in other tables.

We do not mind helping you, but you need to make everything perfectly clear.
yahya263
Hi,
Sorry that I could not explain it to you properly.
I have mentioned ProductTable because when I click the plus sign there,I get all the transactions related to that particular product ID. All the calculations are based on these transactions. For Eg:
1. Total qty of each product purchased is the sum of quantities of TranTypeID "1" for the particular ProductID. (I don't know whether we have to use Transactions table or Trans query)
Is it correct if I say we have to use the following fields from the transaction table for the calculations?
ProductID
TranType (or TranTypeID)
Qty
Rate
Regards
Yahya
strive4peace
Hi Yahya (what is your name?)

It is not a matter of not explaining properly, it is a matter of HELPING you, not doing it for you. You mentioned these calculations:

1.Total quantity of each product purchased.
2.Total qty sold of each product .
3.Qty remaining for each product.
4.Average buy price of each product.
5.Average sale price of each product.
6.Total investment.
7.Individual and total Profit/Loss and its %
8.Total buy price of remaining products

please annotate this list with the tablename.fieldname for each calculation and we will help with the equations. Thank you
yahya263
Hi Crystal,
My Name is Yahya (It is the Islamic name of John the Baptist).
I hope the below given information is correct.
1.Total quantity of each product purchased :Transactions.TranTypeID,Qty
2.Total qty sold of each product : Transactions.TranTypeID,Qty
3.Qty remaining for each product:Transactions.TranTypeID,Qty
4.Average buy price of each product:Transactions.TranTypeID,Qty,Rate
5.Average sale price of each product:Transactions.TranTypeID,Qty,Rate
6.Total investment:Transactions.TranTypeID,Qty,Rate
7.Individual and total Profit/Loss and its %:Transactions.TranTypeID,Qty,Rate
8.Total buy price of remaining products:Transactions.TranTypeID,Qty,Rate
Regards
Yahya
strive4peace
Hi Yahya,
Here are SQL statements to get you on your way

CODE
1.Total quantity of each product purchased :Transactions.TranTypeID=1

SELECT Products.[Product Name]
    , Sum(Transactions.Qty) AS SumOfQty
FROM Products
    INNER JOIN Transactions
        ON Products.ProductID = Transactions.ProductID
WHERE (Transactions.TranTypeID=1)
GROUP BY Products.[Product Name];
2.Total qty sold of each product : Transactions.TranTypeID=2
SELECT Products.[Product Name]
    , Sum(Transactions.Qty) AS SumOfQty
FROM Products
    INNER JOIN Transactions
        ON Products.ProductID = Transactions.ProductID
WHERE (Transactions.TranTypeID=2)
GROUP BY Products.[Product Name];
3.Qty remaining for each product:Transactions.TranTypeID
SELECT Products.[Product Name]
    , Sum(IIf([TranTypeID]=1,[Qty],0)) AS QtyPurch
    , Sum(IIf([TranTypeID]=2,[Qty],0)) AS QtySold
    , Sum(IIf([TranTypeID]=1,[Qty],0))-Sum(IIf([TranTypeID]=2,[Qty],0)) AS QtyLeft
FROM Products
    INNER JOIN Transactions
        ON Products.ProductID = Transactions.ProductID
GROUP BY Products.[Product Name];

Has it is important to learn yourself, we do not want to do your assignment for you but, hopefully, help you so that you can do it on your own.
Try the rest of the queries yourself. If you have questions, ask -- but please be specific, do not give us your assignment.

If you do not know what to do with the SQL:
1. make a new query
2. do not add any tables
3. switch to the SQL view
from the menu --> View, SQL
paste the SQL statement
then, look at the design view of the query as well as the results (datasheet view)
yahya263
Hi Crystal,
You are tough ! ( Resembles the Maths mam of my school days. Always she solve the simple problems in class and keeps the tougher ones for HW!)
Thank you very much for your help. As I mentioned earlier I am very new to Access and don't know much about SQL. (Do you have some study materials?)
I have done all other questions by myself and the db is attached for your review and advise, though I am not fully happy with what I have done.
To be specific (?!)
For Item 6, I could not find any method to get the results in a query. Therefore, I have made it in the Report. Same for Item 7(ii) and 8.
Regards
Yahya
strive4peace
Hi Yahya,
6 is simply #1 with the Product Name column removed -- so it would just have one column that shows on the grid, the sum of Quantity.
#7 -- data is already on #3 except percent. what kind of percent is needed?
#8 -- what is this question asking?
yahya263
Hi Crystal,
6 is now clear to me. Thanks
#7- Is the data available in #3 enough? There we have only the quantities.We need the rates also to be included so that we will get the profit/loss for each product. I have already done it as query 7a. Is it ok? The second part is the total percentage of profit or loss, which is done as "query7b" in the reports. Now I think I can have it by removing all other columns of query 7a, as you mentioned for #6
#8- Similar to #7, The I have done the first part (Pur price of each of the remaining products) in Query #8a and its total in report "query8b". Any suggestions?
Finally, where can I get some study materials for basic SQL?
Regards
Yahya
strive4peace
Hi Yahya,
This document covers basics that you should know:

Access Basics -- updated!
http://www.utteraccess.com/forums/showflat...;Number=1595005
8-part free tutorial that covers essentials in Access

there are links in this document to other valuable resources on the Internet as well as books you can read. A good book for SQL would be "SQL Queries for Mere Mortals" by John Viescas.
yahya263
Thank you Crystal
Regards
Yahya
strive4peace
you're welcome, Yahya wink.gif good luck with your class!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.