How to add a Record Based on Existing Record

[prashantdixit]

Hi,
I am developing a stock control software. Iam stuck somewhere.
I have a form "Add new stock" consisting of combobox, text boxes etc. which is used to add records in a table.

I have another form which has "Add Similar To" button.

The table which i used to store records contains a field call "Add More Quantity". If a user enters any numerical value in this field against any record and click on "Add Similar To" button then same "Add New Stock" form should open with all the records pre-populated in corresponding controls in the form. Add New Stock form also contain "Quantity" Field. so the value which user has entered in "Add More Quantity" should now be displayed in "Quantity" field.



Now this will allow user to add another record based on existing record with new values in quantity field.
On clicking 'Add Similar to' button, the "Add New Stock" Form should not open in Edit mode rather it should open in Add mode.

I am a beginner and don't have any idea to do this.

Can someone please help me as its very urgent.

Thanks
Prashant

[jimatqsi]

Your click event code for AddSimilar will look something like this

Private Sub cmdAddSimilar_Click()
On Error GoTo Err_cmdAddSimilar_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stDocName = "frmAddNewStock"
    stLinkCriteria = "[strCustomerID]=" & "'" & Me![strCustomerID] & "'"
    DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormAdd
    Forms!frmAddNewStock!nameofitemtextbox = Me!nameofitemtextbox
    Forms!frmAddNewStock!nameofquantitytextbox = Me!nameofquantitytextbox
    'repeat for any other textboxes you can fill in on the new Form
    
Exit_cmdAddSimilar_Click:
    Exit Sub

Err_cmdAddSimilar_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddSimilar_Click
    
End Sub

Note that when you add a button to a form the wizard does a lot of this for you.

Jim

[prashantdixit]

Thanks a lot it worked

[prashantdixit]

Just found one issue with this.
If you click Add similar button then its working fine for first time. But if you don't add the record rather click on 'Close' button and again click on 'Add Similar to' button then it always opens "Add New Stock" form with populating record from the first row of subform "tblStock_Inven tory_subform" and not that row against which "dblQuantity_Of _Stock_To_Check _Out" is null

Here is the code for addsimilar button:

Private Sub ButtonAddSimilar_Click()
    On Error GoTo Add_similar_stock_based_on_Err

    DoCmd.Echo True, ""
    If (DCount("dblQuantity_Of_Stock_To_Check_Out", "tblStock_Inventory") = 0) Then
        Beep
        MsgBox "No stock added, please ensure that there is data in the ""QUANTITY OF STOCK TO CHECK OUT"" column of the ""Stock Inventory"" sheet. This column is used to identify the stock on which the new stock will be based on.", vbOKOnly, ""
        Exit Sub
    End If
    DoCmd.OpenForm "FormAddNewStock", acNormal, "", "[tblStock_Inventory].[dblQuantity_Of_Stock_To_Check_Out] Is Not Null", acAdd, acNormal
    DoCmd.OpenForm "tblStock_Inventory_subform", acNormal, "", "[tblStock_Inventory].[dblQuantity_Of_Stock_To_Check_Out] Is Not Null", acFormAdd, acHidden
    Forms!FormAddNewStock!ComboProject = Me!tblStock_Inventory_subform!strProject
    Forms!FormAddNewStock!ComboPartNumber = Me!tblStock_Inventory_subform!strPart_Number
    Forms!FormAddNewStock!ComboBatchNumber = Me!tblStock_Inventory_subform!strBatch_Refrence
    Forms!FormAddNewStock!ComboTracabilityRef = Me!tblStock_Inventory_subform!strTracability_Reference
    Forms!FormAddNewStock!ComboStoreLoc = Me!tblStock_Inventory_subform!strStore_Location
    Forms!FormAddNewStock!ComboLocInStr = Me!tblStock_Inventory_subform!strLocation_In_Store
    Forms!FormAddNewStock!ComboPartType = Me!tblStock_Inventory_subform!strPart_Type
    Forms!FormAddNewStock!ComboPartName = Me!tblStock_Inventory_subform!strPart_Name
    Forms!FormAddNewStock!ComboManufacturer = Me!tblStock_Inventory_subform!strManufacturerOrSupplier
    Forms!FormAddNewStock!ComboFinish = Me!tblStock_Inventory_subform!strFinish
    Forms!FormAddNewStock!ComboMaterial = Me!tblStock_Inventory_subform!strMaterial
    Forms!FormAddNewStock!TextBoxPurchaseSpecification = Me!tblStock_Inventory_subform!strPurchase_Specification_Notes
    Forms!FormAddNewStock!TextBoxQuantity = Me!tblStock_Inventory_subform!dblQuantity_Of_Stock_To_Check_Out
    Forms!FormAddNewStock!TextBoxWeight = Me!tblStock_Inventory_subform!dblIndividual_Weight_In_gm
    Forms!FormAddNewStock!TextBoxWeightQuantity = 1


