By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

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

P: 28
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMore_Click()
  2. On Error GoTo Err_cmdMore_Click
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria, stLinkCriteria2 As String
  7.     stDocName = "frmData_Entry"
  9.     stLinkCriteria = "tblCom_Buildings.[Building_ID]=" & Me![txtID]
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  12. Exit_cmdMore_Click:
  13.     Exit Sub
  15. Err_cmdMore_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_cmdMore_Click
  18. End Sub
Mar 30 '12 #1
Share this Question
Share on Google+
7 Replies

P: 759
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.

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:
Expand|Select|Wrap|Line Numbers
  1. Dim stLinkCriteria As String, stLinkCriteria2 As String
Mar 31 '12 #2

Expert Mod 15k+
P: 31,409
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.
Apr 1 '12 #3

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

My relationships are :

Attached Images
File Type: jpg Image1.jpg (64.9 KB, 212 views)
Apr 2 '12 #4

Expert Mod 15k+
P: 31,409
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.
Apr 2 '12 #5

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

Thank you.
Apr 2 '12 #6

Expert Mod 15k+
P: 31,409
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.
Apr 3 '12 #7

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

So: remove the relationship between Building_Type_List table and the Lease_Detail table.
Then, remove the field Building_Type_ID 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_District_ID, ProvinceName).
Also redesign Location_List_Table (Location_ID, Province_ID, LocationName).
Insert this two tables between Location_District and Com_Buildings tables.

Let me know if this help you.
Good luck !
Apr 3 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.