Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).
Course Of The Month
7 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Ms Access - Need to make a Detail items in a SubForm clickable to another form.
Posted on 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.CompanyNam
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.
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.CompanyNam
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.
[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
Active today
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)?
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)?
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. :)
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. :)
In my unbound list box, I alread bind column one "ExpiresIn60Days.CompanyNa
Like this:
Private Sub lstboxExpiring_DblClick(Ca
On Error GoTo Err_lstboxExpiring_DblClic
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contacts"
DoCmd.OpenForm stDocName, , , ExpiresIn60Days.CompanyNam
Exit_lstboxExpiring_DblCli
Exit Sub
Err_lstboxExpiring_DblClic
MsgBox Err.Description
Resume Exit_lstboxExpiring_DblCli
End Sub
Private Sub lstboxExpiring_DblClick(Ca
On Error GoTo Err_lstboxExpiring_DblClic
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contacts"
DoCmd.OpenForm stDocName, , , ExpiresIn60Days.CompanyNam
Exit_lstboxExpiring_DblCli
Exit Sub
Err_lstboxExpiring_DblClic
MsgBox Err.Description
Resume Exit_lstboxExpiring_DblCli
End Sub
Active today
That's not a valid WHERE clause in the OpenForm method. It should look like this:
DoCmd.OpenForm stDocName, , , "SomeFieldName=" & ExpiresIn60Days.CompanyNam
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.CompanyNam
DoCmd.OpenForm stDocName, , , "SomeFieldName=" & ExpiresIn60Days.CompanyNam
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.CompanyNam
I get a "variable not defined" in this:
DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyNam
Specifically on: ExpiresIn60Days
DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyNam
Specifically on: ExpiresIn60Days
Private Sub lstboxExpiring_DblClick(Ca
On Error GoTo Err_lstboxExpiring_DblClic
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contacts"
DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyNam
Exit_lstboxExpiring_DblCli
Exit Sub
Err_lstboxExpiring_DblClic
MsgBox Err.Description
Resume Exit_lstboxExpiring_DblCli
End Sub
On Error GoTo Err_lstboxExpiring_DblClic
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contacts"
DoCmd.OpenForm stDocName, , , "CompanyName=" & ExpiresIn60Days.CompanyNam
Exit_lstboxExpiring_DblCli
Exit Sub
Err_lstboxExpiring_DblClic
MsgBox Err.Description
Resume Exit_lstboxExpiring_DblCli
End Sub
I got it! This is what worked:
Private Sub lstboxExpiring_DblClick(Ca
On Error GoTo Err_lstboxExpiring_DblClic
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contacts"
stLinkCriteria = "CompanyName='" & Forms!MainMenu!lstboxExpir
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_lstboxExpiring_DblCli
Exit Sub
Err_lstboxExpiring_DblClic
MsgBox Err.Description
Resume Exit_lstboxExpiring_DblCli
End Sub
Private Sub lstboxExpiring_DblClick(Ca
On Error GoTo Err_lstboxExpiring_DblClic
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Contacts"
stLinkCriteria = "CompanyName='" & Forms!MainMenu!lstboxExpir
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_lstboxExpiring_DblCli
Exit Sub
Err_lstboxExpiring_DblClic
MsgBox Err.Description
Resume Exit_lstboxExpiring_DblCli
End Sub
Featured Post
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This l…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Course of the Month7 days, 13 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.