I used to know how to do this so it’s doubly frustrating for me. I have a small database with 2 data tables, Purchase Log and Transaction Details.
Purchase Log has the following structure:
Transaction # - this is a text field (and my key field) because it will be assigned elsewhere – I’m just doing this dbase as an audit tool for me and need to include the pre-assigned trans # here.
Date
Vendor (text field)
Description (text field, a short description of order, e.g., office supplies, test equipment, etc.)
Requestor – (text field, name of person requesting the order be placed)
Requestor code (text field, organizational code)
Requestor phone (text field, phone number, uses input mask)
Transaction Details has the following structure:
Index field – key field, just here to act as the key field
Transaction # - foreign key; relates to Transaction # in Purchase Log table
Item P/N
Item Description
Qty
Cost/ea
Date Rec’d
Date Returned
Now, I have the report working fine – it is built from a query based on both tables and totals each order nicely (I have some bogus data in there for testing purposes).
My problem is with the form, for when I’m actually inputting the order data.
It’s a main form and subform. Main form has all the fields from Purchase Log Table. Subform has Item P/N, Item Description, Qty, Cost/ea, LineTotal (calculated field – [Qty]*[Cost/ea]), Date Recd, Date Retd
On the main form, I want a calculated control that gives me the entire order total. In other words, sum the Line Totals. I keep getting an error!
I couldn’t use a query for the form because it would not break it into form and subform; I could only get it to do that by using the wizard and building the form from the tables. If someone has a clue as to why that is, I’d love help with that, as well! It worked fine last week when I first built it but I moved a few fields from one table to another and it hasn’t worked since I built the new query afterward and then tried to build the new form from the new query.
But back to my calculated field on main form problem: I’ve tried summing the LineTotal on the loaded form – didn’t work. I tried summing the LineTotal by using the query my report was built from – that didn’t work. Out of sheer desperation, I even tried using the OrderTotal from my report (which is built from a query) - that didn't work either.
Please, what am I doing wrong?!
I’ve been referring to my Access 2000 Bible but I’ve lost my familiarity with it since it’s been a long time since I’ve used any of this. The problem is I need this relatively quickly (unless I want to do all my tracking in Excel and then transfer everything when I finally get the dbase working later, which I can do, I guess, but I’d rather save myself that effort.) BTW, I’m not getting paid for doing this dbase; I’m just trying to make my job easier for ME. I didn’t ask for this collateral duty but I’ve got it now and I want to simplify it as much as possible so it doesn’t take too much time away from my real job.
Thanks a bunch!
Purchase Log has the following structure:
Transaction # - this is a text field (and my key field) because it will be assigned elsewhere – I’m just doing this dbase as an audit tool for me and need to include the pre-assigned trans # here.
Date
Vendor (text field)
Description (text field, a short description of order, e.g., office supplies, test equipment, etc.)
Requestor – (text field, name of person requesting the order be placed)
Requestor code (text field, organizational code)
Requestor phone (text field, phone number, uses input mask)
Transaction Details has the following structure:
Index field – key field, just here to act as the key field
Transaction # - foreign key; relates to Transaction # in Purchase Log table
Item P/N
Item Description
Qty
Cost/ea
Date Rec’d
Date Returned
Now, I have the report working fine – it is built from a query based on both tables and totals each order nicely (I have some bogus data in there for testing purposes).
My problem is with the form, for when I’m actually inputting the order data.
It’s a main form and subform. Main form has all the fields from Purchase Log Table. Subform has Item P/N, Item Description, Qty, Cost/ea, LineTotal (calculated field – [Qty]*[Cost/ea]), Date Recd, Date Retd
On the main form, I want a calculated control that gives me the entire order total. In other words, sum the Line Totals. I keep getting an error!
I couldn’t use a query for the form because it would not break it into form and subform; I could only get it to do that by using the wizard and building the form from the tables. If someone has a clue as to why that is, I’d love help with that, as well! It worked fine last week when I first built it but I moved a few fields from one table to another and it hasn’t worked since I built the new query afterward and then tried to build the new form from the new query.
But back to my calculated field on main form problem: I’ve tried summing the LineTotal on the loaded form – didn’t work. I tried summing the LineTotal by using the query my report was built from – that didn’t work. Out of sheer desperation, I even tried using the OrderTotal from my report (which is built from a query) - that didn't work either.
Please, what am I doing wrong?!
I’ve been referring to my Access 2000 Bible but I’ve lost my familiarity with it since it’s been a long time since I’ve used any of this. The problem is I need this relatively quickly (unless I want to do all my tracking in Excel and then transfer everything when I finally get the dbase working later, which I can do, I guess, but I’d rather save myself that effort.) BTW, I’m not getting paid for doing this dbase; I’m just trying to make my job easier for ME. I didn’t ask for this collateral duty but I’ve got it now and I want to simplify it as much as possible so it doesn’t take too much time away from my real job.
Thanks a bunch!
