FindNext Error Message

[DoveArrow]

I have a form with two combo boxes, that I'm using to search for
records in a particular table that is tied to a second form. In order
to find the appropriate record, I need to search in two different
fields: Program, and Catalog Year. The following is the code that I've
been using to accomplish just that.

str = Me.Program
DoCmd.OpenForm "frmProgramRequirements"
Forms!frmProgramRequirements!Program.SetFocus
DoCmd.FindRecord str
Do Until Forms!frmProgramRequirements!CatalogYear = Me.CatalogYear
DoCmd.FindNext
Loop
DoCmd.Close acForm, "fselProgramCatalogYear"

Now this works fine the first four or five times. However, on the
fifth or sixth try, the program goes haywire, looping infinitely until
it runs out of memory. Then, every time after that, I get any of three
error messages:

Run-time error '3709': The search key was not found in any record.
(Happens when the second form isn't already open.)
Run-time error '3021': No current record. (Happens when I simply try
to run the sequence again without changing the program or catalog year
and the second form is open.)
Run-time error '2465': Microsoft Office Access can't find the field
'|' referred to in your expression. (Happens when I change the program
and catalog year and the second form is open.)

I am utterly perplexed as to why this is happening, so any insight
that someone might be able to provide would be appreciated. Thanks.

 

[Allen Browne]

Provided the target form is not already open, you could open it filtered to
just the record you want:

Dim strWhere As String
strWhere = "(Program = """ & Me.[Program] & _
""") AND (CatalogYear = " & Me.catalogYear & ")"
DoCmd.OpenForm "frmProgramRequirements", WhereConditon:=strWhere

 

[DoveArrow]

Provided the target form is not already open, you could open it filtered to
just the record you want:

Dim strWhere As String
strWhere = "(Program = """ & Me.[Program] & _
    """) AND (CatalogYear = " & Me.catalogYear & ")"
DoCmd.OpenForm "frmProgramRequirements", WhereConditon:=strWhere

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a form with two combo boxes, that I'm using to search for
records in a particular table that is tied to a second form. In order
to find the appropriate record, I need to search in two different
fields: Program, and Catalog Year. The following is the code that I've
been using to accomplish just that.

   str = Me.Program
   DoCmd.OpenForm "frmProgramRequirements"
   Forms!frmProgramRequirements!Program.SetFocus
   DoCmd.FindRecord str
   Do Until Forms!frmProgramRequirements!CatalogYear = Me.CatalogYear
       DoCmd.FindNext
   Loop
   DoCmd.Close acForm, "fselProgramCatalogYear"

Now this works fine the first four or five times. However, on the
fifth or sixth try, the program goes haywire, looping infinitely until
it runs out of memory. Then, every time after that, I get any of three
error messages:

Run-time error '3709': The search key was not found in any record.
(Happens when the second form isn't already open.)
Run-time error '3021': No current record. (Happens when I simply try
to run the sequence again without changing the program or catalog year
and the second form is open.)
Run-time error '2465': Microsoft Office Access can't find the field
'|' referred to in your expression. (Happens when I change the program
and catalog year and the second form is open.)

I am utterly perplexed as to why this is happening, so any insight
that someone might be able to provide would be appreciated. Thanks.- Hide quoted text -

- Show quoted text -

Now why do you have to go and make it all simple? ;-)

Seriously, though, thank you so much for your help. It now works like
a charm.