From a Form....control/change a specific field for all records. 1

[puforee]

I am using a form to show information from a table. For certain fields on the form, my users may want to keep the current data or remove it. I have added check boxes below the elegible fields for change. If the box is checked, code will remove the selected data from the underlying table after the form is closed.

Now, I want to give the users a choice to check all the boxes for a given field at the same time....for all the records. So, I thought about putting a command button in the form header and when clicked, it would cause the cooresponding check boxes in all records to be set.

Can someone help me with the code for this please?

 

[MajP]

1. Is this a continous of single form view?
2. Is the checkbox bound to a field?

 

[puforee]

This form is set to continous. Yes, the check boxes are bound to a field in the underlining table.

Thanks,

 

[MajP]

so you can then run an update query

dim strSql as string
strSql = "UPDATE table_name SET someField = True..."
currentdb.execute strSql
me.requery

 

[puforee]

OK...I will give it a try. Thanks...I will let you know the results.

 

[puforee]

OK...I am sure I did not do this exactly correct because I got an error:
Code: Dim strSql As String
strSql = "UPDATE Dup Temp Major ATA Table Create SET ReMoveCmtsBox = True"
Error pointer here CurrentDb.Execute strSql
Me.Requery

Do I need to add quotes around table name or ????? Also, I have the check box set to yes no. Should I use yes or maybe -1?

 

[Andrzejek]

What's your table's name? Is it "Dup Temp Major ATA Table Create"?
What's your check box's name?

Have fun.

---- Andy

 

[MajP]

Any tables or fields with spaces need brackets,
"UPDATE [Dup Temp Major ATA Table Create] SET ReMoveCmtsBox = True"
You should be able to use -1 or true

Is the Create part of the table name or was that leftover from a make table query?

 

[puforee]

OK....I created a regular query to do the update and then stole the SQL and got this.

"UPDATE [Dup Temp Major ATA Table Create] SET [Dup Temp Major ATA Table Create].[Remove Time] = -1;"

This worked perfectly. Thanks for putting me on the correct path.

 

[MajP]

To do yourself a big favor and save a lot of headaches, use underscores or camelback for spaces

tableDupTempMajorATA
table_Dup_Temp_Major_ATA
I would prefix with "table" not suffic, if you are going to use that convention.

 

[puforee]

Good point. I left the table on the front because I was duplicating a table to prepare to change and create new information. Eventually the new information will be appended to the original table. Sort of a Make From thingy for my users. Saves them a lot of time when trying to create ALMOST duplicate information.

And, yes, I have a bad habit of getting wory with my names and not using Underscores...By the whay, what is a camelback?

 

[Andrzejek]

Also, I don't think you need to repeat the table name in your statement:

"UPDATE [Dup Temp Major ATA Table Create] SET [Dup Temp Major ATA Table Create].[Remove Time] = -1;"

Which makes short and sweet:

"UPDATE [Dup Temp Major ATA Table Create] SET [Remove Time] = -1;"


Have fun.

---- Andy

 

[MajP]

sorry typo. Should be "camelCase"

http://en.wikipedia.org/wiki/CamelCase.

But for anything in access do not leave a space, it will bite you in both Sql and vba code.

 

[Andrzejek]

I would add to MajP's suggestion: do not use reserved words in Access as table or field's names, like Table, Name, Date, Time, Case, etc.

Have fun.

---- Andy