Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Unanswered: open form with visible/not visible command button based on combobox selection

    Hi All -
    I have a database with a combobox for employees to select their name. When they do, it takes them to a filtered form. On this form I want a button to show up (or not show up) depending on the employee.

    I am really new to VBA and can't seem to get this coding to work. For the OnClick event on the combobox I have:

    Private Sub CmdLogin_Click()
    If IsNull(Me.Form("CboLogin")) Then
    MsgBox "No Employee Selected"
    End If
    Exit Sub

    'Open form based on employee selected'
    DoCmd.OpenForm "FrmTimeSheet", , , "EmployeeName=" & CboLogin.Value
    DoCmd.Close acForm, "Employee Login"
    Exit Sub

    'Open Command Button on form based on employee selected from combobox'
    If Me.CboLogin = "JohnSmith" Then
    Me.JohnSmithReport.Visible = True
    End If

    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A number of things; first off
    Code:
    Private Sub CmdLogin_Click()
    If IsNull(Me.Form("CboLogin")) Then
       MsgBox "No Employee Selected"
    End If
    Exit Sub
    will accomplish nothing. The OnClick event of a Combobox only fires when a selection is actually made, and you cannot 'select' Null. The only way a Combobox can be Null is if nothing is selected, or if a selection has been made and then is deleted. Obviously, no event of the Combobox is going to fire in the former case, where a selection is not made, and the latter is a rare happening, in my experience. So checking a Combobox for Nulls needs to be made in an event not connected to the Combobox.

    How/where are you planning on execute this code from? Is it supposed to be part of the above Sub CmdLogin_Click?
    Code:
    'Open form based on employee selected'
    DoCmd.OpenForm "FrmTimeSheet", , , "EmployeeName=" & CboLogin.Value
    DoCmd.Close acForm, "Employee Login"
    Exit Sub
    Not sure what the Exit Sub command is supposed to be doing, here. Also, assumming that EmployeeName is just that, a name, and hence Text (as opposed to a Number) the

    "EmployeeName=" & CboLogin.Value

    needs to be

    "EmployeeName='" & CboLogin.Value & "'"

    Syntax in Where clauses and Criterias varies according to the Datatype of the Fields involved. Your original

    "EmployeeName=" & CboLogin.Value

    would only be valid if EmployeeName were defined as a Number.

    Not sure about where this code is to be executed, either. Is it on the original Employee Login Form or the FrmTimeSheet Form?
    Code:
    'Open Command Button on form based on employee selected from combobox'
    If Me.CboLogin = "JohnSmith" Then
       Me.JohnSmithReport.Visible = True
    End If
    Get back to us with answers to the posed questions and we'll see if we can guide you along!

    Welcome to dBforums!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2014
    Posts
    3
    "The OnClick event of a Combobox only fires when a selection is actually made, and you cannot 'select' Null. The only way a Combobox can be Null is if nothing is selected, or if a selection has been made and then is deleted. Obviously, no event of the Combobox is going to fire in the former case, where a selection is not made, and the latter is a rare happening, in my experience. So checking a Combobox for Nulls needs to be made in an event not connected to the Combobox."

    I have this code on the command button, so if someone clicks the command button "OK" without selecting their name, they get that message box. It seems to work okay

    How/where are you planning on execute this code from? Is it supposed to be part of the above Sub CmdLogin_Click?

    I am not really sure...I stole it from the internet. Again, this fires on the same command button as above and seems to do its job. When I select employee "John Smith" and then click the command button it takes me to a split form that only has records for John Smith.

    Not sure about where this code is to be executed, either. Is it on the original Employee Login Form or the FrmTimeSheet Form?

    Code:
    'Open Command Button on form based on employee selected from combobox'
    If Me.CboLogin = "JohnSmith" Then
    Me.JohnSmithReport.Visible = True
    End If

    This is where I am really stuck...I have no idea where this code should belong. It makes sense that it would be on the command button on the login form because I want the split form to open with the Report Command Button either visible or not depending on the selection.

    If you have any other suggestions on getting to the this end result that would be very helpful:

    I would like an employee to select their name, be taken to a split form where only their time entries are visible, and then have a command button that shows only their report. Now I tried 2 different ways of getting this report to show up....I created a report for each person and am trying to make the command button that takes them to that report visible, AND I also created a report containing all employees in an attempt to filter it based on my combobox selection. I haven't had luck with either and that is pretty much where I am stuck with this.

    I hope I answered your questions...I am grateful for any advice I could receive on this!

  4. #4
    Join Date
    Jun 2014
    Posts
    3
    I posted a big long reply that answered all of your questions, but somehow it didn't post. Regardless, I cleaned up my code quite a bit and I feel like I am on the right track. However, when I run the code, it seems like it is defaulting to the False statement. The Form opens with the correct records but the command button is not visible. If I take out the ELSE statement, then both buttons remain visible.

    This code is on the command button's OnClick event of the Employee Login form which holds the combobox. The green works very well...the red is where I am stuck.


    Option Compare Database
    Option Explicit

    Private Sub Command1_Click()
    If IsNull(Me.Form("cbologin")) Then
    MsgBox "No Employee Selected"
    End If

    DoCmd.OpenForm "FrmTimeSheet", , , "EmployeeName=" & cbologin.Value


    If Me.Form("cbologin") = "JohnSmith" Then
    Forms!FrmTimeSheet.JohnSmith.Visible = True
    Else
    Forms!FrmTimeSheet.JohnSmith.Visible = False
    End If

    If Me.Form("cbologin") = "JaneDoe" Then
    Forms!FrmTimeSheet.JaneDoe.Visible = True
    Else
    Forms!FrmTimeSheet.JaneDoe.Visible = False

    End If

    DoCmd.Close acForm, "Employee Login"

    End Sub


    If you know of a better way to achieve the following goal please let me know: I want the employee to select their name from a combobox and then click OK. I want a split form to open that is filtered with only that employees records, and I want a button to be visible that stores a report with only their records. This command button with the report is what I am trying to make visible/hidden depending on the employee.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •