Re: find record on load

From: FrustratedAccessUser (FrustratedAccessUser_at_discussions.microsoft.com)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 13:49:06 -0700

OMG!!!!!! THANK YOU SO MUCH!!!!! You have no idea, I've been trying to
crack this code everyday since Friday. I'M SO RELIEVED!!!! For future
reference, and others who have my problem: I ended up splitting up the code,
I took Tom's advice and tacked on the criteria portion with the command
button on the subform with the OpenForm command. In the event of non
matching fields, it would create a new record. So in the load event of the
opened form, I tacked on a simple If statement testing to see if it was a new
record Form(FormName).NewRecord (evaluates to true or false). If it was,
I went ahead and had it fill in the appropriate fields. It works
wonderfully!!! I cannot thank you enough.

"Tom Ross" wrote:

> I think it would be much easier to set your filter for the second form as
> you call it from the main form.
> The button on the main form calling the secondform probably has a
> docmd.openform action. Add a criteria to that call
>
> strCriteria = "Project_Number ='" & Me.ProjectControlonMainForm & "'"
> DoCmd.OpenForm "SecondForm", , , strCriteria
>
> (this example assumes Project number is a text field and is delimited with
> single qoutes)
>
>
> "FrustratedAccessUser" <FrustratedAccessUser@discussions.microsoft.com>
> wrote in message news:B9C626E6-A5DC-4426-B21F-DA70C761C6EC@microsoft.com...
> > I have a main form (Plan Status) with a subform (z_Plan Status). On the
> > subform there is a command button that opens another form depending on the
> > Project Number field in the main form. The form that opens has the
> following
> > code in the on load event:
> >
> > Private Sub Form_Load()
> >
> > If Not IsNull(Forms("Plan Status").[ProjectNumber]) Then
> > If Me.Dirty Then 'Save before move.
> > Me.Dirty = False
> > End If
> >
> > strWhere = "[Project_Number] = " & Forms("Plan
> > Status").[ProjectNumber]
> > With Me.RecordsetClone
> > .FindFirst strWhere
> > If .NoMatch Then
> > DoCmd.GoToRecord , , acNewRec
> > Me.Project_Number = Forms("Plan Status").ProjectNumber
> > Else
> > Me.Bookmark = .Bookmark
> > End If
> > End With
> > End If
> >
> > End Sub
> >
> > What I'm trying to do is to have the form open to the record matching the
> > project number field. And if there isn't a matching record already, I
> would
> > like it to create one and fill in a field on the record. The problem with
> > this code, is it assumes that there is never a match and always puts in a
> new
> > record (the new record command works exactly how I want it to). Any ideas
> on
> > the find record portion?
>
>
>