Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2002
    Location
    São Paulo/Brazil
    Posts
    5

    Angry Unanswered: You can't reference a property or method for a control unless the control has de foc

    Hello, friends.

    I'm trying to do this very simple thing on ms access:

    -I have a form with combos from some tables - ok nice
    -The user select some filter criteria from the combos -ok easy
    -The user press a button that starts a very simple macro:
    Outputto query
    format excel
    Close - very simple.

    The query joins some tables, using the values of the forms in the usual manner where Database.Name = Forms![MainForm]![ComboName].[Text] and so.

    I spent an entire day to discover what a hell I was doing wrong, trying to use public variables and so. It was like the combo were allways blank. The where clause simply ignored the combo.

    When I'd trying with variables, the mistery was revealed.

    It raised this funny error message:
    You can't reference a property or method for a control unless the control has de focus.

    What an absurd! So how it's possible to do what I'm tring to do?

    When the outputto command is issued it pops a dialog asking about overwrite the file and the focus is lost.

    Should I write the value in a table before issuing this command?

    That's my last resource... I don't believe that. I must have a cleaner way.

    Thanks for any help,
    Darci
    Last edited by darcimf; 10-17-02 at 22:46.

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Use:
    Forms![MainForm]![ComboName] in the query

    or in code:
    Forms![MainForm]![ComboName]
    Forms![MainForm]![ComboName].Column(0)

    check to see what the value is - you may need:
    Forms![MainForm]![ComboName].Column(1) or (2)


    Forms![MainForm]![ComboName] will display the value of the bound column. This may not be the value that you are looking for. If that is the case then you will have to grab the value by using a function or pass the value to another control(hidden) and reference that control in the query.
    Last edited by Rockey; 10-17-02 at 23:20.

  3. #3
    Join Date
    Oct 2002
    Location
    São Paulo/Brazil
    Posts
    5

    Re: You can't reference a property or method for a control unless the control has de foc

    Hello, Rockey.
    Many thanks.

    It almost worked.
    I've made a simple query to test.

    SELECT Forms![formMenuPrincipal]![editExcelDataInicial] AS DataInicial, Forms![formMenuPrincipal]![editExcelDataFinal] AS DataFinal, Forms![formMenuPrincipal]![ComboExcelConvenio] AS Convenio, Forms![formMenuPrincipal]![ComboExcelAnestesico] AS Anestesico, Forms![formMenuPrincipal]![ComboExcelNomeCirurgiao] AS Cirurgiao, Forms![formMenuPrincipal]![ComboExcelNomeAnestesista] AS Anestesista;


    After the changes you said, I'd changed the combos, press the button that issues this macro:
    openquery (query above)
    Outputto (none, excel format)

    What happens is that the query is shown with all fields ok, it worked, except for the date, which uses textbox, but that's not the point for now.

    The excel worksheet is open blank, with only the column names displayed.

    We are getting there!

    Can you help me?

    Many thanks, again, in advance,
    Darci

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Is the date the source of the error. Loose that field for now and try it.

    Why do you open the query? Just for testing?

    What is none?-Outputto (none, excel format)

  5. #5
    Join Date
    Oct 2002
    Location
    São Paulo/Brazil
    Posts
    5
    Originally posted by Rockey
    Is the date the source of the error. Loose that field for now and try it.
    Ok. I'll test asap.

    Why do you open the query? Just for testing?
    Yes. Just to test what is the result.
    What is none?-Outputto (none, excel format)
    I've tryied to use Outputto (query, excel format) and now I've omited the query.

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    You are using a macro correct?

    I believe that you need to include the Object Type or it will not function.

  7. #7
    Join Date
    Oct 2002
    Location
    São Paulo/Brazil
    Posts
    5
    Hello, Rockey.

    Thanks for your help. Yes, the macro contained the object type.

    To help you help me I've managed to isolate the problem in a simple file.

    If you have problems to download or execute, please let me know.

    Many thanks, again.

    Darci
    Attached Files Attached Files
    Last edited by darcimf; 10-18-02 at 23:16.

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    This does not work when you are not using the keyword FROM.
    Not sure why that is in this case and not in the case of the solution provided below.

    Try this query and you will see the data is exported.
    SELECT TableColors.ID, TableColors.Colors
    FROM TableColors
    WHERE (((TableColors.Colors)=[Forms]![Form1]![Combo2]));


    For your solution - use functions to snag the values - same as trying to use variables in a query.

    Query: SELECT GrabColor() AS Color, GrabName() AS Name;


    Put the following functions in a module.

    Function GrabColor() As String
    On Error GoTo PROC_ERR

    GrabColor = [Forms]![Form1]![Combo2]

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT


    End Function

    Function GrabName() As String
    On Error GoTo PROC_ERR

    GrabName = [Forms]![Form1]![Combo0]

    PROC_EXIT:
    Exit Function

    PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT


    End Function


    Another way to skin the cat would be to change you query to a make-table query; use the temp table as to object of your export; and then delete the temp table.



    Maybe someone else can provide better insite as to why this does not work in your original format.
    Last edited by Rockey; 10-19-02 at 02:00.

  9. #9
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Examples attached:
    Attached Files Attached Files

  10. #10
    Join Date
    Oct 2002
    Location
    São Paulo/Brazil
    Posts
    5
    Hello, Rockey.

    Many thanks!!! You've saved my week!

Posting Permissions

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