please help

[plans01275]

Hi everyone i have never used macros or VBA before so i dont know what i am doing sorry to be a pain, all i want to do is change all records in a field from a table . i can get the macro to open the table and select the field but can't get the macro find the word yes and replace with the word no.
Can any one tell step by step what to do i have looked on the net for help and finding very hard to understand what i need to do

many thanks

Pete

 

[madEG]

you can make a query that will help...

update your_table_name set column1 = 'Yes' where column1='No';

just create new query, and choose design view, then click ok, and just close the box... right click on the window and choose sql view... and just manually type in the SQL.

You can do it!

-Matt G.

 

[plans01275]

hi matt my knowledge is limited i can bulid tables, forms and reports but everything else is new to me ihave made a query to find records to go on a report form and that took me 3 days of reading and re doing the query
the stage im at is this i enter details in the form, after i have entered details i have a button which sets the query to find records which is a field in a table that has the word "yes" then all records are displayed in the report form, then i print out the records, Once printing is complete i want the word in the field to be change from "yes" to the word "no" can tell me step by step how to do this with a macro, VBA

Many thanks

Pete

 

[Adam Caramon]

Let me see if I understand this.

1. You have a table with various fields. One of these fields is a Yes/No field.
2. You have a query in which the criteria on your Yes/No field is set to "Yes" (i.e. the query is showing you all of the records that have a Yes value).
3. You have a form based on a table. Once you add records to this form you are clicking a button someone else created on this form which launches a report. The report that is launched is based off the query so that only Yes records are showing on the report.
4. You want to expand the functionality of the button so that when you click it, all of the normal actions are executed and in addition you want to button to modify all records that were in the previous query by changing their Yes/No field value from Yes to No.

Ok, assuming I have everything above correct, I have a possible solution for you, albeit it is not a VBA solution.

Here's what you can do:

1. Copy the query that currently filters for all records with the 'Yes' value in the Yes/No field.
2. Paste the query and name it 'qryChangeYestoNo'
3. Open qryChangeYestoNo in design view.
4. Click on the drop-down arrow next to the 'Query Type' button (the icon looks like cascaded database windows initially).
5. Select 'Update Query'.
6. In the YesNo field the criteria should currently say 'Yes'. Do not change it. In the Update to: field type in 'No' (leave the apostrophes out of course).
7. Save the query
8. Close the query.
9. Click on Forms.
10. Open the form you use in design view.
11. Click the Command Button icon to add a new button to your form.
12. Click on the form somewhere to add the new button.
13. The command button wizard will pop up. Click 'Miscellanous' under the title Categories.
14. Click 'Run Query' under the Actions heading.
15. Click the next button.
16. Click 'qryChangeYestoNo'.
17. Click the next button.
18. Click the 'Text' radio button.
19. In the text box next to the Text radio button type in 'Change Yes to No'.
20. Click the next button.
21. Name the command button btnChangeYestoNo.
22. Click the finish button.
23. Click the view icon so that you enter Form View.

Ok, those steps have successfully added the button that will update the data in your table. Now, after you add new records through the form, then click the 'Print Report' button (previously created) you should print out your report as you used to.

After that, click on the 'Change Yes to No' button. You will get a few Windows prompts telling you that you are about to update data, are you sure you want to, and then it will even tell you how many records will be affected. Click 'Yes' through the prompts.

Once you are through the prompts all records in your table that had a Yes value will be changes to a No value.

Words of caution: This is a very jury-rigged way of accomplishing what it is I think you are trying to do. There are definitely more efficient ways of doing this, however without spending more time and being more aware of your specific database I cannot explain those ways.

Never, ever do any experimental programing on your live database (including the instructions I gave you above). Always make a copy of your database and play around in the copy before making any changes to your live database. I cannot stress this enough.

Hope this helps some.

Adam

 

[plans01275]

Hi Adam

I have done what you sugested ... it works
thank you very much

Pete

 

[WIS]

Hi everyone i have never used macros or VBA before so i dont know what i am doing sorry to be a pain, all i want to do is change all records in a field from a table . i can get the macro to open the table and select the field but can't get the macro find the word yes and replace with the word no.
Can any one tell step by step what to do i have looked on the net for help and finding very hard to understand what i need to do

many thanks

Pete

Sorry just realised you've got the answer.