none
Add a subrecord without resetting primary record RRS feed

  • Question

  • Hey guys, I have two tables. One includes the name of clients and the other includes the history of those client's orders. I made a form with two listboxes that filters records of the client and his orders. Now I want to use the same form so I can search if the client exists and if he doesn't I want to add a new client, so I want to be able to create the client and then start adding orders to the profile. My problem is when I click my button to add a new order, it resets the entire record of client as well with the runcmd.adrecordgotonew. How can I make it so that it adds a new record to the client's subrecord table instead of resetting the client as well? Don't know if I'm making any sense.
    Tuesday, August 27, 2019 12:42 PM

Answers

  • Here's the issue I'm running into. If I have a new client and I want to add him, then add an order associated with the client when I press the new client button it all works, BUT when I tab into my subform to enter my first order, the client information deletes.


    You might like to take a look at SaveDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates means of forcing the user to save a record by means of a command button.  It does this partly by enabling/disabling the appropriate buttons with code in the form's module.  The simple form in the demo does not include a subform, but I temporarily created and added an orders subform to the form to confirm that it would not be possible to move focus to the subform until a new or edited parent record had been saved by means of the command button.  The behaviour was as expected.


    Ken Sheridan, Stafford, England

    • Marked as answer by InnVis Wednesday, August 28, 2019 2:25 PM
    Tuesday, August 27, 2019 8:34 PM

All replies

  • Create a main form bound to the clients table and a subform bound to the orders table, linked by the client ID.

    The user can then create a new record in the orders subform for the current client. Access will automatically fill in the linking field in the new record.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 27, 2019 1:31 PM
  • What about if I enter a completely new client? I have a function in my form that prevent save by making mSaved as a boolean. The issue I'm having is that if I enter a new client and I click to start a new order before saving the client, it deletes all my data. Ideally I'd like for it all to stay until I hit save.
    Tuesday, August 27, 2019 2:14 PM
  • I don't know the code you're using, obviously, but you could check whether the main form has a value for the linking field before creating a record in the subform.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 27, 2019 2:40 PM
  • Should I put the new record button in the subforms or in the main form? For example if I put RunCommand acCmdRecordsGoToNew on the order subform, will it retain the link to the "client" and if I do the reverse to the client, and add a new record will it mess up the orders?
    Tuesday, August 27, 2019 3:03 PM
  • To add a new client, you can use a New Record button on the main form. RunCommand acCmdRecordsGoToNew called from the main form will add a new client.

    To add a new order, you can use a New Record button on the subform. RunCommand acCmdRecordsGoToNew called from the subform will add a new order in the subform for the current client in the main form.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Tuesday, August 27, 2019 3:51 PM
  • Here's the issue I'm running into. If I have a new client and I want to add him, then add an order associated with the client when I press the new client button it all works, BUT when I tab into my subform to enter my first order, the client information deletes. The issue is coming from this bit of code:

    rivate Sub Form_BeforeUpdate(Cancel As Integer)
    
    If mSaved = False Then
    Cancel = True
    Me.Undo
    Cancel = False
    End If
    
    End Sub

    I use this to prevent unintentional records without pressing the "SAVE" button. Is there anyway to adapt this code to still prevent saving without clicking the save button, but not deleting my data when I tab over to new form?

    Tuesday, August 27, 2019 3:56 PM
  • I fear that'll be complicated.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 27, 2019 4:14 PM
  • Doh! I wonder If I add unbound textboxes to the main form. HMMMM It would feel like an awful transition.
    Tuesday, August 27, 2019 4:25 PM
  • I tried to alter the code to validate the data before it would be saved, but if user clicks out of it multiple time ignoring the warning, it will save the incomplete fields regardless, any ideas how I can improvise on this OR keep the data temporarily displaying until I hit save without actually saving?

    If IsNull([txtVADPatientLastName]) Then
        MsgBox "Please enter a patient's LAST NAME.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientLastName].SetFocus
        mSaved = False
        Exit Sub
    ElseIf IsNull([txtVADPatientFirstName]) Then
        MsgBox "Please enter a patient's FIRST NAME.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientFirstName].SetFocus
        mSaved = False
        Exit Sub
    ElseIf IsNull([txtVADPatientDOB]) Then
        MsgBox "Please enter a patient's Date of Birth.", vbCritical, "REQUIRED FIELD"
        [txtVADPatientDOB].SetFocus
        mSaved = False
        Exit Sub
    ElseIf MsgBox("Do you wish to save this new patient now?", vbYesNo) = vbYes Then
        mSaved = True
        Me.lstVADPatients.Requery
    Else
    mSaved = False
    End If
    
    If mSaved = False Then
    Cancel = True
    Me.Undo
    Cancel = False
    End If

    Tuesday, August 27, 2019 5:04 PM
  • I also tried to bind the main form to a query and creating a new client and a new record for that client works well BUT, I cannot create a new record for existing client because it goes to a completely new line on the query wiping out the client information with it. Any work arounds?
    Tuesday, August 27, 2019 6:57 PM
  • wiping out the client information with it. Any work arounds?

    Hi InnVis,

    What happens if you remove the   Me.Undo   line?

    Imb.

    Tuesday, August 27, 2019 7:13 PM
  • Without Me.Undo, the record saves regardless. =/
    Tuesday, August 27, 2019 8:27 PM
  • Without Me.Undo, the record saves regardless. =/

    That is because it is followed by Cancel = False.  Delete that line too.

    What happens in that case?

    Imb.

    Tuesday, August 27, 2019 8:31 PM
  • Here's the issue I'm running into. If I have a new client and I want to add him, then add an order associated with the client when I press the new client button it all works, BUT when I tab into my subform to enter my first order, the client information deletes.


    You might like to take a look at SaveDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates means of forcing the user to save a record by means of a command button.  It does this partly by enabling/disabling the appropriate buttons with code in the form's module.  The simple form in the demo does not include a subform, but I temporarily created and added an orders subform to the form to confirm that it would not be possible to move focus to the subform until a new or edited parent record had been saved by means of the command button.  The behaviour was as expected.


    Ken Sheridan, Stafford, England

    • Marked as answer by InnVis Wednesday, August 28, 2019 2:25 PM
    Tuesday, August 27, 2019 8:34 PM
  • Hey this is a great repository! Your suggestion worked, but I ended up doing a simpler workaround with an invisible subform sitting on top of the exact same fields on the query form. The subform becomes visible when the "new order" button is clicked and disapears when not needed! 
    Wednesday, August 28, 2019 2:26 PM