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
wu******@usc.edu

[Olaf Rabbachin]

Hi,

On Fri, 19 Dec 2003 14:38:56 -0800, Stuart E. Wugalter wrote:

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?

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(vbcrlf & "Enter a Value for Allel1 1:", _
"Data-Update")

'anything entered?
if not isnull(varuserinput) 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.recordsaffected) & " 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.caption=varuserinput

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]

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" <Ol*********@IntuiDev.com> wrote in message
news:bs*************@news.t-online.com...

Hi,

On Fri, 19 Dec 2003 14:38:56 -0800, Stuart E. Wugalter wrote:

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?
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(vbcrlf & "Enter a Value for Allel1 1:", _
"Data-Update")

'anything entered?
if not isnull(varuserinput) 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.recordsaffected) & " 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.caption=varuserinput

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

[Pieter Linden]

"Stuart E. Wugalter" <wu******@usc.edu> wrote in message news:<br**********@gist.usc.edu>...

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
wu******@usc.edu

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.Execute "INSERT INTO MyTable(Field1,Field2) VALUES (" &
Forms!MyForm!TxtField1 & ",'& Forms!MyForm!numField2
&");",dbFailOnError

DoCmd.OpenQuerydef("MyQuery")

....