473,326 Members | 2,076 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Fill field with chosen value from a drop down combo box

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
2 2331
-----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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Elvis V. | last post by:
Good morning, I have a table with three fields, Buildings, Floors and Rooms. This is what I would like to do; in my form when I select Building1 in my drop down box for Buildings, when I go to the...
3
by: sao | last post by:
I am currently using Access 2000. In my table it is the following fields that are set up: 1 - Staff Name 2 - Department This table is already populated with 100+ records (staff along with...
2
by: G.Gerard | last post by:
Hello Does anyone know how to get an entire memo field to be displayed in a combo box drop down list? Example - if the memo field contains the following : This is a test to see if
4
by: Sherwood Botsford | last post by:
Table Markers ID (Primary Key) This&That PointClass (Combo box) Points Table PointClasses PointClass (primary key) Points (number) Description (Text)
8
by: Lyn | last post by:
Hi, Can anyone tell me how the initial value displayed in Combo Box is determined when a form is opened? I am loading the dropdown from one field ("CategoryName") of a table, with "ORDER BY ". ...
2
by: Jeremy Dillinger | last post by:
I have a program setup to pull data from a database. My database table has things such as (category, Item, price, etc.) In my program I want to have multiple list boxes that will have a pull down...
8
by: sara | last post by:
I have a table where a few of the users entered vendor names ALL IN UPPER CASE. I have created forms to edit the data, but I can't seem to allow changing JOE SMITH to Joe Smith. What to I have...
3
by: penny111 | last post by:
Hi there, For my application, i need to have 3 drop down lists 1. drop down list of folder names 2. drop down list of documents in the folder selected 3. drop down list of instances of the...
1
by: bytenut | last post by:
I would like to display two field values in a combo after selecting from the list items... i.e. my drop-down shows two fields from a lookup, but when I select the item, only the first field value is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.