How to save new form records

[sud2017]

Hi Expert,



I have a Template Form which I am using for cost estimation. It contain some default values for itemID, itemName, Price, Qty, TotalCost and QuoteID. Once the user completes the cost estimation for a particular QuoteID, I want the values to be saved and then be able to make new entries for new QuoteID and then keep saving the form records as the user perform new estimations in this costing form.

But presently I am not able to save new records on form, only the existing values get overwritten. I have added a Save command button, but that doesn't help to save new record in the form.

All fields in my form belong to a table and doesn't have any textbooks.

I am still learning Access so any help or directions would be greatly appreciated. Thanks.

[davegri]

Attachment 29792
At the bottom of your form, click on the indicated icon to show a blank form for a new record.

[Micron]

It might help to also point out that if you go to an existing record and need to change something on your bound form, simply navigating off the record should effect the changes (one of the single arrow buttons).

[sud2017]

Hi Davegri,

Clicking that icon gets me to a new record in the form, however, I want to use the preexisting values of the form including itemname, itemID, and unit price and be able to edit the quantity and then save the form for a new QuoteID. Idea is that few customers order 1 door assembly and few order multiple door assemblies, so my form contains all items and qty that makes 1 door assembly, and then when a customer order 3 door assemblies, I want to open the same form, change the quantity for items for 3 door assemblies and then save the quote with a new id.

In summary, I want to edit few values of existing form and save them with a new quoteID for future access. attached is screenshot of my form. Thank you very much for any possible help, still learning the stuff.

[aytee111]

Need more information.

?? You would like to create a new quote based on "default values for itemID, itemName, Price, Qty, TotalCost". These records are stored on a table. To create a new quote, add a record to the quote table, look up the new quote ID, add these default records to the quote items table with the new quote ID. Then display these new records on your form so that you can change the quantity.

[sud2017]

With few changes on what you described:-

Step1 - User opens up a Mainform that displays 10 quote opportunities in combobox. User selects one opportunity with unique OpportunityID, which opens up the quote form that contains default values for itemID, itemName, Price, Qty, Total Cost". For example, these default quantities are sufficient to make 1 door (items include door frame, handle, door sign etc.).

Step2 - User can change the quantity in the form which is opened for multiple door assemblies and then save this form with a unique QuoteID. Once the form is saved, I want it to save the component list with default values and changed quantities to that particular QuoteID, in order to view it later.

Step3: If user opens that quote opportunity again for a different customer, he can change quantity again and save the quoteform with next QuoteID

Step4: After few times, we will have many QuoteIDs saved for different customers.

PS - currently all my fields, itemID, itemName, Qty, Price, Description, QuoteID are from one table.

I hope it gives some clarity. Thanks so much for your time in helping me. Need to get it resolved to show to my manager early next week and struggling with it for quite some time

Need more information.

?? You would like to create a new quote based on "default values for itemID, itemName, Price, Qty, TotalCost". These records are stored on a table. To create a new quote, add a record to the quote table, look up the new quote ID, add these default records to the quote items table with the new quote ID. Then display these new records on your form so that you can change the quantity.

[davegri]

Could you post your DB?
I think the table you have will be the Opportunities table, and not change unless the quote variables change. It will have its own form to update values when prices, descriptions, etc change.
Then, you need a customer table and an orders table.
The new records will go into the orders table, with predefined values from the reference table using a form like TestForm1.
Does this sound like the way to go?

[Micron]

struggling with it for quite some time

I take it that no one stepped up to help at the access-programmers uk forum where you started with this.
It's interesting that this subject has come up again in this forum - see https://www.accessforums.net/showthread.php?t=67332
2 or 3 suggestions were given in that thread and I thought the temp table method was the chosen one, but I might be mixing up that notion with another forum. It seems I've responded to this issue at least 2x lately (aside from yours).

[aytee111]

1 - user selects copportunity - make a listbox of the entries to display the records for that opportunity
2 - command button Add New Quote (change names as needed)

Private Sub cmdAddNewQuote_Click()
    Dim rst As Recordset, NewID As Long
    
        'add a new record to the quotes table and get the ID
    Set rst = CurrentDb.OpenRecordset("Quotes_table", dbOpenDynaset)
    rst.AddNew
    NewID = rst!id
    rst.Update
    
        'run an append query to copy the records from the opportunities table - based on the ID in the select combobox
    DoCmd.RunSQL "INSERT INTO QuoteItems_table ..." & NewID & " AS QuoteID WHERE OpportunityID=" & Me!SelectOpportunity & ";"
    
        'display the new quote (this assumes that the quotes table is the record source for the main form)
    Me.Filter = "ID=" & NewID
    Me.FilterOn = True
    Me.Requery
    
End Sub

3 - have 1 textbox for the quantity, in the AfterUpdate of this field (if it is >0) run an update query to take the records from the opportunity table, multiply each record's quantity by the new value.

[sud2017]

