+1 to table value

[Master Klick]

I have a form with a listbox I can use to select specific records in a table. I need to either:

1. Have a button that when clicked adds +1 to the quantity, permanently, of the record I have selected in the list box. The value has to be added to the table to update the record.

2. Another option, if possible, and/or easier, would be to instead of having a button have a text box that populates with the quantity of the selected item but can be changed right on the form and have it update the table to match.

I was working on both and I have no idea how to reference a specific value in a specific record of the table, let alone have value and record dependent on the current selection in a list box.

If what I want is even possible, all I should need is an example of the code I described in BOLD.

[Dal Jeanis]

Lots of ways to do what you are asking for.

1) In a bound form, you could have a text box bound to the field you want to update, and update it manually.

2) Or, you could have a "Increment" command button, that would execute SQL like

strSQL = "UPDATE [Mytable] SET [counter] = [counter] + 1 WHERE [Key] = " & [txtKey]

[txtKey] would be the name of the text box that is bound to the key of the table on MyTable.

The SQL command to update a record is UPDATE, and here's a reference page for it...
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Here's a very thorough SQL / VBA tutorial...
http://www.fontstuff.com/access/acctut15.htm

3) If items were selected in a listbox, and if the listbox contained a key as well as the displayable value, then you could loop through all the items in the list, build and execute the SQL for each item that had been selected.

[Dal Jeanis]

Let me back up for a minute.

First, make sure there is a key on your table. Usually it's called ID, and it's set up as Autokey, unique, indexed.

Second, when you load the list box, you include the key as one of the columns. It can be hidden, so the user doesn't see it, but the programmer needs to know it's there.

So, let's say the listbox [lstCity] is going to have cities in it, and the RowSource is

="SELECT cityID, cityName & "", "" & cityState FROM Cities;"

The Bound column is 1, and column width is 0",2" so that only the city name and state shows. LimitToList is set to yes.

Here's a reference that you can look at for list boxes -
http://office.microsoft.com/en-us/ac...005187786.aspx

Here's a random example of what those values loaded in the listbox might be. Only the second column would be displayed.

13  Cincinatti, OH
24  Frisco, TX
69  San Francisco, CA
92  Zumberg, LA

So, when someone picks Cincinatti, OH out of the City list, the control [lstCity] then has the value 13. If they pick San Francisco, CA, it gets the value 69.

Behind your button that says "Increment", there will be code that builds and runs the SQL. There's usually error trapping and so on, but it boils down to this:

Dim strSQL AS string

strSQL = "UPDATE [Cities] SET [visitors] = [visitors] + 1 WHERE [CityID] = " & [lstCity] &";"

DoCmd.RunSQL strSQL

So, if the user picked Cincinatti, the value of [lstCity] would become 13, and the strSQl would look like this:

UPDATE [Cities] SET [visitors] = [visitors] + 1 WHERE [CityID] = 13;

That SQL, when executed, will update one, and only one, record on the [Cities] table.

[Master Klick]

Ok, I think I got something wrong here.

Private Sub PlusOne_Click()
Dim strSQL As String
strSQL = "update [ImportSheet] set [Quantity] = [Quantity] + 1 where " &
[List] & ";"
DoCmd.RunSQL strSQL
End Sub

ImportSheet is the table I need the Quantity value from. List is the name of the list box on the form that references all the records through a query. This is giving me a syntax error in the "where" clause. I'm guessing that I'm not referencing something properly?

[Master Klick]

This code...:

Private Sub PlusOne_Click()
Dim strSQL As String
strSQL = "update [Inventory] set [Quantity] = [Quantity] + 1 where " & Me![QuantityCount] & ";"
DoCmd.RunSQL strSQL
End Sub

...very nearly works. Problem is it wants to update all 23335 records. I need to make it change only the one selected. Any ideas?

[Dal Jeanis]

The key to the one Inventory Item you have selected needs to be in that SQL. So what's the name of the key field on your Inventory table?
Assuming the key field name was ItemID on the inventory table, then your SQL should be like this -

strSQL = "UPDATE [Inventory] SET [Quantity] = [Quantity] + 1 WHERE [ItemID] = " &  
[List] & ";"

