Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • 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
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.

 
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

Featured Post

Technology Partners: 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!

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