Yes, I will post my DB tonight after cleaning it up, its on my home computer.

Per your suggestion, I have created a relationship diagram in SKETCH 1 Image and Flow diagram of my FORMS in Sketch 2 Image below.

I think I will have to sort out these things first before jumping on to create a form that will allows me to "create new quote" and "view saved quoted" for different opportunities. You are right, there are different quote OPPORTUNITIES (called Door, Roof, Floor etc.) and then there are many Quotes that is created by users for single opportunity. Fore example, Opportunity1 (Doors) will have many QuoteIDs (such as Doors001, Doors002, Doors003 ...so on). These Quote IDs (Doors001, Doors002, Doors003...) will use same OPPORTUNITY Template, and will be saved as new QuoteID after changing quantities and saving the Quote for a specific customer. These Quotes are saved for viewing in future.

PROBLEM IS - my current Quote Form has all fields coming from ONE TABLE, so when I try to create a new Quote, I ONLY CHANGE THE QUANTITY of these items (BUT the item name, description, and UnitPrice remain the same). But when I do it, the quantities get overwritten on the same fields and the Table is saved for same fields with overwritten values for QuoteID and with new quantities. So I am not able to create different Quotes records that share same quote template. There will be many such quote template in my DB going forward. I hope I have not confused you more.

SKETCH 1 - Relationship Diagram



SKETCH 2 - FLOW OF FORMS

Could you post your DB?
I think the table you have will be the Opportunities table, and not change unless the quote variables change. It will have its own form to update values when prices, descriptions, etc change.
Then, you need a customer table and an orders table.
The new records will go into the orders table, with predefined values from the reference table using a form like TestForm1.
Does this sound like the way to go?

[sud2017]

I will have to digest this information and am sure to have some questions. So I'll bug you again. thanks for being so helpful.

1 - user selects copportunity - make a listbox of the entries to display the records for that opportunity
2 - command button Add New Quote (change names as needed)

Private Sub cmdAddNewQuote_Click()
    Dim rst As Recordset, NewID As Long
    
        'add a new record to the quotes table and get the ID
    Set rst = CurrentDb.OpenRecordset("Quotes_table", dbOpenDynaset)
    rst.AddNew
    NewID = rst!id
    rst.Update
    
        'run an append query to copy the records from the opportunities table - based on the ID in the select combobox
    DoCmd.RunSQL "INSERT INTO QuoteItems_table ..." & NewID & " AS QuoteID WHERE OpportunityID=" & Me!SelectOpportunity & ";"
    
        'display the new quote (this assumes that the quotes table is the record source for the main form)
    Me.Filter = "ID=" & NewID
    Me.FilterOn = True
    Me.Requery
    
End Sub

3 - have 1 textbox for the quantity, in the AfterUpdate of this field (if it is >0) run an update query to take the records from the opportunity table, multiply each record's quantity by the new value.

[aytee111]

Note: I had a quotes header table and a quote items table, in your drawing it looks like you put the items on to the Orders table. Orders require an order header table and an order items table.

[sud2017]

Which fields should these tables contain and how many different tables should I have?

Quote Header Table, Quote Item Table, Order Header Table, Order Items Table

Please advise. Thank you!

Note: I had a quotes header table and a quote items table, in your drawing it looks like you put the items on to the Orders table. Orders require an order header table and an order items table.

[aytee111]

You need a table to store header information, fields that apply to everything. Examples are customer, ID number, date entered, salesman, etc. Then you need the details that apply to that ID number (as shown in post # 4). Those are the minimum tables you need. (Think of something you have personally received, a receipt or a bill or something, see the information at the top, that is the header information, then there are multiple lines of detailed information below, those are the items that apply to it.)

Some businesses require a quote prior to an order being created, some businesses do not. Do you wish to track quotes separately from orders, such as a customer will ask you for a quote which you give them, but it does not become an order? If you do not need to keep track of them then the two quote tables are not required, if all your business is done through orders only.

Talk us thru your business requirements and we can help further.

[sud2017]

Understood. Thanks. If I have separate Tables to store header information (header info will include 'Quote ID' and 'Description' from QuotesT Table, and 'OppID' from OpportunityT Table), how could I link the QuoteID and OppID to the specific order records stored in details of the form (which is from Orders Table), to be able to view the unique QuoteID record in future. Kindly suggest. Thanks again.

You need a table to store header information, fields that apply to everything. Examples are customer, ID number, date entered, salesman, etc. Then you need the details that apply to that ID number (as shown in post # 4). Those are the minimum tables you need. (Think of something you have personally received, a receipt or a bill or something, see the information at the top, that is the header information, then there are multiple lines of detailed information below, those are the items that apply to it.)

Some businesses require a quote prior to an order being created, some businesses do not. Do you wish to track quotes separately from orders, such as a customer will ask you for a quote which you give them, but it does not become an order? If you do not need to keep track of them then the two quote tables are not required, if all your business is done through orders only.

Talk us thru your business requirements and we can help further.