Solved

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

Posted on 2011-09-09
16
323 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
[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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

751 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