Using combobox to update a field

  • Thread starter Thread starter NHiorns
  • Start date Start date
N

NHiorns

I am not sure if the combobox is the right tool to use,
but what I am trying to achieve is to update a range of
boolean fields to True using two comboboxes.

The two comboboxes will use the same table and field
(call this field 'ID', contains numeric range e.g. 1 -
8000) for the lookup, with the first one specifying the
From: and the second the To:

Once the selection is done, by clicking on a button, use
the From: and To: selections to update a boolean field in
the same table to True (call this field 'Selected').

I have tried trawling the web for similar example code
but to no avail. Using Access 2000.
 
NHiorns said:
I am not sure if the combobox is the right tool to use,
but what I am trying to achieve is to update a range of
boolean fields to True using two comboboxes.

The two comboboxes will use the same table and field
(call this field 'ID', contains numeric range e.g. 1 -
8000) for the lookup, with the first one specifying the
From: and the second the To:

Once the selection is done, by clicking on a button, use
the From: and To: selections to update a boolean field in
the same table to True (call this field 'Selected').
Click to expand...


Use the button's Click event to run code that executes an
Update query:

Dim db As Database
Dim strSQL As String

On Error GoTo ErrHandler
strSQL = "UDPDATE thetable SET [Selected] = TRUE " _
& "WHERE [ID] Between " & Me.cboFrom & " And " _
& Me.cboTo
db.Execute strSQL, dbFailOnError
ExitHere:
On Error Resume Next
Set db = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ExitHere
End Sub
 
I would use two text boxes: StartNum and EndNum. It is
faster to enter (not having to scroll from 1 to get to
5000) and allows for validation (numbers were entered and
start is less than end).

Use the button's Click event to run this code (change the
table, fields and button to your names):
--Watch for line wrap---


Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim tmp As Integer
Dim strSQL As String

'StartNum and EndNum must be numeric
If Not IsNumeric(StartNum) Then
StartNum = ""
StartNum.SetFocus
MsgBox "Enter a number between 1 and 8000"
Exit Sub
End If
If Not IsNumeric(EndNum) Then
EndNum = ""
EndNum.SetFocus
MsgBox "Enter a number between 1 and 8000"
Exit Sub
End If

' EndNum must be greater than StartNum
If EndNum < StartNum Then
tmp = EndNum
EndNum = StartNum
StartNum = tmp
End If

'set all records to false
'comment out the next line if you don't want to clear
previous selections
CurrentDb.Execute "UPDATE Table2 SET Table2.Selected =
False;"

'now set record that are between Start and End to true
strSQL = "UPDATE Table2 SET Table2.Selected = True "
strSQL = strSQL & "WHERE (((Table2.ID)>=[forms]!
[form1]![StartNum] "
strSQL = strSQL & " And (Table2.ID)<=[forms]![form1]!
[EndNum]));"

'now do it!!
CurrentDb.Execute strSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


HTH

Steve
 
I have done as you suggested and created two text boxes and named them StartNum and EndNum. I modified your code and added to button Click event, but I am getting the following error ...

"Too few parameters. Expected 2."

Modified code below for you to check.

Private Sub btnUpdate_Click()
On Error GoTo Err_btnUpdate_Click

Dim tmp As Integer
Dim strSQL As String

'StartNum and EndNum must be numeric
If Not IsNumeric(StartNum) Then
StartNum = ""
StartNum.SetFocus
MsgBox "Please enter a number ...."
Exit Sub
End If
If Not IsNumeric(EndNum) Then
EndNum = ""
EndNum.SetFocus
MsgBox "Please enter a number ...."
Exit Sub
End If

' EndNum must be greater than StartNum
If EndNum < StartNum Then
tmp = EndNum
EndNum = StartNum
StartNum = tmp
End If

'set all records to false
'comment out the next line if you don't want to clear previous selections
CurrentDb.Execute "UPDATE Labels SET Labels.Selected = 0"

'now set record that are between Start and End to true
strSQL = "UPDATE Labels SET Labels.Selected = 1 "
strSQL = strSQL & "WHERE (((Labels.ID)>=[forms]![Label]![StartNum] "
strSQL = strSQL & " And (Labels.ID)<=[forms]![Label]![EndNum]));"

'now do it!!
CurrentDb.Execute strSQL

Exit_btnUpdate_Click:
Exit Sub

Err_btnUpdate_Click:
MsgBox Err.Description
Resume Exit_btnUpdate_Click

End Sub
 
Sorry, my error. I created the SQL by making a select
query, then changing it to an update query and I forgot to
put the control values outside of the quotes.

I didn't test the code (obviously). Note that I changed
the 1 (one) in the first strSQL line to -1. In Access,
zero is False and -1 is True. SQL wouldn't know that False
equals 0 and True equals -1 .... good catch..


Replace the strSQL lines with these:

strSQL = "UPDATE Labels SET Labels.Selected = -1 "
strSQL = strSQL & " WHERE Labels.ID >= " & [Forms]!
[Label]![StartNum]
strSQL = strSQL & " And Labels.ID <= " & [Forms]!
[Label]![EndNum] & ";"


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
-----Original Message-----
I have done as you suggested and created two text boxes
Click to expand...
and named them StartNum and EndNum. I modified your code
and added to button Click event, but I am getting the
following error ...
"Too few parameters. Expected 2."

Modified code below for you to check.

Private Sub btnUpdate_Click()
On Error GoTo Err_btnUpdate_Click

Dim tmp As Integer
Dim strSQL As String

'StartNum and EndNum must be numeric
If Not IsNumeric(StartNum) Then
StartNum = ""
StartNum.SetFocus
MsgBox "Please enter a number ...."
Exit Sub
End If
If Not IsNumeric(EndNum) Then
EndNum = ""
EndNum.SetFocus
MsgBox "Please enter a number ...."
Exit Sub
End If

' EndNum must be greater than StartNum
If EndNum < StartNum Then
tmp = EndNum
EndNum = StartNum
StartNum = tmp
End If

'set all records to false
'comment out the next line if you don't want to clear previous selections
CurrentDb.Execute "UPDATE Labels SET Labels.Selected = 0"

'now set record that are between Start and End to true
strSQL = "UPDATE Labels SET Labels.Selected = 1 "
strSQL = strSQL & "WHERE (((Labels.ID)>=[forms]! [Label]![StartNum] "
strSQL = strSQL & " And (Labels.ID)<=[forms]![Label]! [EndNum]));"

'now do it!!
CurrentDb.Execute strSQL

Exit_btnUpdate_Click:
Exit Sub

Err_btnUpdate_Click:
MsgBox Err.Description
Resume Exit_btnUpdate_Click

End Sub

.
Click to expand...
 
Back
Top