Update table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pietro - 26 Dec 2007 20:24 GMT

Hi,

I've 2 Textboxes on my form: FROM,TO and a combobox called AGENT
I want to select an agent in the combobox then press on a button so that I
may Write the agent in all the records between FROM and TO in a tabe called
CAP
How can i do this?

Reply to this Message

John W. Vinson - 26 Dec 2007 20:49 GMT

>Hi,
>
[quoted text clipped - 3 lines]
>CAP
>How can i do this?

You'ld run an Update query. The click event of the button would execute the
query - something like

Private Sub cmdFillAgent_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String
On Error GoTo Proc_Error
strSQL = "UPDATE CAP Set Agent = """ & Me!cboAgent & """ WHERE" _
& " [Datefield] >= #" & Me![FROM] & "# AND [Datefield] <= #" _
& " Me![TO] & "#")
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in FillAgent:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

Adapt to your actual control and field names.

John W. Vinson [MVP]

Reply to this Message

Pietro - 26 Dec 2007 20:58 GMT

Thank you for your reply...
Starting from "strSQL = "UPDATE CAP Set Agent..." the command is written in
red meaning that ther's an error..
appreciate your quick reply...

> >Hi,
> >
[quoted text clipped - 28 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 26 Dec 2007 21:17 GMT

>Thank you for your reply...
>Starting from "strSQL = "UPDATE CAP Set Agent..." the command is written in
>red meaning that ther's an error..
>appreciate your quick reply...

Check to see if there are unbalanced quotemarks; copy and paste your actual
code to a message here if there's nothing obvious.

John W. Vinson [MVP]

Reply to this Message

Pietro - 26 Dec 2007 23:09 GMT

Her's the query id did,it gives me error : "Cannot update
'forms]![assignement]![agent';field not updatable"

UPDATE Cap SET Cap.[Forms]![Assignment]![agent] = "Assigned to"
WHERE (((Cap.ID) Between [Forms]![Assignment]![from] And
[Forms]![Assignment]![to]));

Note also that the fields FROM and TO don't refer to date fields but an
autonumber field.

> >Thank you for your reply...
> >Starting from "strSQL = "UPDATE CAP Set Agent..." the command is written in
[quoted text clipped - 5 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 26 Dec 2007 23:53 GMT

>Her's the query id did,it gives me error : "Cannot update
>'forms]![assignement]![agent';field not updatable"

What's the datatype of Agent? Is it a Lookup field??? Where is the agent name
actually stored - and what is it that you actually want to update? Remember:
*YOU* can see your database, and you know its structure; we don't!

>UPDATE Cap SET Cap.[Forms]![Assignment]![agent] = "Assigned to"
>WHERE (((Cap.ID) Between [Forms]![Assignment]![from] And
>[Forms]![Assignment]![to]));

Now... do you want to set the name of the agent to the text string "Assigned
To"? I'm not understanding your table structure at all, then.

>Note also that the fields FROM and TO don't refer to date fields but an
>autonumber field.

Should still work as you've written it.

John W. Vinson [MVP]

Reply to this Message

Pietro - 27 Dec 2007 10:10 GMT

Well,
I'll explain to you everything in details:
We are working in a call center environment,we receive customers requests
and we have to assign a certain number of cases for each agent,what I'm
trying to do is to write in the field AGENT, (Unbound textbox on the form
"Assignement"),the name of the agent to whom i want to assign a certain
number of cases thais certain number starts from the unbound Textbox FROM to
the unbound Textbox TO,so once i click on the button "Assign" the cases From
ID (autonumber field) ,for example 5223 to ID 5233 is assigned to the
selected agent.

> >Her's the query id did,it gives me error : "Cannot update
> >'forms]![assignement]![agent';field not updatable"
[quoted text clipped - 16 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 27 Dec 2007 17:09 GMT

>Well,
>I'll explain to you everything in details:
[quoted text clipped - 6 lines]
>ID (autonumber field) ,for example 5223 to ID 5233 is assigned to the
>selected agent.

Ok... that's the source of my confusion. A textbox IS NOT A FIELD! It's a
Control; fields are in Tables, controls are on Forms. I was working on the
assumption that you had a table field named Agent (in some table) which you
wanted to use to update another table.

Do the records with autonumber ID's 5223 through 5233 already exist in the
table? If so you need an Update query; if you want to create eleven new
records you need an Append query.

Also you're now saying there's a textbox AGENT in which you type the name of
the agent. In your original post you said there was a combo box. Which is it?

And what's the datatype of the field in the Assignement (correct spelling
Assignment if you care) table? Is it a text field with the agent's name, or
(probably preferable) a Number field with the numeric AgentID?

John W. Vinson [MVP]

Reply to this Message