List change by code

[nevstic]

I wonder if any nice kind person could give me the answer to the following.
I have a list box with 3 columns, date / time / Count
I have the number 10 in the count column (which come from a table)
I also have a text box (unbound) which I enter a Y or a N.
I would like to reduce the Count by one every time I enter a Y in the text box.
Can I do this and if so How!! heeelllpp please,.
Brgds
Nick

 

[markgrizzle]

In the textbox AfterUpdate event, use

CurrentDB.Execute "UPDATE tbl SET FieldName = [FieldName]-1 Where PKField = " & me!ListBox & ";"

me.listbox.requery

Substitute your field and tablenames for tbl, FieldName, and PKField. I've assumed your listbox bound column is the same as the PKField in the table

 

[nevstic]

Hi Mark and thanks for your quick reply.
I have put the code as follows.

Private Sub Text83_AfterUpdate()
CurrentDb.Execute "UPDATE InddateTime SET Count = [Count]-1 Where Count = " & Me!List80 & ";"
Me.List80.Requery

End Sub

Although I have not been able t make it work yet I think it mat be because the Code field which I need updated is in fact not the bound column.Is this stopping it from working?
Brgds
Nick

 

[nevstic]

Hi Mark
re my last, please read "Count" where I wrote "Code"
thanks
Nick

 

[nevstic]

Hiya Mark I am pleased to say that the bound column was the problem, which I have now solved.thankyou.
just one small thing though and that is I have 3 columns
and many rows, so when the code works it changes the count all the rows, when I really only need the row which is selected.
Is there a way round this please.?
rgds
Nick

 

[markgrizzle]

Hi Nick:
The Where clause should be limiting the update to the selected row. Try writing a select query for the fields in the table and filter it with Where Count = " & Me!List80

I suspect the filter is returning more than one row because Count isn't a primary key field for the table.

If not, then try adding the primary key field to the listbox as the bound column, and in the listbox ColumnWidths property, set the first column width to 0". You'll also need to increase the listbox ColumnCounnt property by one.

Then modify the CurrentDB.Excute... to filter on the PKField in your table, which is now the bound column.

Mark

 

[nevstic]

Hi Mark
Ok I'll give it a try.although I may, in the words
of the great A.Swarz "I'll be back"

Many thanks once again
Nick

 

[nevstic]

Hi Mark
Well I am afraid that I did not get on toooo well with this.But what I did was to put some of the code in the "on Click" event of the listbox itself..like so

Private Sub List80_Click()

CurrentDb.Execute "UPDATE InddateTime SET Count = [Count]-1"
Me.List80.Requery

This works Ok but again for the whole of the column.
Could I add a piece of code to only activate the row on which I "click" or am I back to squareone.
really appreciate you help and time on this one.
Brdgs
Nick

 

[markgrizzle]

Hi Nick:
What is the name of the field you're using for the bound column in your listbox? Is this field the primary key for the table you're updating?

 

[nevstic]

Hi Mark
I do not have a primary key in the underlying table because
I have a duplication of dates in it.
Date / Time / Count . do I need to have a primary key to make it work ?
Also what does PK mean ?

Rgds
Nick

 

[markgrizzle]

Nick
You could make a primary key using all three fields. Pk is just the abbreviation for primary key.

The problem is that without someway to uniquely identify the row you want to update, the sql updates all the matching rows.

You could add an autonumber field to the table which will give you a pk field to use for this.

Then the sql will only update the row you have selected in the listbox.

 

[nevstic]

ok thanks Mark
I will give it a try.

Given the time diff are you in the USA?

Rgds
Nick

 

[markgrizzle]

lol, I'm in nyc; I'm just a nightowl.

 

[nevstic]

Hi Mark
well I've tried just about everything
it works ok on the after update but again
all of the rows, even with a PK field added.

I know its a bit of a cheek, but would you take a look
at it for me, I would gladly buy you 20 beers when I am next in NYC , which is normally twice year !.
It is only 260kb zipped up.
You would no doubt spot the error immediately.

Rgds
Nick

 

[markgrizzle]

Will do Nick, send it to mgrizzle@aol.com, and I'll take a look.
Mark

 

[markgrizzle]

Nick:
I made the following changes, and emailed the mdb back to you. I'm posting them here in case anyone else follows this thread.

I changed the AfterUpdate procedure from

CurrentDb.Execute "UPDATE InddateTime SET Count = [Count]-1 where Count=" & Me!List80 & ";"

to CurrentDb.Execute "UPDATE InddateTime SET Count = [Count]-1 where ID=" & Me!List80 & ";"

This way the sql is looking at the unique ID column, and will limit the update to the matching row.

I also changed List80's bound column from 4 (Count) to 1 (ID).

Let me know if that worked.
Mark