Solved Best way to use value when form is opened from another form.

G

Gaztry80

Member
Local time
Today, 22:49
Joined
Aug 13, 2022
Messages
62
Hi,

I have frmTableA (persons) and want to open frmTableB when I double click on the "Name" column from frmTableA.
frmTableB opens the TableB (friends) table, where friends can be entered which need to correspond to the NameId from frmTableA.
Opening frmTableB I have managed by a small vba code.

The way I am currently doing this is by setting the defaultvalue of NameId in frmTableB to "=[Forms]![frmTableA]![NameId]" and this is working.
However, when frmTableB is open and I navigate back to frmTableA and select another record, a mistake could be made when I then go back again to frmTableA.
Could somebody let me know if their is a better way of establishing what I want with frmTable B? :D

Already many thanks for your help.
 

Attachments

I have frmTableA (persons) and want to open frmTableB when I double click on the "Name" column from frmTableA.
Click to expand...

You are doing this via a DoCmd.OpenForm which is the correct method.

a mistake could be made when I then go back again to frmTableA.
Click to expand...

You have to be more specific. What mistake are you fearful of?

In general its good practice to just have one form open at a time. Not at the forefront, but open. My advice would be to close frmTableA when you open frmTableB. Then when you need to go back to frmTableA provide a button or some other method to get back there. And of course close frmTableB when you do.
 
plog said:
You are doing this via a DoCmd.OpenForm which is the correct method.



You have to be more specific. What mistake are you fearful of?

In general its good practice to just have one form open at a time. Not at the forefront, but open. My advice would be to close frmTableA when you open frmTableB. Then when you need to go back to frmTableA provide a button or some other method to get back there. And of course close frmTableB when you do.
Click to expand...
I don't like the Idea that after opening frmTableB that corrupted data can be entered when first selecting a different record in frmTableA (*edit) and then refresh frmTableB. Having a possibility to enter biased data is not something I want. Therefore, when opening frmTableB, the NameId should always stay the same till the form is closed and re-opened by another record.
 
Last edited:
Your current frmTableB should be 2 forms (main and sub).

The main from should be based on TableA and should open to just 1 record from it. The subform should be a continous form based on TableB. You then have a child/parent relationship between the forms so that the subform always uses the record in the main form. NameID on the subform goes away because it gets that value from the main form. Other than that frmTableB can look exactly like it does now.


Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).
support.microsoft.com support.microsoft.com
 
I don't like the Idea that after opening frmTableB that corrupted data can be entered when first selecting a different record in frmTableB and then refresh frmTableB. Having a possibility to enter biased data is not something I want. Therefore, when opening frmTableB, the NameId should always stay the same till the form is closed and re-opened by another record.
Click to expand...
Then you need to actually validate the data being saved in the correct event. Use the Form's BeforeUpdate event. That event allows you to cancel the update if you find an error. This event cannot be bypassed. It is the last event that fires before a record gets saved. To say this a diffeent way. NO record will ever be saved by Access if it has not successfully passed through the form's BeforeUpdate event. Think of this event as the flapper at the bottom of a funnel. If the data passes the validation tests, the flapper is open and the record passes through to be saved. If the data has an error, you close the flapper by using :

Cancel = True

And that prevents the record from being saved. Very simple. One event you need to understand and learn how to control.

Also, having multiple forms open at one time leads to issues and you are seeing one of them. If you want to leave frmA visible, then open frmB as a dialog. That stops the code in frmA from running and the only way to return control to frmA is by closing frmB. Perhaps a better option for you would be a subform. Using the master/child links will keep the two forms synchronized. If you reposition frmA, frmB will automatically be repositioned based on the linking fields.

Other options - (sometimes Access gives us too much flexibility.)
1. Using your current setup. When you open frmB, you must first check to see if it is already open. If it is open, you MUST close it before opening it again or the new selection will not show in frmB. It will be left with the previous selection. This is what is currently happening if I understand your question.
2. I always hide frmA before opening frmB unless for some reason the user needs to see both at the same time, in which case I always open frmB as a dialog.
3. If frmA does not need to be visible but you want it to stay open, then use
Me.Visible = False before you open frmB. The user still has to close frmB to get back to frmA and this happens in the Unload event of frmB. You Open frmA with no criteria. That just makes the hidden instance visible again so you can now select a different frmB record to view.
 
