open form to specific record in subform

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

I have a search form called fSearch. It is a continuous form that searched
for specific records from the tDeliverable table. Contains ProjectID and
DelivID.

I also have a fProject (ProjectID) form that has a subform called
fDeliverable(ProjectID and DelivID).

In the FSearch form, when I click on a button next to a specific Deliverable
record I want the fProject form to open and display the selected Project in
the tProject mainform and the selected Deliverable in the fDeliverable
subform.

The below button criteria only opens to the Project in the main form.

stLinkCriteria = "[ProjectID]=" & Me![ProjectID] DoCmd.OpenForm
stDocName, , , stLinkCriteria

Thanks in advance
 
Hi Deb,

The trick is to use the optional WhereCondition parameter (stLinkCriteria)
in such a manner that it *uniquely* identifies the record of interest. Not
knowing the structure of your database, I'm assuming that ProjectID in the
recordset of fProject is not a unique identifier, for example, a primary key
or uniquely indexed field [Indexed = Yes (No Duplicates)].


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
stLinkCriteria = "[tDeliverable].[DelivID]=" &
[Forms]![fProject]![f000Deliverable].[Form]![DelivID]

What am I doing wrong?
--
deb


Tom Wickerath said:
Hi Deb,

The trick is to use the optional WhereCondition parameter (stLinkCriteria)
in such a manner that it *uniquely* identifies the record of interest. Not
knowing the structure of your database, I'm assuming that ProjectID in the
recordset of fProject is not a unique identifier, for example, a primary key
or uniquely indexed field [Indexed = Yes (No Duplicates)].


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

deb said:
I have a search form called fSearch. It is a continuous form that searched
for specific records from the tDeliverable table. Contains ProjectID and
DelivID.

I also have a fProject (ProjectID) form that has a subform called
fDeliverable(ProjectID and DelivID).

In the FSearch form, when I click on a button next to a specific Deliverable
record I want the fProject form to open and display the selected Project in
the tProject mainform and the selected Deliverable in the fDeliverable
subform.

The below button criteria only opens to the Project in the main form.

stLinkCriteria = "[ProjectID]=" & Me![ProjectID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks in advance
Click to expand...
Click to expand...
 
When I try this
stLinkCriteria2 = "[ProjectID]=" & Me.ProjectID And
"[Forms]![fProject]![f000Deliverable].[Form]![DelivID]=" & Me.DelivID

I get a type mismatch. Please help!!
The mian form ID is ProjectID and the sub form ID is DelivID

--
deb


deb said:
stLinkCriteria = "[tDeliverable].[DelivID]=" &
[Forms]![fProject]![f000Deliverable].[Form]![DelivID]

What am I doing wrong?
--
deb


Tom Wickerath said:
Hi Deb,

The trick is to use the optional WhereCondition parameter (stLinkCriteria)
in such a manner that it *uniquely* identifies the record of interest. Not
knowing the structure of your database, I'm assuming that ProjectID in the
recordset of fProject is not a unique identifier, for example, a primary key
or uniquely indexed field [Indexed = Yes (No Duplicates)].


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

deb said:
I have a search form called fSearch. It is a continuous form that searched
for specific records from the tDeliverable table. Contains ProjectID and
DelivID.

I also have a fProject (ProjectID) form that has a subform called
fDeliverable(ProjectID and DelivID).

In the FSearch form, when I click on a button next to a specific Deliverable
record I want the fProject form to open and display the selected Project in
the tProject mainform and the selected Deliverable in the fDeliverable
subform.

The below button criteria only opens to the Project in the main form.

stLinkCriteria = "[ProjectID]=" & Me![ProjectID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
Hi Deb,

Your example indicates that [DelivID] is a numeric field. Is this correct?

What do you get when you print the result to the Immediate Window, ie.:

stLinkCriteria = "[tDeliverable].[DelivID]=" _
& [Forms]![fProject]![f000Deliverable].[Form]![DelivID]

Debug.Print stLinkCriteria

Do you see the expected result?

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
I think you want something like this:

stLinkCriteria2 = "[ProjectID]=" & Me.ProjectID _
& " And [Forms]![fProject]![f000Deliverable].[Form]![DelivID]=" _
& Me.DelivID

In this case, both the ProjectID and DelivID fields would be numeric data
types (not text). The name of the container control for your subform must be:
f000Deliverable
This may or may not be the same name as the subform itself.

Are you using Option Explicit?

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Does your VBA code compile without any errors? In the Visual Basic Editor
(VBE), click on Debug | Compile ProjectName

where ProjectName is the name of your VBA project.

What do you get when you add a Debug.Print statement?

Debug.Print stLinkCriteria2


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Thank you for your help!!

stLinkCriteria2 = "[ProjectID]=" & Me.ProjectID _
& " And [Forms]![fProject]![f000Deliverable].[Form]![DelivID]=" _
& Me.DelivID

Gives me
[ProjectID]=24 And [Forms]![fProject]![f000Deliverable].[Form]![DelivID]=590

The ID numbers are correct for both main and subform. however the forms are
blank when displayed...
--
deb


Tom Wickerath said:
Hi Deb,

Your example indicates that [DelivID] is a numeric field. Is this correct?

What do you get when you print the result to the Immediate Window, ie.:

stLinkCriteria = "[tDeliverable].[DelivID]=" _
& [Forms]![fProject]![f000Deliverable].[Form]![DelivID]

Debug.Print stLinkCriteria

Do you see the expected result?

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

deb said:
stLinkCriteria = "[tDeliverable].[DelivID]=" &
[Forms]![fProject]![f000Deliverable].[Form]![DelivID]

What am I doing wrong?
Click to expand...
Click to expand...
 
Hi Deb,

So I take it you are no longer getting the Type Mismatch error?

If the fProject form opens to a new record, instead of the intended record,
then the criteria is apparently not correct to uniquely identify a record in
this form's recordset. What is the recordsource for this form (ie. is it a
table or a query)? What field or combination of fields uniquely identify a
record in this table or query?

What about some of my other questions from before?
1.) Are you using Option Explicit?
2.) Does your VBA code compile without any errors?
3.) Your example indicates that [DelivID] is a numeric field. Is this correct?

