Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    johnpaul is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    21

    Use a command button to open a form

    Hi,

    I am having problems with 2 forms

    I have a form which is called EmployeeRecord - This is a search form that I plan to have command buttons on to open up my other forms so I ca create an employee record.

    I have managed to to get this form to display the fields from my employee table.



    I am am having issues getting the other table to link to the top form when I open them with a command button.

    If i drag my other forms onto my search form they will work how I want.
    If I crate a cmdbutton I can open the form and enter data but it will not referance the linked fields on the 2 tables "EmployeeNumber"

    EmployeeNumber is PK on my employee table and FK on my table I wish to have the cmdbutton open.

    can anyone help me?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this what you're trying to do?

    BaldyWeb wherecondition
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    pbaldy-

    I am trying to use the code on http://www.baldyweb.com/Bookmark.htm, but the PK i'm using is a text field. How would the code change? I tried to modify it myself...I'll bet you can guess how that turned out.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Just like on the wherecondition page. Additions in red:

    rs.FindFirst "EmpID = '" & lngBookmark & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Thank you, but I am still getting an error:

    "Run-time error '3077': Syntax error in string expression."

    Here is what I have:

    Code:
    Private Sub cmdOpenfrmIndividualDataEdit_Click()
    Dim rs As Object
    Dim lngBookmark As Long
    'set a variable to the current record
    lngBookmark = Me.EmployeeID
    'open the new form
    DoCmd.OpenForm "frmIndividualDataEdit"
    'take it to the selected record
    Set rs = Forms!frmIndividualDataEdit.RecordsetClone
    rs.FindFirst "EmployeeID = " & lngBookmark & "'"
    Forms!frmIndividualDataEdit.Bookmark = rs.Bookmark
    Set rs = Nothing
    End Sub

  6. #6
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Quote Originally Posted by thekruser View Post
    Thank you, but I am still getting an error:

    "Run-time error '3077': Syntax error in string expression."

    Here is what I have:

    Code:
    Private Sub cmdOpenfrmIndividualDataEdit_Click()
    Dim rs As Object
    Dim lngBookmark As Long
    'set a variable to the current record
    lngBookmark = Me.EmployeeID
    'open the new form
    DoCmd.OpenForm "frmIndividualDataEdit"
    'take it to the selected record
    Set rs = Forms!frmIndividualDataEdit.RecordsetClone
    rs.FindFirst "EmployeeID = " & lngBookmark & "'"
    Forms!frmIndividualDataEdit.Bookmark = rs.Bookmark
    Set rs = Nothing
    End Sub
    It is failing on

    Code:
    rs.FindFirst "EmployeeID = " & lngBookmark & "'"

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You missed the single quote just before the close of the first set of quotes (just after the equals sign). Also, if you EmployeeID field is text, the variable should be declared as String rather than Long. You can get away with that if it's a text field with numbers in it, but it will blow up if you put letters in it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Well, now it is not generating any errors and is opening the form, but it is not going to the correct record. It just defaults to the first record in the table regardless of the person selected in the first form.

  9. #9
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    I found the problem. The cbo I was using was unbound. Fixed it. Thanks for all of your help!

  10. #10
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Actually, one more question...how do I get form 1 to close when I hit the "do it to it" button?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working. I wouldn't expect the combo to have to be bound, but it would require that the bound column be the appropriate field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use

    DoCmd.Close...

    Because the recently opened form will have focus, you'll have to specify which form you want to close (if unspecified, the active object will close). More info on the arguments in VBA help on Close.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Wow...I just keep coming up with problems. When I closed form 1 manually it errored again. This time it said it could not complete the action due to creating duplicate entries in the PK field. I guess binding was the wrong answer, but now I'm back to square one.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I just tested with an unbound combo and it worked as expected. Can you just delete the control source and see if it works then?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    thekruser is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    93
    Well, I am getting closer, but not there yet. Now the button doesn't error, it closes, other form opens, but it opens to the wrong record. Here is what I have now:

    Code:
    Private Sub cmdOpenfrmIndividualDataEdit_Click()
    Dim rs As Object
    Dim lngBookmark As String
    'set a variable to the current record
    lngBookmark = Me.EmployeeID
    'open the new form
    DoCmd.OpenForm "frmIndividualDataEdit"
    'take it to the selected record
    Set rs = Forms!frmIndividualDataEdit.RecordsetClone
    rs.FindFirst "EmployeeID = '" & lngBookmark & "'"
    Forms!frmIndividualDataEdit.Bookmark = rs.Bookmark
    Set rs = Nothing
    DoCmd.Close acForm, "frmSelectIndividual"
    End Sub
    Might just need to walk away from it for the day and revisit it tomorrow.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-27-2010, 02:27 PM
  2. Replies: 1
    Last Post: 07-07-2010, 11:06 AM
  3. Export Command Button in Form
    By jjmartinson in forum Forms
    Replies: 3
    Last Post: 08-25-2009, 01:28 AM
  4. Adding Command Button To Form
    By uneek78 in forum Forms
    Replies: 7
    Last Post: 03-27-2009, 07:43 PM
  5. Open a linked subform with a command button
    By flablueeyedblond in forum Forms
    Replies: 0
    Last Post: 11-18-2005, 01:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums