loop through subform and update 2 tables access 2007 vba Closed

Question

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...


Configuration: Windows XP / Internet Explorer 8.0

Zohaib R · Answer

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.

smd_excel · Answer

unable to uploade files thru the comment box...do u have an email id..thanks alot

Zohaib R · Answer

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.

smd_excel · Answer

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

smd_excel · Answer

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,

Zohaib R · Answer

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.

Loop through subform and update 2 tables access 2007 vba

6 responses

Similar discussions

Subscribe To Our Newsletter!