Solved

How to filter a From by passing a value from another form

Posted on 2011-09-09
16
320 Views
Last Modified: 2012-05-12
I have 2 forms
Form 1: [frm_DE_VacancyDatabase] and Form2: [frm_CY_NY_Change].
Now Form 2 has 2000 records, but I want to filter it our by the [id] passed from Form 1.
So I did the code on Form2.Open event. But seems do not work, still shows 2000+ records.

I attached my code (form open and form load), please help, thanks.


Private Sub Form_Load()
On Error GoTo Err_Form_Load
'Me.ServerFilterByForm = filterCondtition
Me.ServerFilter = filterCondtition
Me.Refresh

Forms![SwitchBoard]![PropertyNumber].Caption = Me.[Prop #]
'Forms![SwitchBoard]![Unit].Caption = Me.Unit
Me.Repaint

Exit_Form_Load:
    If (closeForm) Then
        DoCmd.Close acForm, "frm_CY_NY_Change", acSaveNo
    End If
    Exit Sub

Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Load
 End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
' This occurs BEFORE Form_Load() event handler
  closeForm = False
  filterCondtition = "[ID]=" & Forms![frm_DE_VacancyDatabase].[ID]
  Me.Repaint
  MsgBox "form id is" + (CStr(Forms![frm_DE_VacancyDatabase].[ID]))
  
  
Exit_Form_Open:
    closeForm = True
    Exit Sub
    
Err_Form_Open:
MsgBox "Following error occurred trying to open form:" + vbCrLf + vbCrLf + Err.Description
Resume Exit_Form_Open
End Sub

Open in new window

0
Comment
Question by:FinghtingMiao
  • 7
  • 5
  • 2
  • +1
16 Comments
 

Author Comment

by:FinghtingMiao
ID: 36513390
Oh, Form 1 and Form 2 use different record srouce but both of source (tables) have the same column called [ID], which need to be passed across forms.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 36513416
How are you opening form2 from Form1?  The OpenForm method includes an optional Filter parameter.  Assuming you have a command button "cmd_OpenForm2" on form one, the click event of that would look something like:

Private Sub cmd_OpenForm2_Click

Docmd.Openform "Form2", acNormal, "[ID] = " & me.ID

End Sub

If [ID] is text, it would look like:

Docmd.Openform "Form2", acNormal, "[ID] = """ & me.ID & """"

or

Docmd.Openform "Form2", acNormal, "[ID] = " & chr$(34) & me.ID & chr$(34)

0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 36513429
open form2 with a criteria


from form [frm_DE_VacancyDatabase]  


docmd.openform "frm_CY_NY_Change",,,"ID=" & me.ID

if ID is text use this

docmd.openform "frm_CY_NY_Change",,,"ID='" & me.ID & "'"




0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36513433
it is better to use the Where condition instead of the Filter

0
 

Author Comment

by:FinghtingMiao
ID: 36513475
fyed and capricorn1

Yes, I am open form 2 from form 1 with a open botton.
I am going to try the solution ur provided, thanks.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36513477
Where's your code that opens the second form?  I'd use the WHERE condition in the 4th argument of the OpenForm command or macro.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36513493
dqmq,
it is been taken cared of with post from http:#a36513429 
0
 

Author Comment

by:FinghtingMiao
ID: 36513582
I have tried the solution.
On the form 1-> Openform 2 button, I have the click event with the code I attached, but still does not work.
Is anything wrong with my code?
Private Sub CY_NY_Change_Click()

Dim stDocName As String
Dim stLinkCriteria As String
    
stDocName = "frm_CY_NY_Change"
DoCmd.OpenForm stDocName, acNormal, "[ID] = " & Me.ID, stLinkCriteria, acFormEdit, acDialog
'DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog

Exit_CY_NY_Change_Click:
    Exit Sub

Err_CY_NY_Change_Click:
    MsgBox Err.Description
    Resume Exit_CY_NY_Change_Click
    
End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36513645

Use the code  from http:#a36513429
0
 

Author Comment

by:FinghtingMiao
ID: 36513657
Sorry, actually the solution works.

Thank you all!
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 36513662
Private Sub CY_NY_Change_Click()

Dim stDocName As String
Dim stLinkCriteria As String
    
stDocName = "frm_CY_NY_Change"

DoCmd.OpenForm stDocName, acNormal,, "[ID] = " & Me.ID,  acFormEdit, acDialog


Exit_CY_NY_Change_Click:
    Exit Sub

Err_CY_NY_Change_Click:
    MsgBox Err.Description
    Resume Exit_CY_NY_Change_Click
    
End Sub

Open in new window




0
 
LVL 42

Expert Comment

by:dqmq
ID: 36513805
>it is been taken cared of with post from http:#a36513429

Sorry, I don't intend to waste anyone's time giving duplicate answers.  It's just that when I looked at the question, it had been sitting there for about 20 minutes, unanswered.  It seems that in the minute or two it took me to type in a 1 sentence answer and press submit, 30 minutes of time on the EE clock had transpired.  Occasionally, EE seems to exist in a time warp.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36513875
np
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 36514452
Capricorn said "it is better to use the Where condition instead of the Filter"

Generally that is what I do as well, but that limits the results of the OpenForm to only those records which meet the criteria.  If the OP wants to be able to "unfilter" the recordset, he/she can if the "FilterCondition" is used instead of the "WhereCondition".

I've never tried using both a Filter and a Where condition, as the OP did in #36513582

Don't forget to award points.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36516073
in most cases the "Filter" does not work as expected.
0
 

Author Closing Comment

by:FinghtingMiao
ID: 36523250
Thank you ALL!
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 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