Save form record to a new table

[auntielo]

Hi there

Disclaimer: I am very new and still struggling with .... just about everything!

Long Story Short:
I have a form, I want the data to save to a new table when I click submit.

Long Story:
The table the form is based on is to help me auto fill some of the data, all the people connected to an account. The form is to put in a new task for one of the accounts, and I want to be able to submit the form record to a Task Table.

All of the instructions I have been able to find only discuss saving to the underlying table. I am probably not using the right search terms.

Any help would really be appreciated!

 

[auntielo]

The closest I have found so far is this but I haven't gotten it to work yet.

It won't let me post a link but it is from 1038924, post 3

Private Sub EnterPNcmd_Click()
Dim SQL_Text As String
SQL_Text = "INSERT INTO costingresultstbl (enditemnumber,PN,Enditemcustomer,comments,current cost,currentmatl,currentlabor,currentoh,ohrateinpu t,laborrateinput,description,Laborhours,labordolla rscalc,ohdollarscalc,PartType) SELECT '" & Me.EndItemtxt & "','" & Me.ItemMasterPNtxt & "','" & Me.Customer & "','" & Me.Commentstxt & "','" & Me.CurrentCosttxt & "','" & Me.Materialtxt & "','" & Me.Labortxt & "','" & OHtxt & "','" & OHRatetxt & "','" & LaborRatetxt & "','" & Descriptiontxt & "','" & LaborHourstxt & "','" & LaborDollarstxt & "','" & OHDollarstxt & "','" & Frame238 & "'"

DoCmd.RunSQL (SQL_Text)
End Sub

 

[plog]

I have a form, I want the data to save to a new table when I click submit.

What's the long story? Because this is very wrong. If you told me you weren't new I would let it pass (well, I wouldn't push as hard), but this poor practice coupled with your inexperience leads me to believe this is not the correct method to solve whatever it is your ultimate issue is.

So, why a new table for each ?

 

[auntielo]

Well. I have one table that has all the accounts and the 30 or so people associated with those accounts. Figuring out who belongs to which account has always been the worst part of data entry.

So I need
Account
Account number
Start Date
End Date
Project Type
Project Description
Notes
Followup
Team1
Team2
Team3
Team4
Team5
... etc

So I put a combo box at the top that I can use to find the account fast, and then if fills in the information for the account and the team. Then I added combo and text boxes to get in the project information.

It is completely likely I am attacking this the wrong way. I had already created a form that gave me everyone attached to a firm. So I basically stole that and added the project options on the form.

 

[plog]

You really didn't explain table(s) very well. Especially why you need to create so many tables dynamically. From your description in the first paragraph you should have 3 tables:

Accounts - holds account data
Staff - holds all unique staff members
Assignments - holds which staff go to which accounts.

Then looking at the data you posted you have an error. You should not numeratefield names (team1, team2, etc.). Instead you need a new table to hold that team data with one record per team, not 1 field per team.

I recommend you read up on normalization before you proceed further.

 

[auntielo]

They aren't really in as Team1 Team2. I just thought it would be easier than the role titles which are like Account Manager, Sales Executive, Account Representative, etc.

I have been having trouble figuring out how to normalize this data without having 30 tables. The only thing they will always have in common is the specific account. Do I really have to have a table for each role?

I scraped what I was doing and start over. The form is now based on a query of the Table with all the Task data and a query that gives me all the team members I need.

I just started researching to figure out if that is enough to get me to where I can save records from the form to a table. Can I? Or do i have to make a separate query to make a table and then build the form off that?

 

[plog]

You need to forget about forms for a while. Focus on tables only. Perhaps it would be best if you set up the Relationship Tool (Datanbase Tools -> Relationships), take a screenshot and then paste that to the forum.

 

[auntielo]

Trouble is, I needed the form.

I will probably have to update it as I learn more, but I got it working so it loads clean, lets me choose an account, auto fills the team info and has combo boxes for all of the trial data. I added a clear button and a Do you want to save message, (had to put those in there it kept adding when I was testing!), a new button if i have multiple entries, and the submit button that saves it to a table and a report for deliverables.

Even if it isn't perfect, it is much easier than a spreadsheet of columns to GN and almost 1500 rows.

It isn't the best way to learn, but I am on a time crunch.

Now I just have to finalize 3 reports,make a quarterly summary form, and fix my account finder form. I might have to post again for that one. I didn't like where my database was going so I started a new one and pasted it in. I think I corrupted it or something. I may have to do another combo box search, but I really liked how I got it working before.