Use a data template to reduce entry process

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Our product is sold by modular components, so each time a quotation is
prepared, an itemized list of components needed to “build†the unit is input
by a user (there are thousands of possible combinations, not to mention color
and top material choices, plus possible add-on options). Preceding the list
of components is a summarized “header†that describes what the built unit
will look like (provides the product series description, overall dimensions,
etc). In an effort to save time when quoting, I would like to enter templates
that could be used to complete part of the process. To do this, I would
create 2 tables, linked by an id number. Tbl_Item would have the descriptive
lines of the unit, and tbl_ItemDetails would have the components needed for
the unit (which can vary from as few as 4 components, up to about 25 or
more), it would be a one-to-many relationship.
I have set up a form where the user selects the desired unit from a list
that comes up based on selections from 2 combo boxes (to narrow down the
selection).
This part is working fine. What I need help with is the next step - When
the user finds and selects one of the records, I would like the information
for the selected record from tbl_Item and it’s associated parts (from
tbl_ItemDetails) to then copy to new records in tbl_quote_items and
tbl_quote_items_detail. These records would then display in a new window, and
the user could complete the quote by adding in any other additional
components (optional accessories, etc.) to tlb_ItemDetails.
I know it’s not good practice to repeat data, but it’s necessary in this
case, because all of the parts need to be stored with the quotation. Any
suggestions on if this is even possible and how to start on it would be so
appreciated. Thank you in advance.
Louise
 
Hi Louise,

You can achieve this with two insert into's...

docmd.runsql "insert into tbl_quote_items (FIELD_LIST) select FIELD_LIST
from tbl_Items where ItemID = " & me.SELECTEDITEMCOMBO
docmd.runsql "insert into tbl_quote_items_detail (FIELD_LIST) select
FIELD_LIST from tbl_Items_Detail where ItemID = " & me.SELECTEDITEMCOMBO

Remember to grab the ID that you need to link your Order and include that in
your insert, otherwise you will be in a bit of strife.

Hope this helps.

Damian.
 
Thank you Damian. I will try this approach. I'm not terribly experienced in
using code of this type, but it sounds pretty straightforward. Greatly
appreciate your help!
Louise
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top