Trouble with the following

O

Opal

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..... :-(
 
J

John W. Vinson

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
O

Opal

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/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/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.
 
J

John W. Vinson

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
O

Opal

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/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top