Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have two forms: Employee Lookup & Edit Record. These forms work, for the most part, as you would expect...look up an employee on the Employee Lookup form; if you want to edit the information for the person, click the Edit Record button to have the Employee Lookup form close & the Edit Record form open to the desired record. (At this point, the Edit Record form is filtered to show only the desired record.)

    When all editing is done, if you click the Save Changes button, I can get the Edit Record form to close and the Employee Lookup form to open but it's either opening as an unfiltered form to the first record (not the record that was edited)...or it's opening as a filtered form to the edited record.

    I'm looking for assistance, using VB, in opening the Employee Lookup form (unfiltered) to the specified record.

    The names of the forms are:
    • frmEmployees
    • frmEmployee_Edit

    The unique identifier for each record is EmpID.

    (I hope I didn't confuse anyone...my explanations can sometime come off that way.)

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Why not just leave the Lookup form open?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MarkLiquorman' post='792147' date='04-Sep-2009 16:02']Why not just leave the Lookup form open?[/quote]

    Aesthetics...the lookup form is larger than the edit form so I was hoping to keep a less-cluttered screen. (Granted two windows isn't exactly cluttered, but...)

    However, that brings up a different - although slightly related - issue in that after all changes are made on the edit form and saved, the changes aren't reflected when you return to the lookup form. I've used requery & refresh but it hasn't worked as hoped. (Even though I've been able to use VB to accomplish most of what I want, my knowledge is limited and any coding I've done will probably indicate that.)

    My primary goal is to have a lookup form and an edit form. If changes are required, clicking a button will open the edit form. Once any/all changes are made, a button will save the changes, the edit form will close and the lookup form will again be displayed with the just-edited record visible reflecting all changes. If at all possible, the lookup form in all cases will be unfiltered so that you can scroll through all records.

    At this point I'm not so much concerned with the process...just the end result. I'm all for leaving the lookup form open...I'll just adjust the size of the forms.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Another option is to simply make the Display form not visible when the Edit form is opened, and when the Edit form is closed turn the visible property of the Display form back on. That may make the aesthetic issue easier to deal with. It's a single line of code each time. Forms!frmDisplayForm.Visible = False and Forms!DisplayForm.Visible = True.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='egoche' post='792195' date='05-Sep-2009 08:59']Aesthetics...the lookup form is larger than the edit form so I was hoping to keep a less-cluttered screen. (Granted two windows isn't exactly cluttered, but...)[/quote]

    If you open the edit form Maximized then it will completely hide the lookup form. That is one way to avoid the clutter.
    But when you close the Edit form, the lookup form would need to be requeried to see any changes you made.

    Here is some code I have used to do a similar job. It stores the value of the key field, then requeries the form, then finds the record where the key equals the stored value.
    If you were reopening the form, you could pass it the value of the key using openargs.

    Code:
    Private Sub Form_Activate()
    	Dim lngBuyerID as long
    	DoCmd.Maximize
    	lngBuyerid = Me.BuyerID
    	Me.Requery
    	Set rs = Me.RecordsetClone
    	If lngBuyerid > 0 Then
    		rs.FindFirst "[BuyerID] =" & lngBuyerid
    		Me.Bookmark = rs.Bookmark
    	End If
       
    End Sub
    Regards
    John



  6. #6
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='792203' date='04-Sep-2009 23:50']Another option is to simply make the Display form not visible when the Edit form is opened, and when the Edit form is closed turn the visible property of the Display form back on. That may make the aesthetic issue easier to deal with. It's a single line of code each time. Forms!frmDisplayForm.Visible = False and Forms!DisplayForm.Visible = True.[/quote]

    I used similar coding but the lookup form didn't reflect the changes that were made using the edit form.

  7. #7
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='792204' date='04-Sep-2009 23:56']If you open the edit form Maximized then it will completely hide the lookup form. That is one way to avoid the clutter.
    But when you close the Edit form, the lookup form would need to be requeried to see any changes you made.

    Here is some code I have used to do a similar job. It stores the value of the key field, then requeries the form, then finds the record where the key equals the stored value.
    If you were reopening the form, you could pass it the value of the key using openargs.

    Code:
    Private Sub Form_Activate()
    	Dim lngBuyerID as long
    	DoCmd.Maximize
    	lngBuyerid = Me.BuyerID
    	Me.Requery
    	Set rs = Me.RecordsetClone
    	If lngBuyerid > 0 Then
    		rs.FindFirst "[BuyerID] =" & lngBuyerid
    		Me.Bookmark = rs.Bookmark
    	End If
       
    End Sub
    [/quote]

    As I mentioned, my knowledge of VB is limited so please understand if my questions seem basic. That being said, I believe I can follow what your coding is directing, but I'm confused about a couple of things...mostly with where the code should be placed. I apologize for this but...can you be more specific about where everything is? And I'll need to declare rs as a recordset, right?

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a demo, which is slightly changed. This has all the code in the "show details" button.

    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

    Code execution stops when you open a form with acDialog, then continues when the form is closed.
    so after the edit form is closed, the list form requeries itself, then finds the right record.

    You are right about needing to declare rs as recordset or object. I had it declared at a higher level so I forgot to include it previously.
    Attached Files Attached Files
    Regards
    John



  9. #9
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='792603' date='08-Sep-2009 23:56']I attach a demo, which is slightly changed. This has all the code in the "show details" button.

    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

    Code execution stops when you open a form with acDialog, then continues when the form is closed.
    so after the edit form is closed, the list form requeries itself, then finds the right record.

    You are right about needing to declare rs as recordset or object. I had it declared at a higher level so I forgot to include it previously.[/quote]

    Ahhh...I didn't think about storing the record ID in the button that opens the edit form. (Although I still would have needed help with the coding!) Thanks so much...I'll try to get it incorporated today and see how it works out!

Posting Permissions

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