Splitform w/Subform. subforms new record button applying to parent forms record (1 Viewer)

[Jordonjd]

Hi everyone, Im hoping someone can help with this.

I have a Splitform"Stocktable" and it has a subform "StockMove"
(The splitform only displays the datasheet half and the individual fields are hidden)

A user will select a line/record in the Datasheet and relating stock moves appear in the "stockmove" subform.
There is a new record button in the subform

I have noticed that if a user is editing a field in the parent form "location" field, but they have not exited/moved to another field then the "New Record" button applies the new record to the parent form "StockTable".

Im trying to understand if there is away i can "save/Update" that field so the New record button in the subform does not accidently create new record in the parent form?

(the purpose of this form is that people select different stock lines and can create new stock movement relating to them in the subform so if the new record applies to the parent form, there is potential for people to create stock moves that do not relate to any stock)

Thanks in advance and let me know if you need any further info etc

 

[theDBguy]

Hi. That's interesting. Are you able to post a demo version of your db, so we can take a look?

 

[Jordonjd]

Hi. That's interesting. Are you able to post a demo version of your db, so we can take a look?

Im not entirely sure how to do that, and i may potentially die from embarrassment if anyone with genuine access/DB knowledge saw my Frankenstein of a database

I can have a look at stripping a copy back for people to see...

 

[theDBguy]

Im not entirely sure how to do that, and i may potentially die from embarrassment if anyone with genuine access/DB knowledge saw my Frankenstein of a database

I can have a look at stripping a copy back for people to see...

Thanks. In the meantime, can you post some screenshots?

 

[Pat Hartman]

Are you using an Access split form or are you just describing a regular form that you created that has more than one subform on it?

 

[Jordonjd]

Thanks. In the meantime, can you post some screenshots?

Sure,

Are you using an Access split form or are you just describing a regular form that you created that has more than one subform on it?

I am using a splitform with subforms,

I dont know the terminology but i can tell that because the user has not yet moved onto the next field from the parent form that the field is still in a state of "not fully updated" so when the subforms new record button is clicked that parent record state seems to maintain the focus and the new record code runs on the parent form instead of the subform.

I just select a record in the parent form and click the subforms button it works fine,
and if you edit a record it works fine,
it just seems to be if the user has not finished navigating away from a field in the record that was/is being amended

 

[Jordonjd]

Thanks. In the meantime, can you post some screenshots?

Thanks,

This is the form:

Top half = parent form
bottom half = subform
parent/child is" "ID" & "StockID"

if the record is selected and the"New Stock Move" button is clicked it will create a new stock move

Which is fine.. but if a field is being edited and has not been moved away from (sorry for bad terminology) like this

Then it creates the new record in the parent form

Sorry for the messy post

 

[theDBguy]

Sorry for the messy post

No worries. Thanks for posting those images. Now, can you post the code behind the "New Stock Move" button?

 

[Jordonjd]

No worries. Thanks for posting those images. Now, can you post the code behind the "New Stock Move" button?

Sure, i just used the "Convert Macro to VBA" function (again apologies for lack of naming the control). The button is within the subform

Private Sub Command70_Click()

On Error GoTo Command70_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If


Command70_Click_Exit:
    Exit Sub

Command70_Click_Err:
    MsgBox Error$
    Resume Command70_Click_Exit

End Sub

 

[theDBguy]

Sure, i just used the "Convert Macro to VBA" function (again apologies for lack of naming the control). The button is within the subform

Private Sub Command70_Click()

On Error GoTo Command70_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If


Command70_Click_Exit:
    Exit Sub

Command70_Click_Err:
    MsgBox Error$
    Resume Command70_Click_Exit

End Sub

Okay, see if this makes a difference. Before the DoCmd line to go to a new record, try setting the focus to the subform first.

 

[Jordonjd]

Okay, see if this makes a difference. Before the DoCmd line to go to a new record, try setting the focus to the subform first.

I have tried setting the focus to specific controls on the subform sadly without working. to the best or my knowledge i believe i just tried setting the focus to just the subform but sadly no.

I have just realised that this is occurring if the parent record is in the progress of being changed or has been changed but it has not yer moved onto a new record/line. (not specific to the fields of the line like i first mentioned)

It cant think that i would not have noticed such an obvious error to be in the form, its been used for literally months

 

[Gasman]

Sure, i just used the "Convert Macro to VBA" function (again apologies for lack of naming the control). The button is within the subform

Private Sub Command70_Click()

On Error GoTo Command70_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If


Command70_Click_Exit:
    Exit Sub

Command70_Click_Err:
    MsgBox Error$
    Resume Command70_Click_Exit

End Sub

Are you actually ever going to get a MacroError populated?

 

[Jordonjd]

Are you actually ever going to get a MacroError populated?

Sorry it probably sounds stupid but i dont know what you mean. I've always just used the macro's for new records etc and i only converted it to vba to see if there was a way of fixing this problem.

All of my vba knowledge is literally from googling and paste and changing things until it works somehow

