How do I create a table from data in a form ?

[Jackie L]

1) Are the fields on your form all unbound or are they in another table?
Either way, you can make a Make Table query where the criteria line would
contain the reference to the key field on that form. The fields used would
depend on if the form is based off a table or not. This can obviously be
done in code also but the query might be easier at this point. Then, you can
run the query off a button on your form to save it into the new table (you
may want to turn your warnings off and then on again after the table is
created).

2) On the record source of your form design, just choose the query you have
created. It will automatically run each time you open the form. If you need
it to run while the form is open, you will have to Requery based on an event
or a button. Let us know if you need help with that.

Hope this helps.

 

[Guest]

I have a database. It is displayed in a form.
Questions 1.
I want to take the data that is displayed in the current record (in the
form) and create a new table out of it. This means that the table will have
one record in it. eg. The current selected info in the form being displayed.
eventually I want to do this programically but cant see how to do it simply
through menu's


Questions 2.
A second question is, how can I apply a specific query to the form. When I
create queries and then open them they open in a table format. I want to
open my form and then just choose the query and have the form display the
records that match.

 

[John Vinson]

I have a database. It is displayed in a form.

Umm... no. Jargon alert: in Access, a "Database" is the .mdb file, the
container for all of your Tables (where the data is actually stored),
Forms, Reports, Queries, and so on.

A Form does not contain data. It's a tool, a window into the data
which is stored in a Table (and ONLY in a table).

Questions 1.
I want to take the data that is displayed in the current record (in the
form) and create a new table out of it. This means that the table will have
one record in it. eg. The current selected info in the form being displayed.
eventually I want to do this programically but cant see how to do it simply
through menu's

This would appear to be backwards from the usual way that databases
are designed. Typically, you would design a set of normalized Tables
to contain all of the data that you will want to use in the future;
Queries to link and filter the data; and Forms to enter and display
the data. If you're creating a one-record table in order to display or
print that data, it's almost certain that you would be better off to
instead create a Query selecting that record; it's very, very unusual
to create a new Table, and certainly not appropriate if you are just
doing so to display the data.

Questions 2.
A second question is, how can I apply a specific query to the form. When I
create queries and then open them they open in a table format. I want to
open my form and then just choose the query and have the form display the
records that match.

Set the Recordsource property of the form to the name of the query.

If you're doing multiple queries which are identical except for the
criteria, consider using a "Parameter Query" instead. That is, rather
than having a query qryParis with a criterion of "Paris" on the City
field, and another qryLondon with "London", have a single query with a
criterion of

[Enter city:]

or, better, have a Form with an unbound textbox txtCity and use a
criterion of

[Forms]![MyForm]![txtCity]


John W. Vinson[MVP]

 

[Guest]

Thanks for your help.

Lets dispense with question 2 for the moment as its a little confusing for
me to deal with them both at the same time.

What I have done is created a table query. In fact I use a [?] on the
criteria because I want it to ask me what customer number I want. The
problem I seem to have is that when I go to save the table File/Save I get
a window opening and it wants to save the Query as either a Query or Form or
Report. I don't want any of these. I want to save it as a Table.
I'm using Access 2000.

Thanks.

 

[Jackie L]

There are various types of queries: Select, Cross tab, Make table, etc. In
your query design, on the menu bar pick Query, Make table and assign a name
for your new table. Every time you run the query, it will delete the
previous table and make a new one based on the query results. Be sure to
use the key field of your record as criteria in your new query.

Hope this makes sense.

Thanks for your advice.
Your right it is an odd way of doing things. I tried to describe the problem
as being one of capturing the data of the current record being displayed in
the form to a table.
I recognize that the form is (basically) just a pretty way of displaying a
table. However when you are looking at the form, as it displays the record
of data and it is currently displaying the data you need then this would be
the best place to save it.

The reason I want to save it to a table is because I have a macro that
saves that table automatically to a Excel template. In other works.

1. Look at one record in form
2. If need be run query on form perhaps a select query.
3. save that one record to a table
4. run macro that updates an excel template.

