Hello! I'm working with Access2002. I created a form based on a table's value, but need to save it to a temporary table (to base another form's data on). Can anyone assist with the macro/coding for this task? Or is there a better way of accomplishing this task?
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 Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Save form data to a temporary table 1
- Thread starter dylan03
- Start date
-
1
- #2
Whether there's a better way pretty much depends on what data it is that you're copying; and what relationship(s) exist between affected tables.
Perhaps you could be a bit more specific about what information you are copying, and why you are using a temp table.
A partial answer:
You could use VB to get values from the form and add to tables, or you could write an append query that locates the record your form is currently displaying and updates the temp table.
Perhaps you could be a bit more specific about what information you are copying, and why you are using a temp table.
A partial answer:
You could use VB to get values from the form and add to tables, or you could write an append query that locates the record your form is currently displaying and updates the temp table.
- Thread starter
- #3
I'll try to be short... This is for an auto sales db. My tables include: customer (name, address, etc), inventory, (make,model,year) & sales (unit price, sales tax, financing info). The concept is this...
1. Choose the customer name (which populates the related fields from the Customer Table) and then save any changes to the customer table. This step is complete.
2. Choose the car from the current inventory (which also then populates the related car information from the inventorytbl) and mark the selected auto "sold" (a field in the inventorytbl). This step is complete.
3. Fill in the Sales information (sales price, tax, financing info), etc.
4. Gather all this information to produce the sale documents necessary. These would include information from all the mentioned tables.
The problem that I'm running into is how to join/link them together. I can't figure out how to join the customer & inventory tables together.
Currently, the Customertbl.CustomerName is joined to the Salestbl.CustomerName & the Sales.VinID is joined to the Inventory.VINID....
If I could figure out how to connect the Customer & Inventory table together, I wouldn't have to create the temporary table.
Thank you SOOOOOOOOOO much for your assistance!
1. Choose the customer name (which populates the related fields from the Customer Table) and then save any changes to the customer table. This step is complete.
2. Choose the car from the current inventory (which also then populates the related car information from the inventorytbl) and mark the selected auto "sold" (a field in the inventorytbl). This step is complete.
3. Fill in the Sales information (sales price, tax, financing info), etc.
4. Gather all this information to produce the sale documents necessary. These would include information from all the mentioned tables.
The problem that I'm running into is how to join/link them together. I can't figure out how to join the customer & inventory tables together.
Currently, the Customertbl.CustomerName is joined to the Salestbl.CustomerName & the Sales.VinID is joined to the Inventory.VINID....
If I could figure out how to connect the Customer & Inventory table together, I wouldn't have to create the temporary table.
Thank you SOOOOOOOOOO much for your assistance!
AH! This should be pretty easy, and you shouldn't need to copy data.
Your customer table has customer info and a unique ID for each customer (autonumbers can be useful for this). A given customer may particpate in one or more sales.
Your inventory table also should have a unique ID for each inventory item (an autonumber again is convenient).
Your sales table has sales information, and a sales ID for that particular sale. A given sale can have one and only one customer, and one or more Sold items.
A Sold Items table would contain the inventory ID of what was sold, the price it was sold at, perhaps a quantity (e.g. floor mats), the price it was sold at, and the associated Sales table ID.
So, you have three main forms;
Customers, Inventory, and Sales.
The Sales form has:
a sub form: Sold Items. After you create a sold items form, use the wizard to add the sub form to your sales form.
a combo box for the customer
The Sold Items form has:
a combo box for the inventory item.
With this approach, you shouldn't need to use a temp table, and the information you need will be automatically copied where it's necessary.
This answer is lacking in much detail, so please don't hesitate to answer any more questions.
Your customer table has customer info and a unique ID for each customer (autonumbers can be useful for this). A given customer may particpate in one or more sales.
Your inventory table also should have a unique ID for each inventory item (an autonumber again is convenient).
Your sales table has sales information, and a sales ID for that particular sale. A given sale can have one and only one customer, and one or more Sold items.
A Sold Items table would contain the inventory ID of what was sold, the price it was sold at, perhaps a quantity (e.g. floor mats), the price it was sold at, and the associated Sales table ID.
So, you have three main forms;
Customers, Inventory, and Sales.
The Sales form has:
a sub form: Sold Items. After you create a sold items form, use the wizard to add the sub form to your sales form.
a combo box for the customer
The Sold Items form has:
a combo box for the inventory item.
With this approach, you shouldn't need to use a temp table, and the information you need will be automatically copied where it's necessary.
This answer is lacking in much detail, so please don't hesitate to answer any more questions.
- Thread starter
- #5
Thanks for all of your help, but I'm getting stuck on something. The customer information is entered into a table from the website (a credit application). So, I need to allow the sales rep to edit any misinformation.
Currently, I have a combo box fill in all the form fields (from the Customertable) in the OnUpdate event.
Can I still do this with your suggestion? If so, how? I would need to basically query the ExistingCustomerQry and have it fill in the form fields, but don't know how.
Thanks again for your help. I really appreciate your thoroughness
Currently, I have a combo box fill in all the form fields (from the Customertable) in the OnUpdate event.
Can I still do this with your suggestion? If so, how? I would need to basically query the ExistingCustomerQry and have it fill in the form fields, but don't know how.
Thanks again for your help. I really appreciate your thoroughness
- Thread starter
- #6
You might want a query based on all three tables for reports, but I don't think you need it for data entry.
Your salesmen should be able to update any customer information using your stand-alone customer form.
Additionally, inventory should be updatable using the stand-alone inventory form.
Sales are handled via the sales/sales item form.
If your salesman finds they need to update customer information while entering data into the sales form, you could provide a button to invoke the Customer Form, and set it's filter property to the customer ID currently displayed in the sales form.
It would be the same when adding inventory items to a sales order.
Your salesmen should be able to update any customer information using your stand-alone customer form.
Additionally, inventory should be updatable using the stand-alone inventory form.
Sales are handled via the sales/sales item form.
If your salesman finds they need to update customer information while entering data into the sales form, you could provide a button to invoke the Customer Form, and set it's filter property to the customer ID currently displayed in the sales form.
It would be the same when adding inventory items to a sales order.
- Status
Similar threads