Also, I didn't ask this earlier, but is the ProjectID field also a numeric
data type?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
There are 2 seperate forms. 2 ID's ProjectID and DelivID, both numeric

The first form is a search form. It has ProjectID and DelivID (1 to many).
It searches for and displays the DelivIDs within each project in a continuous
form. There is a button next to the DelivID.

I want to click this button and have the "second form" display with the
ProjectID used in the main form and DelivID used in the subform.

stLinkCriteria2 = "[ProjectID]=" & Me.ProjectID _
& " and [Forms]![fProject]![f000Deliverable].[Form]![DelivID]= " _
& Me.DelivID

The first part - "[ProjectID]=" & Me.ProjectID works great. If I use it by
itself it does display the correct projectID in the main form.

but the second part does not reach the subform to get the delivID criteria.
& " and [Forms]![fProject]![f000Deliverable].[Form]![DelivID]= " _
& Me.DelivID

What is wrong with the second part of the criteria? How can I make it reach
the subform and not the main form? currently when I use the whole criteria
it only displays a blank page.
Here is what the immediate window displays...
[ProjectID]=24 and [Forms]![fProject]![f000Deliverable].[Form]![DelivID]= 590.

As mentioned before, If I use the ProjectID criteria alone. it works. It
looks like the DelivID criteria is not getting to the subform.

Would it make a difference if the subform is in a tab?

Please don't give up on this. It is important to my db.

Thank you!!
--
deb


Tom Wickerath said:
Hi Deb,

So I take it you are no longer getting the Type Mismatch error?

If the fProject form opens to a new record, instead of the intended record,
then the criteria is apparently not correct to uniquely identify a record in
this form's recordset. What is the recordsource for this form (ie. is it a
table or a query)? What field or combination of fields uniquely identify a
record in this table or query?

What about some of my other questions from before?
1.) Are you using Option Explicit?
2.) Does your VBA code compile without any errors?
3.) Your example indicates that [DelivID] is a numeric field. Is this correct?

Also, I didn't ask this earlier, but is the ProjectID field also a numeric
data type?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

deb said:
Thank you for your help!!

stLinkCriteria2 = "[ProjectID]=" & Me.ProjectID _
& " And [Forms]![fProject]![f000Deliverable].[Form]![DelivID]=" _
& Me.DelivID

Gives me
[ProjectID]=24 And [Forms]![fProject]![f000Deliverable].[Form]![DelivID]=590

The ID numbers are correct for both main and subform. However, the forms are
blank when displayed...
Click to expand...
Click to expand...
 
Hi Deb,

Are you able to send me a compacted and zipped copy of your database,
preferably in the Access 2000 or 2002/2003 file format? If necessary, remove
any private information from a copy of the database.

My e-mail address is available at the bottom of the Contributors page,
indicated below in my signature. Please do not post your e-mail address (or
mine) to a newsgroup reply--doing so will only attract the unwanted attention
of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Back
Top