How to save unbound field text to table?

deko

I need to create different recordsets based on queries that use data from
unbound fields in a form. I've discovered that I can't do this, and instead
need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that
way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

Nov 12 '05 #1

Stewart Allen

Look at the CREATE TABLE statement in the help file.
Once the table is created with the fields you require, save the value of the
unbound field there.

Stewart

"deko" <dj****@hotmail.com> wrote in message
news:jS******************@newssvr25.news.prodigy.c om...

I need to create different recordsets based on queries that use data from
unbound fields in a form. I've discovered that I can't do this, and instead need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that
way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

Nov 12 '05 #2

Chowdhury, Ziaul

Create a table with field you required
then refer FORM to save value

Example
INSERT INTO TABLENAME
VALUES(FRM![EMPNO], FRM![XX])
"Stewart Allen" <sa****@REMOVETHISwave.co.nz> wrote in message
news:bl**********@news.wave.co.nz...

Look at the CREATE TABLE statement in the help file.
Once the table is created with the fields you require, save the value of the unbound field there.

Stewart

"deko" <dj****@hotmail.com> wrote in message
news:jS******************@newssvr25.news.prodigy.c om...

I need to create different recordsets based on queries that use data from unbound fields in a form. I've discovered that I can't do this, and

instead

need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

Nov 12 '05 #3

Larry Linson

How many of these little one-record tables are you likely to spread around,
cluttering your database, and how in the world do you plan to manage and
actually use that many tables? I suggest you reconsider: an Append query
will not overwrite something -- not unless there's some humungous bug in
Access that we don't know about.

Larry Linson
Microsoft Access MVP

"deko" <dj****@hotmail.com> wrote in message
news:jS******************@newssvr25.news.prodigy.c om...

I need to create different recordsets based on queries that use data from
unbound fields in a form. I've discovered that I can't do this, and instead need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that
way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

Nov 12 '05 #4

deko

Thanks for your comments -- I would like to find a better solution, to be
sure. I'm all ears if you have an alternative.

However, an Append query would require that I first delete whatever is in
the "temp" table (otherwise how do I know which row I want); an Update query
requires that a row already exists. With a Make Table query, the table can
be created and the old table overwritten in one operation.

So there would be only one temp table. I'd have several Make Table queries,
but each would create a table with the same name, overwriting any previously
created table. The purpose here is to create a recordset based on a query
that uses unbound data as a parameter -- once that is done, the temp table
can be overwritten with new data (for a different recordset operation).
"Larry Linson" <bo*****@localhost.net> wrote in message
news:MX*******************@nwrddc02.gnilink.net...

How many of these little one-record tables are you likely to spread around, cluttering your database, and how in the world do you plan to manage and
actually use that many tables? I suggest you reconsider: an Append query
will not overwrite something -- not unless there's some humungous bug in
Access that we don't know about.

Larry Linson
Microsoft Access MVP

"deko" <dj****@hotmail.com> wrote in message
news:jS******************@newssvr25.news.prodigy.c om...

I need to create different recordsets based on queries that use data from unbound fields in a form. I've discovered that I can't do this, and

instead

need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

Nov 12 '05 #5

Pieter Linden

"deko" <dj****@hotmail.com> wrote in message news:<jS******************@newssvr25.news.prodigy. com>...

I need to create different recordsets based on queries that use data from
unbound fields in a form. I've discovered that I can't do this, and instead
need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that
way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

If you're going to analyze this stuff, you almost have to write it to
a table. Do your data sets have a consistent structure? If you want
to write from an unbound form to a *single* table, your best bet is to
bind the form to the table. That way the database engine does all the
work for you. If you don't like that, you can either use the Execute
statement off the database object... but it's SLOW! Or you could use
a recordset object... Just wondering, but why do you want to be able
to create so many tables? How are you going to analyze them if your
data is all over the place? A union query? (That's gonna get kinda
ugly!)

Nov 12 '05 #6

deko

Thanks for the reply. I suppose I was not entirely clear in my first
post... please see recent reply to Larry Linson's post for clarification.

In any case, I think you've hit it on the head - the way to go is to bind
the data to a form, rather than trying to work with unbound data.

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

"deko" <dj****@hotmail.com> wrote in message

news:<jS******************@newssvr25.news.prodigy. com>...

I need to create different recordsets based on queries that use data from unbound fields in a form. I've discovered that I can't do this, and instead need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

If you're going to analyze this stuff, you almost have to write it to
a table. Do your data sets have a consistent structure? If you want
to write from an unbound form to a *single* table, your best bet is to
bind the form to the table. That way the database engine does all the
work for you. If you don't like that, you can either use the Execute
statement off the database object... but it's SLOW! Or you could use
a recordset object... Just wondering, but why do you want to be able
to create so many tables? How are you going to analyze them if your
data is all over the place? A union query? (That's gonna get kinda
ugly!)

Nov 12 '05 #7

deko

Here's what now have (working okay):
==>First, Update tblOutput with some criteria:

UPDATE tblOutput SET Wildcard = Forms!frmMain!frmCn!FindString;

==>Then, look for FindString through a number of stored queries based on
user criteria, for example:

