How to make a new table from fields entered in form

[calgary]

Hi, I want to know how to create a new table from 4 fields entered in a form. The reason I'm doing this, because I'm creating a report that goes through numerous macros to come up with the results. And in the beginning I'm creating a table that is use in all my queries. But not knowing how to do any Visual Basic or not knowing a lot about Access, need some help completing this.

My 4 fields are description, route #, blk length and waste factor.

In the form I have the end user enter the description, which is unique, then the last 3 fields populate on the form. I want to have a command button that creates a table for these 4 fields. Then all my queries will run in the macro I've already created, giving me my final report.

Thanks!

 

[Alansidman]

Why not just create a query using the four fields. Are the four fields in your form bound to a table already? Putting the the information in a new table is contrary to normalization theory that dictates data should only be stored in one place and not duplicated.

 

[Jeanette Cunningham]

Usually in access, create the table first then build the form, using the table as its record source.
The table is created first, with the 4 fields for description, route #, blk length and waste factor. Be sure to avoid symbols in the field names, and it is a good idea to avoid spaces in field names.
Note that the word 'description' is a reserved word and should not be used for a field name. You could call it RDescription - (r short for route) instead.

The reason to base the form on an existing table is to greatly simplify the data entry process - this is what access is very good at - saving data in a table without needing to write complicated code to use with an unbound form.

 

[smig]

if you need these fields to be used in query you can use
Forms![MyForm].[FieldName]

no need to save them into a table

 

[calgary]

Thank you for your suggestions, appreciate! I do understand the skepticism in your replies, but this is a database that was created by someone else and I'm just trying to do something for the end user while the main programmer is gone. I don't want to start fixing everything, just want to get this completed, though do understand it will need to fix at sometime.

I'm trying to go with smig suggestion and just create a table from the form. I created a make table query with my fields being Expr1: [Forms]![Another way Description]![Description] and so forth, but didn't create the table and didn't give any errors...

Any suggestions?

Thanks!

 

[smig]

I didn't suggest to put these fields into a table
My suggestion was to use it directly in the query, either as fields or as criteria for watever you need.

First thing you must make sure form is open when you open the query.
Use the Build... to help you get the correct fields, including the correct syntex.
First set the query as Select Query to make sure you get what you need. After you are sure you have what you need turn it into a MakeTable Query.

You can rename the Expr1 names to whatever you like

skepticism is only because puting these textBox fileds into a table makes no sence.
If you realy want to put the data into a table why not do it it in the normal way - create a table and bound the form to that table. You can easily show one record only (The first one) and change it every time you close the form.

 

[calgary]

Ok, don't think I'm explaining myself or I'm just not making sense.

This form has the 4 fields in it for the end user to request what data they want ran for a report. It use to create a table from these 4 fields, which then in turns runs a macro that runs many queries to calculate an ending report. All the queries are linked to this table created in the form.

Now there is a user that wants to just enter the description (from a drop down) and automatically populates the other 3 fields. I have the description (drop down) an unbound field that is attached to the main table and that works. But I need to create
this table somehow so I don't have to change all the queries from the first process and
this is where I'm having troubles.

Does this make sense?

Thanks!

 

[smig]

It does make sense, though I don't think it's the right way to do it
read my previous post how to do it

 

[calgary]

Ok, think I got it! But when you do the Forms![MyForm].[FieldName], how do you express numerics? One field is a numeric(double number) and the other a percent.

Thanks!

 

[smig]

It won't care what is the format of the data in the field or how you represent (Format) it. it will take the data as it is in the field.
If you represent 12.0368 as 12.04 (Format = fixed) it will still take the original data (12.0368)

 

[calgary]

I'm having troubles with my waste factor field, it's type is percent. In my table comes out as Ẹ. Can't find out anywhere what that means??? Any help on this?

Thanks!

 

[smig]

set the format of the field in the table to what you like

E is either for very big or very small numbers, like:
1.3987E37 = 1.3987*10^37

 

[calgary]

Great! I got it! Thanks for your help!!! I'll mark this completed.

 

[smig]

Glad we could help