473,322 Members | 1,287 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,322 software developers and data experts.

Opening a record in specific form on clicking from another subform

Hi
I have a subform that displays a query result.
I want to be able to click on any record on that subform and that opens up that specific record in a form called PUN Prefill.
I am not a developer so specific steps will be required.

Right now I cant see any controls for the subform that allows to double click on each showing record in the subform.

Thanks

Attached Images
File Type: jpg ss.jpg (44.3 KB, 791 views)
Jan 31 '16 #1
9 1592
zmbd
5,501 Expert Mod 4TB
In order to do what you want, you will not be able to use the query directly within the subform control as you've currently done.

Instead, you will need to create a new form using that query as its recordsource. Set this new form as the Source Object and double check the Master/Child field relationships. I usually set the default view as datasheet for subforms and let the wizard place the controls... in the datasheet view only the order of the fields matters; however, that's just my data. It's within this new form that you will develop the code to open the "PUN" form.
Jan 31 '16 #2
Thanks understood. But when i create a on dbl click event, it opens up the PUN form at first record in the table. I want to display the record that was double clicked.
Feb 1 '16 #3
zmbd
5,501 Expert Mod 4TB
Please post your code.
Please post the SQL for the subform record source
Remember to format both using the [CODE/] button in the post toolbar. :)

We'll also need the name of the table and the field that has the common information that is the record source for the master form.

I think however, you'll be using a variation on:
Me!subformname("fieldname")
Feb 2 '16 #4
The code is attached. This is from ON DBL CLICK event on the LISTBOX which displays results of a query. Problem is that this is opening up a range of records in the form PUN Prefill with any data with the field UW matching with the listbox record.
I want to be able to go to that particular record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub searchresults_DblClick(Cancel As Integer)
  2.  
  3. Dim stDocName As String
  4.     Dim stLinkCriteria As String
  5.     stDocName = "PUN Prefill"
  6.  
  7.     stLinkCriteria = "[UW]= '" & Me![searchresults] & "'"
  8.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  9. End Sub
  10.  
Feb 8 '16 #5
zmbd
5,501 Expert Mod 4TB
(...)Problem is that this is opening up a range of records in the form PUN Prefill with any data with the field UW matching with the listbox (...)
stLinkCriteria = "[UW]= '" & Me![searchresults] & "'"
Logically then your field [UW] is not a unique value for any given record.

If you want a particular record, then you need the primary key or a set of criteria that uniquely identifies the record of interest.

On a side note, something I really prefer is the named parameters method, so instead of
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm stDocName, , , stLinkCriteria
You can have:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm FormName:= stDocName, _
  2.    WhereCondition:= stLinkCriteria
This makes it more obvious that your passing the "WHERE" clause of an SQL query to the form.... so say your form has the recordset "tbl_example" then when use the DoCmd.OpenForm method as above the recordset effectively becomes
Expand|Select|Wrap|Line Numbers
  1. SELECT (fields here)
  2. FROM tbl_example
  3. WHERE stLinkCritereia
...
I'm going to forward you a copy of my new-to-access resources list... please check your bytes.com inbox.
...
Feb 8 '16 #6
u got it sir... UW is not unique.
How do I use primary key in this case or any other criteria to identify the record. There isnt any unique field after all except for the default autonumber in the table.
Plus I am a beginner and a non programer. I am just collecting code and compiling it.
Feb 8 '16 #7
zmbd
5,501 Expert Mod 4TB
You haven't provided enough detail about your current design; however, in general you would change the [UW] to the field with the primary key, most likely the autonumber field you mentioned. Of course, this is predicated on the Me![SearchResults] control having that field as part of its rowsource - if not, then you will have to redesign the control, and potentially other aspects fo the form/database, to include this value.

Even "just collecting code" requires a basic skill set. If you will look at the "Tool Kit" I pm'd you, therein you will find a basic tutorial about how to build a database. You will need to master that tutorial before you will have the basic skills needed to accomplish much in Access, let-alone, something such as you are attempting here.
Feb 8 '16 #8
how about if I add another criteria to the code like this. This is giving me error. I believe using two criteria will solve the problem and land the user to the right record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub searchresults_DblClick(Cancel As Integer)
  2.  
  3. Dim stDocName As String
  4.     Dim stLinkCriteria As String
  5.         stDocName = "PUN Prefill"
  6.  
  7.     stLinkCriteria = "[Assigned]= '" & Me![searchresults] & "'" And [Quote Number]= '" & Me![searchresults] & "'"
  8.  
  9.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  10. End Sub
Feb 8 '16 #9
zmbd
5,501 Expert Mod 4TB
Line 7 is malformed:

Expand|Select|Wrap|Line Numbers
  1. '
  2.  stLinkCriteria = "[Assigned]= '" & _
  3.     Me![searchresults] & _
  4.     "' And [Quote Number]= '" & _
  5.     Me![searchresults] & "'"
even with that it will not work as intended.

Insert:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print stLinkCriteria
after line 7 in your code.
Run your form as normal...
<ctrl><g> to open the immediate window.

You will see the resolved string you are attempting to pass as the "WHERE" clause to the form. Does the resolved string make sense? Can you use the same information for [Assigned] and [Quote Number]?

Without your data-set, asking if some arbitrary set of fields will uniquely identify a record in your data-set is like asking a blind-person if an apple, a grape, or an orange are coloured blue - and then spray-painting them silver. :-)


You absolutely must work thru the tutorial I sent you... otherwise, you simply will not have the concepts needed to accomplish this task.
Feb 8 '16 #10

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

Similar topics

1
by: Max Harvey | last post by:
Hi, I made up a nice little form which had its own sub form in it. I made a litle VB code so that when I pressed a button it would move form the form (frmConference) to the subform...
1
by: hriceb | last post by:
I know about the SendObject command but I think my question is a bit different. I have TableA that includes FName, LName, etc... and EMail. Generally, data is entered / changed via FormA I...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
6
by: Paul T. RONG | last post by:
Dear all, The OrderDetail subform has following fields: OrderDetailID OrderID ProdID Quantity Price how to get the OrderDetailID of a record I sellected in Orderdetail subform using...
1
by: John | last post by:
Hi I have this sub in a main form; Sub OpenForm(ByVal x As String) Select Case x Case "1" form1 = New frmClients form1.TopLevel = False Me.FormsPanel.Controls.Add(form1)
1
by: BartonConstruction | last post by:
Greetings all, I have a main form (frmClients) with two subforms (subVisits) (subAccount). I got the subforms to reflect what the main form is showing by linking master and child fields (I...
2
by: whitc26 | last post by:
Let me preface: I'm a novice, and have no programming experience. I have created an access database and have a few tables in it. I have created a form called "clients" This form opens up and...
8
by: Paul Craig | last post by:
Hi, I am currently able to open forms given a string variable using the following code: Dim strForm As String = "Form1" Dim theForm As Form theForm =...
1
by: Coll | last post by:
I have a form with a subform on it. On my main form, you select an employee id number and the top portion of form fills in with data, and the bottom portion of the form,which is a subform, lists...
1
by: prananv | last post by:
Hi Good Morning, I am in position to solve a problem in MS access 2007. Problem: I have a Main form and contains subform. Based on combo selection the subform records are displayed. Main...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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: 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: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.