Fill field with chosen value from a drop down combo box

[Dannic]

I need to fill a table with values. The table has about 6000 records
but the records will not have the same value in this field. I thought
I had figured out a way to do this easily but it is giving me an
error.

Object or provider is not capable of performing requested operation.

Basicly I am choosing a department in a combo box(Combo1) and a charge
number(Combo3) in the form. They are both unbound.

I want to add the charge number to the CALFILES table which is chosen
in the charge number box for all records with the department chosen in
Combo1

Here is the code.

Private Sub Command0_Click( )
On Error GoTo Err_Command0_Cl ick

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

Set con = Application.Cur rentProject.Con nection

stSql = "SELECT * FROM [CALFILES] WHERE [Department]=" &
Me![Combo1]

Set rs = CreateObject("A DODB.Recordset" )
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
While (Not (rs.EOF))
rs![ChargeNumber] = Me![Combo3]
rs.MoveNext
Wend

rs.Close
Set rs = Nothing
Set con = Nothing
Exit_Command0_C lick:
Exit Sub

Err_Command0_Cl ick:
MsgBox Err.Description
Resume Exit_Command0_C lick

End Sub

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would be easier to run a query instead of looping thru a recordset to
update a table. E.g.:

Put this in a query object's SQL view:

PARAMETERS Forms!form_name !Combo1 Long,
Forms!form_name !Combo3 Long;
UPDATE CalFiles
SET ChargeNumber = Forms!form_name !Combo3
WHERE Department = Forms!form_name !Combo1

Substitute the ComboBoxes' form's name for "form_name" in the above
parameters. I set the parameters' data types to Long, change to the
appropriate data type.

Run the query from the OnClick event of the CommandButton.

For later maintenance, it helps if the control names are more
descriptive than Combo1, Combo3. How about cboDepartment and
cboChargeNumber , respectively?

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRfg2YechKq OuFEgEQKSNQCfYF 5B/I4q7qjF+L2N/wa7ULgNXjsAoJOV
6/wo8+L0bnDw8oZmt CtPlACK
=s49R
-----END PGP SIGNATURE-----
Dannic wrote:

I need to fill a table with values. The table has about 6000 records
but the records will not have the same value in this field. I thought
I had figured out a way to do this easily but it is giving me an
error.

Object or provider is not capable of performing requested operation.

Basicly I am choosing a department in a combo box(Combo1) and a charge
number(Combo3) in the form. They are both unbound.

I want to add the charge number to the CALFILES table which is chosen
in the charge number box for all records with the department chosen in
Combo1

Here is the code.

Private Sub Command0_Click( )
On Error GoTo Err_Command0_Cl ick

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

Set con = Application.Cur rentProject.Con nection

stSql = "SELECT * FROM [CALFILES] WHERE [Department]=" &
Me![Combo1]

Set rs = CreateObject("A DODB.Recordset" )
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
While (Not (rs.EOF))
rs![ChargeNumber] = Me![Combo3]
rs.MoveNext
Wend

rs.Close
Set rs = Nothing
Set con = Nothing
Exit_Command0_C lick:
Exit Sub

Err_Command0_Cl ick:
MsgBox Err.Description
Resume Exit_Command0_C lick

End Sub

[Dannic]

MGFoster <[e-mail] > wrote in message news:<Gh******* ************@ne wsread1.news.pa s.earthlink.net >...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It would be easier to run a query instead of looping thru a recordset to
update a table. E.g.:

Put this in a query object's SQL view:

PARAMETERS Forms!form_name !Combo1 Long,
Forms!form_name !Combo3 Long;
UPDATE CalFiles
SET ChargeNumber = Forms!form_name !Combo3
WHERE Department = Forms!form_name !Combo1

forgot about a sql type query. That should work just fine.
For later maintenance, it helps if the control names are more
descriptive than Combo1, Combo3. How about cboDepartment and
cboChargeNumber , respectively?

I would if it were reoccuring. This is a one time deal to get the
database filled with almost the proper data for a new field. So once
I have this done I'll never need the darn thing again.

Thanks. Let ya know the outcome.

Billie Kennedy