INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Exit subform without affecting mainform

Exit subform without affecting mainform

(OP)
Hello, I’m working with a master form and subform. I have a closed button on the subform, because at times I want to only close or exit out of the subform while the master form remains opened. The problem I’m running into is that when I click the closed button on the subform, it also closes the master form at times when I need it to remain opened.
Can someone help me with this?

Thanks very much in advance.

RE: Exit subform without affecting mainform

If the subform is on the main form then perhaps instead of closing the form, change the subform to visible = false and then you'll also need a button or mechanism to unhide it (visible = true) if you need the subform to be visible again before you close the main form.

RE: Exit subform without affecting mainform

(OP)
Ok, in the On Load even of the master form I have the subform set to visible = false and that works just fine. However, when both the master form and subform are opened, when necessary, using the closed event button, I would like to only close the subform without it also closing the master form. I have a closed button on the subform which contain the following:

Private Sub cmdClose_Exit(Cancel As Integer)
DoCmd.Close acForm, "Recs_Returned Subform", acSaveYes
End Sub

RE: Exit subform without affecting mainform

HGoodloe,
Did you try what sxschech suggested? The only issue might be since the button is on the subform, I don't think you can set the subform to visible=False. I think you might need to move the focus to the main form and then make the subform invisible.

Duane
Hook'D on Access
MS Access MVP

RE: Exit subform without affecting mainform

(OP)
Yes, I tried visible = false, but it didn't work. The click event for the close button on the subform doesn't include a visible or invisible option for the main form.

RE: Exit subform without affecting mainform

You will need to add a button, modify an existing button or use an event on the main form to hide the subform. If you already have a close button on your subform, then instead of having it on the subform, place it on the main form. You can position it as close as possible to the subform, or perhaps adjust the wording to indicate that the close button is for the subform.

RE: Exit subform without affecting mainform

(OP)
Ok, I understand. In fact in one of my past projects I did put a close button on the main form to close the subform. This time around I wanted to try closing the subform with a close button on it. But, I'm beginning to see that that is probably not possible. For this project, I went ahead and put the close on the main form and everything seems to be working just fine.

Thanks for your help and be blessed.

RE: Exit subform without affecting mainform

(OP)
Hello, this should be my last question for a while. I have a form with some text boxes and one of them is for a Code Number (number datatype). The form also contains an Add New Record button. I’m trying to code the Code Number field to reject any duplicate numbers. Yes, I’m aware that there’s an option in the field table properties that will allow for duplicates and no duplicates. But in this case, I would like to code the Code Number text box on the form to reject duplicate numbers along with a message box. Is this possible?

RE: Exit subform without affecting mainform

You could add some code to the after update event of the text box that would check for duplicates using DLookup(). Without specific table and field names we can't be much more specific.

Duane
Hook'D on Access
MS Access MVP

RE: Exit subform without affecting mainform

(OP)
Table name is County_Rec, Fields are Code_Number(don't want duplicates), Grant_Document, Mortgage_Type and Sales_Type.
The same field names are on the form.

Thanks

RE: Exit subform without affecting mainform

You can use something like this for new records. I'm not sure if this is an issue for existing records.

CODE --> vba

Private Sub Code_Number_BeforeUpdate(Cancel As Integer)
    If Form.NewRecord Then
        If Not IsNull(DLookup("code_Number", "County_Rec", "Code_Number=" & Me.Code_Number.Text)) Then
            MsgBox "Duplicate Code Number."
            Cancel = True
        End If
    End If
End Sub 

Duane
Hook'D on Access
MS Access MVP

RE: Exit subform without affecting mainform

(OP)
Ok, that code worked.
Thanks very much dhookom

RE: Exit subform without affecting mainform

(OP)
Sorry to bother you again. The BeforeUpdate code is still working just fine. However, when the message box pops up when there’s a duplicate, if I should click the refresh button to regain access to the combo box, instead of refreshing, the duplicates message continues to appear. I use a combo box to verify that a new none duplicate code_number was added to the list.

I also have three other buttons on the form that when clicked, they also display the duplicates message, although the code itself is actually in the BeforeUpdate event. Also, if I right click the form to select design view to examine VBA code or when clicking the X in the upper right corner of the form to close out, I still get the duplicates message. I played around with various other forms of code to resolve this issue, but no success.

RE: Exit subform without affecting mainform

Please clarify if you want to "reject any duplicate numbers" or not. Aren't you given the opportunity to enter a non-duplicate number? What is the primary key field of the table?

Duane
Hook'D on Access
MS Access MVP

RE: Exit subform without affecting mainform

(OP)
Yes, I do want to reject any duplicate numbers in which the BeforeUpdate code you provided on yesterday does exactly that. With the BeforeUpdate code you provided, when I enter a number that is a duplicate, I then hit the tab button in which the message box appears, and that’s what I want. But, I don’t want to see the message box when I click the refresh button. But, I don’t want it having any effect on the refresh button or the other buttons. What I means is, when I key in a number that turns out to be a duplicate, the message box will appear as it should.
The problem occurs when after the message box appears, and before I add a correct unique number, I may decide to click the refresh button before addition a unique number.

RE: Exit subform without affecting mainform

I'm not sure how you are refreshing but it shouldn't call the after update of the control. Maybe you just need to set the control value to Null.

Duane
Hook'D on Access
MS Access MVP

RE: Exit subform without affecting mainform

(OP)
dhookom, I just solved my problem. In the BeforeUpdate code that you provided, right below Cancel = True
I added Undo.

Private Sub Code_Number_BeforeUpdate(Cancel As Integer)
If Form.NewRecord Then
If Not IsNull(DLookup("code_Number", "County_Rec", "Code_Number=" & Me.Code_Number.Text)) Then
MsgBox "Duplicate Code Number."
Cancel = True
Undo
End If
End If
End Sub

THANKS AGAIN FOR YOUR HELP. The code you provided was a huge jumpstart for where I was trying to go.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close