473,320 Members | 1,951 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to save unbound field text to table?

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
11 17195
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
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
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
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
"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
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

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
"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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Stephan | last post by:
Hi, I'm using Visual Studio 2003 (C#) with the integrated Crystal Report software and have the following question: How can I assign a value (string) to an unbound (string) field in Crystal...
2
by: Dave Griffiths | last post by:
Access 97 - I have a form with a single unbound text field. I want to have a timer event which periodically saves the contents of that field. But I noticed that if the focus stays on the field then...
2
by: Todd | last post by:
Hello, I'm curious if anyone knows of a way (if one exists) to tell a form (in Access 2002 VBA) to sort on an unbound column of a combo box on the form. Here's what I want to do: A combo box...
2
by: Zlatko Matiæ | last post by:
Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a...
10
by: Matthew Wells | last post by:
Hello. I've converted a bound Access 2000 form which displays data retrieved from an Access 2000 database to an unbound form. Now my hyperlinks don't work. I'm assuming it's because the form...
5
by: jamesnkk | last post by:
I have an unbound text - txtfreight on a Subform, when I enter a value , it does not save to the Main form table immediately, unless I close and open up the form. How can I force to save immediately...
4
by: Susan Bricker | last post by:
I have a form that displays record information in Continuous Record display (scrollable list). One of the fields in the record is an Integer value called "rcode" (reason code). But, I don't want...
2
by: KC-Mass | last post by:
I have a form that is used to ID and then load Excel files into Access. I use labels on the form to record which file was last loaded. That was accomplished with a simple lblFileLoaded =...
1
by: MyWaterloo | last post by:
I have an unbound text box on my main form that is used to show the sum totals in my sub form. I also have a field on my main form that has a control source in a table. This bound field needs to...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.