Changing one field in an updateable query to a new value on a form--without querying

[John Baker]

HI:

I have a situation where I wish to enter a value in an unbound field on a form and then
change the same field in each record on the form so that it reflects the value entered.
THe form is suppored by an updatable query (linked to a table). I would rather not use a
query approach because it will result in a need for a new form and will create its own
complications (although this could be done) I would like to make the change so that it
reflects immediately on the form in each record. I am working with macros to accomplish
this, and am having a few problems when i get to the end of the table (somehow I am
running off the end).

Can anyone suggest an easy, non query, method to accomplish this?

Regards

John Baker

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If I understand you correctly, you can probably use the Control's
AfterUpdate event procedure to set the DefaultValue to whatever the
user entered. E.g.:

Private Sub MyControl_After Update()

Me!MyControl.De faultValue = Me!MyControl.Va lue

End Sub

HTH,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP/eBvoechKqOuFEgE QIAxgCfU+uMNAiz jMNnEGaQ0/nVrcQ/AYUAoNuy
xpwLljpIFdSHJKO iqZIC+/Dc
=BF9A
-----END PGP SIGNATURE-----
John Baker wrote:

HI:

I have a situation where I wish to enter a value in an unbound field on a form and then
change the same field in each record on the form so that it reflects the value entered.
THe form is suppored by an updatable query (linked to a table). I would rather not use a
query approach because it will result in a need for a new form and will create its own
complications (although this could be done) I would like to make the change so that it
reflects immediately on the form in each record. I am working with macros to accomplish
this, and am having a few problems when i get to the end of the table (somehow I am
running off the end).

Can anyone suggest an easy, non query, method to accomplish this?

Regards

John Baker

[Salad]

John Baker wrote:

HI:

I have a situation where I wish to enter a value in an unbound field on a form and then
change the same field in each record on the form so that it reflects the value entered.
THe form is suppored by an updatable query (linked to a table). I would rather not use a
query approach because it will result in a need for a new form and will create its own
complications (although this could be done) I would like to make the change so that it
reflects immediately on the form in each record. I am working with macros to accomplish
this, and am having a few problems when i get to the end of the table (somehow I am
running off the end).

Can anyone suggest an easy, non query, method to accomplish this?

Regards

John Baker

In the afterupdate event of the textfield, you could call an update query. Example below

Dim strSQL As String
strSQL = "UPDATE Table1 SET Table1.Item1 = [Forms]![MainForm]![TextField];"
Docmd.RunSQL strSQL

You could create the update query in the query builder if you are working with macros and
call the query instead. The above query updates ALL Item1 fields in Table1. You may want
to add filters if necessary.

[John Baker]

Thanks:

