Results 1 to 11 of 11
  1. #1
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101

    Open form based on criteria of another form, msg box is criteria not met

    I am trying to come up with the correct code for the following scenario. I have a pop-up form with that opens with the PhnIndex number is click on another form. On the pop-up form I have a command button to view change history. When click it will open the form based on the PhnIndex field of the pop-up form. I have an issue if the record does not have a change history. When the button is click it brings up a blank form.
    What I would like to do is to have a message box display "This station does not have any change history".
    The table where the changes are stored is named audManagedIP. The PhnIndex field is a number field and the only constant in the record since it is the primary key in the main table where all records are stored.
    This is the code I tried but the blank form still pops up



    Code:
    Private Sub Command54_Click()If Nz(DLookup("PhnIndex", "audManagedIP"), "") = "" Then
         MsgBox "There is no change history for this station."
         Exit Sub
    Else
    DoCmd.OpenForm "frm_auditviewcare", , , "[phnindex] =" & Forms!frm_care_edit.PhnIndex
    DoCmd.Close acForm, "frm_care_edit", acSaveYes
    End If
    
    
    End Sub
    Any help on this would be greatly appreciated!!!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you could use DCount() function to determine the number of records that would be returned and only open the form if it returns lager than 0
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Private Sub Command54_Click()
    If DCount("PhnIndex", "audManagedIP"),"PhnIndex= " & me.PhnIndex & ")" = 0 Then
         MsgBox "There is no change history for this station."
         Exit Sub
    Else
    Me.PhnIndex is a control on your form holding the value of PhnIndex.

  4. #4
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thank you for the response davergi. I entered the code you provided and I get a compile error.

    Click image for larger version. 

Name:	cmderror_041018.PNG 
Views:	9 
Size:	17.2 KB 
ID:	33492

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Oops, don't need that red close parens.
    Code:
    If DCount("PhnIndex", "audManagedIP"),"PhnIndex= " & me.PhnIndex & ")" = 0 Then

  6. #6
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    I removed the bracket you noted but now it gets a error on the "Then" statement.

    Click image for larger version. 

Name:	Inv_0.JPG 
Views:	6 
Size:	38.9 KB 
ID:	33495

    I am starting to think that maybe Dcount is not the way to go. Let me see if I can better explain what I am trying to do.

    In my database I have an Audit function to track and edits, adds and deletes.
    I have a continuous form where on the PhnIndx field I have an onclick CBA code to open a pop-up to that record for editing.

    Click image for larger version. 

Name:	Inv_1.jpg 
Views:	6 
Size:	117.5 KB 
ID:	33497

    As you can see from the screen shot at the bottom of the form I have a button to view change history, if there is any.
    When the history form is opened the pop-up form is closed. Did that due to the history form is a tabbed form.

    Click image for larger version. 

Name:	Inv_2.jpg 
Views:	6 
Size:	98.6 KB 
ID:	33499

    My issue is when the record on the pop-up form does not have any change history, it does not appear in the audit table.
    When that command button is clicked it brings up the history form and it is blank. The Close button Is programmed to open the pop-up form again back to the original record and close the history form. When history form is blank
    the pop-up form comes up blank instead of the original record.
    I am trying to get a message to pop-up if there is no change history for a record.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    With the above information, it appears that the field holding PhnIndex is [Phn Idx] so:
    Code:
    If DCount("PhnIndex", "audManagedIP","PhnIndex= " & [Phn Idx] & ")" = 0 Then
    The name should be the name of the textbox holding the phone index (3415).

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    PhnIndex holds a number?
    Then
    DCount("PhnIndex", "audManagedIP","PhnIndex= " & [Phn Idx] ) = 0 ??
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    vad77 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    USA
    Posts
    101
    Thanks for getting me in the right direction davergi. I got it to work with the following code.

    Code:
    Private Sub Command54_Click()If DCount("PhnIndex", "audManagedIP", "PhnIndex= " & [PhnIndex] & "") = 0 Then
         MsgBox "There is no change history for this station."
         Exit Sub
    Else
    DoCmd.OpenForm "frm_auditviewcare", , , "[phnindex] =" & Forms!frm_care_edit.PhnIndex
    DoCmd.Close acForm, "frm_care_edit", acSaveYes
    End If
    
    
    End Sub
    It seems the ) between the quotes was the issue, I moved it to the right of the quote and it works great.
    Thanks for the help.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Good catch. Glad you got it working.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Have to wonder - what's with the empty string concatenated onto the end? Other than that, I don't see how it differs from what I posted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-08-2016, 10:06 PM
  2. Open form based on search criteria
    By cactuspete13 in forum Forms
    Replies: 7
    Last Post: 03-06-2013, 10:17 AM
  3. Replies: 3
    Last Post: 12-25-2012, 12:43 AM
  4. Open (sub)form linking 2 criteria on current form
    By websterh in forum Programming
    Replies: 2
    Last Post: 02-07-2011, 11:56 PM
  5. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 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