select one record in update query

[Guest]

TI

I have a Client form (with a Status subform) that records client infomaiton to both Client and Status tables (one to many if it matters). When the user is finished entering the information a comand button is clicked (saving the data an opening a new form). Before the form opens I want to run an action query to update some fields. I do this by using DoCmd.OpenQuery which works, but runs the query against all the records in the database when I only need it to update the current one

Alternatively I can hand set the criteria field in the query to the exact StatusID field I need and then run the criteria but that is impractical

So how can I select the StatusID based on the current form (subform actualy) and update just that record

Michael

 

[Allen Browne]

Create a string representing the query statement you want to run, and
Execute the string.

Example:

Dim strSQL As String

strSQL = "UPDATE [MyTable] SET [MyField] = 999 WHERE [StatusID] = " &
Me.[NameOfYourSubform].Form!StatusID & ";"

dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

TIA

I have a Client form (with a Status subform) that records client

infomaiton to both Client and Status tables (one to many if it matters).
When the user is finished entering the information a comand button is
clicked (saving the data an opening a new form). Before the form opens I
want to run an action query to update some fields. I do this by using
DoCmd.OpenQuery which works, but runs the query against all the records in
the database when I only need it to update the current one.

Alternatively I can hand set the criteria field in the query to the exact

StatusID field I need and then run the criteria but that is impractical.

So how can I select the StatusID based on the current form (subform

actualy) and update just that record?

 

[Guest]

Allen

Thanks for your help. My where clause follow
WHERE (((Status.StatusID)=[Me].[StatusSubForm].[Form]![StatusID]))

When I press the command button I get an "Enter Parameter Value" Message bo
asking me to enter Me.StatusSubForm.Form!StatusI

The command button is located on the form not the subform. Will that make a difference

TI

Michae

----- Allen Browne wrote: ----

Create a string representing the query statement you want to run, an
Execute the string

Example

Dim strSQL As Strin

strSQL = "UPDATE [MyTable] SET [MyField] = 999 WHERE [StatusID] = "
Me.[NameOfYourSubform].Form!StatusID & ";

dbEngine(0)(0).Execute strSQL, dbFailOnErro

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

TI

infomaiton to both Client and Status tables (one to many if it matters)
When the user is finished entering the information a comand button i
clicked (saving the data an opening a new form). Before the form opens
want to run an action query to update some fields. I do this by usin
DoCmd.OpenQuery which works, but runs the query against all the records i
the database when I only need it to update the current one

 

[Allen Browne]

1. Did you concatenate the value from the control into the string (as in the
example)? That's different from just including a reference to the control
within the string.

2. If so, check the Name of the subform control. It can be different from
the name of the form in it contains (its SourceObject). To check, open the
main form in design view, right-click the edge of the subform, choose
Properties, and look at the Name (on the "Other" tab).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for your help. My where clause follows
WHERE (((Status.StatusID)=[Me].[StatusSubForm].[Form]![StatusID]));

When I press the command button I get an "Enter Parameter Value" Message box
asking me to enter Me.StatusSubForm.Form!StatusID

The command button is located on the form not the subform. Will that make a difference?

TIA

Michael

----- Allen Browne wrote: -----

Create a string representing the query statement you want to run, and
Execute the string.

Example:

Dim strSQL As String

strSQL = "UPDATE [MyTable] SET [MyField] = 999 WHERE [StatusID] = " &
Me.[NameOfYourSubform].Form!StatusID & ";"

dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

TIA

infomaiton to both Client and Status tables (one to many if it matters).
When the user is finished entering the information a comand button is
clicked (saving the data an opening a new form). Before the form opens I
want to run an action query to update some fields. I do this by using
DoCmd.OpenQuery which works, but runs the query against all the records in
the database when I only need it to update the current one. the exact
StatusID field I need and then run the criteria but that is impractical. (subform
actualy) and update just that record?

 

[Guest]

1. Did you concatenate the value from the control into the string (as in th
example)? That's different from just including a reference to the contro
within the string