@Gaztry80 i appreciate the like but it is much more useful to the rest of us and to people who find this thread later if you don't just "like" people for responding. Use your words. Did my suggestion lead to a solution or are you still struggling.

How is anyone supposed to figure out if you still need help?
 
Pat Hartman said:
@Gaztry80 i appreciate the like but it is much more useful to the rest of us and to people who find this thread later if you don't just "like" people for responding. Use your words. Did my suggestion lead to a solution or are you still struggling.

How is anyone supposed to figure out if you still need help?
Click to expand...
@Pat Hartman , I was reading your comment at 00:30 on my mobile before going to bed and hit the like button, since you gave me some eye openers ;)
You gave me several options, so I am trying and playing arround to see which one is best for me to implement.
Afterwards I will off course respond on this thread :)!

Update:
I have used your suggestion to open the form as a Dialog (please see attachement) and below code:
Code: 
DoCmd.OpenForm "frmTableB", acNormal, ,"NameId=" & NameId.Value , , acDialog
Which I found on the following thread: https://www.access-programmers.co.u...rm-as-dialog-and-then-goto-new-record.318724/

In my case I want to see frmA, this solution is perfect because it will not be possible to enter biased data.
In addition, I like your method to use the Form's BeforeUpdate event, but I think it is a more difficult solution in this scenario.
 

Attachments

Last edited:
I like your method to use the Form's BeforeUpdate event, but I think it is a more difficult solution in this scenario.
Click to expand...
I'm glad you "like" it. It wasn't a wishy-washy suggestion. If you want to prevent bad data from being saved, you need to validate the data and the form's BeforeUpdate event is the single, simplest solution. Here is a sample. It is the complete validation code for a Members form so the field names should make sense. There is also a little security thrown in. In this app, the users picked records off a task list to update and that process temporarily locked the record so two people wouldn't try to edit the same record at the same time.
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim LookupDate As Date
    
On Error GoTo Err_Proc

    bPreventClose = True

    If Me.txtLockedBy = Forms!frmLogin!txtUserID Then
    Else
        Cancel = True
        Me.Undo
        MsgBox "Someone else has locked this record.  Update is not allowed at this time.", vbOKOnly + vbInformation
        Exit Sub
    End If

    If ValidateUserRole(gReviewRole) = True Or _
        ValidateUserRole(gAppealRole) = True Then  'Audit/Review/or Helpdesk
    Else
        Cancel = True
        Me.Undo
        MsgBox "You are not authorized to change this data.", vbOKOnly + vbInformation
        DoCmd.OpenForm Me.OpenArgs, , , , , acWindowNormal
        DoCmd.Close acForm, "frmMembers"
        Exit Sub
    End If

    If IsNull(Me.VerLtrSentDT) Then
        If IsNull(Me.VerDocRecDT) Then
        Else
            MsgBox "Please enter Verification Letter Sent Date.", vbOKOnly + vbInformation
            Cancel = True
            Me.VerLtrSentDT.SetFocus
            Exit Sub
        End If
    Else
        If IsNull(Me.VerLtrSentDT) Then
        Else
            If Me.VerDocRecDT <= Me.VerLtrSentDT Then
                MsgBox "Return date must be greater than sent date.", vbOKOnly + vbInformation
                Cancel = True
                Me.VerDocRecDT.SetFocus
                Exit Sub
            End If
        End If

    End If

    If IsNull(Me.AmnestyLtrSentDt) Then
        If IsNull(Me.ReturnedAmnestyPkgDt) Then
        Else
            MsgBox "Please enter Amnesty Letter Sent Date.", vbOKOnly + vbInformation
            Cancel = True
            Me.AmnestyLtrSentDt.SetFocus
            Exit Sub
        End If
    Else
        If IsDate(Me.ReturnedAmnestyPkgDt) Then
            If Me.ReturnedAmnestyPkgDt <= Me.AmnestyLtrSentDt Then
                MsgBox "Return date must be greater than sent date.", vbOKOnly + vbInformation
                Cancel = True
                Me.ReturnedAmnestyPkgDt.SetFocus
                Exit Sub
            End If
        End If
    End If
    
    If Me.DOB > DateAdd("yyyy", -18, Date) Then
        If MsgBox("Member is < 18 years old.  Is that correct?", vbYesNo) = vbYes Then
        Else
            MsgBox "Please correct DOB.", vbOKOnly
            Cancel = True
            Me.DOB.SetFocus
            Exit Sub
        End If
    End If
    If DateDiff("yyyy", Me.DOB, Date) > 65 Then
        If MsgBox("member is > 65 years old.  Is that correct?", vbYesNo) = vbYes Then
            If DateDiff("yyyy", Me.DOB, Date) > 100 Then
                MsgBox "DOB is invalid.", vbOKOnly
                Cancel = True
                Me.DOB.SetFocus
                Exit Sub
            End If
        Else
            MsgBox "Please correct DOB.", vbOKOnly
            Cancel = True
            Me.DOB.SetFocus
            Exit Sub
        End If
    End If
    
    If Me.txtCompanyEmail & "" = "" And Me.txtHomeEmail & "" = "" Then
        If MsgBox("Both email addresses are empty.  Do you still want to save?", vbOKOnly) = vbNo Then
            Cancel = True
            Me.txtCompanyEmail.SetFocus
            Exit Sub
        End If
    End If
    
    Me.UpdatedBy = Forms!frmLogin!txtNetworkID
    'Me.UpdatedBy = Environ("UserName")
    Me.UpdatedDT = Now()
    bPreventClose = False
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
    End Select
