Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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

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
FinghtingMiao
Asked:
FinghtingMiao
  • 7
  • 5
  • 2
  • +1
4 Solutions
 
FinghtingMiaoAuthor Commented:
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
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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

0
 
FinghtingMiaoAuthor Commented:
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
 
dqmqCommented:
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
 
Rey Obrero (Capricorn1)Commented:
dqmq,
it is been taken cared of with post from http:#a36513429 
0
 
FinghtingMiaoAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:

Use the code  from http:#a36513429
0
 
FinghtingMiaoAuthor Commented:
Sorry, actually the solution works.

Thank you all!
0
 
Rey Obrero (Capricorn1)Commented:
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
 
dqmqCommented:
>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
 
Rey Obrero (Capricorn1)Commented:
np
0
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
in most cases the "Filter" does not work as expected.
0
 
FinghtingMiaoAuthor Commented:
Thank you ALL!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now