Should be simple: Click a button, all checkboxes become selected in a table

[Sydcomebak]

Private Sub Command120_Click()
UPDATE[Listings] SET [Lot_Selected] = 1 
DoCmd.OpenForm "Market_Review_Report", acNormal, , sWHERE
End Sub

Nope!

Gah!

-Dave

 

[PNGBill]

If you are trying to Update fields in a table and then open a form (with Where Clause) that will display the table and expect to see changes made with only two lines of code, I think it is not possible.

You need to define your strWhere.

You need to dim your recordset and database.

 

[Sydcomebak]

OK, forget the new form.

Private Sub Command120_Click()
UPDATE[Listings] SET [Lot_Selected] = 1 
End Sub

Can you type up a sample of what you mean that I can work with? This is my first DB.

 

[PNGBill]

Will do but what happened between 2008 and 2012 ??

 

[PNGBill]

I assume you have a Form open and have ticked a check box or done something on the form and now want to make the change to one or more records in a table ??

If the form is Bound to a table, then most likely the form control you click on is also bound. This cahnge can be done just like that ie, the form allows you to change the table, normally one record at a time.

If you really want all the records in a table to be changed on teh click of a command Button on a form, thenyou will need some code to make this happen.

Think about what is happening here...
You need to Interupt the operator needs which often means reading the data on the Form.
This done by two lines of code like this.

Dim MyVariable As String
MyVariable = Me.yourcontrolname

This is simplistic as it assumes the data is string, not date or number.
Also, yourcontrolname is not what you type.

Then you need to define your Recordset and Database.

Then you need to decide on a method to make the change.

Then you make the change

Maybe before I type a copy of MS Access Help you explain in more detail what you trying to achieve and what you have done so far.

 

[pbaldy]

Try

CurrentDb.Execute "UPDATE [Listings] SET [Lot_Selected] = -1", dbFailOnError

Edit: there is a space after update that isn't showing up.

 

[Sydcomebak]

Everyone gets thanks, and Pbaldy gets the blue ribbon!

 

[Sydcomebak]

PNG, the DB from 2008 never panned out, and that was actually a PHP/html project. This one is Access.

 

[pbaldy]

LOL! Happy to help.

 

[Sydcomebak]

Error: Too few parameters. Expected 2

CurrentDb.Execute "UPDATE [Listings] SET [Lot_Selected] = -1 WHERE [Garage] = Check124.Value", dbFailOnError

 

[Beetle]

Check124, which is presumably a check box on your form, needs to be referenced outside of the SQL string (i.e. not inside the quotes that delimit the string);

Dim strSQL As String

strSQL = "Update Listings Set [Lot_Selected] = -1 Where [Garage] = " & Me.Check124

CurrentDb.Execute strSQL, dbFailOnError

 

[pbaldy]

I suspect Sean has identified at least part of the problem. Since the error specified 2, if you still get an error double check the spelling of everything, and the data type of Garage.

 

[Sydcomebak]

Thanks, guys!
Too bad there isn't a Paypal donation button for me to drop 99cents everytime someone gives me a working line of code! Totally worth it!
-Dave

 

[Sydcomebak]

RT Error 3061
Too few parameters. Expected 1

Dim strSQL As String

strSQL = "Update Listings Set [Lot_Selected] = -1 Where [Garage] = " & Me.Check124

CurrentDb.Execute strSQL, dbFailOnError

 

[pbaldy]

The error is basically telling you there's something it can't resolve. Double check the spelling of the table and field names. What is the data type of Garage, and what is in Check124 (descriptive names pay off in the long run)? This may help debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

And make sure [Lot_Selected] shouldn't really be [Lot Selected] (space rather than underscore).

 

[Sydcomebak]

Spelling all looks to be good.

Does it matter that [Garage] is in the "Houses" table?

The tables are joined in the form with the button executing the command, does it have to be joined in the sql statement too?

 

[pbaldy]

Yes, it matters. You'd have to join them in your SQL if that field is necessary to isolate the records to be updated.

 

[Sydcomebak]

See attached image for my structure and spelling of fields.

 

[Sydcomebak]

Better image and my current select code:

SELECT Listings.*, Houses.*, Models.*, Models.[Model Name], Houses.Garage, Listings.Lot_Selected FROM Models INNER JOIN (Houses INNER JOIN Listings ON Houses.Lot_ID = Listings.Lot_ID) ON Models.ID = Houses.Model_ID;