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

x
?
Solved

Ms Access - Need to make a Detail items in a SubForm clickable to another form.

Posted on 2013-02-04
11
Medium Priority
?
599 Views
Last Modified: 2013-02-04
I have a subform that is populated by the union of two queries. The result are displayed in an unbound listbox.

The contents are software license keys and expire dates for software we write for our customers

My desire is to be able to dbl click on an item in the list box and have it take me to the Contact Management Form for the customer in question.

This is the Row Source for my unbound List box:

SELECT ExpiresIn60Days.CompanyName, ExpiresIn60Days.Item AS Product, ExpiresIn60Days.[What's Expiring], ExpiresIn60Days.EXPIRES, ExpiresIn60Days.SerialNo FROM ExpiresIn60Days ORDER BY ExpiresIn60Days.EXPIRES;


I have a button on my main menu that takes me to the Contacts Form, from there I have to select the contact from a drop down list.

This is the button that take me to the Contact Form:
Private Sub EditContacts_Click()
On Error GoTo Err_EditContacts_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Contacts"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_EditContacts_Click:
    Exit Sub

Err_EditContacts_Click:
    MsgBox Err.Description
    Resume Exit_EditContacts_Click
   
End Sub


And here is the code for the combo box that opens the customer in the Contact Form
Private Sub Combo22_AfterUpdate()
 Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustNumber] = " & Str(Me![Combo22])
    Me.Bookmark = rs.Bookmark
End Sub


My goal is to click anywhere on the item in the list box, and have it open the contact form with the same customer as who I clicked on in the list box.

Thanks.
Please ask any questions needed to make this work.
0
Comment
Question by:jasgot
[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
  • 8
  • 3
11 Comments
 
LVL 85
ID: 38852434
Set the value of strLinkCriteria to the correct CustNumber:

    DoCmd.OpenForm stDocName, , , "[CustNumber]=" & Me.SomeValue

Of course, the Listbox where this code is fired would need to be able to get the value of CustNumber - is that value available somewhere (like on the parent form of that listbox, perhaps)?
0
 

Author Comment

by:jasgot
ID: 38852572
The customer number comes from that Contacts Table.  Should I add that table to the union I use for the list box? Or is there another way to get that data?

This is my row source for the combo box on the contact form that lets me select the customer:
SELECT Contacts.CompanyName, Contacts.CustNumber, Contacts.ContactPerson FROM Contacts ORDER BY Contacts.CompanyName;

Now that I think about it, the customer number represents ONE of the contact pserons at a given company.  The list box I want to click in is based on the company name only. So I'm not sure how I would select a company name and the have it open the contact form with a customer number. It doesn;t matter which customer number/contact name I use because each contact record show the same licensing info (based on the company)

My union is only editable in SQL, so it will be a challenge for me to add another table without help. Design view agreese with me, SQL view does not. :)
0
 
LVL 85
ID: 38852609
Does "ExpiresIn60Days.CompanyName" = "Contacts.CompanyName"?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:jasgot
ID: 38852886
Yes it does.
0
 

Author Comment

by:jasgot
ID: 38852931
In my unbound list box, I alread bind column one "ExpiresIn60Days.CompanyName " of the row source when I look at the data properties. Maybe this will help.
0
 

Author Comment

by:jasgot
ID: 38852946
Like this:
Private Sub lstboxExpiring_DblClick(Cancel As Integer)
On Error GoTo Err_lstboxExpiring_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Contacts"
    DoCmd.OpenForm stDocName, , , ExpiresIn60Days.CompanyName

Exit_lstboxExpiring_DblClick:
    Exit Sub

Err_lstboxExpiring_DblClick:
    MsgBox Err.Description
    Resume Exit_lstboxExpiring_DblClick
End Sub
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38852971
That's not a valid WHERE clause in the OpenForm method. It should look like this:

DoCmd.OpenForm stDocName, , , "SomeFieldName=" & ExpiresIn60Days.CompanyName

So the CompanyName is the same, and you now want to open the Contacts form to that record? Is the field "CompanyName" in the Contact form's RecordSource? If it is, then the OpenForm above could be something like this:

DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyName
0
 

Author Comment

by:jasgot
ID: 38852989
I get a "variable not defined" in this:
DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyName

Specifically on: ExpiresIn60Days
0
 

Author Comment

by:jasgot
ID: 38852992
Private Sub lstboxExpiring_DblClick(Cancel As Integer)
On Error GoTo Err_lstboxExpiring_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Contacts"
    DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyName

Exit_lstboxExpiring_DblClick:
    Exit Sub

Err_lstboxExpiring_DblClick:
    MsgBox Err.Description
    Resume Exit_lstboxExpiring_DblClick
End Sub
0
 

Author Comment

by:jasgot
ID: 38853290
I got it! This is what worked:

Private Sub lstboxExpiring_DblClick(Cancel As Integer)
On Error GoTo Err_lstboxExpiring_DblClick

    Dim stDocName As String
    Dim stLinkCriteria As String
   
    stDocName = "Contacts"
    stLinkCriteria = "CompanyName='" & Forms!MainMenu!lstboxExpiring & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_lstboxExpiring_DblClick:
    Exit Sub

Err_lstboxExpiring_DblClick:
    MsgBox Err.Description
    Resume Exit_lstboxExpiring_DblClick
End Sub
0
 

Author Closing Comment

by:jasgot
ID: 38853292
You got me in the right direction to solve my problem. Thank you very much.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

604 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