Open form to a particular record

  • Thread starter biganthony via AccessMonster.com
  • Start date
B

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
 
B

biganthony via AccessMonster.com

Hi,

Forgot to add, EmployeeID field is a text field.

Anthony.
 
B

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.
 
B

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.
[quoted text clipped - 17 lines]
Thanks,
Anthony
 
B

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)

biganthony via AccessMonster.com said:
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.
[quoted text clipped - 17 lines]
Thanks,
Anthony
 
B

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)
[quoted text clipped - 23 lines]
 
B

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?

biganthony via AccessMonster.com said:
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)
[quoted text clipped - 23 lines]
Thanks,
Anthony
 
B

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]
 

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

Similar Threads


Top