Running Access 2003 and trying to open a form with the following:

If Len([cboEmployee] & "") <> 0 And IsNull(Me.FromDate) And
IsNull(Me.ToDate) Then
strWhere = "EmployeeID =" & Employee
DoCmd.OpenForm "frmReflection", , , strWhere
End If

And the form opens to a blank record when the combo box has a value.

Even this does the same thing:

If Not IsNull(me.cboEmployee And IsNull(Me.FromDate) And
IsNull(Me.ToDate) Then
strWhere = "EmployeeID =" & Employee
DoCmd.OpenForm "frmReflection", , , strWhere
End If

What am I missing... I think I have been staring at this too
long..... :-(

On Thu, 10 Nov 2011 14:02:18 -0800 (PST), Opal <(E-Mail Removed)> wrote:

>Running Access 2003 and trying to open a form with the following:
>
> If Len([cboEmployee] & "") <> 0 And IsNull(Me.FromDate) And
>IsNull(Me.ToDate) Then
> strWhere = "EmployeeID =" & Employee
> DoCmd.OpenForm "frmReflection", , , strWhere
> End If
>
>And the form opens to a blank record when the combo box has a value.
>
>Even this does the same thing:
>
> If Not IsNull(me.cboEmployee And IsNull(Me.FromDate) And
>IsNull(Me.ToDate) Then
> strWhere = "EmployeeID =" & Employee
> DoCmd.OpenForm "frmReflection", , , strWhere
> End If
>
>What am I missing... I think I have been staring at this too
>long..... :-(

What's the datatype of EmployeeID? What are the RowSource and the Bound Column
of the combo box Employee? If you put a breakpoint in your code (mouseclick
the grey bar to the left of the DoCmd.OpenForm line) what is the value of
strWhere?

My guess is that either EmployeeID is a Text field requiring quotemarks:

strWhere = "[EmployeeID] = '" & Me.Employee & "'"

or the combo box isn't returning the expected value.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Nov 10, 5:23*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Thu, 10 Nov 2011 14:02:18 -0800 (PST), Opal <tmwel...@hotmail.com> wrote:
> >Running Access 2003 and trying to open a form with the following:
>
> > * *If Len([cboEmployee] & "") <> 0 And IsNull(Me.FromDate) And
> >IsNull(Me.ToDate) Then
> > * * * *strWhere = "EmployeeID =" & Employee
> > * * * *DoCmd.OpenForm "frmReflection", , , strWhere
> > * * * *End If
>
> >And the form opens to a blank record when the combo box has a value.
>
> >Even this does the same thing:
>
> > * *If Not IsNull(me.cboEmployee And IsNull(Me.FromDate) And
> >IsNull(Me.ToDate) Then
> > * * * *strWhere = "EmployeeID =" & Employee
> > * * * *DoCmd.OpenForm "frmReflection", , , strWhere
> > * * * *End If
>
> >What am I missing... I think I have been staring at this too
> >long..... *:-(
>
> What's the datatype of EmployeeID? What are the RowSource and the Bound Column
> of the combo box Employee? If you put a breakpoint in your code (mouseclick
> the grey bar to the left of the DoCmd.OpenForm line) what is the value of
> strWhere?
>
> My guess is that either EmployeeID is a Text field requiring quotemarks:
>
> strWhere = "[EmployeeID] = '" & Me.Employee & "'"
>
> or the combo box isn't returning the expected value.
>
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com

The EmployeeID is a number data type. The combo box has a row source
pointing to the employee table.
There are two columns but the bound column is the primary key which is
autonumber.

I will have to check the breakpoint in the morning. Thanks for your
prompt reply.

On Thu, 10 Nov 2011 16:00:38 -0800 (PST), Opal <(E-Mail Removed)> wrote:

>The EmployeeID is a number data type. The combo box has a row source
>pointing to the employee table.
>There are two columns but the bound column is the primary key which is
>autonumber.

Well, that should be ok then... the other question is, what's the Recordsource
of the report you're opening? It does include the EmployeeID I presume? If you
open the query as a datasheet, does it contain the selected ID (or are there
other criteria or filters on the form that might be excluding it)?

And... following Sherlock Holmes's advice to never ignore the obvious solution
- is the Form's DataEntry property perhaps set to Yes? If so you'll only see
the blank new record, not any existing ones.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Nov 10, 7:44*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Thu, 10 Nov 2011 16:00:38 -0800 (PST), Opal <tmwel...@hotmail.com> wrote:
> >The EmployeeID is a number data type. *The combo box has a row source
> >pointing to the employee table.
> >There are two columns but the bound column is the primary key which is
> >autonumber.
>
> Well, that should be ok then... the other question is, what's the Recordsource
> of the report you're opening? It does include the EmployeeID I presume? If you
> open the query as a datasheet, does it contain the selected ID (or are there
> other criteria or filters on the form that might be excluding it)?
>
> And... following Sherlock Holmes's advice to never ignore the obvious solution
> - is the Form's DataEntry property perhaps set to Yes? If so you'll only see
> the blank new record, not any existing ones.
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com

Thanks! John.... your questions helped me troubleshoot this better
and
I found the problem. I was a little overzealous with some coding and
it was confusing either the on open or on load events. Either way,
I found my problem and all is good. Its good to just have a sounding
board once in a while when you get stuck and can't see the forest for
the trees :-)