Need to Open a form based on selection to a specific record

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

Answer 1

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?

Answer 2

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?

Answer 3

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.

Answer 4

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?

Answer 5

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

Answer 6

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.

Answer 7

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???

Answer 8

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?

Answer 9

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!! :)

Answer 10

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Answer 11

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.