added the data from a form to another access table

[donnie051280]

i created a form which is opened by a query. it displays several fields. I need to find a way to export/add these results into a new access table. I need to do by using a button..

thanks

 

[M8KWR]

you could write an ammend query, that takes the value from the form fields, and then updates another table within access.


The only thing is that the ammend query need a table / query to start with, so you could use the one you use for your form, but take the id number of the record tht you are on, on your form and send this to the ammend query so you are updating the table with the correct values.

I hope this helps, if not let me know, or if you need any help in writing the ammend query

 

[donnie051280]

Hi
brief description of what i want:
the query takes a reference from form1 then opens form2(the one i want to add the data). The fields all come from different tables but all are related in some way. Example.. the job number number is in one table but i need to find out several different things about this job number, so my query searches the other tables and finds them. I need to save this data!!
I havent yet created the table i want to save the form data to..
so how would i use the ammend query to work this out

thanks for your time

 

[M8KWR]

if your opening a new form, then you could pass the information from form1 to form2. By saying when form2 opens me.text1 = [forms]![form1]![text1] and so on.

But this would only work with form1 open.

Hope this makes sense

 

[donnie051280]

yeah.. that makes sense
but how would i save the data in the 7 fields inside the form to a table.. Basically everytime i run the query and get the results on the form i want to have a button on that form which saves all this data to a table
sorry if it sounds confusing.
thanks
don

 

[M8KWR]

No it doesn't.

What you would do is open form2, and then say create new record, then once you are on a blank record, then you copy over the 7 fields you require.

To open Form2 and just create a new record, but you will not be able to view any other records with the form, the code is:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Form2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click
    
End Sub

This is assuming that the form also looks at other records within a table, so you would have to ask the form to create another record, because you do not want to overwrte data. That would cause some issues.

 

[donnie051280]

thanks heaps for your help
i have never used VB before so im sorry if this may sound stupid

do i need to add any information to this part

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

such as the file to save it to.. etc
what is the name of the database file which the information is saved to

and do i use this information in the VB code of the button that saves the data.. or the button that opens subform2.. or does this open form2 and saves the data

 

[donnie051280]

or if its makes its easier
just a button which saves the data from a form into a table

 

[M8KWR]

Ok lets take it step by step.

Your on Form1 and you can see the information you want to be copied to another table.

You press a button that opens up Form2

On Error GoTo Err_Command16_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Form2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command16_Click:
    Exit Sub

Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click

### this code above would go into the click procedure of the command button.

Then form2 open up.

In the Form2 load procedure you would write code like the following to copy the information from Form1 to Form2.

me.text1 = [forms]![form1]![text1] 
me.text2 = [forms]![form1]![text2] 
me.text2 = [forms]![form1]![text2]

You need to assign a table to form2, so that the data is going to be enetered in somewhere.

I hope this makes sense, if not let me know, if your stuck, you are more them welcome to send me the file you are working on, and i'll have a look for you to see where you may be going wrong.

 

[M8KWR]

If you want a button to copy the info of form1 into a table you would write a ammend query as below. But you will have to change the table name and field names

INSERT INTO Name_of_Table( Text1, Text2 )
SELECT [forms]![form1]![txt1] AS Expr1, [forms]![form1]![txt2] AS Expr2;

Have a command button to then run the query. It will prompt you to if you are sure you want to ammend 1 record to the table, if you want to get rid of this warning message then let me know i can give you some code to make it disappear.

 

[donnie051280]

thanks for your help
i finally got it working

don

 

[donnie051280]

actually
just one more question???
how to you get rid of that prompt about ammend???