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

Synchronize main form with the selected record in the sub form

my subform filters the rcords of the main form based on partID
When I select one record on the subform I want the main goes to the same record / how can I do that?
thanks
Dec 11 '16 #1
3 3986
gnawoncents
214 100+
Can you give us an example of what you mean? I'm not entirely sure what setup you have and the relationship between main and subform.
Dec 12 '16 #2
jforbes
1,107 Expert 1GB
There are a few ways to do this. Like gnawoncents has asked, if you can provide what you have, it will make it easier to narrow down what might best work for you.

It sounds to me like you could run the ComboBox Wizard and select the "Find a Record on my form based on the value I selected in my combo box" option. Then after the ComboBox has been created, you can right-click on it and change it to a ListBox. This doesn't use a SubForm, but it's pretty simple to implement and maintain. This wont Filter the MainForm, just move to the appropriate record:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo11_AfterUpdate()
  2.     ' Find the record that matches the control.
  3.     Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.clone
  6.     rs.FindFirst "[PartID] = " & Str(Nz(Me![Combo11], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. End Sub
If you need a SubForm, there are a few different ways I can think of doing this:

The First, which is probably easiest, but I'm not a fan of is to setup a Query for the MainForm that has a criteria expression that points to the SubForm, then things will either be automatic, or only need a MainForm.Requery from the SubForm OnCurrent Event. This will Filter the MainForm. The Criteria would be something like this:
Expand|Select|Wrap|Line Numbers
  1. Forms![MainForm]![SubForm].Form![PartID] 
The Second, is to write a VBA routine for the SubForm OnCurrent Event that applies a Filter to the MainForm:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Forms("MainForm").Form.Filter = "[PartID] = " & Me!PartID
  3.     Forms("MainForm").Form.FilterOn = True
  4. End Sub
The Third, is to write a VBA routine for the SubForm OnCurrent Event that just finds the Record on the MainForm:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim rs As Object
  3.     Set rs = Forms("MainForm").Form.Recordset.clone
  4.     rs.FindFirst "[PartID] = " & Me!PartID
  5.     If Not rs.EOF Then Forms("MainForm").Form.Bookmark = rs.Bookmark
  6. End Sub
The Fourth, and very cool, but tricky way to do this is to hook into the SubForm's events from the MainForm. This one is tricky, but works very well once it's up and working. This code is all located on the MainForm:
Expand|Select|Wrap|Line Numbers
  1. Private WithEvents fSubForm As Access.Form
  2. Private Sub Form_Load()
  3.     ' Hook into Subform Event
  4.     Set fSubForm = Me.SubForm.Form
  5.     fSubForm.OnCurrent = "[Event Procedure]"
  6. End Sub
  7. Public Sub fSubForm_Current()
  8.     ' Subform OnCurrent
  9.     Me.Filter = "PartID" & Me.SubForm!PartID
  10.     Me.FilterOn = True
  11. End Sub
  12.  
Dec 13 '16 #3
Gnawoncents and jforbs thank you
I shall try to understand your answer and solve the problem
I'll try to expand the explanation: The project is designed to manage inventory items.
Form frmMaintenance is used to manage and track maintenance operations.
This form has the following fields : IDoperate (auto number), IDitem (link to another form frmInvItems) , Dateoperate , Kindoperate etc.
Recently I have added Subform control which lincked to the main form by the IDitem
as child field and master field. I need the subform show me all the maintenance operations carried out on this specific item in the current record of the main form. That work fine ok.
For example, when operation number 5 is selected (IDoperate =5) ( IDitem = 105203) which is scales (Kindoperate= calibration) , Then the subform shows me 3 rows :
5 105203 calibration 01/06/2016
29 105203 fix 17/09/2016
37 105203 fix 05/10/2016
I need this to work in the opposite direction so if I select the 29 row (e.g.) in the sub form
it should become the current record in the main form. But this does not happen
I must apologize for my bad English
And thank you very much for your help
Don Quixote
Dec 17 '16 #4

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

Similar topics

1
by: | last post by:
Is there anyway to update a control held on my main form from a child form? I've tried making the control itself public but I still can't seem to access it
3
by: TDIOwa | last post by:
I have a form (frmAppointment) with a subform on it (frmAppointmentsub1). I have placed a command button on the main form which calls the function below from the module. The purpose of this...
7
by: Stu | last post by:
Hi, I have a combobox who's values change the recordsource of the form. Within this form, there is a subform, whos records also need to change pending the value in the combobox. I am able to get...
3
by: gsb58 | last post by:
Hi! A mainform is being used to show records from a table in an sql database. A button on the main form will load a new form that allows the user to add, delete, update and search certain...
3
by: lavu | last post by:
How would I pass parameters from the main form to my subform at load time. I use Activator.CreateInstance to create the subform. Thanks in advance. Ps: I tried googling for this but found no...
3
by: darnnnel | last post by:
Hi Everyone, I have a form “FormMain” on which i have a combo box which pulls values from a table. If the combo box does not have the choice the user is looking for, i added a button that opens a...
2
by: bigukfan | last post by:
I have an application that is used to collect clinical data from hospital patients involved in research. Various forms are used to collect specific data, often blood results. When the users enter...
1
by: Stoic | last post by:
Hi, I have an entry form with a field(combo box) called cboGetCode. I also have a sub form (continuous form) with number of fields, also an entry form. Now, I have added a code behind the cboGetCode...
17
RockKandee
by: RockKandee | last post by:
I am working with Access 2013 in Windows 8 Due to changes I made to make another form work, I have created myself a new problem with a different form. I have 2 tables Table 1 = parent Table 2...
16
by: Joe Y | last post by:
In my Access program, I have a main form that holds recipe general information and a sub-form that keeps the actual formula. The main form name is “recipe” and Record Source is a table named...
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...
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: 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: 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
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...

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.