Update Table Based on Textbox Values From - To

[Ihussein]

Dears,
The idea is that I'm trying to update all the records of the table based on From - To numbers, CmdUpdate on Click Event to update the table, please note that I need to filter for certain location before updating, and previous month data to be kept so where in another table for future reference, I have 5 different locations that r to be updated frequently on monthly basis, the amount of data are about 300,000 records. Attached image 4 more clarification. Thanks

[zmbd]

My advise is to make a copy of your database before doing any of the following.

using the copy, you can transfer this method to working file after you've refined the method.
1) Create a simple select query that will return a set of records that match those that you will be updating. This will ensure that only the intended records will be affected in the update step.

2) Create a parameter based select query out of the query developed in step 1 - in the above link there are several options. Personally I would use the form method. Use a control for your "from_id", one for "to_id", and a third for your update value... this will be used once your select query is returning the correct records.

3) Once your select query is returning the correct record set. Change it to an update query, insert the third control name in to the query as done with the first two.

+ Your image in dropbox is not showing and I am currently on an unblocked PC.
Please use the advanced posting option to upload the image to the post.

[Ihussein]

Thanks for the support, but steps 2 & 3 are a bit confusing me. I'm attaching sample Database, the cmdUpdate is to update both ReceivedDate and MothOfIssuance columns under tblMontlhlyData, hope I'm not make confusion or mixing up the thinks. Regards

[zmbd]

Oh, thank you for the database; however, I am currently working on a restricted network - thus, unable to d/l files for now.

I'm sorry, I reviewed the link for the tutorial and they are using the silly macros instead of VBA. The link used to lead to a fairly straight forward tutorial - but MS has "updated" things again :)

However, the basic concepts to place the form name and control name in the query for the WHERE criteria and then again for the update value still hold true.

I see that you are using an on_click event in a command button to trigger the task, so I'll work with things from there; however, before we go too much farther how much experience do you have with Access and VB.

Once I have that information I can work up an example for you that you should then be able to take the concepts and apply them to your situation.

-z

[Ihussein]

Okay no worries, I'm good in access and VB, u can go ahead and start working up the example. One question if u don't mind, what do u mean by silly macros instead of VB? as I'm using macros frequently in in my database.
Once again thanks for the support

[zmbd]

Ok,
Because that last link has changed so much from the simplistic methodology. Attached is an example database that walks thru the steps I mentioned in post#2 and the tutorial.

I've tried to keep it simple; however, there is one form that has some bells-n-whistles. Keep in mind that I've bodged this out in a short time frame so it's not totally polished, my intent here to provide some examples.


++ as for macros... they are good for what they can do and in very specific situations such as for the autoexec, autokeys, navigation-control-form-tiered-controls-with-split database, and sharepoint/webapps are the only way to go; however, you'll find that VBA is generally a much more robust environment for application development.

+ Just for clarity, although VB and VBA are cousins, there are some differences in how they handle different situations; however, that's another thread topic altogether! :)
++ and related, in Access the Macro language is a different beast from VBA. Although in Excel, Macro and VBA are used interchangeable - leading to a lot of confusion when one moves from Excel/Word to Access.


BOL

[Ihussein]

Much appreciated, I got the idea but still need to do few changes to adapted to my database, regarding the VB I will further explore the topic, I know most of the basics and very familiar with it but not really detailed one.
Thanks