Save data entered when using [enter a value:] in SQL

[Stuart E. Wugalter]

Hello All!

I have a button on a form that is tied to the following query:

UPDATE tblSEQTAQ AS A
SET A.taqcall = [Enter a Value for Allele 1:]
WHERE (((A.taqcall)=" Allele 1"));

The user looks at the form and decides to assign a code for the text Allele
1. It works fine.
The user now wants to see something like this on the form:

Allele 1: 2 2

2 2 was the text she typed in that replaced the text Allele 1 in the above
query. "Allele1:" above is a label. Is there a way to "store" the value she
entered (2 2) and display it?

The only solution I have thought of so far is to create a table that will
store these values and then use that table for a data source. This would
require the above query to write the values to two tables at once. I have
tried this unsuccessfully.

Any help will be greatly appreciated.

Stuart E. Wugalter
Zilkha Neurogenetic Institute
Keck School of Medicine
University of Southern California
wugalter@usc.ed u

[Olaf Rabbachin]

Re: Save data entered when using [enter a value:] in SQL

Hi,

On Fri, 19 Dec 2003 14:38:56 -0800, Stuart E. Wugalter wrote:
[color=blue]
> I have a button on a form that is tied to the following query:
>
> UPDATE tblSEQTAQ AS A
> SET A.taqcall = [Enter a Value for Allele 1:]
> WHERE (((A.taqcall)=" Allele 1"));
>
> The user looks at the form and decides to assign a code for the text Allele
> 1. It works fine.
> The user now wants to see something like this on the form:
>
> Allele 1: 2 2
>
> 2 2 was the text she typed in that replaced the text Allele 1 in the above
> query. "Allele1:" above is a label. Is there a way to "store" the value she
> entered (2 2) and display it?[/color]

I suggest using a little code for that.
Instead of using an inputbox (provided via your parameter), query that
value from a small sub (quick-hack!):

sub UpdateData
dim varUserInput as variant

'get user's input
varuserinput = _
UserQuery(vbcrl f & "Enter a Value for Allel1 1:", _
"Data-Update")

'anything entered?
if not isnull(varuseri nput) then
dim db as dao.database
db.execute
"UPDATE tblSEQTAQ AS A " & _
"SET A.taqcall = " & varuserinput & " " & _
"WHERE A.taqcall = " & chr(34) & Allele 1 & chr(34)

msgbox cstr(db.records affected) & " record(s) have been updated.", _
vbinformation," Data-update: Allele 1"

'here, you could transfer the user's input i.e. to a control on your form:
me!lblMyLabel.c aption=varuseri nput

set db=nothing
endif
end sub

The function UserQuery used above is from a sample that you may download
from my website (see sig) and which acts as a little more convenient
InputBox. However, using InputBox would be an alternative as well.

Cheers,
Olaf [MVP]
--
My .02: www.Resources.IntuiDev.com

[Stuart E. Wugalter]

Re: Save data entered when using [enter a value:] in SQL

Thank you Olaf. I am just learning VBA for Access, but I will try your code
and see if it works. In the mean time, if anyone knows of an SQL solution,
then please let me know. Thanks again, Stuart


"Olaf Rabbachin" <Olaf_NoSpam@In tuiDev.com> wrote in message
news:bs045l$u09 $07$1@news.t-online.com...[color=blue]
> Hi,
>
> On Fri, 19 Dec 2003 14:38:56 -0800, Stuart E. Wugalter wrote:
>[color=green]
> > I have a button on a form that is tied to the following query:
> >
> > UPDATE tblSEQTAQ AS A
> > SET A.taqcall = [Enter a Value for Allele 1:]
> > WHERE (((A.taqcall)=" Allele 1"));
> >
> > The user looks at the form and decides to assign a code for the text[/color][/color]
Allele[color=blue][color=green]
> > 1. It works fine.
> > The user now wants to see something like this on the form:
> >
> > Allele 1: 2 2
> >
> > 2 2 was the text she typed in that replaced the text Allele 1 in the[/color][/color]
above[color=blue][color=green]
> > query. "Allele1:" above is a label. Is there a way to "store" the value[/color][/color]
she[color=blue][color=green]
> > entered (2 2) and display it?[/color]
>
> I suggest using a little code for that.
> Instead of using an inputbox (provided via your parameter), query that
> value from a small sub (quick-hack!):
>
> sub UpdateData
> dim varUserInput as variant
>
> 'get user's input
> varuserinput = _
> UserQuery(vbcrl f & "Enter a Value for Allel1 1:", _
> "Data-Update")
>
> 'anything entered?
> if not isnull(varuseri nput) then
> dim db as dao.database
> db.execute
> "UPDATE tblSEQTAQ AS A " & _
> "SET A.taqcall = " & varuserinput & " " & _
> "WHERE A.taqcall = " & chr(34) & Allele 1 & chr(34)
>
> msgbox cstr(db.records affected) & " record(s) have been updated.", _
> vbinformation," Data-update: Allele 1"
>
> 'here, you could transfer the user's input i.e. to a control on your[/color]
form:[color=blue]
> me!lblMyLabel.c aption=varuseri nput
>
> set db=nothing
> endif
> end sub
>
> The function UserQuery used above is from a sample that you may download
> from my website (see sig) and which acts as a little more convenient
> InputBox. However, using InputBox would be an alternative as well.
>
> Cheers,
> Olaf [MVP]
> --
> My .02: www.Resources.IntuiDev.com[/color]

[Pieter Linden]

Re: Save data entered when using [enter a value:] in SQL

"Stuart E. Wugalter" <wugalter@usc.e du> wrote in message news:<brvusd$ln 6$1@gist.usc.ed u>...[color=blue]
> Hello All!
>
> I have a button on a form that is tied to the following query:
>
> UPDATE tblSEQTAQ AS A
> SET A.taqcall = [Enter a Value for Allele 1:]
> WHERE (((A.taqcall)=" Allele 1"));
>
> The user looks at the form and decides to assign a code for the text Allele
> 1. It works fine.
> The user now wants to see something like this on the form:
>
> Allele 1: 2 2
>
> 2 2 was the text she typed in that replaced the text Allele 1 in the above
> query. "Allele1:" above is a label. Is there a way to "store" the value she
> entered (2 2) and display it?
>
> The only solution I have thought of so far is to create a table that will
> store these values and then use that table for a data source. This would
> require the above query to write the values to two tables at once. I have
> tried this unsuccessfully.
>
> Any help will be greatly appreciated.
>
> Stuart E. Wugalter
> Zilkha Neurogenetic Institute
> Keck School of Medicine
> University of Southern California
> wugalter@usc.ed u[/color]

Probably a LOT easier if you use an unbound form to grab the value.
Then you could do something like (1) write the value to a table; (2)
execute the query.

For (1) you could either open an recordset based on the table and
insert a record or do something like....

CurrentDB.Execu te "INSERT INTO MyTable(Field1, Field2) VALUES (" &
Forms!MyForm!Tx tField1 & ",'& Forms!MyForm!nu mField2
&");",dbFailOnE rror

DoCmd.OpenQuery def("MyQuery")

....