That would mean that your listbox should be loaded with this code, bound column = 1

="SELECT ItemID, ItemName FROM Inventory;"

And, as a general case, it will make your life much easier in the long run if you'll add the control type to the front of the name, and use a descriptive name. The listbox to select an inventory item might be [lstInvItems], the table that contains Inventory might be tblInventory, and so on. That's also why I suggested [itemID] rather than the default name [ID] - if every stinking table calls its key ID, you can't tell who's what from reading it.

[Master Klick]

Where exactly do I put the other code? I tried it in the control source but then it won't let me select anything.

[Dal Jeanis]

It goes in the list control's Row Source. Make sure the Column Count is 2, Column Width is 0";2" . (If the column count was 1, you wouldn't see anything with column width set like that.)

Leave off the = and the quotes. In the Row Source property for the listbox it should look like this:

SELECT ItemID, ItemName FROM Inventory;

In VBA setting for a listbox called lstInvItems it should look like this:

Me![lstInvItems].RowSource = "SELECT ItemID, ItemName FROM Inventory;"

and, of course, use the actual names from your table (which I called Inventory) for the key (which I called ItemID) and the field the user is selecting (which I called ItemName).

[Master Klick]

I already have a code in the listbox row source. Changing it will stop everything else from working. Gonna need another way to do this.

Just need some way to edit a record value from the form. And it has to choose the record based on the listbox selection. Can't change the listbox parameters though, too many other things are dependent on it. I don't care if this is accomplished with a button, a text box or what.

[Dal Jeanis]

Please post the following properties of your listbox:
On the Format Tab
Column Count :
Column Widths :

On the Data Tab
Control Source:
Row Source :
Row Source Type:
Bound Column :
Limit To List :

On the Other Tab -
Name :

Tell me the following properties of your table to be updated:

Name of Table:
Name of Primary Key Field:
Name of Field to be Incremented:

Tell me the following properties of the control to cause the update to occur (for example a command button):
Control Name:
Type of Control:

[Master Klick]

On the Format Tab
Column Count : 8
Column Widths : 0";2";1.5";2";1";1";0";0"

On the Data Tab
Control Source:
Row Source : SELECT InvQuery.ID, InvQuery.Name, InvQuery.Edition, InvQuery.[Card Type], InvQuery.[Card Subtype], InvQuery.Color, InvQuery.[Image Path] FROM InvQuery;
Row Source Type:Table/Query
Bound Column : 1
Limit To List : N/A

On the Other Tab -
Name : List

Tell me the following properties of your table to be updated:

Name of Table: Inventory
Name of Primary Key Field: ID
Name of Field to be Incremented: Quantity

Tell me the following properties of the control to cause the update to occur (for example a command button):
Don't have one in the form at the moment. I don't care if I have to use a couple buttons to add or subtract 1 from the value or if its a textbox where I can enter the value manually. I think I'd rather have the textbox though, because I know how to add button controls to that.

I would just upload the file but it won't let me. Sorry, I have no idea why.

[RuralGuy]

I would just upload the file but it won't let me. Sorry, I have no idea why.

Compact and Repair your db and then ZIP it up. It should let you upload the zip file.

[Dal Jeanis]

Okay, add a command button called cmdAddOne, and behind the On Click event, add the following code:

Private Sub cmdAddOne_Click()
Dim strSQL As String
strSQL = "UPDATE [Inventory] SET [Quantity] = [Quantity] + 1 " & _ 
          "WHERE [Inventory].[ID] = " & Me![ List] & ";"
DoCmd.RunSQL strSQL
End Sub

Warning - remove the space between [ and List]. The forums were attempting to treat that as HTML code and adding an erroneous carriage return.

[Master Klick]

That works PERFECT! Thank you.

[Dal Jeanis]

You're welcome.

Quick note - When all the exact names and values are available, coding a button like that is pretty simple. Making sure that everything is bound to the right column and such is the key, and you already had it hooked up right. Of course, even if the control had been bound to a different column of the Row Source than we needed, we could have used the
[List].Column(x) to get the appropriate field value, instead of using the default boundcolumn. But, it would have taken three or more posts back and forth to explain all that, so I just collected the information and gave you the appropriate code for your situation.