Page 1 of 3 123 LastLast
Results 1 to 15 of 44

Thread: Need a macro

  1. #1
    Join Date
    Oct 2010
    Posts
    175

    Unanswered: Need a macro

    If anyone knows of a simple macro that can enable my users to open a form from a separate form that contains a combo box...and open it to a specific record on the form based on their selection...please let me know....

    I forgot to mention that my combo box lists "last name", "first name", "middle initial", and each comes from it's own field....(if that makes any sense)....I'm not sure if that has anything to do with how the macro should be done.

    Thanks in advance.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you can use VBA code instead of a macro:

    Open a second form to the record

    The OpenForm macro has a similar argument.
    Paul

  3. #3
    Join Date
    Oct 2010
    Posts
    175
    Thanks Paul. Can you write the code for me? I have no experience in VBA and don't know where to begin.

    I guess the questions now that I have are as follows:

    You gave me the following as an example:

    DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

    Which would translate to me as:

    DoCmd.OpenForm "MainForm", , , "FieldName = Last Name, First Name, Middle Initial" (I'm not sure about this)...

    Where do I enter this code...is this under "code builder"? When it asks for "FieldName", what would go in there if I have a combo box that contains separate fields such as last name, First name, middle initial? Do I need to put the commas in the code as you have above. And what is & Me.ControlName?
    Last edited by akanick; 10-29-10 at 14:26. Reason: more detail

  4. #4
    Join Date
    Oct 2010
    Posts
    51
    DoCmd.OpenForm [formName as string], acNormal, WhereCondition:="RecordID = "& Me.ComboBox.Colum(i)
    No time like the present to start learning.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I did write the code; all you have to do is substitute your names. If you don't know where the code goes:

    First VBA
    Paul

  6. #6
    Join Date
    Oct 2010
    Posts
    175
    Perhaps I'm not clear with my question.

    Now I know where the code goes. But the way I have my combo box set up, it pulls last name, first name, middle initial from a table (*all in one line). I would like have the combo box pull up my main form by the name that the user selects.

    The issue I'm having is that last name, first name, middle initial are separate fields within the main form.

    So I don't know how to write the code that would enable the user to select the full name from the combo box and have it pull up the record in form view from my main form because there are three different fields...

    Do you see what I mean?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, using names would not typically be a good idea; sooner or later you'll have two people with the same name. Most of us would use a numeric key field. To do it like that, this type of thing, presuming the names are in separate columns of the combo row source:

    Code:
    DoCmd.OpenForm "SecondFormName", , ,"LastName = '" & Me.ComboName.Column(1) & "' AND FirstName = '" & Me.ComboName.Column(2) & "' AND MiddleInitial = '" & Me.ComboName.Column(3) & "'"
    You can see another reason I'd recommend a simple numeric key.
    Paul

  8. #8
    Join Date
    Oct 2010
    Posts
    175
    Okay, I tried to enter that code.

    DoCmd.OpenForm "SecondFormName", , ,"LastName = '" & Me.ComboName.Column(1) & "' AND FirstName = '" & Me.ComboName.Column(2) & "' AND MiddleInitial = '" & Me.ComboName.Column(3) & "'"

    ...and it came back with a "Compile Error"

    ...method or data member not found

    and it highlighted ".ComboName"....

    I guess maybe I'm supposed to replace "ComboName" with something...but what?

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Uh, the name of your combo. Plus all the field names need to be yours. Plus the "SecondFormName" needs to be yours.
    Paul

  10. #10
    Join Date
    Oct 2010
    Posts
    175
    Okay so now it's working to open up the form I want, but it's opening to a blank record.

    Here's the code I entered....please look at it for me.

    DoCmd.OpenForm "Main Form", , , "LastName = '" & Me.Participants.Column(1) & "' AND FirstName = '" & Me.Participants.Column(2) & "' AND MiddleInitial = '" & Me.Participants.Column(3) & "'"

    Thanks.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Are those 3 your actual field names? Do they match up with the 2nd, 3rd and 4th columns in the combo's row source?
    Paul

  12. #12
    Join Date
    Oct 2010
    Posts
    51
    Then you don't have any records that match. One way to check this is to make sure your where clause is correctly structured. If you are in 2007 you can set TempVars, it would be much easier in VBA though.

    Code:
    Dim sWhere as String
    sWhere = "LastName = '" &  Me.Participants.Column(1) & "' "
    sWhere = sWhere & "AND FirstName = '" & Me.Participants.Column(2) & "' "
    sWhere = sWhere & "AND MiddleInitial = '" & Me.Participants.Column(3) & "'"
    
    Debug.Print sWhere
    Also remember that Listboxes and combo boxes are 0 indexed. So the column number is (n-1) First column =0, second =1, third =2, etc...

  13. #13
    Join Date
    Oct 2010
    Posts
    175
    Well...now that I'm looking at it, I did not use middle initial at all.

    SELECT TTPFQUERY.[Individual ID], TTPFQUERY.Expr1 FROM TTPFQUERY ORDER BY TTPFQUERY.Expr1;

    Above is my combo's row source.

  14. #14
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by akanick View Post
    Well...now that I'm looking at it, I did not use middle initial at all.

    SELECT TTPFQUERY.[Individual ID], TTPFQUERY.Expr1 FROM TTPFQUERY ORDER BY TTPFQUERY.Expr1;

    Above is my combo's row source.
    Individual ID would be a much better choice to use rather than the individual name segments. Unless you don't need a specific person and just want the nearest guy with the name you're looking for.

  15. #15
    Join Date
    Oct 2010
    Posts
    175
    Yeah, the problem with that is people don't look up records by individual id.

    I want my users to be able to pull up a record by name.

Posting Permissions

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