AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
I am new to access and was wondering if you could help me with this problem.
I creating a form. I have a option group and combo box where the option group includes a 'Fund' and 'Index'. If the user selects 'Fund,' I want the combo box to give Fund1, Fund2 etc. If the user selects 'Index,' I want the same combo box to give Index1, Index2 etc.
I am not sure if I am meant to be using a code or VBA module.
Any help will be much appreciated.
Cheers
ANSWER: Code is contained in a VBA module, so they are, essentially, the same thing. And you do need to use VBA here.
What you need are lookup tables for funds and indexes. Then the Rowsource of your combobox might look like this:
SELECT FundID, FundName from tluFund ORDER BY Fundname;
or
SELECT IndexID, IndexName from tluIndex ORDER BY IndexName;
You then put code in the After Update event of your option group like so:
IF Me.optFundIndex = 1 Then
Me.cboFundIndex.RowSource = "SELECT FundID, FundName from tluFund ORDER BY Fundname;"
Else
Me,cboFundIndex.RowSource = "SELECT IndexID, IndexName from tluIndex ORDER BY IndexName;"
End If
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
---------- FOLLOW-UP ----------
QUESTION: Hi Scott,
Thanks a lot, I was able to create the combo box using your code. The combo captures the 'fund' or 'index' names.
I have two further combo boxes specifying the 'start date' and 'end date.'
I have two questions:
a. How can I access the values entered on the form such as fundname, start date etc.
b. I need to use these fields to get a average price from a index or fund table between the start and end dates. Is there any function that will help me compute an average from selected records.
Once again thanks for the help above.
Answer You can reference a value entered into a control on a form using the syntax:
=Forms!formname!controlname
You can create a group by query that returns an average of value within a date range.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
