Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Location
    Australia
    Posts
    23

    Unanswered: form set focus problem / Form referring

    Hi all,

    I have a form (Main Menu, popup-yes) which has a combo box. I have a button which launches a query with the criteria Like [forms]![Main Menu]![cboProduct] which filters just the selected item in the combo box. I am changing it to launch a report of the query data so it looks nicer and also so i can have a close button which will reopen the main menu form.

    With all the buttons, i am using vba to close the main menu (docmd.close) and then open the report or form or whatever.

    The problem i have is:
    When i add this code to the combo box button (OpenComboQuery) onClick property.
    Code:
    'close main menu
    docmd.close
    'open combo report
    docmd.openreport "DocumentComboReport", acViewReport
    The report doesnt run properly and opens a parameter window. I figured out that this is because i am closing the main menu (which contains the combo selection) before the report opens.

    What i would like to do is open the report, set focus to the main menu and then close the main menu. When changing the report to a popup form, it goes over the top of the main menu which is still open and that does the job but I would like to know how to refer to a form .

    How do I refer to a form in vba code? I know i can refer to a control on a form like combobox1.setfocus. How do i refer to a form and set focus. i tried Main Menu.setfocus which doesnt work.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by MinusZero View Post
    I have a form (Main Menu, popup-yes) which has a combo box. I have a button which launches a query with the criteria Like [forms]![Main Menu]![cboProduct] which filters just the selected item in the combo box. I am changing it to launch a report of the query data so it looks nicer and also so i can have a close button which will reopen the main menu form.

    With all the buttons, i am using vba to close the main menu (docmd.close) and then open the report or form or whatever.

    The problem i have is:
    When i add this code to the combo box button (OpenComboQuery) onClick property.
    Code:
    'close main menu
    docmd.close
    'open combo report
    docmd.openreport "DocumentComboReport", acViewReport
    The report doesnt run properly and opens a parameter window.
    A couple of comments. When you say
    Code:
    Like [forms]![Main Menu]![cboProduct]
    I hope that cboProduct contains a star (*) somewhere in the value, because otherwise Like won't work. Or you may want to say some variation of
    Code:
    Like "*" & [forms]![Main Menu]![cboProduct] & "*"
    See Like in the Help file.

    This isn't your question however.

    My guess is that the query is a Select query, and the query is the RecordSource for the report. If this is the case, what's happening is that you're trying to pass the parameter to the query instead of passing it to the report. The best, in my opinion, is to remove the parameter from the query, delete the button that launches the query and all its code, and change the OpenReport statement to
    Code:
    DoCmd.OpenReport "DocumentComboReport",acViewPreview,,"<VariableName> = '" & cboProduct.Value & "'"
    This assumes that cboProduct is string. If it's numeric, it should be
    Code:
    DoCmd.OpenReport "DocumentComboReport",acViewPreview,,"<VariableName> = " & cboProduct.Value
    without the single quotes. This opens the report with the query's value already set, and there's no need to pass any additional parameters.
    Of course, change <VariableName> to the query column name. Also, I changed acViewReport to acViewPreview, and "Like" to "=". If you need to, change them back. My way is per Access 2003. I don't know what version you're using.

    In addition to all the above, remove the VBA from the OnClick event and put it all in the AfterUpdate event. A combo box OnClick event fires as soon as you click on the combo, before any updates are available, so in essence, you're passing a non-existent parameter to the query. The combo box's value is only available at (or after) the AfterUpdate event.

    You can follow the OpenReport statement with
    Code:
    DoCmd.Close acForm, "Form Name in quotes Here"
    Sam

  3. #3
    Join Date
    May 2012
    Location
    Australia
    Posts
    23
    Thats great feedback thanks. All good I hadnt thought of using the after update and basically skipping a step

    After thinking about it though I have done it a little differently, thinking about it overnight helps.

    I have removed my button to launch the query and moved the code to the after update on the combo.

    cboProduct
    Code:
    Private Sub cboProduct_AfterUpdate()
    DoCmd.OpenReport "documentComboReport", acViewReport
    DoCmd.OpenForm "main menu"
    DoCmd.Close
    End Sub
    I did it this way so i can close the main menu. Its a round-a-bout way, but it works.

    Then on my report close button i have this to reopen the main menu. This is better because when i wasnt closing it, the combo still had the last selection in it.
    Code:
    Private Sub close_combo_report_Click()
    DoCmd.Close
    DoCmd.OpenForm "Main Menu"
    End Sub
    Last edited by MinusZero; 06-05-12 at 20:23.

Posting Permissions

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