I have a form with a combobox linked to a table. the user selects or keys the desired information in the combobox and several fields fill in with the information from the table. I want the user to fill in other blank text fields and then save all the information to another table (for query and reports), how do automate the save to the other table. Thanks for the help.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Updating table with access form 1
- Thread starter mpsteve
- Start date
-
1
- #2
mpsteve,
Sounds like you could do it with an append query. After the user has filled in all the text boxes with data, run an append query (maybe from a command button). The query should:
1. Append to the new table
2. Use each control on the form as the record source
3. Append each control to a field in the table.
So, for each field, in the query builder, do something like this:
Field: [Forms]![YourForm]![YourTextBox1]
Table:
Sort:
AppendTo: Field1
Do this for each control that you want to send to another table, and it's done. IN this manner, only data from one active form will append at a time.
There are other ways to do this, but if your form is bound to one table, and you need to get the data into another table, an append query is the way to go.
Let me know if I'm not on the right track, and we'll work on something else.
-Patrick
Nine times out of ten, the simplest solution is the best one.
Sounds like you could do it with an append query. After the user has filled in all the text boxes with data, run an append query (maybe from a command button). The query should:
1. Append to the new table
2. Use each control on the form as the record source
3. Append each control to a field in the table.
So, for each field, in the query builder, do something like this:
Field: [Forms]![YourForm]![YourTextBox1]
Table:
Sort:
AppendTo: Field1
Do this for each control that you want to send to another table, and it's done. IN this manner, only data from one active form will append at a time.
There are other ways to do this, but if your form is bound to one table, and you need to get the data into another table, an append query is the way to go.
Let me know if I'm not on the right track, and we'll work on something else.
-Patrick
Nine times out of ten, the simplest solution is the best one.
- Thread starter
- #3
Sometimes if you're bringing in information from more than one table, Access will think that there are multiple records when there really aren't. It does this (I think) because it creates a join between the two tables and assumes that you're bringing in data from both of them. Then, although there should only be one record because all of the information from one table is unique, the record repeats because information from the other table is not unique, which causes the query to have that number of records.
Whew.
The solution is this:
Right click up in the add tables section of the query and select query properties. Then in the unique values row select "yes". If all the records are duplicates, this will limit the returned dataset to only the unique values in the query. So, if they're all repeats of each other, only one will be appended.
When I'm working with append queries, I often set the query up as an append query, and then change it to a select query to make sure that the right information is flowing. Whatever you see when you change it to a select query and then run it is the same information that will be appended. It helps clear up problems like this.
Let me know if that does it, and I'm glad I could help.
-Patrick
Nine times out of ten, the simplest solution is the best one.
Whew.
The solution is this:
Right click up in the add tables section of the query and select query properties. Then in the unique values row select "yes". If all the records are duplicates, this will limit the returned dataset to only the unique values in the query. So, if they're all repeats of each other, only one will be appended.
When I'm working with append queries, I often set the query up as an append query, and then change it to a select query to make sure that the right information is flowing. Whatever you see when you change it to a select query and then run it is the same information that will be appended. It helps clear up problems like this.
Let me know if that does it, and I'm glad I could help.
-Patrick
Nine times out of ten, the simplest solution is the best one.
- Thread starter
- #5
- Status
Similar threads
