Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: Can't find subform via macro

    Hi,

    I have a very simple macro in a subform. If I run the subform by itself the macro works fine. If I run the main form (which includes the subform), I get this error message:

    "... can't find the form "subform" referred to in a macro."

    Any help to a bloody beginner will be highly appreciated!

    Best regards
    Andy

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    any chance of tellng us what the macro is?
    it may well be that you ae runign a macro which is specific to a particular form or process
    you may have to rewrite the macro to explicitly refer to the sub form..

    persoanlly I tend not to use macros.... usually you can get the same effect using VB code.. and you can be very very specific in code. functionality Im guessing that macros and vbcode probably end up as the same pcode so it doesn't rally matter whether you prefer to use macros or code, with the sole exception being code is a hack of a lot more powerful and flexible.

    you may have to chage the rference to the sub form tobe somehtign like forms!<myformname>
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2008
    Posts
    3
    Yes, sure.. will try to explain:

    The subform has a few drop-down menus for the user to select certain properties of a product. But some combinations from 2 different combo-boxes are invalid.

    As said before, I'm a bloody beginner to Access and tried to solve this by Macro (with functions similar to xls-formulas). But I'd be happy to do it in VB code, I just don't know how to do.. Never "learned" Visiual Basic.

    The macro has 2 conditions

    1) if ( (Combobox1 = A and Combobox2 <> B) or (Combobox1 <> A and Combobox2 = B) ) then SetValue (Textbox, "ERROR!")

    2) if the combination fits, it should not show anything in the textbox.

    If you tell me how to do in VB, that'd be great.. But I'll probably keep asking you very stupid questions on how to do this and that...

    Thx upfront!
    Andy

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    To paraphrase what I think is your requirement.. you want to validate user input in combo boxes to make sure its correct, and display an error message if there is a problem.

    ok so first things first VBA (Visual Basic for Applications) is a subset of VB, 'all' it does is expose objects and properties that are specifically targeted at office applications. effectively if you are use to writing macros in Excel then you are de facto writing VBA code. if you've been putting any code in your macros then you are already writing VBA.

    Access is a lightly different beast to Excel.... you don't have all those excel functions.. you have a whole raft of Access functions in place.

    often the trickiest part is trying to work out the best place to insert the code. Access provides a series of hooks which you can hang code off.. these hooks are called events, and farily closely map to windows API events/messages... the thing to bear in mind in Access is that virtually any control has events and the parent for/report has events some times the events have the same name. I suspect for this exercise you should be putting your validation code in the forms before update event. this event is triggerred before an update occurs, and allows the developer the chance to cancel the update because of problems

    open your subform in design mode, right click the form to open the properties and select before update, and select event procedure and then the ...

    you should see something similar to...
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    End Sub
    place your logic in between those lines.
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    if combobox1 = "A" and ComboBox2 = "B" then
      cancel=true
    elseif (Combobox1 <> "A" and Combobox2 = "B")  then
      cancel=true
    end if
    if cancel = true then
      msgbox "Oh shaggy, this idiot of a user has screwed up"
    end if
    'note if cancel is set to true then the data will not be updated and the form will remain open
    End Sub
    rather than set a value in a text box.. what I'm proposing you do is show a message box which requires a users attention.. you "may" want to change the error message

    not knowing what you valid values are I've assumed they are text hence ="A"
    if its a number drop the ""

    you can tinker with the msgbox function to set a message a caption title and symbol.. the form is
    msgbox message,symbol,title
    eg
    msgbox "you can't do that" & vbcrlf & "if box1=A, box 2 cannot =B" & vbcrlf & "OR" & vbcrlf & "if box 2 = B box 1 cannot equal A",vbwarning,"Stupid user alert"
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2008
    Posts
    3
    healdm, thx a lot!!

    will try it either on the weekend.. it's about time to get off work here in SHA..

    until then.. best regards from the fast east and have a nice WE.
    a.

  6. #6
    Join Date
    Apr 2008
    Posts
    5

    re:can't find the macro

    This error comes when you run a macro against the wrong type of document. To solve these problems, check that you’ve spelled the macro variable name right and that you are referencing it in an appropriate scope. You can also check the opening or closing parenthesis or omitting an argument or specifying an extra argument. Best way of preventing data loss from macro is to backup your database or you can use compact and repair utility or access repair utility which will recover and repair the macros.

Posting Permissions

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