I tried that, and set it up thus:
Private Sub NewMod__AfterUp date()
Dim strSQL As String
strSQL = "UPDATE tblPOModsmaker SET tblPOModsmaker. MOD number =
[Forms]![Makemodq1]![NewMod#];"
DoCmd.RunSQL strSQL

End Sub
I am getting an error which says "Syntax error in update statement". Can you see the
syntax problem, because I cant!

Thanks

John

Salad <oi*@vinegar.co m> wrote:

John Baker wrote:

HI:

I have a situation where I wish to enter a value in an unbound field on a form and then
change the same field in each record on the form so that it reflects the value entered.
THe form is suppored by an updatable query (linked to a table). I would rather not use a
query approach because it will result in a need for a new form and will create its own
complications (although this could be done) I would like to make the change so that it
reflects immediately on the form in each record. I am working with macros to accomplish
this, and am having a few problems when i get to the end of the table (somehow I am
running off the end).

Can anyone suggest an easy, non query, method to accomplish this?

Regards

John Baker

In the afterupdate event of the textfield, you could call an update query. Example below

Dim strSQL As String
strSQL = "UPDATE Table1 SET Table1.Item1 = [Forms]![MainForm]![TextField];"
Docmd.RunSQL strSQL

You could create the update query in the query builder if you are working with macros and
call the query instead. The above query updates ALL Item1 fields in Table1. You may want
to add filters if necessary.

[John Baker]

HI:

Thank you for the response.

Iam not into VBA much, so the syntax often escapes me:

The table I wish to change is called: tblPOmodsmaker
The field is: MOD number
I wish to change it to a value that is in a form called: Makemodq1
The field on that form (in an unbounded field) is called:Newmod#
Could you spell out how it would be setup using the AfterUpdate procedure.

Thanks a million

John

MGFoster <[e-mail] > wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If I understand you correctly, you can probably use the Control's
AfterUpdate event procedure to set the DefaultValue to whatever the
user entered. E.g.:

Private Sub MyControl_After Update()

Me!MyControl.De faultValue = Me!MyControl.Va lue

End Sub

HTH,

MGFoster:::mgf 00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP/eBvoechKqOuFEgE QIAxgCfU+uMNAiz jMNnEGaQ0/nVrcQ/AYUAoNuy
xpwLljpIFdSHJK OiqZIC+/Dc
=BF9A
-----END PGP SIGNATURE-----
John Baker wrote:

HI:

I have a situation where I wish to enter a value in an unbound field on a form and then
change the same field in each record on the form so that it reflects the value entered.
THe form is suppored by an updatable query (linked to a table). I would rather not use a
query approach because it will result in a need for a new form and will create its own
complications (although this could be done) I would like to make the change so that it
reflects immediately on the form in each record. I am working with macros to accomplish
this, and am having a few problems when i get to the end of the table (somehow I am
running off the end).

Can anyone suggest an easy, non query, method to accomplish this?

Regards

John Baker

[Salad]

John Baker wrote:

Thanks:

I tried that, and set it up thus:

Private Sub NewMod__AfterUp date()
Dim strSQL As String
strSQL = "UPDATE tblPOModsmaker SET tblPOModsmaker. MOD number =
[Forms]![Makemodq1]![NewMod#];"
DoCmd.RunSQL strSQL

End Sub

I am getting an error which says "Syntax error in update statement". Can you see the
syntax problem, because I cant!

Thanks

Well....MOD is function that can get the modulus between 2 numbers. And I NEVER create
table/field names with spaces, dashes, underscores or whatever. Ex: You use Mod Number. I
use ModNumber. That way I avoid extra keystrokes (the need to surround by []'s) and
frankly...it looks better. But let's look at your statement.

Mod Number should be tblPOModsmaker.[MOD number].

If this was a query, it'd run fine. However, in VBA you need to parse data out so Access knows
what to do. Ex: Access flunks on [Forms]![Makemodq1]![NewMod#] when it is contained in the
string. It has NO idea what the heck that is. But...if you change it to
strSQL = "UPDATE tblPOModsmaker " & _
"SET tblPOModsmaker.[MOD number] = " & [Forms]![Makemodq1]![NewMod#]
then that should work....except if
newmod# is a string or date.

Ex "Where DateField = #" & Date & "#"
Ex: "Where MyName = '" & varName & "'"

Did you see where name is quote, single quote, quote. If you know that a text filed could
contain double or single quotes, you need to futz with that. There is discussion on quotes in
the Access Developers Guide. You can also check google for that.

In my example, I figure NewMod# is a number. No quotes or #'s are needed

[John Baker]

Thank you vey much.

John baker

Salad <oi*@vinegar.co m> wrote:

John Baker wrote:

Thanks:

I tried that, and set it up thus:

Private Sub NewMod__AfterUp date()
Dim strSQL As String
strSQL = "UPDATE tblPOModsmaker SET tblPOModsmaker. MOD number =
[Forms]![Makemodq1]![NewMod#];"
DoCmd.RunSQL strSQL

End Sub

I am getting an error which says "Syntax error in update statement". Can you see the
syntax problem, because I cant!

Thanks

Well....MOD is function that can get the modulus between 2 numbers. And I NEVER create
table/field names with spaces, dashes, underscores or whatever. Ex: You use Mod Number. I
use ModNumber. That way I avoid extra keystrokes (the need to surround by []'s) and
frankly...it looks better. But let's look at your statement.

Mod Number should be tblPOModsmaker.[MOD number].

If this was a query, it'd run fine. However, in VBA you need to parse data out so Access knows
what to do. Ex: Access flunks on [Forms]![Makemodq1]![NewMod#] when it is contained in the
string. It has NO idea what the heck that is. But...if you change it to
strSQL = "UPDATE tblPOModsmaker " & _
"SET tblPOModsmaker.[MOD number] = " & [Forms]![Makemodq1]![NewMod#]
then that should work....except if
newmod# is a string or date.

Ex "Where DateField = #" & Date & "#"
Ex: "Where MyName = '" & varName & "'"

Did you see where name is quote, single quote, quote. If you know that a text filed could
contain double or single quotes, you need to futz with that. There is discussion on quotes in
the Access Developers Guide. You can also check google for that.

In my example, I figure NewMod# is a number. No quotes or #'s are needed