storing a value in an unbound table

  • Thread starter Thread starter terry w
  • Start date Start date
T

terry w

Hello
When I change the value in my textbox txtValue, I want to store its value in
a table tblStorage NOT bound to my form. Can some kind soul point out to
this Access newbie how to do that. What do I put in the txtValue's
AfterUpdate event?

grateful for any help
 
Hello
When I change the value in my textbox txtValue, I want to store its value in
a table tblStorage NOT bound to my form. Can some kind soul point out to
this Access newbie how to do that. What do I put in the txtValue's
AfterUpdate event?

grateful for any help
Click to expand...

What's the context? WHY do you want to do this? Would a small subform based on
tblStorage (with just one textbox in it) be suitable?

If you do want to do it in code... do you want to add a new record into
tblStorage each time, or update an existing record - if so which?
 
thank you for responding

I just want a way to retain this value of txtValue in tblStorage so that I
can retrieve it the next time I open my application. (tblStorage only has
one cell). txtValue will have a value of 1,2,3 or 4, representing various
configuration options for my form. The next time my form opens, I'll use
DLookup to retrieve the value. I'm just not sure how to store the value of
txtValue in tblStorage.
Click to expand...

If the table has only the one record (Access has records, not 'cells')
and you just wish to keep track of that one number value, code the
control's AfterUpdate event:

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = " &
Me.[txtValue], dbFailOnError

Change FieldName to whatever the actual name of the table field is.
It's assumed the datatype of [FieldName] is a Number datatype, not
Text.
 
thanks fredg - that's exactly what I needed. For my own education, how would
the syntax be changed if the datatype was String?

terry

fredg said:
thank you for responding

I just want a way to retain this value of txtValue in tblStorage so that I
can retrieve it the next time I open my application. (tblStorage only has
one cell). txtValue will have a value of 1,2,3 or 4, representing various
configuration options for my form. The next time my form opens, I'll use
DLookup to retrieve the value. I'm just not sure how to store the value of
txtValue in tblStorage.
Click to expand...

If the table has only the one record (Access has records, not 'cells')
and you just wish to keep track of that one number value, code the
control's AfterUpdate event:

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = " &
Me.[txtValue], dbFailOnError

Change FieldName to whatever the actual name of the table field is.
It's assumed the datatype of [FieldName] is a Number datatype, not
Text.
Click to expand...
 
You would need to add quotes around me.txtValue. One method of doing
that is shown below.

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = " &
Chr(34) & Me.[txtValue] & Chr(34), dbFailOnError

IF it were a DATE field then you would need toa dd date delimiters. One
method to do that would be

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = " &
Foramt(Me.[txtValue],"\#yyyy\-mm\-dd\#"), dbFailOnError

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


terry said:
thanks fredg - that's exactly what I needed. For my own education, how would
the syntax be changed if the datatype was String?

terry

fredg said:
thank you for responding

I just want a way to retain this value of txtValue in tblStorage so that I
can retrieve it the next time I open my application. (tblStorage only has
one cell). txtValue will have a value of 1,2,3 or 4, representing various
configuration options for my form. The next time my form opens, I'll use
DLookup to retrieve the value. I'm just not sure how to store the value of
txtValue in tblStorage.

:

Hello
When I change the value in my textbox txtValue, I want to store its value in
a table tblStorage NOT bound to my form. Can some kind soul point out to
this Access newbie how to do that. What do I put in the txtValue's
AfterUpdate event?

grateful for any help
What's the context? WHY do you want to do this? Would a small subform based on
tblStorage (with just one textbox in it) be suitable?

If you do want to do it in code... do you want to add a new record into
tblStorage each time, or update an existing record - if so which?
Click to expand...
If the table has only the one record (Access has records, not 'cells')
and you just wish to keep track of that one number value, code the
control's AfterUpdate event:

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = " &
Me.[txtValue], dbFailOnError

Change FieldName to whatever the actual name of the table field is.
It's assumed the datatype of [FieldName] is a Number datatype, not
Text.
Click to expand...
Click to expand...
 
thanks fredg - that's exactly what I needed. For my own education, how would
the syntax be changed if the datatype was String?

terry

fredg said:
thank you for responding

I just want a way to retain this value of txtValue in tblStorage so that I
can retrieve it the next time I open my application. (tblStorage only has
one cell). txtValue will have a value of 1,2,3 or 4, representing various
configuration options for my form. The next time my form opens, I'll use
DLookup to retrieve the value. I'm just not sure how to store the value of
txtValue in tblStorage.

:

Hello
When I change the value in my textbox txtValue, I want to store its value in
a table tblStorage NOT bound to my form. Can some kind soul point out to
this Access newbie how to do that. What do I put in the txtValue's
AfterUpdate event?

grateful for any help

What's the context? WHY do you want to do this? Would a small subform based on
tblStorage (with just one textbox in it) be suitable?

If you do want to do it in code... do you want to add a new record into
tblStorage each time, or update an existing record - if so which?
Click to expand...

If the table has only the one record (Access has records, not 'cells')
and you just wish to keep track of that one number value, code the
control's AfterUpdate event:

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = " &
Me.[txtValue], dbFailOnError

Change FieldName to whatever the actual name of the table field is.
It's assumed the datatype of [FieldName] is a Number datatype, not
Text.
Click to expand...
Click to expand...

The actual string value needs to be enclosed within quotes, "Smith"

CurrentDb.Execute "Update tblStorage Set tblStorage.[FieldName] = """
& Me.[txtValue] & """", dbFailOnError

For clarity that's
" " " & Me.[txtValue] & " " " "

Date values need to be enclosed within the date delimiter symbol #

[DateField] = #" & [DateVariable] & "#"

For more information, look up in VBA help:
Restrict Data to a Subset of Records
 
Back
Top