Thank you so much. Works like a charm. I would have let
you know sooner, but my computer died.
Now, I want to have a user press a button on a form, and
then look at the records in a table, and add the same
number of records to another table. I know I need to use
an INSERT statement. Not sure of the syntax.
-----Original Message-----
Ok. lets try an adjustment on the query.
strSQL = "UPDATE Orders SET [Orders].[" & Me.cboWeek & "]
=
[Orders].[Choice];"
--
Wayne Morgan
MS Access MVP
in message
Thanks for the help. The UPDATE query now works, but i
have 10 records in the table, and every record has a
different value in the choice field. But the query I am
running takes the choice of the 1st record, and
populates
every record with that value in my desired column.
Help!
-----Original Message-----
You left out the semicolon (
at the end. Also, is the
field defined as a
number field. You are storing a number but it could be
defined as Number or
Text in the table. If it is defined as a number, the
result should look like
this, without the quotes around the 2. Also, I forgot
to
readjust the syntax
for a query instead of code.
"UPDATE Orders SET Orders.Fields("1") = 2;"
and readjusting to query syntax:
That would change the strSQL= line to the following.
strSQL = "UPDATE Orders SET [Orders].[" & Me.cboWeek
& "]
= " & rs!Choice &
";"
This should give
UPDATE Orders SET [Orders].[1] = 2;
Or, if it is a text field
strSQL = "UPDATE Orders SET [Orders].[" & Me.cboWeek
& "]
= """ & rs!Choice
& """;"
This will give
UPDATE Orders SET [Orders].[1] = "2";
--
Wayne Morgan
MS Access MVP
in message
Ok, heres the Update query:
strSQL = "UPDATE Orders SET Orders.Fields(""" &
Me.cboWeek & """) = """ & rs!Choice & """"
CurrentDb.Execute strSQL, dbFailOnError
All I want to do is move what is in the Choice field
of
my row into the field named 1-36. So if the user
chooses
1 in the combo box, and the choice field is 2, the
query
looks like:
"UPDATE Orders SET Orders.Fields("1") = "2""
The code receives a syntax error.
I really appreciate you taking the time to help me.
Thanks.
-----Original Message-----
First, to go back a message.
rs!Fields(CStr(cboWeek)) = rs!Choice
Your original message said that you want the user to
fill in a textbox then
when you push a button, store the value of the
textbox
in the field defined
by the combo box. This indicates to me that the
textbox
is an unbound
textbox, yet you are trying to set one field of the
recordset equal to
another field in the recordset, not the value of an
unbound textbox. To do
the latter, the above should look more like:
rs!Fields(CStr(cboWeek)) = Me.NameOfTextbox
Now, to do the update query instead of looping
through
the recordset:
strSQL = "UPDATE Orders SET Orders.Fields(""" &
Me.cboWeek & """) = """ &
Me.NameOfTextbox & """;"
CurrentDb.Execute strSQL, dbFailOnError
This will update the Field defined by the combo box
for
ALL records in the
Orders table and set it to the value in the textbox.
The
quotes are set up
for a text value in the textbox. If this is a number
field and the value
should be a number it will need a minor alteration.
--
Wayne Morgan
Microsoft Access MVP
"Tom Nowak" <
[email protected]>
wrote
in message
How would I run an Update Query?
-----Original Message-----
Ok, the first thing to check then is that the rs!
Fields
(CStr(cboWeek)) is
being accepted properly and that Access isn't
trying
to "help" by turning
the number back into a number. Try the
Debug.Print
statement in my previous
message before you get to this line.
Also, you may want to try a different approach.
Running
an Update Query
would do the same thing and be much faster than
stepping
through the
recordset.
--
Wayne Morgan
Microsoft Access MVP
message
The rs!Fields(CStr(cboWeek)) = rs!Choice line
gives
me
the
error.
-----Original Message-----
The cboWeek.SetFocus shouldn't matter since
you're
no
longer using the Text
property of the combo box. Which line gives
the
error
message?
Also, you are looping from 1 to Recordcount.
Some
recordsets don't give an
accurate recordcount until you do a MoveLast
to
fully
populate the
recordset. An option here would be to us a Do
Loop
and
loop until EOF (end
of file). You are currently using a MoveNext
after
each
change. If that
should change, then Update won't be called
automatically
for the recordset
and you'll need to do it yourself (rs.Update).
Try this to make sure we are referring to the
field
properly.
Debug.Print rs!Fields(CStr(cboWeek))
This should send the current value of the
field
to
the
debug window. Does
this give the correct value?
--
Wayne Morgan
Microsoft Access MVP
"Tom Nowak"
<
[email protected]>
wrote
in message
[email protected]...
Heres the code I am now using:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM orders"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open
End With
Set Me.Recordset = rs
cboWeek.SetFocus
Dim x As Integer
For x = 1 To rs.RecordCount
rs!Fields(CStr(cboWeek)) = rs! Choice
rs.MoveNext
Next x
MsgBox ("Done")
When I run the code I receive:
Run Time Error 3265
Item cannot be found in the collection
corresponding
to
the requested name or ordinal.
Help!
-----Original Message-----
You can't use the Text property of a
control
unless
the
control has the
focus. Also, the name of the field isn't
cboWeek,
your
syntax needs a minor
adjustment for that. If the bound column
(the
column
that the combo box's
value comes from) is the column with the
number
(if
there is only one
column, it is) then
rs!Fields(cboWeek)
Value is the default, so the above would be
the
same as
rs!Fields(cboWeek.Value). There are 3 ways
to
refer
to
an item in a
collection.
1) rs!Fields(1) - this is the index number,
not
the
numbers you are using as
a name.
2) rs!Fields("FieldName") - this is the one
used
above,
only a variable has
been used in place of the text here.
3) rs!FieldName - this one works because
Fields
is
the
default collection of
the recordset object.
Since your field names are numbers, there
may
be a
conflict between option
one and two above. You may need to change
the
example
to:
rs!Fields(CStr(cboWeek))
to make sure that the number is taken as a
string
and
is
therefore the name
and not the index of the field.
--
Wayne Morgan
Microsoft Access MVP
message
[email protected]...
I am writing code behind an Access form,
and I
have a
database table with 36 fields named 1,
2, 3
etc to
36. I
want the user to click on a number in a
combo
box
on
the
form, and enter info into a text box.
Then,
after
clicking a button on the form, I want the
text
saved
into
the table field using the number in the
combo
box.
For example if the user enters "Jim" in
the
text
box
and
chooses 5 in the combo box, I want "Jim"
saved to
field 5
in the table. I have this line in the
code:
rs!cboWeek.Text
but this generates an error.
Please help.
.
.
.
.
.
.
