?
Solved

Create A Form in Access that controls the output of another form

Posted on 2006-05-04
9
Medium Priority
?
293 Views
Last Modified: 2011-09-20
I'm trying to recreate a form that uses a drop down list. When and item in the list is selected, I would like it to open a specific form.

For Example. When the first form opens, the user would see a drop list with 4 Values. When Value 1 is selected from the list, it should open a form that contains the correspoding data for Value 1. The Corresponding data in the Value 1 form will display an OLE Object.

I hope this is clear. Please let me know if you have further questions.
0
Comment
Question by:dlapinsk
6 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16612033
Say you have txtCustName, txtCity and few more on your 1st form.  You may type values in it or fill them by via a combo box.  Later on, you could even do partial searches if you choose to use text boxes.
----------
Open your second form in design view and under [CustName] field, in the criteria box have:

IFF(fnCustName()="<All>",[CustName],fnCustName())

Above code would allow you to enter all four text boxes or leave some blank.  When it is left blank, function fnCustName(0 is to return "<All>" string therefore field value [CustName] will be taken.  What this means, the criteria gets ignored as if it wasn't there at first place.  However, it the value of function fnCustName() was some valid string, it will filter the record source accordingly.

For each text box (criteria) you will have a function.  These functions need to be placed in a Standard module, under module tab.  Please do one criteria at a time.  After you handled 1st one, add your second one.

Public Function fnCustName()As String  'might be different for other criteria
Dim strTemp As String
strTemp = Nz(Forms!Form1!txtCustName,"<All>")
Msgbox strTemp & " <-- remove this message after a test"
fnCustName=strTemp
End Function

mike

For partial search, add a new question later on.
0
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 16612061
If you want to use a combo box, you can use this in the after update event:

Private Sub Combo1_AfterUpdate()
Dim Val, Val1, Val2, Val3 As String
Val = Me.Combo1.Value
Val1 = "Value 1"
Val2 = "Value 2"
Val3 = "Value 3"

    Select Case Val    ' Evaluate Number.
      Case Val1 ' Opens first form
        DoCmd.OpenForm "frmForm1", acNormal
     
      Case Val2 ' Opens second form
        DoCmd.OpenForm "frmForm2", acNormal
     
     Case Val3 ' Opens third form
        DoCmd.OpenForm "frmForm3", acNormal
     
     Case Else    ' Opens forth form
        DoCmd.OpenForm "frmForm4", acNormal
    End Select


End Sub

Flyster
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 16612823
I read somewhere that this:

Dim Val, Val1, Val2, Val3 As String

creates val, val1 and val2 as variants not strings....
only val3 is a string.

so
dim val1 as string
dim val2 as string
dim val as string is required if you want them all to be strings.

in this case i doubt it makes a diference but i am feeling nosey and pedantic today.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dlapinsk
ID: 16617724
Flyster

I tried using the code you recommended, but the event doesn't trigger.

Here is the code I input into the procedure.

Private Sub Combo7_AfterUpdate()
Dim Val, Val1, Val2, Val3 As String
Val = Me.Combo7.Value
Val1 = "Fundraising"
Val2 = "Marketing"
Val3 = "Student Health Center"
Val4 = "Clinical Training"

    Select Case Val    ' Evaluate Number.
      Case Val1 ' Opens first form
        DoCmd.OpenForm "Impact on Fundraising and Marketing", acNormal
     
      Case Val2 ' Opens second form
        DoCmd.OpenForm "Impact on Fundraising and Marketing", acNormal
     
     Case Val3 ' Opens third form
        DoCmd.OpenForm "Impact on Student Health Centers", acNormal
     
     Case Val4    ' Opens forth form
        DoCmd.OpenForm "Impact on Student Health Centers", acNormal
    End Select

End Sub

Please let me know your thoughts. Thanks! David
0
 
LVL 22

Expert Comment

by:Flyster
ID: 16621622
Dave, the code looks good to me. I tried it on my machine and it worked fine. Are you sure you have the form names entered exactly? Here's something else you can try. Open the form in design view. Do a Alt+F11. Go to the line that has “Private Sub Combo7_AfterUpdate()” and left click in the column just left of “Private”. This should put a dot in the column and highlight the line. Now open your form and make a selection. The visual basic editor should open and the top line will be highlighted yellow. Press the F8 key until the highlight reaches “Select Case Val”. Do a mouseover on “Val” and see what the value is. (A smart tag should open up when you do this) It should match EXACTLY to Val 1,2,3 or 4. If not. Nothing will happen.  Note: When your done, be sure to remove the "dot", or the editor will open every time you select the combo box.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 16883694
i'd be interested in flyster responding to my coment about multiple declarations as variants until explicit declaration,

but my contribution does not, in my opinion, merit any points here.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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