Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30

    Help with OpenForm Macro

    Hi,

    I am trying to create a macro that will open a form name frm_EEWorkHist to a specific record when I double click on a text field of a subform. If the record is new on the subform I want the macro to open the form frm_EEWorkHist to add a new record. The Macro has two Where Conditions [EEID] and [WorkID] and it works fine when both fields are not null. The problem is, if one of the Fields is null then it gives me an error. I need to tell the Macro that if [WorkID] is null then add a new record with the [EEID] = to whatever it was on the parent form and [WorkID] = blank, (hope I'm making any sense here).

    Here is the code:

    Click image for larger version. 

Name:	macro1.PNG 
Views:	18 
Size:	17.5 KB 
ID:	32569


    Click image for larger version. 

Name:	macro2.PNG 
Views:	17 
Size:	16.9 KB 
ID:	32570

    This is the error:
    Click image for larger version. 

Name:	error.PNG 
Views:	16 
Size:	29.1 KB 
ID:	32571

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Convert the macro to VBA - far easier to work with and troubleshoot (and more people to help you!).

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If WorkID is a text type field, the parameter needs apostrophe delimiters.

    AND [WorkID]='" & [WorkID] & "'"

    If it is a number type field then don't use & ""

    If you want to allow the WorkID to not be provided, probably need an IIf() to conditionally construct the WHERE CONDITION. Easily done in VBA, not sure about macro.

    Agree with aytee111. I have never used macros (with exception of an AutoExec macro).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    Quote Originally Posted by aytee111 View Post
    Convert the macro to VBA - far easier to work with and troubleshoot (and more people to help you!).
    How do i go about converting this Macro to VBA? I'm just a beginner at this.

    Thank you!

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the top left of the window it says "Convert Macro to Visual Basic".

    Sometimes this seems to be grayed out. Here is the solution:
    "The solution I found was, rather than loading the form in Design View,
    I clicked the Macro button under Create on the Ribbon which brought up
    the Macro Design window. Under the Action Catalog window, there is a
    section called "In this Database." I clicked down to the form and
    textbox which contained the macro I wanted to convert, then double-
    clicked. The macro displayed in the left hand window and the "Convert
    Macro's to Visual Basic", was not grayed out so I clicked it and the
    conversion was simple after that."

    You will see that the VBA code follows the macro and you can easily see what is going on, it will all feel very similar to you.

    Once you have done this, post the VBA code here. You can double-click anywhere from the left side of the subroutine, this will highlight the code. Copy it and then come here, click on the "#" icon for code and paste it.

  6. #6
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    Thank you both for such a prompt reply!

    Aytee,

    As you stated, the convert to macro was grayed out but your solution worked perfectly. Below is the converted macro.

    Code:
    Option Compare Database
    
    '------------------------------------------------------------
    ' Macro1
    '
    '------------------------------------------------------------
    Function Macro1()
    On Error GoTo Macro1_Err
    
    
        With CodeContextObject
            On Error Resume Next
            If (.Form.Dirty) Then
                DoCmd.RunCommand acCmdSaveRecord
            End If
            If (.MacroError.Number <> 0) Then
                Beep
                MsgBox .MacroError.Description, vbOKOnly, ""
                Exit Function
            End If
            On Error GoTo 0
            DoCmd.OpenForm "frm_EEWorkHist", acNormal, "", "[EEID]=" & .EEID & " AND [WorkID]=" & .WorkID & "", , acDialog
            If (Not IsNull(.EEID)) Then
                TempVars.Add "CurrentID", .EEID
            End If
            If (IsNull(.EEID)) Then
                TempVars.Add "CurrentID", Nz(DMax("[EEID]", .Form.RecordSource), 0)
            End If
            DoCmd.Requery ""
            DoCmd.SearchForRecord , "", acFirst, "[EEID]=" & TempVars!CurrentID
            TempVars.Remove "CurrentID"
        End With
    
    
    
    
    Macro1_Exit:
        Exit Function
    
    
    Macro1_Err:
        MsgBox Error$
        Resume Macro1_Exit
    
    
    End Function

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    OK, now I can see straight!

    There are two ID's you are working with. EEID is from the parent form - how can this be blank? I cannot see how that can happen. WorkID is from the subform table and can be blank due to the fact that the record has not been saved?

    If this is the case and you are wanting to open the form based on these two ID's (do you really need both or will WorkID be enough?), then you can simplify the code to this:

    Code:
    Private Sub txt1_DblClick(Cancel As Integer)
    
        If Me.Dirty = True Then Me.Dirty = False
        DoCmd.OpenForm "frm_EEWorkHist, , , "WorkID=" & Me!WorkID & " EEID=" & Me!EEID
        
    End Sub

  8. #8
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    Hi Aytee111, I need to open the form when [WorkID] is blank on the subform not EEID. EEID is never blank. I want to open the form to edit the record when EEID and WORKID is not null. If WorkID is null then Add a new record in frm_EEWorkHist. (EEID will still have a value at this point).

  9. #9
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    Here is the form. If I click on Eemployee ID where Work ID has a value it open frm_EEWorkHist on that record (works great). Now if WorkID is (new) then i want to open frm_EEWorkHist on that specific [EEID] and add a new [WorkID] on the table Tbl_EEWorkHist.

    Click image for larger version. 

Name:	CallOut.PNG 
Views:	10 
Size:	57.9 KB 
ID:	32578

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I still need to know about work ID - is it an autonumber, is it the primary key of the record? Why is it displayed to the user? Is this subform a query displaying data from multiple tables, and what is different about the new form? The reason I am asking is that after you have added the new record on the second form, this data will need to be refreshed. If it is the same record then there will be issues leaving it like it is.

  11. #11
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    is it an autonumber (YES), is it the primary key of the record?(Yes, its the primary key on Tbl_EEWorkHist) Why is it displayed to the user? (To show that it is the last job on the history of the employee) Is this subform a query displaying data from multiple tables (Yes, here is the query :SELECT Tbl_EEWorkHist.*, Tbl_Employees.EE_LastName, Tbl_Employees.EE_FirstName FROM Tbl_Employees INNER JOIN Tbl_EEWorkHist ON Tbl_Employees.EEID = Tbl_EEWorkHist.EEID, and what is different about the new form? (The new form helps input the data in a FORM manner rather than in the datasheet view which allows me to display less fields on the subform) The reason I am asking is that after you have added the new record on the second form, this data will need to be refreshed. If it is the same record then there will be issues leaving it like it is.( I have a save button on the second form)

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Make it simpler. The way you have it right now is rather confusing and it will help both the user and you to simplify it.
    - Either don't allow additions on the subform and have a "NEW" button, the user will click it and the second form will open where they enter a new record. Then when that form closes the subform will be refreshed to include the new record.
    - Or allow them to add the new record on the subform then open the second form with the code posted above (the record already added). The subform data will still need to be refreshed.

    The record source for the subform does not need to contain the employee table, that data is already available to the user in the parent form.

  13. #13
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    Hi Aytee111,

    I'm back! So, I was trying to put the add a new record button on the parent form to add a record on the subform but EEID is not passing to frm_EEWorkHist . How do i do this?

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the BeforeInsert event on the second form, add this:
    Me!EEID=Forms!parentformname!EEID

  15. #15
    griztkojr is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    30
    Awesome!!! It worked!! Thank you Thank you!!!

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

Similar Threads

  1. onclick openform macro causes syntax error
    By gunitinug in forum Access
    Replies: 2
    Last Post: 08-25-2017, 12:03 AM
  2. Replies: 1
    Last Post: 12-14-2015, 05:32 PM
  3. Web Database: OpenForm Macro to Specific Record
    By besuchanko in forum Macros
    Replies: 3
    Last Post: 07-25-2013, 12:17 PM
  4. Replies: 14
    Last Post: 07-17-2013, 06:46 AM
  5. OpenForm macro
    By tguckien in forum Forms
    Replies: 3
    Last Post: 07-06-2010, 09:12 AM

Tags for this Thread

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