Command button to save select fields in a form

[mdpems]

The organization I work for needs to use a form that we can send to a billing department that shows supplies we used and Mission#'s. I have a form to select supplies used and total the costs of those supplies. I also have a field on this form that is called Mission#. What I want to do is create a command button that will save those two fields only to another table. Is there a way to do this. I was also wondering if I can have this button set to close the form after it saves that info to the table.

Thanks for your help in advance.

[Scott Price]

The organization I work for needs to use a form that we can send to a billing department that shows supplies we used and Mission#'s. I have a form to select supplies used and total the costs of those supplies. I also have a field on this form that is called Mission#. What I want to do is create a command button that will save those two fields only to another table. Is there a way to do this. I was also wondering if I can have this button set to close the form after it saves that info to the table.

Thanks for your help in advance.

It sounds like you are intending to save a calculated result (Totals) in a table, is this right? This isn't a very good practice, as it consumes a lot of database overhead making the calculations, then saving, then re-calculating, then re-saving ad infinitum. It is a far better practice IMHO to make the calculations in a query, which is then available dynamically for any reporting, etc. that you wish to do.

Regards,
Scott

[mdpems]

The total will already post on the main form. What I want to do is save the total posted and the mission number it matches into another table. So basically all I want to save is just those two fields into another table.

[Scott Price]

If the table already exists you can run an append query from vba to insert the new records into the table. Otherwise you could also use a make table query from vba.

The general syntax of an append query is like this:

Dim MySQL As String
 
MySQL = "INSERT INTO tblIngredient(ItemName) VALUES (" & "'" & Me!txtFindAsUTypeValue & "'" & ");"
 
DoCmd.Setwarnings = False
DoCmd.RunSQL MySQL
DoCmd.Setwarnings = True
 
DoCmd.Close acForm, "tfrmHistory"

You will, of course have to change the names to reflect your table/field and form names... This can go into the on-click event of your command button.

Regards,
Scott

[mdpems]

For some reason I am still having problems with this code. I am getting a syntax error in the INSERT INTO line. I have changed the names with no results. I may have not been exactly clear on what I was trying to do. I have two tables in this data base with one form. On one table I have two columns labled Item and Cost for the supplies that we use per mission. I had to create a form with unbound text boxes labled patient names, date, mission number, and total. The total is calculated by selecting numerous items from a list box and multiplying them by cost and quantity. The second table I have for this form is called Supplies Total. This has two columns labled mission# and Total. What I want to do is create the one button that will save just those two fields only into the supplies total table. I think that the append query is on the right track but the code I tried wasn't working. I did get it to show an error one time stating that this was not an updateable query. Here is the code I inserted in VBA. The text14 is the name of mission number text box on the form. Thanks again for all the help.

Private Sub Command32_Click()
Dim MySQL As String
 
MySQL = "INSERT INTO [Supplies_Total](Mission#) VALUES (" & "'" & Me!Text14 & "'" & ");"
 
 
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
 
DoCmd.Close acForm, "tfrmHistory"

[Scott Price]

For some reason I am still having problems with this code. I am getting a syntax error in the INSERT INTO line. I have changed the names with no results. I may have not been exactly clear on what I was trying to do. I have two tables in this data base with one form. On one table I have two columns labled Item and Cost for the supplies that we use per mission. I had to create a form with unbound text boxes labled patient names, date, mission number, and total. The total is calculated by selecting numerous items from a list box and multiplying them by cost and quantity. The second table I have for this form is called Supplies Total. This has two columns labled mission# and Total. What I want to do is create the one button that will save just those two fields only into the supplies total table. I think that the append query is on the right track but the code I tried wasn't working. I did get it to show an error one time stating that this was not an updateable query. Here is the code I inserted in VBA. The text14 is the name of mission number text box on the form. Thanks again for all the help.

Private Sub Command32_Click()
Dim MySQL As String
 
MySQL = "INSERT INTO [Supplies_Total](Mission#) VALUES (" & "'" & Me!Text14 & "'" & ");"
 
 
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
 
DoCmd.Close acForm, "tfrmHistory"

Two tables, one form. In the second table you want to add the totals?

Does it not work to use a query that combines the two tables into one record source for your form (making the calculations in the query), then bind the totals text box to the correct field in the second table?

As for the code, try using without the second set of "'" in other words: ...VALUES (" & Me!Text14 & ");"

Also, you'll have to remove the DoCmd.Close line (or change the form name from tfrmHistory, unless your form is also named tfrmHistory :-)

Regards,
Scott