Update records with a checkbox

[mkashif83]

Hi,

I have a main form (AccountInfo) with subform (ReviewInfo) in it. Subform contains multiple reviews done on the Account# showing in main form. I am trying to make only one of the reviews as ACTIVE review. I have a checkbox field in my table from to which the ReviewInfo subform is bound.
What I want to do is that when a user adds a new Review to an existing account, user will check "ACTIVE" checkbox, and I would like for it to remove the Active checkbox from the previous reviews.

For example, account # 1234 has 5 different reviews. A user adds Review#6, and selects ACTIVE checkbox, I want the check boxes from previous 5 reviews to be removed so that only review # 6 is active.

Can anyone please help?

thanks,

 

[JamesMcS]

Not sure, but how about an update query in the on click event of the check box? Using docmd.runsql, you can just make it update all the check boxes on previous record to 0. I'd cheat though - design the query as normal then just copy and paste the SQL from query design into the code.

Don't forget to specify a condition so it only updates the check boxes from that account though....!

 

[vbaInet]

On the After_Insert event of your form, you would run an UPDATE query to change the value of the Active field on the corresponding record. This is how you run the UPDATE query (once you've set that up):

Dim db as Database

Set db = CurrentDb
db.Execute "Name_Of_Query"

 

[JamesMcS]

Not seen that method before... every day's a school day vba!

 

[vbaInet]

Not seen that method before... every day's a school day vba!

Only used for Action Queries though James. So, it won't work on a Select query.

 

[JamesMcS]

Nice, I'll give it a try next time. Have a good evening/morning/afternoon all!

 

[vbaInet]

Haha, and you. Cheers

 

[mkashif83]

James, thanks the replying so fast.Update query works fine. The only issue with that is that it prompts user that an update query is about to run and gives user the option to proceed or cancel.
I am hoping i can accomplish this without any user prompt and make it almost invisible process to user.

I did think about disabling the update query prompt from Access Options but I don't want to disable the prompt for other update queries.

Is it possible to do this without running a query? perhaps a code?

Thanks for your help.

 

[vbaInet]

James, thanks the replying so fast.Update query works fine. The only issue with that is that it prompts user that an update query is about to run and gives user the option to proceed or cancel.
I am hoping i can accomplish this without any user prompt and make it almost invisible process to user.

I did think about disabling the update query prompt from Access Options but I don't want to disable the prompt for other update queries.

Is it possible to do this without running a query? perhaps a code?

Thanks for your help.

The code I gave you doesn't prompt. You don't seem to have tried it.

 

[mkashif83]

Hi VbaInet,

I'm sorry I read the post from my cell phone and didn't see your reply after James first reply. I was referring to his code. Thanks for your help as well. Here is what happens when I try your suggested code:

First, nothing happens when I write the code for After Insert Event of the form. But when I change it to Active field's On Click event, I get an error which says "compile error, User-defined type not defined".
For that error I added the Microsoft DAO 3.6 library. I hope that's what I was supposed to do?
Then after adding the library, now when I click the Active checkbox, I get this error: "Run-time error '3061'. Too few parameters. Expected 2"
Here is my code:
Private Sub Active_Click()
Dim db As Database
Set db = CurrentDb
db.Execute "qryUpdateMarkActive"
End Sub

Can you please help with this too?

Thanks,

 

[vbaInet]

There's nothing wrong with the code but you should have it in the After_Insert event of the form.

Let's see the sql of your query.

 

[JamesMcS]

I haven't checkede in for a while, but if we were going down the docmd... route, you'd need another one - "docmd.setwarnings false", and another one at the end of the code to say setwarnings true.

A moot point now I know!

 

[mkashif83]

Hi James,
Thanks for the updates. Yes, setting the warnings on and off works great for my purpose.
Hi vbaInet,
Although, I'm able to accomplish my task with update query method, just curious what I was doing wrong in your method. Please review the SQL if you have time.

I appreciate both of you helping me. I learned a few new things.
Thanks,

UPDATE qryReRvw SET qryReRvw.Active = 0
WHERE (((qryReRvw.[Re-Rvw ID])<>[Forms]![frmFreeze]![subformRe-Review].[Form]![Re-Rvw ID]) AND ((qryReRvw.[Hogan Number])=[Forms]![frmFreeze]![subformRe-Review].[Form]![Hogan Number]));

 

[vbaInet]

Missing closing bracket:

UPDATE qryReRvw SET qryReRvw.Active = 0
WHERE (((qryReRvw.[Re-Rvw  ID])<>[Forms]![frmFreeze]![subformRe-Review].[Form]![Re-Rvw ID])  AND ((qryReRvw.[Hogan  Number])=[Forms]![frmFreeze]![subformRe-Review].[Form]![Hogan Number]))[COLOR=Red][B])[/B][/COLOR];

See if that works now.

 

[mkashif83]

Won't let me save. Error msg says Extra ")". I think all the opening/closing brackets in my code check out.

 

[vbaInet]

That's right, the brackets were correct as you had it.

Drop the hyphens and spaces in the names of your fields, subforms etc. There are known problems using those characters as part of names.