End Sub
 
Pat Hartman said:
I'm glad you "like" it. It wasn't a wishy-washy suggestion. If you want to prevent bad data from being saved, you need to validate the data and the form's BeforeUpdate event is the single, simplest solution. Here is a sample. It is the complete validation code for a Members form so the field names should make sense. There is also a little security thrown in. In this app, the users picked records off a task list to update and that process temporarily locked the record so two people wouldn't try to edit the same record at the same time.
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim LookupDate As Date
   
On Error GoTo Err_Proc

    bPreventClose = True

    If Me.txtLockedBy = Forms!frmLogin!txtUserID Then
    Else
        Cancel = True
        Me.Undo
        MsgBox "Someone else has locked this record.  Update is not allowed at this time.", vbOKOnly + vbInformation
        Exit Sub
    End If

    If ValidateUserRole(gReviewRole) = True Or _
        ValidateUserRole(gAppealRole) = True Then  'Audit/Review/or Helpdesk
    Else
        Cancel = True
        Me.Undo
        MsgBox "You are not authorized to change this data.", vbOKOnly + vbInformation
        DoCmd.OpenForm Me.OpenArgs, , , , , acWindowNormal
        DoCmd.Close acForm, "frmMembers"
        Exit Sub
    End If

    If IsNull(Me.VerLtrSentDT) Then
        If IsNull(Me.VerDocRecDT) Then
        Else
            MsgBox "Please enter Verification Letter Sent Date.", vbOKOnly + vbInformation
            Cancel = True
            Me.VerLtrSentDT.SetFocus
            Exit Sub
        End If
    Else
        If IsNull(Me.VerLtrSentDT) Then
        Else
            If Me.VerDocRecDT <= Me.VerLtrSentDT Then
                MsgBox "Return date must be greater than sent date.", vbOKOnly + vbInformation
                Cancel = True
                Me.VerDocRecDT.SetFocus
                Exit Sub
            End If
        End If

    End If

    If IsNull(Me.AmnestyLtrSentDt) Then
        If IsNull(Me.ReturnedAmnestyPkgDt) Then
        Else
            MsgBox "Please enter Amnesty Letter Sent Date.", vbOKOnly + vbInformation
            Cancel = True
            Me.AmnestyLtrSentDt.SetFocus
            Exit Sub
        End If
    Else
        If IsDate(Me.ReturnedAmnestyPkgDt) Then
            If Me.ReturnedAmnestyPkgDt <= Me.AmnestyLtrSentDt Then
                MsgBox "Return date must be greater than sent date.", vbOKOnly + vbInformation
                Cancel = True
                Me.ReturnedAmnestyPkgDt.SetFocus
                Exit Sub
            End If
        End If
    End If
   
    If Me.DOB > DateAdd("yyyy", -18, Date) Then
        If MsgBox("Member is < 18 years old.  Is that correct?", vbYesNo) = vbYes Then
        Else
            MsgBox "Please correct DOB.", vbOKOnly
            Cancel = True
            Me.DOB.SetFocus
            Exit Sub
        End If
    End If
    If DateDiff("yyyy", Me.DOB, Date) > 65 Then
        If MsgBox("member is > 65 years old.  Is that correct?", vbYesNo) = vbYes Then
            If DateDiff("yyyy", Me.DOB, Date) > 100 Then
                MsgBox "DOB is invalid.", vbOKOnly
                Cancel = True
                Me.DOB.SetFocus
                Exit Sub
            End If
        Else
            MsgBox "Please correct DOB.", vbOKOnly
            Cancel = True
            Me.DOB.SetFocus
            Exit Sub
        End If
    End If
   
    If Me.txtCompanyEmail & "" = "" And Me.txtHomeEmail & "" = "" Then
        If MsgBox("Both email addresses are empty.  Do you still want to save?", vbOKOnly) = vbNo Then
            Cancel = True
            Me.txtCompanyEmail.SetFocus
            Exit Sub
        End If
    End If
   
    Me.UpdatedBy = Forms!frmLogin!txtNetworkID
    'Me.UpdatedBy = Environ("UserName")
    Me.UpdatedDT = Now()
    bPreventClose = False
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
    End Select
