Filter a specific value

  • Thread starter Thread starter malindra
  • Start date Start date
M

malindra

Could anybody help me to check what's wrong with my code.
what i'm trying to do is i created a button on form#1--
product category field. When I double click on the field
it will open form #2 which have following code under
Activate. My goal is I want to be able to open the same
field as what I have on form#1.


Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
If Forms![tbl_recruitment]!
[InterviewTransSubform].Form.RecordsetClone.RecordCount >
0 Then
DoCmd.GoToControl "InterviewerName"
DoCmd.FindRecord Forms![tbl_recruitment]!
[InterviewTransSubform]!Form.[InterviewerName]
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub

Thanks in advance

Malindra
 
Hi Malindra

Do you want your second form to show *only* the record corresponding to the
interviewer on Form 1, or do you want it to show all records, with the
current record initially set to the matching one?

If you want to show only the one record, then pass a WhereCondition argument
to OpenForm:
Dim strFilter as String
strFilter = "InterviewerName=""" & Me.InterviewerName & """"
DoCmd.OpenForm "frmSecondForm", WhereCondition := strFilter

If you want to just set the initial record, then pass your filter string as
the OpenArgs argument:
DoCmd.OpenForm "frmSecondForm", OpenArgs := strFilter

Then, in your second form's Load event, navigate to the required record:
If Len(Me.OpenArgs)>0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
 
I modified the code little bit and finally it works good,
but I'm still working on the case that user send a Null
value from form#1. What should I do on this case

--It generate an erro#3077

Thanks,
Malindra
-----Original Message-----
Hi Malindra

Do you want your second form to show *only* the record corresponding to the
interviewer on Form 1, or do you want it to show all records, with the
current record initially set to the matching one?

If you want to show only the one record, then pass a WhereCondition argument
to OpenForm:
Dim strFilter as String
strFilter = "InterviewerName=""" & Me.InterviewerName & """"
DoCmd.OpenForm "frmSecondForm", WhereCondition := strFilter

If you want to just set the initial record, then pass your filter string as
the OpenArgs argument:
DoCmd.OpenForm "frmSecondForm", OpenArgs := strFilter

Then, in your second form's Load event, navigate to the required record:
If Len(Me.OpenArgs)>0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Could anybody help me to check what's wrong with my code.
what i'm trying to do is i created a button on form#1--
product category field. When I double click on the field
it will open form #2 which have following code under
Activate. My goal is I want to be able to open the same
field as what I have on form#1.


Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
If Forms![tbl_recruitment]!
[InterviewTransSubform].Form.RecordsetClone.RecordCount >
0 Then
DoCmd.GoToControl "InterviewerName"
DoCmd.FindRecord Forms![tbl_recruitment]!
[InterviewTransSubform]!Form.[InterviewerName]
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub

Thanks in advance

Malindra
Click to expand...


.
Click to expand...
 
That depends on what you want to do when InterviewerName is null. Do you
want to (a) give an error message (b) open the form showing all records (c)
open the form at a blank record?

All this can be controlled in the code which opens the form. For example:

If IsNull(Me.InterviewerName) Then
MsgBox "No interviewer"=
Else
strFilter = ...
DoCmd.OpenForm ...
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Malindra said:
I modified the code little bit and finally it works good,
but I'm still working on the case that user send a Null
value from form#1. What should I do on this case

--It generate an erro#3077

Thanks,
Malindra
-----Original Message-----
Hi Malindra

Do you want your second form to show *only* the record corresponding to the
interviewer on Form 1, or do you want it to show all records, with the
current record initially set to the matching one?

If you want to show only the one record, then pass a WhereCondition argument
to OpenForm:
Dim strFilter as String
strFilter = "InterviewerName=""" & Me.InterviewerName & """"
DoCmd.OpenForm "frmSecondForm", WhereCondition := strFilter

If you want to just set the initial record, then pass your filter string as
the OpenArgs argument:
DoCmd.OpenForm "frmSecondForm", OpenArgs := strFilter

Then, in your second form's Load event, navigate to the required record:
If Len(Me.OpenArgs)>0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Could anybody help me to check what's wrong with my code.
what i'm trying to do is i created a button on form#1--
product category field. When I double click on the field
it will open form #2 which have following code under
Activate. My goal is I want to be able to open the same
field as what I have on form#1.


Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
If Forms![tbl_recruitment]!
[InterviewTransSubform].Form.RecordsetClone.RecordCount >
0 Then
DoCmd.GoToControl "InterviewerName"
DoCmd.FindRecord Forms![tbl_recruitment]!
[InterviewTransSubform]!Form.[InterviewerName]
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub

Thanks in advance

Malindra
Click to expand...


.
Click to expand...
Click to expand...
 
This is the code that I have:

If Not IsNull(Me.cmb1) Then
strFilter = "[InterviewerID]=" & Me![cmb1]
DoCmd.OpenForm "frminterviewers", , , , , acDialog,
strFilter
Me![cmb1].Requery
Else
DoCmd.OpenForm "frminterviewers", , , , ,
acDialog, "gotonew"
DoCmd.GoToRecord , , acNewRec
Me![cmb1].Requery
End If

cmb1: combo box that store InterviewerID

In this case if is null it's supposed to open the second
form and go to new record, but Since we have the code
OnLoad on the second form. It give me an error instead.

and following code is the code under Onload on my second
form:

If Me.OpenArgs = "GotoNew" And IsNull([InterviewerID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If

If Not IsNull(Me.OpenArgs) Then
DoCmd.GoToRecord , , acNewRec

If Len(Me.OpenArgs) > 0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

End If
End If

Please advise,
Malindra
-----Original Message-----
That depends on what you want to do when InterviewerName is null. Do you
want to (a) give an error message (b) open the form showing all records (c)
open the form at a blank record?

All this can be controlled in the code which opens the form. For example:

If IsNull(Me.InterviewerName) Then
MsgBox "No interviewer"=
Else
strFilter = ...
DoCmd.OpenForm ...
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I modified the code little bit and finally it works good,
but I'm still working on the case that user send a Null
value from form#1. What should I do on this case

--It generate an erro#3077

Thanks,
Malindra
-----Original Message-----
Hi Malindra

Do you want your second form to show *only* the record corresponding to the
interviewer on Form 1, or do you want it to show all records, with the
current record initially set to the matching one?

If you want to show only the one record, then pass a WhereCondition argument
to OpenForm:
Dim strFilter as String
strFilter = "InterviewerName=""" & Me.InterviewerName & """"
DoCmd.OpenForm "frmSecondForm", WhereCondition := strFilter

If you want to just set the initial record, then pass your filter string as
the OpenArgs argument:
DoCmd.OpenForm "frmSecondForm", OpenArgs := strFilter

Then, in your second form's Load event, navigate to the required record:
If Len(Me.OpenArgs)>0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Could anybody help me to check what's wrong with my code.
what i'm trying to do is i created a button on form#1--
product category field. When I double click on the field
it will open form #2 which have following code under
Activate. My goal is I want to be able to open the same
field as what I have on form#1.


Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
If Forms![tbl_recruitment]!
[InterviewTransSubform].Form.RecordsetClone.RecordCount
Click to expand...
0 Then
DoCmd.GoToControl "InterviewerName"
DoCmd.FindRecord Forms![tbl_recruitment]!
[InterviewTransSubform]!Form.[InterviewerName]
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub

Thanks in advance

Malindra


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Malindra

Do you really need to open the second form showing all records? If not,
pass strFilter as the WhereCondition (4th argument) and specify DataMode :=
acAdd (5th argument) if the combo is null. Then your Form_Load procedure
and OpenArgs will not be needed at all.

If you want to continue this way, use this code for Form_Load:

If Len(Me.OpenArgs) > 0 Then
If Me.OpenArgs = "gotonew" Then
DoCmd.GoToRecord , , acNewRec
Else
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Malindra said:
This is the code that I have:

If Not IsNull(Me.cmb1) Then
strFilter = "[InterviewerID]=" & Me![cmb1]
DoCmd.OpenForm "frminterviewers", , , , , acDialog,
strFilter
Me![cmb1].Requery
Else
DoCmd.OpenForm "frminterviewers", , , , ,
acDialog, "gotonew"
DoCmd.GoToRecord , , acNewRec
Me![cmb1].Requery
End If

cmb1: combo box that store InterviewerID

In this case if is null it's supposed to open the second
form and go to new record, but Since we have the code
OnLoad on the second form. It give me an error instead.

and following code is the code under Onload on my second
form:

If Me.OpenArgs = "GotoNew" And IsNull([InterviewerID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If

If Not IsNull(Me.OpenArgs) Then
DoCmd.GoToRecord , , acNewRec

If Len(Me.OpenArgs) > 0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else

End If
End If

Please advise,
Malindra
-----Original Message-----
That depends on what you want to do when InterviewerName is null. Do you
want to (a) give an error message (b) open the form showing all records (c)
open the form at a blank record?

All this can be controlled in the code which opens the form. For example:

If IsNull(Me.InterviewerName) Then
MsgBox "No interviewer"=
Else
strFilter = ...
DoCmd.OpenForm ...
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I modified the code little bit and finally it works good,
but I'm still working on the case that user send a Null
value from form#1. What should I do on this case

--It generate an erro#3077

Thanks,
Malindra

-----Original Message-----
Hi Malindra

Do you want your second form to show *only* the record
corresponding to the
interviewer on Form 1, or do you want it to show all
records, with the
current record initially set to the matching one?

If you want to show only the one record, then pass a
WhereCondition argument
to OpenForm:
Dim strFilter as String
strFilter = "InterviewerName=""" &
Me.InterviewerName & """"
DoCmd.OpenForm "frmSecondForm", WhereCondition :=
strFilter

If you want to just set the initial record, then pass
your filter string as
the OpenArgs argument:
DoCmd.OpenForm "frmSecondForm", OpenArgs := strFilter

Then, in your second form's Load event, navigate to the
required record:
If Len(Me.OpenArgs)>0 Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


in message
Could anybody help me to check what's wrong with my
code.
what i'm trying to do is i created a button on form#1--
product category field. When I double click on the
field
it will open form #2 which have following code under
Activate. My goal is I want to be able to open the same
field as what I have on form#1.


Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
If Forms![tbl_recruitment]!

[InterviewTransSubform].Form.RecordsetClone.RecordCount
Click to expand...
0 Then
DoCmd.GoToControl "InterviewerName"
DoCmd.FindRecord Forms![tbl_recruitment]!
[InterviewTransSubform]!Form.[InterviewerName]
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub

Thanks in advance

Malindra


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top