I cannot see how to save a query to be a table. I can save a query to be a
query, or form, or report. but not a table.

Any thoughts
Thanks.

I have a database. It is displayed in a form.

Umm... no. Jargon alert: in Access, a "Database" is the .mdb file, the
container for all of your Tables (where the data is actually stored),
Forms, Reports, Queries, and so on.

A Form does not contain data. It's a tool, a window into the data
which is stored in a Table (and ONLY in a table).

Questions 1.
I want to take the data that is displayed in the current record (in the
form) and create a new table out of it. This means that the table will have
one record in it. eg. The current selected info in the form being displayed.
eventually I want to do this programically but cant see how to do it simply
through menu's

This would appear to be backwards from the usual way that databases
are designed. Typically, you would design a set of normalized Tables
to contain all of the data that you will want to use in the future;
Queries to link and filter the data; and Forms to enter and display
the data. If you're creating a one-record table in order to display or
print that data, it's almost certain that you would be better off to
instead create a Query selecting that record; it's very, very unusual
to create a new Table, and certainly not appropriate if you are just
doing so to display the data.

Questions 2.
A second question is, how can I apply a specific query to the form. When I
create queries and then open them they open in a table format. I want to
open my form and then just choose the query and have the form display the
records that match.

Set the Recordsource property of the form to the name of the query.

If you're doing multiple queries which are identical except for the
criteria, consider using a "Parameter Query" instead. That is, rather
than having a query qryParis with a criterion of "Paris" on the City
field, and another qryLondon with "London", have a single query with a
criterion of

[Enter city:]

or, better, have a Form with an unbound textbox txtCity and use a
criterion of

[Forms]![MyForm]![txtCity]


John W. Vinson[MVP]

 

[Guest]

Thanks for your advice.
Your right it is an odd way of doing things. I tried to describe the problem
as being one of capturing the data of the current record being displayed in
the form to a table.
I recognize that the form is (basically) just a pretty way of displaying a
table. However when you are looking at the form, as it displays the record
of data and it is currently displaying the data you need then this would be
the best place to save it.

The reason I want to save it to a table is because I have a macro that
saves that table automatically to a Excel template. In other works.

1. Look at one record in form
2. If need be run query on form perhaps a select query.
3. save that one record to a table
4. run macro that updates an excel template.

I cannot see how to save a query to be a table. I can save a query to be a
query, or form, or report. but not a table.

Any thoughts
Thanks.

I have a database. It is displayed in a form.

Umm... no. Jargon alert: in Access, a "Database" is the .mdb file, the
container for all of your Tables (where the data is actually stored),
Forms, Reports, Queries, and so on.

A Form does not contain data. It's a tool, a window into the data
which is stored in a Table (and ONLY in a table).

Questions 1.
I want to take the data that is displayed in the current record (in the
form) and create a new table out of it. This means that the table will have
one record in it. eg. The current selected info in the form being displayed.
eventually I want to do this programically but cant see how to do it simply
through menu's

This would appear to be backwards from the usual way that databases
are designed. Typically, you would design a set of normalized Tables
to contain all of the data that you will want to use in the future;
Queries to link and filter the data; and Forms to enter and display
the data. If you're creating a one-record table in order to display or
print that data, it's almost certain that you would be better off to
instead create a Query selecting that record; it's very, very unusual
to create a new Table, and certainly not appropriate if you are just
doing so to display the data.

Questions 2.
A second question is, how can I apply a specific query to the form. When I
create queries and then open them they open in a table format. I want to
open my form and then just choose the query and have the form display the
records that match.

Set the Recordsource property of the form to the name of the query.

If you're doing multiple queries which are identical except for the
criteria, consider using a "Parameter Query" instead. That is, rather
than having a query qryParis with a criterion of "Paris" on the City
field, and another qryLondon with "London", have a single query with a
criterion of

[Enter city:]

or, better, have a Form with an unbound textbox txtCity and use a
criterion of

[Forms]![MyForm]![txtCity]