End Sub
Click to expand...
Quite some code above my level haha 😅. But I want to learn, so I have tried to make something simple based on your suggestion (please see attachment). I doubt if "Me.txtNameId = Me.txtNameIdLoadValue" is the best way to do the validation. What do you think?
 

Attachments

When you look at the code, look at each statement. You should be able to make sense of them. The nested If's might seem confusing but they are quite logical.
Code: 
If sometest = True Then
    '' true path for sometest
    If sometest2 = True then
        '' true path for sometest2
    Else
        '' false path for sometest2
    End If
Else
    '' false path for sometest
    If sometest3 = true then
        ''true path for sometest3
    Else
        '' false path for sometest3
    End If
End If
Try to understand the nested logic for the dates. Notice that when an error is found, the code sets Cancel = true to cancel the update and exits the sub after displaying an error message.

We can't even begin to guess what your validation criteria might be. Write it in English and we'll help you to convert it to code.

What are you trying to refer to with "Me.txtNameIdLoadValue"? Why might it be different from Me.txtNameID?

Keep in mind that controls that contain autonumbers cannot be updated. You also can set any control's locked property to Yes to prevent updating.
 
Pat Hartman said:
When you look at the code, look at each statement. You should be able to make sense of them. The nested If's might seem confusing but they are quite logical.
Code: 
If sometest = True Then
    '' true path for sometest
    If sometest2 = True then
        '' true path for sometest2
    Else
        '' false path for sometest2
    End If
Else
    '' false path for sometest
    If sometest3 = true then
        ''true path for sometest3
    Else
        '' false path for sometest3
    End If
End If
Try to understand the nested logic for the dates. Notice that when an error is found, the code sets Cancel = true to cancel the update and exits the sub after displaying an error message.

We can't even begin to guess what your validation criteria might be. Write it in English and we'll help you to convert it to code.

What are you trying to refer to with "Me.txtNameIdLoadValue"? Why might it be different from Me.txtNameID?

