Criteria to Select Field to Update

[tighe]

all,

i am looking for a way in an update query to variably select the field that
will be updated in the table. the infomation will always be from the same
control on a form but depening on a combo box "type" a different field will
need to be updated.

I have tried this two ways.
One to use an multi iif statement in the fileld selection:
IIf([Type]="Branch",[Branch],IIf([Type]="k1",[k1]... this kept returning
errors: not a valid name, invalid characters or punctuation or too long, the
field is too small( this is when i added the form reference for type,
"[Forms]![edit_process]![Type]=")

two have the criteria for each field from the table i might update to have a
criteria for itself similar to the above in the hopes that the field would
only be updated if the criteria was met. this didnt work at all because, of
course, that is not how an update query works.

of course these were just my ideas for a solution and hope that i'm not
wasting time with a double post, couldn't find anything similar to this
situation i would prefer not to have a different query for each "Type"
instead would just keep each field through the process, letting my users see
the Type they are working on.

Thanks for all the help past and present.

 

[KARL DEWEY]

UNTESTED UNTESTED UNTESTED
I think you can do what you want by putting the IIF statement in the
UpdateTo row like this --
FIELD: BRANCH TYPE
TABLE: YourTbl
YourTbl
Update To: IIf([Field]<>"Branch","New Data", [Branch])
IIf([Field]<>"Type","New Data", [Type])

In other words if the field is not selected for update then it is update
with the contents of what is in the field now.

 

[John Spencer]

Probably the best way would be to build the SQL string in VBA and execute that
string

Does the combobox return the name field to be updated or something else?

Here is a basic snippet of code that you might include behind a button on the
form. Caution (UNTESTED code follows)

Private Sub ButtonUpdate_Click()
Dim strSQL as String
Dim db as DAO.Database

strSQL = "UPDATE [YourTableName]" & _
" SET [" & Me.NameOfCombox & "] = """ & Me.SomeTextControl & """

Set db = CurrentDb()

db.Execute strSQL, dbFailOnError

Msgbox "Updated " & db.recordsaffected & " records to " & me.SomeTextControl

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[tighe]

That did the trick.
thanks.

UNTESTED UNTESTED UNTESTED
I think you can do what you want by putting the IIF statement in the
UpdateTo row like this --
FIELD: BRANCH TYPE
TABLE: YourTbl
YourTbl
Update To: IIf([Field]<>"Branch","New Data", [Branch])
IIf([Field]<>"Type","New Data", [Type])

In other words if the field is not selected for update then it is update
with the contents of what is in the field now.

--
Build a little, test a little.

all,

i am looking for a way in an update query to variably select the field that
will be updated in the table. the infomation will always be from the same
control on a form but depening on a combo box "type" a different field will
need to be updated.

I have tried this two ways.
One to use an multi iif statement in the fileld selection:
IIf([Type]="Branch",[Branch],IIf([Type]="k1",[k1]... this kept returning
errors: not a valid name, invalid characters or punctuation or too long, the
field is too small( this is when i added the form reference for type,
"[Forms]![edit_process]![Type]=")

two have the criteria for each field from the table i might update to have a
criteria for itself similar to the above in the hopes that the field would
only be updated if the criteria was met. this didnt work at all because, of
course, that is not how an update query works.

of course these were just my ideas for a solution and hope that i'm not
wasting time with a double post, couldn't find anything similar to this
situation i would prefer not to have a different query for each "Type"
instead would just keep each field through the process, letting my users see
the Type they are working on.

Thanks for all the help past and present.

 

[John W. Vinson]

all,

i am looking for a way in an update query to variably select the field that
will be updated in the table. the infomation will always be from the same
control on a form but depening on a combo box "type" a different field will
need to be updated.

I have tried this two ways.
One to use an multi iif statement in the fileld selection:
IIf([Type]="Branch",[Branch],IIf([Type]="k1",[k1]... this kept returning
errors: not a valid name, invalid characters or punctuation or too long, the
field is too small( this is when i added the form reference for type,
"[Forms]![edit_process]![Type]=")

The root of the problem is that it appears that you have a different field in
the table for each Type. This is incorrect table design!!! You should NOT be
storing data (a type) in a fieldname, or storing the same kind of data in
several different fields.

What is the actual structure of your table? What are these fields?