Solved

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

Posted on 2011-09-09
16
316 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
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)

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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