Keep in mind that controls that contain autonumbers cannot be updated. You also can set any control's locked property to Yes to prevent updating.
Click to expand...
Okay @Pat Hartman, let me explain:
When opening frmTableA the user sees persons (Name) with a corresponding id (NameId). When double click on a Name (record) form frmTableB opens. This form is related to TableB, with the columns NameId, FriendId and Friend. When this frmTableB opens by the double click event, the goal is that the user can add new friends to corresponding NameId of TableA. Therefore my first step is to use the following VBA code on the double click event:

Code: 
DoCmd.OpenForm "frmTableB", , , "NameId=" & NameId.Value

Next step is then to add new friends in frmTableB, which are related to the NameId record which was used in the double click event in frmTableA. Because NameId needs to get filled in frmTableB > I have placed the standard value of NameId in frmTableB as =[Forms]![frmTableA]![NameId]. This is working fine, but as we noticed biased data can be entered when frmTableB is opened and the user navigate back to frmTableA and selects another record. When the user don't notice this and goes back to frmTableB, he/she could think that a Friend is added to a certain Name, but the frmTableB is using the wrong/different NameId. Therefore, my first solution was to use your suggestion to use open the form as Dialog, but I also want to understands what the best way would be to do it with a beforeupdate event. In attached example, the beforeupdate event is used to do a valudating and it works like this:


1.) When double click in frmTableA on a record > frmTableB opens.
2.) The value of NameId from frmTableA is filled in frmTableB in the text box: txtNameIdLoadValue.
3.) The standard value of NameId in frmTableB is still related to the selected record of frmTableA (NameId), which is called txtNameId.
4.) Now I run the below code in the beforeupdateevent which is checking if the values from frmTable B > txtNameId and txtNameIdLoadValue are the same. In case they are the same the user is allowed to enter a new Friend in frmTable B.

Code: 
If Me.txtNameId = Me.txtNameIdLoadValue Then
    Else
        Cancel = True
        Me.Undo
        MsgBox "NameId is not corresponding.  Update is not allowed at this time.", vbOKOnly + vbInformation
        Exit Sub
    End If

My question is if this validation method between txtNameIdLoadValue and txtNameId in combination with placing the value in the text from step 2 is the most appriopriate way of coding and reaching my goal? I have the feeling the placing the value of NameId in txtNameIdLoadValue is not the best way.

Thank you for thinking with me :)!
 

Attachments

That is how you do validation but in the case of a FK, you should populate it yourself. Do not leave it up to the user.

You need to either pass the NameID in the OpenArgs or you need to reference formA. The best place to populate the FK is formB's BeforeInsert event. That event runs as soon as someone types the first character into the form and it runs for each new record added.

Me.NameID = Forms!frmTabldA!NameID

or if you used the openargs

Me.NameID = Me.OpenArgs

Also, FYI - NEVER use "Name" as a column name. It is a reserved word. Now, Date, Month, etc are all function names. Don't use those either.
 

Attachments

Pat Hartman said:
That is how you do validation but in the case of a FK, you should populate it yourself. Do not leave it up to the user.

You need to either pass the NameID in the OpenArgs or you need to reference formA. The best place to populate the FK is formB's BeforeInsert event. That event runs as soon as someone types the first character into the form and it runs for each new record added.

Me.NameID = Forms!frmTabldA!NameID

or if you used the openargs

Me.NameID = Me.OpenArgs

Also, FYI - NEVER use "Name" as a column name. It is a reserved word. Now, Date, Month, etc are all function names. Don't use those either.
Click to expand...
Thanks for the advice. I have now implemented OpenArgs, this works best.
 
You must log in or register to reply here.

Similar threads

D
Solved Running a public sub when form gets focus
Replies
2
Views
205
dobseh
D
V
Missing Value in combo Box while Opening another form using openArgs
Replies
3
Views
317
Uncle Gizmo
Uncle Gizmo
S
Open another form to display record
2
Replies
21
Views
428
silentwolf
S
P
Problems once a record is retrieved to a form
Replies
15
Views
357
Pat Hartman
P
bsacheri
Access crashes when switching Form View to Datasheet View
Replies
2
Views
150
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom