Solved

Getting DropDown to Work

Posted on 2010-11-11
9
278 Views
Last Modified: 2012-05-10
I have made dozens of these dropdowns on forms. For some reason I can't get this one to work.
Basically, I want to choose the name from the dropdown list, and have it populate the form DropDown.mdb
0
Comment
Question by:4charity
  • 4
  • 4
9 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 34113838
Your dropdown is an unbound control and there are no events.. If you just set the combo box control to InsuredName it will select the correct record.
DropDown.mdb
0
 

Author Comment

by:4charity
ID: 34113862
I changed the control source on the combo box to InsuredName. It chanes the insured name, but not the rest of the controls.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 34114089
Based on the limited textboxes on the form change the rowsource for the listbox to

SELECT i.InsuredName, d.InsuredPrimaryContact, d.InsuredEmail, d.InsuredPhone FROM tblInsuredName as i, tblInsureDetails as d WHERE i.InsuredID = d.InsuredID

So the query for the combo here returns the name and associated contact - run this query individually so you can see what it produces.

Ensure number of columns (property is ColumnCount) is set to 4 in the combo

then on the afterupdate you reference the value to the textboxes like this

Private Sub cboInsuredName_AfterUpdate()
    Me.InsuredName = cboInsuredName.Column(0)
    Me.InsuredPrimaryContact = cboInsuredName.Column(1)
    Me.InsuredEmail = cboInsuredName.Column(2)
    Me.InsuredPhone = cboInsuredName.Column(3)
End Sub


This will then take the values from the combo and populate the form
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:4charity
ID: 34114121
@rockiroads:

Could I also add a filter here, instead? If so, what would be the best way to do that?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 34114130
What would be better though if you wanted to add more columns and maybe use the id for other things is like this

The rowsource for the combo should be

SELECT InsuredID, InsuredName FROM tblInsuredName

Column count is 2, but column width is something like this  "0;2.54". Reason for this is we hide the first column, no need to show the id.

Then on the afterupdate event you have the id so you can read the table(s) and do whatever

Private Sub cboInsuredName_AfterUpdate()
    dim rs as dao.recordset

    set rs=currentdb.openrecordset("select * from tblInsureDetails where insuredid = " & cboInsuredName.value)
   
    Me.InsuredPrimaryContact = rs!InsuredPrimaryContact
    Me.InsuredEmail = rs!InsuredEmail
    Me.InsuredPhone = rs!InsuredPhone

    'etc

    rs.close
    set rs=nothing
End Sub
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34114178
>> Could I also add a filter here, instead? If so, what would be the best way to do that?

I am assuming filtering the combo? Well yes you can. What do you want to filter on? Take for example the name. You could introduce a textbox and a button over it
eg

txtFilter and cmdFilter

then on the click update the rowsource
eg

private sub cmdFilter_Click
    if trim(nz(Me.txtFilter,"")) = "" then
        msgbox "It helps if you enter something"
    else
        cboInsuredName.rowsource = "SELECT .... FROM ... WHERE InsuredName LIKE '*" & Me.txtFilter & "*'"
    end if
end sub


I left the query above like that as I do not know which approach you would like to take but essentially its the WHERE part

if the combo doesnt refesh add this after setting the rowsource

cboInsuredName.Requery


0
 

Author Comment

by:4charity
ID: 34114511
OK, not to overthink this, I looked back at my past posts, and came up with this one simple line of code:

Me.Filter = "[InsuredID]= " & Me.cboInsuredName
0
 

Author Closing Comment

by:4charity
ID: 34114517
OK, not to overthink this, I looked back at my past posts, and came up with this one simple line of code:

Me.Filter = "[InsuredID]= " & Me.cboInsuredName
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34116379
Ah, I thought it was the combo you wanted to filter and not the form.  Remember to use FilterOn to toggle between filtering and not
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

785 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