Open a form to a specific record including correct record in subform

[rovral]

I have a search form that displays a bunch of sales records in a subform based on sql. I also have a more button that will take you to the data entry form based on the id # in the search subform and the ID # in the data entry form. The data entry form also has a subform that displays the sales details. There can be more than one sales record for a particular building. The problem I have is there a way to not only open the data entry form to the specific building record but also have the subform display the correct sale. Currently the subform shows the first sale in the list. The code for the more button is as follows:

Private Sub cmdMore_Click()
On Error GoTo Err_cmdMore_Click

    Dim stDocName As String
    Dim stLinkCriteria, stLinkCriteria2 As String

    stDocName = "frmData_Entry"
    
    stLinkCriteria = "tblCom_Buildings.[Building_ID]=" & Me![txtID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdMore_Click:
    Exit Sub

Err_cmdMore_Click:
    MsgBox Err.Description
    Resume Exit_cmdMore_Click
End Sub

[Mihail]

I think is needed to post the structure for both tables. Also will help if you post a pic with the relationship between this tables.

Extra:
In your code the line 5 is incorrect (even if it work) because only stLinkCriteria2 is dimensioned as string.
stLinkCriteria actually have a VARIANT data type.

Correct syntax is:

Dim stLinkCriteria As String, stLinkCriteria2 As String

[NeoPa]

I suggest that you pass some indicator of the record required into the form when it is opened (Use the OpenArgs parameter) so that the form code can select the specified record when it's opened.

[rovral]

Open form to specific record

stLinkCriteria2 was a mistake, I have taken that out.

My relationships are :
[imgnothumb]http://bytes.com/attachments/attachment/6264d1333379522/image1.jpg[/imgnothumb]

[NeoPa]

rovral:
stLinkCriteria2 was a mistake, I have taken that out.

... which leaves you (and, by extension, us) with almost nothing.

There is also nothing in this post that indicates you've read or understand what's been said, other than a response to a request for structure that consists of a relationship diagram. I can't imagine anyone will feel this is something they can work with.

Very little is expected of you, but responding to what people post would certainly count as something that is.

[rovral]

No worries, I will look to someplace else for answers since you obviously don't understand what I am asking.

Thank you.

[NeoPa]

rovral:
No worries, I will look to someplace else for answers since you obviously don't understand what I am asking.

I don't understand because you haven't asked any question properly, and you haven't replied when requested, with the requisite information.

On the other hand, it is your absolute right to take your question elsewhere. Please do so with my blessings.

[Mihail]

In table Com_Buildings you store the Building_Type_I D.
So, if you know the Building_ID you already know the Building_Type_I D.
As a result you have not need to store (again) the Building_Type_I D in table Lease_Detail.
Get the idea ?

So: remove the relationship between Building_Type_L ist table and the Lease_Detail table.
Then, remove the field Building_Type_I D from Lease_Detail table.

Redesign the query (if necessary).

More about your database:
I see two more tables with no relationships.
I don't know how you intend to use Land_Use_List table.
About the second one I think that is a very good idea to use it.
More than, I advice you to design one more table: tblProvince (Province_ID, Location_Distri ct_ID, ProvinceName).
Also redesign Location_List_T able (Location_ID, Province_ID, LocationName).
Insert this two tables between Location_Distri ct and Com_Buildings tables.

Let me know if this help you.
Good luck !