none
Need to Open a form based on selection to a specific record RRS feed

  • Question

  • I have form set up that my user selects a particular category and that selection feeds a datasheet child form the records from my main table assigned to that category.  When my user selects a record, I need it to then open a data entry form specific to that particular category - it could be one of several forms as each category has its own data entry form.  I have accomplished that - the problem I am having is that when the data entry form opens, I want it to populate the record data that was selected for the child record (the general customer data from my main table) in the upper area with the data entry piece below ready for data entry.

    Through an IF statement, I can get it to open the specific data entry form based on the category, I cannot get with the specific data pre-populated into that form. 

    Here is the statement I am using:

    Private Sub Form_DblClick(Cancel As Integer)

    Dim varWhereClause As String
    Dim stFormName As String

    varWhereClause = "accnum = " & "'" & Me.accnum.Value & "'"

    If (MeasureID = "PSI 4") Then

    stFormName = "PSI4main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "PSI 6") Then

    stFormName = "PSI6main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "PSI 9") Then

    stFormName = "PSI9main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "PSI 11") Then

    stFormName = "PSI11main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "PSI 12") Then

    stFormName = "PSI12main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "PSI 15") Then

    stFormName = "PSI15main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "IQI 13") Then

    stFormName = "IQI13main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "IQI 15") Then

    stFormName = "IQI15-32main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    Else

    If (MeasureID = "IQI 32") Then

    stFormName = "IQI15-32main"
    DoCmd.OpenForm stFormName, , , varWhereClause

    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If

    I would appreciate any suggestions to make this work.

    Thanks

    Tuesday, October 13, 2015 12:33 PM

Answers

  • Becasue I am using so many different forms, how would you suggest making that into a select case?

    Hi seebert,

    In line with what DB guy suggested, you could try something like:

    Private Sub Form_DblClick(Cancel As Integer)
    
      Dim varWhereClause As String
      Dim stFormName As String
    
      varWhereClause = "accnum = " & "'" & Me.accnum & "'"
    
      
      Select Case measureID
      Case "PSI 4":  stFormName = "PSI4main"
      Case "PSI 6":  stFormName = "PSI6main"
      Case "PSI 9":  stFormName = "PSI9main"
      Case "PSI 11": stFormName = "PSI11main"
      Case "PSI 12": stFormName = "PSI12main"
      Case "PSI 15": stFormName = "PSI15main"
      Case "IQI 13": stFormName = "IQI13main"
      Case "IQI 15": stFormName = "IQI15-32main"
      Case "IQI 32": stFormName = "IQI15-32main"
      Case Else: Exit Sub
      End Select
     
      DoCmd.OpenForm stFormName, , , varWhereClause
    
    End Sub
    

    Imb.

    Tuesday, October 13, 2015 2:10 PM
  • Hi. To open a form to a specific record, you can use the Where clause like you're doing but make sure the form is set up correctly.
    Tuesday, October 13, 2015 5:49 PM
  • Hi seebert,

    I am glad your issue has been resolved, and I suggest you mark the helpful reply as answer to close this thread.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by seebert Thursday, October 22, 2015 7:55 PM
    Thursday, October 22, 2015 5:11 AM

