473,216 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Command button to save select fields in a form

17
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.
Sep 20 '07 #1
5 2738
Scott Price
1,384 Expert 1GB
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
Sep 22 '07 #2
mdpems
17
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.
Sep 24 '07 #3
Scott Price
1,384 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "INSERT INTO tblIngredient(ItemName) VALUES (" & "'" & Me!txtFindAsUTypeValue & "'" & ");"
  4.  
  5. DoCmd.Setwarnings = False
  6. DoCmd.RunSQL MySQL
  7. DoCmd.Setwarnings = True
  8.  
  9. 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
Sep 24 '07 #4
mdpems
17
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command32_Click()
  2. Dim MySQL As String
  3.  
  4. MySQL = "INSERT INTO [Supplies_Total](Mission#) VALUES (" & "'" & Me!Text14 & "'" & ");"
  5.  
  6.  
  7. DoCmd.SetWarnings False
  8. DoCmd.RunSQL MySQL
  9. DoCmd.SetWarnings True
  10.  
  11. DoCmd.Close acForm, "tfrmHistory"
Sep 24 '07 #5
Scott Price
1,384 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command32_Click()
  2. Dim MySQL As String
  3.  
  4. MySQL = "INSERT INTO [Supplies_Total](Mission#) VALUES (" & "'" & Me!Text14 & "'" & ");"
  5.  
  6.  
  7. DoCmd.SetWarnings False
  8. DoCmd.RunSQL MySQL
  9. DoCmd.SetWarnings True
  10.  
  11. 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
Sep 25 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Codeman II | last post by:
Hi there, I am building a form where the user must upload a picture and fill in his details. Now I have a problem as all of this is on the same form. How will I be able to have the Browse...
2
by: Rebecca | last post by:
I have a dynamically created command button on a .net page that adds 1 row to a sql server table when clicked. The page_load event load rows from that table for the user to view, but for some reason...
14
by: Kevin | last post by:
A couple of easy questions here hopefully. I've been working on two different database projects which make use of multiple forms. 1. Where's the best/recommended placement for command buttons...
2
by: Darren | last post by:
Hi, I have a command button which has a macro running in it. The macro on the click event, runs to ensure that certain values in a form are valid, (e.g. the textboxes are not null) and...
13
by: Chris Carlen | last post by:
Hi: Having completed enough serial driver code for a TMS320F2812 microcontroller to talk to a terminal, I am now trying different approaches to command interpretation. I have a very simple...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
20
by: Amanduh | last post by:
Hi, I'm kind of being forced to dive into the whole Access world at my work, and I haven't done coding in years. I've basically created the whole database I want, even made cascading comboboxes. ...
14
by: squrel | last post by:
Hello everyone, I m using some button using toolbar such as Add,Save,View,.... my save button is not working.... it doesnt give me any error but does not save to my database.... or showing in my...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.