By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Fill field with chosen value from a drop down combo box

P: n/a
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_Click

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

Set con = Application.CurrentProject.Connection

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

Set rs = CreateObject("ADODB.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_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQRfg2YechKqOuFEgEQKSNQCfYF5B/I4q7qjF+L2N/wa7ULgNXjsAoJOV
6/wo8+L0bnDw8oZmtCtPlACK
=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_Click

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

Set con = Application.CurrentProject.Connection

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

Set rs = CreateObject("ADODB.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_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


Nov 13 '05 #2

P: n/a
MGFoster <me@privacy.com> wrote in message news:<Gh*******************@newsread1.news.pas.ear thlink.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
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.