473,327 Members | 2,016 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria, stLinkCriteria2 As String
  6.  
  7.     stDocName = "frmData_Entry"
  8.  
  9.     stLinkCriteria = "tblCom_Buildings.[Building_ID]=" & Me![txtID]
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_cmdMore_Click:
  13.     Exit Sub
  14.  
  15. Err_cmdMore_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_cmdMore_Click
  18. End Sub
  19.  
Mar 30 '12 #1
7 2017
Mihail
759 512MB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim stLinkCriteria As String, stLinkCriteria2 As String
Mar 31 '12 #2
NeoPa
32,556 Expert Mod 16PB
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
rovral
28
stLinkCriteria2 was a mistake, I have taken that out.

My relationships are :

Attached Images
File Type: jpg Image1.jpg (64.9 KB, 372 views)
Apr 2 '12 #4
NeoPa
32,556 Expert Mod 16PB
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.
Apr 2 '12 #5
rovral
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
NeoPa
32,556 Expert Mod 16PB
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.
Apr 3 '12 #7
Mihail
759 512MB
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

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

Similar topics

3
by: cefrancke | last post by:
Is there a way to list (through VBA code) all the controls on an open form including subforms, tab controls and all other containers as well. My specific issue is to find all controls with a...
6
by: erick-flores | last post by:
Hello all, Form A & Form B Form B opens when I click on a button from Form A. How do I setup Form B so it will always let me insert new records. Because right know, when I click on the button...
4
by: piscogirl | last post by:
Hi all, Here's my case: I have a form: Event Form and a subform: AttendanceSubForm The user selects a name (from a Person Table) on the subform to register for the event. If the name is...
9
by: StellaUK | last post by:
I have Seen Loads Of Post On This And I Have Spent The Last Hour Trying Everything I Come Across But Still Can Get What I Thought Was A Simple Thing To Work .. LOL I Have 2 Forms Vehicles...
4
by: Leah Trahan | last post by:
I am new at Access, databases, AND posting threads (sorry). I have a subform that is a continuous form based on a tabular query. I enter a particular serial # (on the main form) and the subform...
3
by: kev | last post by:
Hi folks, I have a form for registration (frmRegistration) whereby i have two buttons. One is Save which saves record using the OnClick property. I used wizard to create the save button. The...
8
by: lmurgas | last post by:
FormA = List of organization records bound to table FormB = Tabbed form with details of organization and all other related entities, such as orders, contacts, invoices, (all as subforms bound to...
14
by: keri | last post by:
Hi, Simple version of the question..... How do I use the where clause of the open form command to show an account with a matching ID to be displayed when the form is opened? Eg. I select a...
10
by: Gilberto | last post by:
Hello Is there a way to indicate a form that it should open in the LAST+1 record, so that the user can start entering information directly. Because right now when the user opens the form, it...
4
by: banderson | last post by:
Hello, I am having a problem getting a new record in a subform to append to the underlying table. When I add a new record to the main form, the subform is blank, except for the ID field, which is...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.