I have functions and email generation on my db but i wouldnt be able to create the code from scratch at all

 

[Gasman]

When you run a Macro it looks like any error is reported to the MacroError object.
In VBA it is reported to the Err object.

So I was curious as to whether that MacroError would even get a value if you run that code in VBA. I do not believe it would, so you cannot rely on/use it.?

 

[Jordonjd]

the only other thing i can add is the form works absolutely fine with adding new records to the subform.

If the parent record is selected without being edited.
OR
if it has been edited. Then select another parent form record and then select your original record

That is the "saving/finshing the edit state i was trying to replicate to allow the form to work.

I do have an IF statement saying that the moves cannot be processed if they is no relating StockID but this is really bugging me

Lastly, if i add

RunCommand acCmdSaveRecord

to the AfterUpdate event, you can tab to the next column and the new record button will work ok on the subform.. so i could add this to each editable field on the parent form?

 

[Jordonjd]

When you run a Macro it looks like any error is reported to the MacroError object.
In VBA it is reported to the Err object.

So I was curious as to whether that MacroError would even get a value if you run that code in VBA. I do not believe it would, so you cannot rely on/use it.?

Beyond me im afraid

 

[Pat Hartman]

1. I hate to be pedantic but "split form" has a specific Access meaning. I tried to clarify but you still said you a "split form". I'll try again. A split form is a special type of Access form that shows both a list view and a single record view of the same recordset. You do not have a split form so we can go on from there.
2. When "new" shows on the empty record, it is just a place holder and a new record has NOT been created. A new record will not be created until you dirty the record and then it would still only be created if you had no validation code in the form's BeforeUpdate event to prevent creating "empty" records.
3. It is always good to have some minimum level of validation code in the BeforeUpdate event of every form. If only to check that one single, important field is present and if it isn't, cancel the update and give the user an error message.

If Me.txtCustomerID & "" = "" Then
    Msgbox "Customer is required.", vbOKOnly
    Cancel = True
    Me.txtCustomerID.SetFocus
    Exit Sub
End if

You could include Me.txtCustomerID.Undo but I don't like to back out what the user typed. If they make a mistake, I prefer that they see what is there and are able to correct it.

The form will continue to raise an error every time they try to leave until the fill in CustomerID because you are cancelling the save and preventing the bad data from being saved.

 

[Jordonjd]

Hi Pat,

it is genuinely a split form, as in an actual access split form, I set the fields in the individual half to Not visible and lay the subform over the top

At the time it was the only way i could think of having a datasheet view whilst still being able to specify the selected record for filters, commands etc

I understand it does not "Create" a new record like you said. but there is a blank record with a populated FK of the parent forms PK if it is "created"

You're right i dont have any validation code in the before update event. I have a couple of functions on the"Current" event to lock the subforms fields if the FK is not populated.

That Code you posted seems like a much simpler and proper solution so thank you for that, its really helpful

Maybe it just needs to be built from scratch, even i cringe when i look at this form, and i was may favourite at one point

 

[Pat Hartman]

Noting in the current event or any other event except BeforeUpdate is ever going to prevent a bad record from being saved. Access feels a moral responsibility to save your data so your best option is to go with the flow and use the event that they intended you to use to stop Access from saving a record that should not be saved. It is really best to use events for their intended purpose. Think of the BeforeUpdate event as the flapper at the end of a funnel. If you have no errors, the flapper opens and the record is saved. If there are errors, you cancel the save and normally you just leave the record dirty. However, in some cases, it makes sense to undo all the changes, that way when you cancel the update, the record doesn't save but it is also not dirty so the user can close out of the form gracefully.

If you want to run validation from multiple places, you can make functions that you call from multiple places. The function should return a boolean true or false. True = valid, force = invalid. Then the BeforeUpdate event would call the function and if the result is false, set the cancel flag to true and exit the sub. Other messages and setfocus code would go in the called function.

 

[Jordonjd]

Noting in the current event or any other event except BeforeUpdate is ever going to prevent a bad record from being saved. Access feels a moral responsibility to save your data so your best option is to go with the flow and use the event that they intended you to use to stop Access from saving a record that should not be saved. It is really best to use events for their intended purpose. Think of the BeforeUpdate event as the flapper at the end of a funnel. If you have no errors, the flapper opens and the record is saved. If there are errors, you cancel the save and normally you just leave the record dirty. However, in some cases, it makes sense to undo all the changes, that way when you cancel the update, the record doesn't save but it is also not dirty so the user can close out of the form gracefully.

If you want to run validation from multiple places, you can make functions that you call from multiple places. The function should return a boolean true or false. True = valid, force = invalid. Then the BeforeUpdate event would call the function and if the result is false, set the cancel flag to true and exit the sub. Other messages and setfocus code would go in the called function.

Hi Pat,

Thankyou for the reply, thats really helpful. I'll probably look at my existing functions and see about editing them and calling them from the beforeupdate like you said.

Thanks for everybody elses replies as well

i should have joined this forum a long time ago
Thanks again