Listbox Database

[k0r54]

Hi
Firstly I have a listbox and I want the user to select an item from the list box and then click cmdStartStop

when cmdStartStop is pressed i want it to put "Not Running" into the field Live WHERE the Campaign_Name is = to the lstnotrunning.text

I am trying to put the variable Not Running Select into the **Insert NotRunningSelect** but it doesn't alow me to

Dim NotRunningSelect As String
NotRunningSelect = lstNotRunning.Text
If cmdStartStop.Caption = "Stop Campaign" Then
NotRunning.Source "Select * From T_Campaigns WHERE Campaign_Name = **Insert NotRunningSelect** "
NotRunning.Open , db, adOpenKeyset, adLockOptimistic
NotRunning.Fields(Live).Value = "Not Running"

Any idea on what im doin wrong or a better way to do it

tnx adz

[k0r54]

it has to be something simple

[Pino]

you have to do something like

recordset.add
 
("Field") = "not running"
 
recordset.update

[mendhak]

What is NotRunning? Is it an ADODB.Recordset?

Try this.

Dim NotRunningSelect As String
NotRunningSelect = lstNotRunning.Text
 
If cmdStartStop.Caption = "Stop Campaign" Then  'Why are you doing this?
 
NotRunning.Source "Select * From T_Campaigns WHERE Campaign_Name = '" & NotRunningSelect & "';"
NotRunning.Open , db, adOpenKeyset, adLockOptimistic
NotRunning.Fields(Live).Value = "Not Running"
NotRunning.Update

[k0r54]

yes it is a adodb connection

I am now getting an error of

Runtime Error '3021'
either bof or eof is tru and the current record hs been deleted requested operation requires a current record

The record does exist i know it does.

it is stopping at .feilds....

i am now using the code

Dim NotRunningSelect As String
NotRunningSelect = lstNotRunning.Text
If cmdStartStop.Caption = "Stop Campaign" Then
NotRunning.Source = "Select * From T_Campaigns WHERE Campaign_Name = '" & NotRunningSelect & "';"
NotRunning.Open , db, adOpenKeyset, adLockOptimistic
With NotRunning
.Fields("Live").Value = "Not Running"
.Update
End With
End If

tnx adz

[mendhak]

Do a msgbox of
objrs.bof
objrs.eof
objrs.recordcount

See what you get.

[k0r54]

sorry but how do i do that :S, i cant remember

[mendhak]

MsgBox objrs.BOF

Simple, isn't it?

Do this after you've opened up the recordset.

[k0r54]

sorry yeah i was putting them all on 1 line lol

ok bof and eof are true

and recordcount is 0

[leinad31]

Use lstNotRunning.List(lstNotRunning.ListIndex) directly, storing to a string is just additional code for a short implementation. Make the list style dropdown list. Make sure there are no special formatting (eg, vbTab, vbCrLf, etc) in the record string, you would never get a match with the list item otherwise.

[k0r54]

firstly it can't be a drop down.

i have taken out the variable, and now im a bit confused coz do i use lstnotrunning.text or lstnotrunning.list(lstnotrunning.listindex) to tell it what campaign is currently selected.

But with both i still get the same error,

also as a Q recordcount is at 0 does this mean there are no records at all, or no records matchin the selected item.

Thanks Adam

[k0r54]

??

[mendhak]

It means that there is nothing to match. You need to check the values... there will probably be a slight difference between what's on the form, and what's in the db.

[k0r54]

ok i found the problem

it is at this point

WHERE Campaign_Name = ' " & lstNotRunning.List(lstNotRunning.ListIndex) & " ' ;"

its not finding the selected item i have also tried lstnotrunning.text coz im not sure which on to use.

[k0r54]

?? i have still had no luck

[mendhak]

What sort of content does the listbox (and the db table) hold?

[k0r54]

words

the table has 5 columns
Campaign_Name = text
Start_Date=date but is a text field
Manager = text
Assistant = text
Live = text

the listbox is pulling the data from from this table so i know the spelling is corrent and everything