Well I thought so but I still have the same problem. I opened the SQL view and copy and pasted your example of the where clause into the last line of the SQL statement. I deleted the old semicolon in the line above. I then followed your instructions for 2. and copy and pasted the subform's name form the actual name field to eliminate typos. I still get the same message. So my guess is that I haven't concatenated properly. Can you please be more specific

Thank you

Michae

----- Allen Browne wrote: ----

1. Did you concatenate the value from the control into the string (as in th
example)? That's different from just including a reference to the contro
within the string

2. If so, check the Name of the subform control. It can be different fro
the name of the form in it contains (its SourceObject). To check, open th
main form in design view, right-click the edge of the subform, choos
Properties, and look at the Name (on the "Other" tab)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

Allen

Thanks for your help. My where clause follow

WHERE (((Status.StatusID)=[Me].[StatusSubForm].[Form]![StatusID]))

When I press the command button I get an "Enter Parameter Value" Messag

bo
asking me to enter Me.StatusSubForm.Form!StatusI

The command button is located on the form not the subform. Will that mak a difference
TI
Michae
----- Allen Browne wrote: ----
Create a string representing the query statement you want to run, an Execute the string
Example
Dim strSQL As Strin
strSQL = "UPDATE [MyTable] SET [MyField] = 999 WHERE [StatusID] = " &> Me.[NameOfYourSubform].Form!StatusID & ";
dbEngine(0)(0).Execute strSQL, dbFailOnErro
--

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

TI

infomaiton to both Client and Status tables (one to many if i matters)
When the user is finished entering the information a comand button i
clicked (saving the data an opening a new form). Before the for opens
want to run an action query to update some fields. I do this b usin
DoCmd.OpenQuery which works, but runs the query against all th records i
the database when I only need it to update the current one the exac
StatusID field I need and then run the criteria but that i impractical (subfor
actualy) and update just that record

 

[Allen Browne]

The idea is to create a string.
The string is delimited with quote marks.
Close the string with a closing quote.
Use ampersand to add on the value to be joined into the string.
Then use another ampersand to add on some more.
Open the quotes to enter the rest of the string. (In this case the
semicolon).
That's all on one line in Access: sometimes the lines break here in the
newsgroups.

If the field named in the WHERE clause is of type Text, you need extra
quotes as well. If it is of type Date, you need the # delimiter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

1. Did you concatenate the value from the control into the string (as in the
example)? That's different from just including a reference to the control
within the string.

Well I thought so but I still have the same problem. I opened the SQL

view and copy and pasted your example of the where clause into the last line
of the SQL statement. I deleted the old semicolon in the line above. I
then followed your instructions for 2. and copy and pasted the subform's
name form the actual name field to eliminate typos. I still get the same
message. So my guess is that I haven't concatenated properly. Can you
please be more specific?

Thank you.

Michael

----- Allen Browne wrote: -----

1. Did you concatenate the value from the control into the string (as in the
example)? That's different from just including a reference to the control
within the string.

2. If so, check the Name of the subform control. It can be different from
the name of the form in it contains (its SourceObject). To check, open the
main form in design view, right-click the edge of the subform, choose
Properties, and look at the Name (on the "Other" tab).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for your help. My where clause follows

WHERE (((Status.StatusID)=[Me].[StatusSubForm].[Form]![StatusID]));

When I press the command button I get an "Enter Parameter Value"

Message

box
asking me to enter Me.StatusSubForm.Form!StatusID

that make
a difference?

TIA
Michael
----- Allen Browne wrote: -----
Create a string representing the query statement you want to

run, and
Execute the string.

Example:
Dim strSQL As String
strSQL = "UPDATE [MyTable] SET [MyField] = 999 WHERE [StatusID] = " &> Me.[NameOfYourSubform].Form!StatusID & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError
--

Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

TIA
I have a Client form (with a Status subform) that records client

infomaiton to both Client and Status tables (one to many if it matters).
When the user is finished entering the information a comand button is
clicked (saving the data an opening a new form). Before the

form
opens I

want to run an action query to update some fields. I do this

by

using
DoCmd.OpenQuery which works, but runs the query against all

the
records in

the database when I only need it to update the current one. the exact
StatusID field I need and then run the criteria but that is impractical. (subform
actualy) and update just that record?