Exit subform without affecting mainform

[HGoodloe]

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.

 

[sxschech]

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.

 

[HGoodloe]

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

 

[dhookom]

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

 

[HGoodloe]

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.

 

[sxschech]

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.

 

[HGoodloe]

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.

 

[HGoodloe]

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?

 

[dhookom]

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

 

[HGoodloe]

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

 

[dhookom]

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

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

 

[HGoodloe]

Ok, that code worked.
Thanks very much dhookom

 

[HGoodloe]

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.

 

[dhookom]

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

 

[HGoodloe]

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.

 

[dhookom]

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

 

[HGoodloe]

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.