SELECT [tblEntity].[Entity_ID]
FROM tblEntity
WHERE (([tblEntity].[Company]) IN (SELECT Wildcard FROM tblOutput))
OR (([tblEntity].[LastName]) IN (SELECT Wildcard FROM tblOutput))
OR (([tblEntity].[FirstName]) IN (SELECT Wildcard FROM tblOutput))
OR (([tblEntity].[Salutation]) IN (SELECT Wildcard FROM tblOutput))
OR (([tblEntity].[Title]) IN (SELECT Wildcard FROM tblOutput))

==>Then, create the recordset:

If Me.Condition=True Then Set rst = CurrentDb.OpenRecordset("SELECT
tblTable.Row FROM tblTable WHERE ((tblTable.Entity_ID) IN (SELECT Entity_ID
FROM " & strSearchQry & "))")

==>"strSearchQry" is one of several stored queries that are run based on
user selected criteria.

==>"tblTable.Entity_ID" is a foreign key from tblEntity

Now that I have all the tblTable.Rows (that were defined by user input in an
unbound form field) in a recordset, I can do all kinds of cool stuff. As
far as I know, the above routine is the only way to accomplish this. Bottom
Line: unbound data is a bad thing.
"Larry Linson" <bo*****@localhost.net> wrote in message
news:MX*******************@nwrddc02.gnilink.net...

How many of these little one-record tables are you likely to spread around, cluttering your database, and how in the world do you plan to manage and
actually use that many tables? I suggest you reconsider: an Append query
will not overwrite something -- not unless there's some humungous bug in
Access that we don't know about.

Larry Linson
Microsoft Access MVP

"deko" <dj****@hotmail.com> wrote in message
news:jS******************@newssvr25.news.prodigy.c om...

I need to create different recordsets based on queries that use data from unbound fields in a form. I've discovered that I can't do this, and

instead

need to save the data in question (usually a text string) to a table.

I'm thinking the best way to do this is to use a Make Table query -- that way the table stays small -- one row -- and there is less chance for
something to get overwritten.

But how to create a make table query from unbound data?

SELECT Forms!frmMain!frmCn!FindString INTO tblSearch; -- this fails....

any suggestions welcome!

thx in advance!

Nov 12 '05 #8

Larry Linson

"deko" wrote

Bottom Line: unbound data is a bad thing.

In the vast majority of cases, I agree with you on this.

I've worked on a few unbound data-entry / editing forms, all written by
someone else, all in some client's database, and <SIGH> none of which any of
those clients were willing to replace with bound forms. The old "No, just
use a little more string, chewing gum, and spit -- it'll hold together a
little longer" attitude.

Nov 12 '05 #9

deko

There were 2 pop up forms with unbound data in this database. I bound both
forms to the same table since only one can be open at a time.

The table will always have only one row to hold the various user-search
criteria.

The key to making this work was to configure the forms:

"AllowDeletions=No"
"AllowAdditions=No"

-- is this the same as "DataEntry=Yes" ??
"Larry Linson" <bo*****@localhost.net> wrote in message
news:DO*******************@nwrddc03.gnilink.net...

"deko" wrote

> Bottom Line: unbound data is a bad thing.
In the vast majority of cases, I agree with you on this.

I've worked on a few unbound data-entry / editing forms, all written by
someone else, all in some client's database, and <SIGH> none of which any

of those clients were willing to replace with bound forms. The old "No, just
use a little more string, chewing gum, and spit -- it'll hold together a
little longer" attitude.

Nov 12 '05 #10

David W. Fenton

dj****@hotmail.com (deko) wrote in
<Xj*****************@newssvr29.news.prodigy.com> :

Bottom
Line: unbound data is a bad thing.

As an unqualified statement, that's as meaningless as saying "a
piece of string is very long." Some pieces of string are long, some
are short, some are in between. And it all depends on what you
consider long and short.

Unbound forms in Access are invaluable and almost unavoidable for
use as dialog boxes.

Unbound forms for data editing are much less essential, but given a
particular problem space (e.g., high concurrency requirements),
they may very well be required.

It all depends on the application and the task at hand.

All of my Access apps have literally dozens of unbound dialog
forms.

None of my Access apps has more than a handful of unbound data
editing forms, and some have none at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #11

Larry Linson

Pop-up forms for specifying criteria would be one of my exceptions, because
I'd just consider that to be accepting a user's information to control the
application, not "data handling" (which I classify as data entry or data
editing of stored data).

"deko" <dj****@hotmail.com> wrote in message
news:Mg****************@newssvr29.news.prodigy.com ...

There were 2 pop up forms with unbound data in this database. I bound both forms to the same table since only one can be open at a time.

The table will always have only one row to hold the various user-search
criteria.

The key to making this work was to configure the forms:

"AllowDeletions=No"
"AllowAdditions=No"

-- is this the same as "DataEntry=Yes" ??
"Larry Linson" <bo*****@localhost.net> wrote in message
news:DO*******************@nwrddc03.gnilink.net...

"deko" wrote

> Bottom Line: unbound data is a bad thing.
In the vast majority of cases, I agree with you on this.

I've worked on a few unbound data-entry / editing forms, all written by
someone else, all in some client's database, and <SIGH> none of which any of

those clients were willing to replace with bound forms. The old "No,

just use a little more string, chewing gum, and spit -- it'll hold together a
little longer" attitude.

Nov 12 '05 #12