By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----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_AfterUpdate()

Me!MyControl.DefaultValue = Me!MyControl.Value

End Sub

HTH,

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

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

iQA/AwUBP/eBvoechKqOuFEgEQIAxgCfU+uMNAizjMNnEGaQ0/nVrcQ/AYUAoNuy
xpwLljpIFdSHJKOiqZIC+/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


Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
Thanks:

I tried that, and set it up thus:
Private Sub NewMod__AfterUpdate()
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.com> 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.


Nov 12 '05 #4

P: n/a
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 <me@privacy.com> 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_AfterUpdate()

Me!MyControl.DefaultValue = Me!MyControl.Value

End Sub

HTH,

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

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

iQA/AwUBP/eBvoechKqOuFEgEQIAxgCfU+uMNAizjMNnEGaQ0/nVrcQ/AYUAoNuy
xpwLljpIFdSHJKOiqZIC+/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


Nov 12 '05 #5

P: n/a
John Baker wrote:
Thanks:

I tried that, and set it up thus:

Private Sub NewMod__AfterUpdate()
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

Nov 12 '05 #6

P: n/a
Thank you vey much.

John baker

Salad <oi*@vinegar.com> wrote:
John Baker wrote:
Thanks:

I tried that, and set it up thus:

Private Sub NewMod__AfterUpdate()
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


Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.