Open form to a particular record

[biganthony via AccessMonster.com]

Hi,

I have a continuous form listing some employees. One of the fields displayed
is the EmployeeID field. Is it possible to double-click a particular
EmployeeID field on the continuous form and another form (called
"frmEmployees") open and display the record for the employee that I double
clicked?

I tried the following code and the frmEmployee form opened but at the first
record, not the record for the person I double-clicked in the continuous form.


*********************************
Dim strWhere As String

strWhere = "[EmployeeID] = """ & Me.[EmployeeID] & """"
DoCmd.OpenForm "frmEmployees", , , strWhere

*********************************

Thanks,
Anthony

 

[biganthony via AccessMonster.com]

Hi,

Forgot to add, EmployeeID field is a text field.

Anthony.

 

[Brian]

Dim strWhere As String
strWhere = "[EmployeeID] = '" & [EmployeeID] & "'"
DoCmd.OpenForm "frmEmployees", , , strWhere

The quotes are like this:

Double quote before [EmployeeID].
Single quote, then double quote after the =

Double quote, single quote, double quote at the end.

 

[biganthony via AccessMonster.com]

Brian,

Thanks for replying. I changed my quotes but the same problem exists - the
frmEmployee form opens but always at the first employee's record.

Does it make any difference if the continuous form is not a data entry form?
It displays the data about the employees and I would like to double click
their ID and have their employee record in the frmEmployee form open.

Anthony

Dim strWhere As String
strWhere = "[EmployeeID] = '" & [EmployeeID] & "'"
DoCmd.OpenForm "frmEmployees", , , strWhere

The quotes are like this:

Double quote before [EmployeeID].
Single quote, then double quote after the =

Double quote, single quote, double quote at the end.

Hi,

[quoted text clipped - 17 lines]

Thanks,
Anthony

 

[Brian]

Then, let's look at whether the strWhere is getting the correct information.
Try this:

Dim strWhere As String
strWhere = "[EmployeeID] = '" & [EmployeeID] & "'"
MsgBox "strWhere = " & strWhere
DoCmd.OpenForm "frmEmployees", , , strWhere

Copy & post back here the results of the MsgBox. It should look something
like this:

[EmployeeID] = 'jsmith'

The second question is whether the form being opened has EmployeeID in its
RecordSource, so also copy post here the RecordSource of the form being
opened. It should look something like this:

SELECT * from EmployeeTable

(but we need to make sure EmployeeID is a field in the EmployeeTable)

Brian,

Thanks for replying. I changed my quotes but the same problem exists - the
frmEmployee form opens but always at the first employee's record.

Does it make any difference if the continuous form is not a data entry form?
It displays the data about the employees and I would like to double click
their ID and have their employee record in the frmEmployee form open.

Anthony

Dim strWhere As String
strWhere = "[EmployeeID] = '" & [EmployeeID] & "'"
DoCmd.OpenForm "frmEmployees", , , strWhere

The quotes are like this:

Double quote before [EmployeeID].
Single quote, then double quote after the =

Double quote, single quote, double quote at the end.

Hi,

[quoted text clipped - 17 lines]

Thanks,
Anthony

 

[biganthony via AccessMonster.com]

Brian,

Thanks for replying.

I already checked whether the strWhere is getting the correct information by
printing a message box. It is getting the correct information.

Also the record source for the form is as you have written. (SELECT * FROM
tblEmployees


The only way I have got it working is with the following code :

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

DoCmd.OpenForm "frmEmployees", , , , , acHidden

DoCmd.OpenForm "frmEmployees", acNormal, , strWhere, acFormEdit,
acWindowNormal

***********************************************************************

It seems to only work when the frmEmployees form is already open prior.
I don't know what has happened!

Anthony

Then, let's look at whether the strWhere is getting the correct information.
Try this:

Dim strWhere As String
strWhere = "[EmployeeID] = '" & [EmployeeID] & "'"
MsgBox "strWhere = " & strWhere
DoCmd.OpenForm "frmEmployees", , , strWhere

Copy & post back here the results of the MsgBox. It should look something
like this:

[EmployeeID] = 'jsmith'

The second question is whether the form being opened has EmployeeID in its
RecordSource, so also copy post here the RecordSource of the form being
opened. It should look something like this:

SELECT * from EmployeeTable

(but we need to make sure EmployeeID is a field in the EmployeeTable)

Brian,

[quoted text clipped - 23 lines]

 

[Brian]

What happens when you hard-code a reference to a specific employee ID in the
OpenForm method?

Like this:

DoCmd.OpenForm "frmEmployees", , , "[EmployeeID] = 'jsmith'"

Does this work? If so, then try this:

strWhere = "[EmployeeID] = 'jsmith'"
DoCmd.OpenForm "frmEmployees", , , strWhere

Does this work?

Brian,

Thanks for replying.

I already checked whether the strWhere is getting the correct information by
printing a message box. It is getting the correct information.

Also the record source for the form is as you have written. (SELECT * FROM
tblEmployees


The only way I have got it working is with the following code :

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

DoCmd.OpenForm "frmEmployees", , , , , acHidden

DoCmd.OpenForm "frmEmployees", acNormal, , strWhere, acFormEdit,
acWindowNormal

***********************************************************************

It seems to only work when the frmEmployees form is already open prior.
I don't know what has happened!

Anthony

Then, let's look at whether the strWhere is getting the correct information.
Try this:

Dim strWhere As String
strWhere = "[EmployeeID] = '" & [EmployeeID] & "'"
MsgBox "strWhere = " & strWhere
DoCmd.OpenForm "frmEmployees", , , strWhere

Copy & post back here the results of the MsgBox. It should look something
like this:

[EmployeeID] = 'jsmith'

The second question is whether the form being opened has EmployeeID in its
RecordSource, so also copy post here the RecordSource of the form being
opened. It should look something like this:

SELECT * from EmployeeTable

(but we need to make sure EmployeeID is a field in the EmployeeTable)

Brian,

[quoted text clipped - 23 lines]

Thanks,
Anthony

 

[biganthony via AccessMonster.com]

Brian,

Thanks for your assistance.

The line of code you suggested does not work - it opens at the first record
(again). ( I did change to an employee ID that I know does exist in the
original form/table.)

If I precede it with:

DoCmd.OpenForm "frmEmployees", , , , , acHidden

it works.

Anyway, It works with that line in so I will keep it as it is.

Anthony

Like this:

DoCmd.OpenForm "frmEmployees", , , "[EmployeeID] = 'jsmith'"

Does this work?

[quoted text clipped - 49 lines]