All replies

  • Hi. With that many choices, I would recommend using a Select Case statement instead. What does your Where clause filter? Can't you adjust it to filter the selected record?
    Tuesday, October 13, 2015 12:55 PM
  • The whereClause looks at the account numer of the record they select.  So if they select a record, it first looks at what the category for the record is and opens the appropriate data entry form, it shoudl then using the account number populate the record with the record they selected using the account number.  Does that make sense?

    Becasue I am using so many different forms, how would you suggest making that into a select case?

    Tuesday, October 13, 2015 1:04 PM
  • Becasue I am using so many different forms, how would you suggest making that into a select case?

    Hi seebert,

    In line with what DB guy suggested, you could try something like:

    Private Sub Form_DblClick(Cancel As Integer)
    
      Dim varWhereClause As String
      Dim stFormName As String
    
      varWhereClause = "accnum = " & "'" & Me.accnum & "'"
    
      
      Select Case measureID
      Case "PSI 4":  stFormName = "PSI4main"
      Case "PSI 6":  stFormName = "PSI6main"
      Case "PSI 9":  stFormName = "PSI9main"
      Case "PSI 11": stFormName = "PSI11main"
      Case "PSI 12": stFormName = "PSI12main"
      Case "PSI 15": stFormName = "PSI15main"
      Case "IQI 13": stFormName = "IQI13main"
      Case "IQI 15": stFormName = "IQI15-32main"
      Case "IQI 32": stFormName = "IQI15-32main"
      Case Else: Exit Sub
      End Select
     
      DoCmd.OpenForm stFormName, , , varWhereClause
    
    End Sub
    

    Imb.

    Tuesday, October 13, 2015 2:10 PM
  • The whereClause looks at the account numer of the record they select.  So if they select a record, it first looks at what the category for the record is and opens the appropriate data entry form, it shoudl then using the account number populate the record with the record they selected using the account number.  Does that make sense?

    Becasue I am using so many different forms, how would you suggest making that into a select case?


    Are you opening a form to add a  new record for the selected category, or are you opening it to "edit" an existing record?
    Tuesday, October 13, 2015 2:55 PM
  • Right now my goal is for it to open an existing record. 

    I did convert my IF statement to the Select Case and I am sucessful to get it to open the right form based on the right category but it still will no pre-populate the form it is opening with the existing record.

    Still stuck!

    Seebert

    Tuesday, October 13, 2015 4:55 PM
  • Hi. To open a form to a specific record, you can use the Where clause like you're doing but make sure the form is set up correctly.
    Tuesday, October 13, 2015 5:49 PM
  • So I am still working this project.  My code after selecting the record from form 1 looks like this:

     Dim varWhereClause As String
      Dim stFormName As String

      Select Case MeasureID
      Case "PSI 4":  stFormName = "PSI4main"
      Case "PSI 6":  stFormName = "PSI6main"
      Case "PSI 9":  stFormName = "PSI9main"
      Case "PSI 11": stFormName = "PSI11main"
      Case "PSI 12": stFormName = "PSI12main"
      Case "PSI 15": stFormName = "PSI15main"
      Case "IQI 13": stFormName = "IQI13main"
      Case "IQI 15": stFormName = "IQI15main"
      Case "IQI 32": stFormName = "IQI15main"
      Case "IQI 15-32": stFormName = "IQI15-32main"
      Case Else: Exit Sub
      End Select
     
      varWhereClause = "accnum = " & "'" & Me.AccNum & "'"
     
      DoCmd.OpenForm stFormName, , , varWhereClause

    It is opening the correct form but if the form that needs to be open is closed, then it opens the form and displays the first record from the table that form is based from.  If the form is already open, it does open the correct form to the correct record.

    Thoughts???

    Tuesday, October 20, 2015 6:59 PM
  • Hi. It's hard to say without seeing what you got but the Filter argument should limit the form to the specified record and if there's none, then the form will open empty. Would you have other code running on the form that could be conflicting with what you're trying to do?
    Tuesday, October 20, 2015 11:08 PM
  • You were right - there was a filter running on open on the data entry form.  I removed that filter and now things are populating correctly.  My problem is working with a database someone else created - I am finding all kinds of unnecessary things.  Thanks for the help!! :)
    Wednesday, October 21, 2015 12:45 PM
  • Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
    Wednesday, October 21, 2015 3:25 PM
  • Hi seebert,

    I am glad your issue has been resolved, and I suggest you mark the helpful reply as answer to close this thread.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by seebert Thursday, October 22, 2015 7:55 PM
    Thursday, October 22, 2015 5:11 AM