Add_similar_stock_based_on_Exit:
    Exit Sub

Add_similar_stock_based_on_Err:
    MsgBox Error$
    Resume Add_similar_stock_based_on_Exit
End Sub

Can you help me with this?

[jimatqsi]

Prashant,
Have you checked the table to see if the new item is already in there in spite of your click on 'Close' button?

I think 1 of 2 things is happening. Either the Add New Stock form is adding the unpopulated record, and the second visit to the form trips over that record being already in the table. Have you got the same error handling in all of your routines? If you do, then I doubt that theory.

Or the calling form is no longer on the same current record it was on during the first click. That would be determined by what other events are happening during this process.

Debug through the calling program, or add a message box(es), so you can see what item number the calling program thinks it is referring to.

Jim

[NeoPa]

Debugging link in case it helps - Debugging in VBA.

[prashantdixit]

I have tried debugging and found that:

When i click on "Add Similar to" button for the first time, Add New Stock Form opens while populating first record in the subform. This is because calling form is no longer on the same current record. The control comes to the first record in the subform and if i take the control in the row which contains value against "dblQuantity_Of _Stock_To_Check _Out", then it populate the field correctly.

That means, i close the Add New Stock button for the first time and there after if i open add new stock form by selecting any row, the value populates corresponding to this row and and not that row against which "dblQuantity_Of _Stock_To_Check _Out" is null


Error Handling is not like as i mentioned in the code. This is just converted from macros that's why it's like that

[NeoPa]

If I understand you correctly, that means you should do one of two things :

1. Close [Add New Stock Form] between uses.
2. Ensure [Add New Stock Form] is re-initialised fully before each use.

Clearly the first would be easier, but it may introduce a certain level of overhead while the form is opened again.

[prashantdixit]

Yes you are right.
I have tried it using macros samething and its working fine but not on click event(using code builder) of Add Similar to button.
Thanks alot for all your prompt replies.

With macro however i want two things:
If user adds new stock or any new record then,
(a). Subform get refreshed and newly added record should be displayed (which i have done using form.requery on Add New Stock Form_Close event)
(b). this new record i.e. the entire "row" should be highlighted in the subform(this subform shows all the records of table in Readonly mode)and focus should be on this row.


I'll be highly obliged if you can help me on this new issue

[NeoPa]

I'm afraid my understanding of your situation is far too unclear for me to offer anything but very general pointers at this point in time.

If you can illustrate what you're thinking about with some attempt at coding it yourself I could maybe help you fill in the blanks.

[prashantdixit]

Hi,
Sorry but i am just a beginnner and trying to learn access/VBA.

Please find attached the screenshot(two screenshots) of application.

Code is same for "Add Similar button" as mentioned above

This is Code converted from Macro in access vba.
The same macro i used on "Add Similar Stock" button