John W. Vinson[MVP]

 

[Guest]

Exactly.
Thanks that is exactly what I need to get started.

Regards

 

[John Vinson]

The reason I want to save it to a table is because I have a macro that
saves that table automatically to a Excel template. In other works.

1. Look at one record in form
2. If need be run query on form perhaps a select query.
3. save that one record to a table
4. run macro that updates an excel template.

I cannot see how to save a query to be a table. I can save a query to be a
query, or form, or report. but not a table.

If you REALLY want to save the data to a table, and then export that
table to Excel, Jackie's suggestion will work.

However, that just adds an expensive extra step that you don't need.

It's perfectly straightforward to export a one-row Query to Excel; it
is not necessary to create a table in order to accomplish your goal.
Try creating a Query with a criterion referencing your form, with

=[Forms]![YourFormName]![YourIDField]

as a criterion on the unique ID. This query can then be exported to
Excel, either manually or using the TransferSpreadsheet method in VBA
code.

John W. Vinson[MVP]

 

[Guest]

The reason I want to save it to a table is because I have a macro that
saves that table automatically to a Excel template. In other works.

1. Look at one record in form
2. If need be run query on form perhaps a select query.
3. save that one record to a table
4. run macro that updates an excel template.

I cannot see how to save a query to be a table. I can save a query to be a
query, or form, or report. but not a table.

If you REALLY want to save the data to a table, and then export that
table to Excel, Jackie's suggestion will work.

However, that just adds an expensive extra step that you don't need.

It's perfectly straightforward to export a one-row Query to Excel; it
is not necessary to create a table in order to accomplish your goal.
Try creating a Query with a criterion referencing your form, with

=[Forms]![YourFormName]![YourIDField]

as a criterion on the unique ID. This query can then be exported to
Excel, either manually or using the TransferSpreadsheet method in VBA
code.

John W. Vinson[MVP]

John, Thanks.

Actually I think that is exactly what I am doing.
However I think to use the TransferSpreadsheet you need to have a table that
is read into Excel. by the TransferSpreadsheet. This is actually the table
Im trying to make.

Eventually I want to make all my actions into one button that does the
following.

+Do a Table Query that asks me which record I want to take data from ( I ve
done this with a [?] in a query that searching on the customer number
field.) -done
+Convert Query into a table - done
+Manipulate table so that data matches my Excel.xls document - not done this
yet
+Use TransferSpeadsheet to take the data now in the newly created table and
put into existing sheet in Excel - done
+Excel now reads links to this sheet and updates Name address etc data. -
done

This is what Im doing.
Any advice / errors are welcomed.

thanks

 

[aaron.kempf]

you can do the transferspreadsheet with a query, cant you?

 

[Guest]

you can do the transferspreadsheet with a query, cant you?

I'm sorry Aaron, I don't understand your comment.

 

[John Vinson]

Actually I think that is exactly what I am doing.
However I think to use the TransferSpreadsheet you need to have a table that
is read into Excel. by the TransferSpreadsheet. This is actually the table
Im trying to make.

From the offline help for the TransferSpreadsheet method (emphasis
added):

TableName Optional Variant. A string expression that's the name of
the Microsoft Access table you want to import spreadsheet data into,
export spreadsheet data from, or link spreadsheet data to, or the
**Microsoft Access select query** whose results you want to export to
a spreadsheet.

Just use a Query selecting the record you want exported in place of
the table. It works; it really does. You can use a Parameter query.

John W. Vinson[MVP]

 

[Guest]

From the offline help for the TransferSpreadsheet method (emphasis
added):

TableName Optional Variant. A string expression that's the name of
the Microsoft Access table you want to import spreadsheet data into,
export spreadsheet data from, or link spreadsheet data to, or the
**Microsoft Access select query** whose results you want to export to
a spreadsheet.

Just use a Query selecting the record you want exported in place of
the table. It works; it really does. You can use a Parameter query.

John W. Vinson[MVP]

John,

Your absolutely right it does work. Great thank you.