Filtering One form from 2 fields of another

  • Thread starter Thread starter Chip
  • Start date Start date
C

Chip

Howdy Again from Oklahoma!

I am really new to ACCESS and VB and I am having a problem opening the form
"frm_SavAction1" from an open form "frm_SAV" and filtering by 2 fields, one
field is on the form and the other field "savtext" is declared in the VB
script. I have had 2 other friends trying to help me but all i get is
confused and further from what i need.

Below is the script from the Event. I was told by a freind that a Run-time
error '2501' is received when you go to a form that the table does not have
an instance of a record with the criteria of the filter (Basically, I was
told that if the filter is trying to create a new record with the filter
criteria then you receive the error "2501" and that i needed to "Handle"
this error with the script to give a new record instead of the error
message) I am not sure my friends are pointing in the correct direction so
i thought i would ask the experts for help. There may be an easier way
and/or my friends may be completely wrong!!!

PLEASE HELP IF POSSIBLE, I NEED TO HAVE THIS FINISHED BY MONDAY 4 APRIL
2005 OR I MAY BE LOOKING FOR ANOTHER JOB...

THANKS IN ADVANCE!!

Here is the script i am using that does not work:

Private Sub Frame277_Click()
On Error GoTo Err_Frame277_Click

If Me.Frame277 = "2" Then

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String
Dim savtext As String
stDocName = "frm_SavAction1"
savtext = "Facility does not have Asbestos Survey and Abatement
Records"
stLinkCriteria = "([SAVDesc]=" & "'" & [savtext] & "') and ([savid] =" &
"'" & Me!SAVNUM & "')"
DoCmd.OpenForm stDocName, , , stLinkCriteria


End If


Exit Sub

Err_Frame277_Click:
If Err.Number = 2501 Then
MsgBox "I have entered the error handling."
DoCmd.OpenForm stDocName, , , stLinkCriteria2
DoCmd.GoToRecord , , acNewRec
Forms![frm_SAVaction1]![SAVDesc] = savtext
Forms![frm_SAVaction1]![SAVID] = Me!SAVNUM


Else

MsgBox Err.Description
Resume Exit_frame277_Click

End If
Exit_frame277_Click:
End Sub
 
Chip,

Although not absolutely clear what you are trying to achieve, I can see
a number of problems - or potential problems - with your code. To begin
with, the error handling should be embedded in the main procedure, not
be a separate procedure on its own; the way your code is, the goto
statement will look for a label that just isn't there, rather than call
a separate procedure, like you seem to expect. Even if the call was
successful, stDocName and savtext wouldn't have a value in
Err_Frame277_Click, as they are locally declared in Frame277_Click, and
don't "live" outside of it.
Furthermore, you are treating al of your fields/controls as text,
whereas I suspect some of them are numeric; for instance, If Me.Frame277
= "2" Then suggests Frame277 returns 2 as a text string, while the test
value of 2 suggests it is probably numeric. If it is indeed numeric, the
expression should be:
If Me.Frame277 = 2 Then
i.e. no quotes around the value; quotes denote text strings.
Likewise, I assume SAVNUM is also numeric.
Also, in the construction of strLinkCriteria, the variables should be
left outside the quotes, so VBA recognises them as such; having them
within the quotes makes VBA think they are just part of a text string.
That said, I shall attempt to give you a version of the code that might
work, if my assumptions are correct:

Private Sub Frame277_Click()
Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String
Dim savtext As String

On Error GoTo Err_Frame277_Click

If Me.Frame277 = 2 Then
stDocName = "frm_SavAction1"
savtext = "Facility does not have Asbestos Survey and Abatement
Records"
stLinkCriteria = "([SAVDesc]='" & savtext & "') And ([savid] =" &
Me!SAVNUM & ")"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit Sub

Err_Frame277_Click:
If Err.Number = 2501 Then
MsgBox "I have entered the error handling."
DoCmd.OpenForm stDocName, , , stLinkCriteria2
DoCmd.GoToRecord , , acNewRec
Forms![frm_SAVaction1]![SAVDesc] = savtext
Forms![frm_SAVaction1]![SAVID] = Me!SAVNUM
Else
MsgBox Err.Description
End If
End Sub

I said "might", because I'm not sure what exactly you are trying to
achieve... a couple of things to note¨
If Frame277 is different to 2, then nothing will happen. Is this what
you intended?
If error handler cicks in and the error is indeed 2501, the form will
open without any filtering, since strLinkCriteria2 is not assigned a
value; if this was intended, then why bother with it at all?

HTH,
Nikos


Howdy Again from Oklahoma!

I am really new to ACCESS and VB and I am having a problem opening the form
"frm_SavAction1" from an open form "frm_SAV" and filtering by 2 fields, one
field is on the form and the other field "savtext" is declared in the VB
script. I have had 2 other friends trying to help me but all i get is
confused and further from what i need.

Below is the script from the Event. I was told by a freind that a Run-time
error '2501' is received when you go to a form that the table does not have
an instance of a record with the criteria of the filter (Basically, I was
told that if the filter is trying to create a new record with the filter
criteria then you receive the error "2501" and that i needed to "Handle"
this error with the script to give a new record instead of the error
message) I am not sure my friends are pointing in the correct direction so
i thought i would ask the experts for help. There may be an easier way
and/or my friends may be completely wrong!!!

PLEASE HELP IF POSSIBLE, I NEED TO HAVE THIS FINISHED BY MONDAY 4 APRIL
2005 OR I MAY BE LOOKING FOR ANOTHER JOB...

THANKS IN ADVANCE!!

Here is the script i am using that does not work:

Private Sub Frame277_Click()
On Error GoTo Err_Frame277_Click

If Me.Frame277 = "2" Then

Dim stDocName As String
Dim stLinkCriteria As String
Dim stLinkCriteria2 As String
Dim savtext As String
stDocName = "frm_SavAction1"
savtext = "Facility does not have Asbestos Survey and Abatement
Records"
stLinkCriteria = "([SAVDesc]=" & "'" & [savtext] & "') and ([savid] =" &
"'" & Me!SAVNUM & "')"
DoCmd.OpenForm stDocName, , , stLinkCriteria


End If


Exit Sub

Err_Frame277_Click:
If Err.Number = 2501 Then
MsgBox "I have entered the error handling."
DoCmd.OpenForm stDocName, , , stLinkCriteria2
DoCmd.GoToRecord , , acNewRec
Forms![frm_SAVaction1]![SAVDesc] = savtext
Forms![frm_SAVaction1]![SAVID] = Me!SAVNUM


Else

MsgBox Err.Description
Resume Exit_frame277_Click

End If
Exit_frame277_Click:
End Sub
Click to expand...
 
Back
Top