Function Add_similar_stock_based_on()
On Error GoTo Add_similar_stock_based_on_Err

    DoCmd.Echo True, ""
    If (DCount("dblQuantity_Of_Stock_To_Check_Out", "tblStock_Inventory") = 0) Then
        Beep
        MsgBox "No stock added, please ensure that there is data in the ""QUANTITY OF STOCK TO CHECK OUT"" column of the ""Stock Inventory"" sheet. This column is used to identify the stock on which the new stock will be based on.", vbOKOnly, ""
        Exit Function
    End If
    DoCmd.OpenForm "tblStock_Inventory_subform", acNormal, "", "[tblStock_Inventory].[dblQuantity_Of_Stock_To_Check_Out] Is Not Null", acAdd, acHidden
    DoCmd.OpenForm "FormAddNewStock", acNormal, "", "[tblStock_Inventory].[dblQuantity_Of_Stock_To_Check_Out] Is Not Null", acAdd, acNormal
    Forms!FormAddNewStock!ComboProject = Forms!tblStock_Inventory_subform!strProject
    Forms!FormAddNewStock!ComboPartNumber = Forms!tblStock_Inventory_subform!strPart_Number
    Forms!FormAddNewStock!ComboBatchNumber = Forms!tblStock_Inventory_subform!strBatch_Refrence
    Forms!FormAddNewStock!ComboTracabilityRef = Forms!tblStock_Inventory_subform!strTracability_Reference
    Forms!FormAddNewStock!ComboStoreLoc = Forms!tblStock_Inventory_subform!strStore_Location
    Forms!FormAddNewStock!ComboLocInStr = Forms!tblStock_Inventory_subform!strLocation_In_Store
    Forms!FormAddNewStock!ComboPartType = Forms!tblStock_Inventory_subform!strPart_Type
    Forms!FormAddNewStock!ComboPartName = Forms!tblStock_Inventory_subform!strPart_Name
    Forms!FormAddNewStock!ComboManufacturer = Forms!tblStock_Inventory_subform!strManufacturerOrSupplier
    Forms!FormAddNewStock!ComboFinish = Forms!tblStock_Inventory_subform!strFinish
    Forms!FormAddNewStock!ComboMaterial = Forms!tblStock_Inventory_subform!strMaterial
    Forms!FormAddNewStock!TextBoxPurchaseSpecification = Forms!tblStock_Inventory_subform!strPurchase_Specification_Notes
    Forms!FormAddNewStock!TextBoxQuantity = Forms!tblStock_Inventory_subform!dblQuantity_Of_Stock_To_Check_Out
    Forms!FormAddNewStock!TextBoxWeight = Forms!tblStock_Inventory_subform!dblIndividual_Weight_In_gm
    Forms!FormAddNewStock!TextBoxWeightQuantity = 1


Add_similar_stock_based_on_Exit:
    Exit Function

Add_similar_stock_based_on_Err:
    MsgBox Error$
    Resume Add_similar_stock_based_on_Exit

End Function

I'll be thankful if you can help me what i mentioned in screenshot

Thanks
Prashant

[NeoPa]

I'm sorry Prashant, but this doesn't even get me to first base. The pictures are unfortunately stored (on this site at the moment) in too low a resolution to mean much, and there is no real explanation of what you're doing and where it's failing.

I think the best I can do for you is to explain as well as I can the fundamentals of duplicating data on a form. From there we can possibly deal with detailed items as they come up. I am not here to design your project for you though. It sounds like this is what you want, but that's not what we do generally. We're happy with specific questions of limited scope. We cannot spend hours on each question. Things couldn't possibly work that way. We are all volunteers helping in our spare time. Anyway, let's see if we can describe the basic concepts and go from there.

When I want to duplicate certain values onto another record, what I do is set the default values for the controls on the form to reflect those values. This enables the operator to choose whether or not to add the record. If no changes are made, then no record is saved. If changes are made, or if the operator chooses to save the record anyway with just the default values, then the record is saved.

I don't understand where your code is coming from, but if there are two forms involved, A and B, and you have code running in A to duplicate values found in A onto form B, then the code doesn't need to (and shouldn't as it simply confuses matters) refer to its own controls using a full reference (Forms!A!Contro lName). Rather, it should use the Me. version of the reference (See Referring to Items on a Sub-Form). This will make the code easier to write and clearer to read.