Loop through subform and update 2 tables access 2007 vba

[Closed]
Report
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
Hello,

am pretty new to access and stuck on my first project...any help will be appreciated

i got a subform that displays filtered data from a combo box. i need

1) to loop through the filtered records in the subform,
2) check if the combobox value, and 3 values from the 1st record in the subform exists in a table
3) if it does not exist, it needs to add all records from a master table to 2 other tables alongwith the data from the subform.

i can send the file to make the above more understandable

thanks alot...


6 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
Hi smd_excel,

I read your VBA programming requirements. I see that you need help on creating a VBA application and use ComboBoxes to filter data. Your idea of sending the sample is great. I would appreciate if you can add a screenshot of the front end VBA application and also of MS Access Database structure.

Please do write back to us.
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

unable to uploade files thru the comment box...do u have an email id..thanks alot
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
Hi smd_excel,

Try uploading your files/pictures to this site and give me the URL for the uploaded files:

www.speedyshare.com

Note: Do not upload any files with any kind of personal information.

Please revert for clarification.
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

http://speedy.sh/7Z8fP/QTMS-Rev9.zip
http://speedy.sh/raR3u/QTMS-Reports.xlsm

i've kinda done most of it with someone else's help.
and need help with aggregating data from a table that i will export into excel and link that to charts.
below is the requirement
there's a table tblReport1
By contractid, by platformid, by level id, by disciplineid and by metricid it should sum up origplanqty, revisedqtydaily,forecastdaily, actualdaily using the dailyplandate by week.
the weekly cutoff is every wednesday e.g. 7Nov12, 14Nov12,21Nov12 etc
i will export this data to the excel file (refer the link) and it will feed the 3 charts ToGo, SCurve & Barchart.
am not sure if the chart can be done in access with the various filters but i've come up with this way.
appreciate the help
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

hi, i was able to do the above through query and am stuck with another problem
http://speedy.sh/G6fSU/Book3.xlsx

. refer link. i copied to the data from access table to excel
i need a running total
it should sum up the SumOfOrigPlanQtyDaily for every change in Test column in a new column e.g.
SumOfOrigPlanQtyDaily Test Runningtotal
100 5773700099030310 100
0 5773700099030310 100
100 5773700099030310 200
100 5773700099030310 300
100 5773700099030310 400
0 5773700099030310 400
0 5773700099030310 400
12 577370009903011 12
84 577370009903011 96

thanks,
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
Hi smd_excel,

Thank you for writing to us. I have seen the files that you have uploaded. As most of the work is done and you need only help with Running Total, I will provide you the steps on how to do the same:

Insert the below mentioned formula in C2:

=SUM($A$2:A2)

Replace $A$2 with first cell following SumOfOrigPlanQtyDaily and use excel fill handle to calculate the running total for rest of the fields. Your data should now look like below:

SumOfOrigPlanQtyDaily Test Runningtotal
100 5773700099030310.00 100.00
0 5773700099030310.00 100.00
100 5773700099030310.00 200.00
100 5773700099030310.00 300.00
100 5773700099030310.00 400.00
0 5773700099030310.00 400.00
0 5773700099030310.00 400.00
12 577370009903011.00 412.00
84 577370009903011.00 496.00

Please revert for clarification.