Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Open forms with same record on a different form

Posted on 2013-01-17
2
Medium Priority
?
484 Views
Last Modified: 2013-01-17
I have a Add New Record form and a Edit Resource Form.

Add Resource Form Fields: (Populated from a combo box that pulls from active directory)
Resource ID
First
Last
Full Name
Email

Edit Resource Form:
Resource ID
First
Last
Full Name
Email
Plus additional information that is needed to add in addition to what was populated from Active Directory.

I want to be able to:
1) Add a Resource.
2) Save and open a new form to edit and add additional information.
3) Open the Edit Resource Form using the same primary ID that is linked to all the data.  

This seems simple and all the code I have tried does not work.

Problem.  When I open up my form in "Edit Mode" by the same Resource ID, it opens the form in Edit mode and brings over the same Resource ID, however, when the form opens and a record displays by default it over rides what Resource ID is already there and now I have my new Resource ID linked to the old records Resource ID's information (First, Last, ect)

I want to be able to open a the second form, display all the records by the Resource ID, not just populate the Resource ID.

Here are both codes that I have tried:

Code 1: (standard access default)
----------------------------------------------------------------
Private Sub cmdEditOpen_Click()
On Error GoTo Err_cmdEditOpen_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmEditResources"
   
    stLinkCriteria = "[ResourceID]=" & "'" & Me![ResourceID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdEditOpen_Click:
    Exit Sub

Err_cmdEditOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdEditOpen_Click
   
End Sub

Code 2:
-------------------------------------------------------------------
Private Sub cmdEdit_Click()
On Error GoTo Err_cmdEdit_Click

    DoCmd.OpenForm "frmEditResources", acNormal, , , acFormEdit
    [Forms]![frmEditResources]![ResourceID] = [Forms]![frmAddResources V2]![ResourceID]
    [Forms]![frmEditResources]![Last] = [Forms]![frmAddResources V2]![Last]
    [Forms]![frmEditResources]![First] = [Forms]![frmAddResources V2]![First]
    [Forms]![frmEditResources]![Full Name] = [Forms]![frmAddResources V2]![Full Name]
    [Forms]![frmEditResources]![Email] = [Forms]![frmAddResources V2]![Email]
     
Exit_cmdEdit_Click:
    Exit Sub

Err_cmdEdit_Click:
    MsgBox Err.Description
    Resume Exit_cmdEdit_Click
   
End Sub
-----------------------------------------------------------------------------------------------------------------------------------

If I combined the efforts on one form, it can get confusing for day to day users who may accidentally add a new user thinking they can search from the drop down.

If there is an easier suggestion, please let me know.

THANKS!
0
Comment
Question by:gracie1972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 38788787
I recommend using the same form for adding and editing.  Put a big "Add New Record" button in the form header or footer, and maybe make a dummy record that will sort to the beginning, in case you have users who will just start typing a new record in the first record that appears.  For severe cases, you could put some code on the BeforeUpdate event that pops up a message when this record is edited -- something like "Please select the correct record in the Select Record combo box, or click the New Record button to create a new record".  Searching is best done with an unbound combo box in the header, with code like this:

Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Comment

by:gracie1972
ID: 38789158
@Helen_Feddema, is there a way to have the drop down that runs my code be hidden unless they select add new record?

I know in SSRS you can hide/unhide data, I was not sure I can use the same logic?

1) Add new record
2) Form clears for adding
3) drop down becomes visible to select from and my other drop down for searching becomes hidden.

Is this to complex?
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question