Open Subform At Certain Recordset Based On Subform
Feb 5, 2005
I have a main form which has a subform control in it. when the main form is opened the sub form control contains a list of events in a sub form (a summary view), for the person selected in the main form.
I want to change the subform to a different one which shows the whole event when i click in a field on the subform, based on the event i clicked in. ( i can do it by opening a new form but not with a new subform)
I can make the form change using the following code:
Private Sub Notes_DblClick(Cancel As Integer)
Me.Parent.subformhere.SourceObject = "eventsfullrecord"
End Sub
What code do i need to make it open to the record i clicked in the summary form, rather than just opening to the first record every time?
Not very good at passing recordsets
Thank you
View Replies
ADVERTISEMENT
Oct 27, 2005
I have a main form (FRMProspects) with a combo box (ComboCoName) that has a query as it's record source to sort the records displayed in it. It is bound by the CustID (autonumber), but displays the CompanyName.
What I want to do is open a sub form FRMNotes, based on the Company name that is selected in the ComboCoName. So for Company XY, I want to click on the Notes command button to open up all the notes for that company only (filtered). FRMNotes is based on a different table, but TBLProspects and TBLNotes are linked by CustID (one to many).
What code do I need for this? I am treading water with VBA!
View 4 Replies
View Related
Mar 7, 2014
I have a form (frmSearch), based on (I think) John Big Bootys code. On it, a search box, which filters results as you type. Works great.
Instead of presenting the search results in a listbox, I wanted it in a subform (sfmSearchresults). Also works great.
Problem was this: I wanted a button on this form, to open another form based on [CustID]. For a listbox, I could use columns(0), but not for a subform. After much trying I got this to work:
Code:
Private Sub cmdVisaPren_Click()
Dim strWhere As String
strWhere = "[CustID] = " & Me.sfmSearchResults.Form.Controls.Item(5)
DoCmd.OpenForm "frmAddCust", acNormal, , strWhere, acFormEdit, acWindowNormal
End Sub
The part that I don't understand is the Item(5). The subform is based on a query (qrySearch), from tblCustomers. tblCustomers fields is as follows:
CustID, FirstName,LastName,Adress1,Newspaper1,Newspaper2.
Item(0) in strWhere would return Adress1 as [CustID].
Item (1) = Newspaper1, Item(2) = Newspaper2, Item(3) = FirstName, Item(4)=LastName, and (finally) Item(5)=CustID.
I don't get the logic. With listboxes, I could just count columns for columns().
Have attached my database. It's just a test database, with 4 people. The final database will have about 1200 people, and many more fields. Its .accdb (Access 2013). I tried converting to Access 2002-2003, but couldn't get it to work.
View 4 Replies
View Related
Aug 14, 2015
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
View 4 Replies
View Related
Sep 1, 2006
I almost have this form done.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
:confused:
View 1 Replies
View Related
Sep 1, 2006
I have a form that is almost complete
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
View 1 Replies
View Related
Dec 16, 2013
I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.
Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList
Here is my code:
Code:
Dim rs As DAO.Recordset
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
If Not (rs.EOF And rs.BOF) Then
Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If
I am getting this error: Object doesn't support this property or method
View 1 Replies
View Related
Mar 16, 2006
I have two subforms on a form. When the user clicks on the 1st Subform the 2nd one will populate depending on what was selected on the first one. I have a click event but the 2nd subform isn't being populated correctly.
Is this possible?
View 7 Replies
View Related
Mar 3, 2006
So far I had several Subforms which would open in a mainForm depending on the combo box value. My combo box values were exactly the same as subform names so that I can easily change subform.recordsource = combo.text and it would show the proper form. Now, I'm thining, instead of having all those forms, I could have only one form and just changing it's recordSource property with the sma combo box, but this time, combo.value would be the name of the SQL string that the form needs to take as it's recordSet.
What I want to know is, if this will work at all, and if it would, where do I have to set my vars...in the Form_load()?!:confused:
tnx
D
View 1 Replies
View Related
Jun 4, 2014
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
View 3 Replies
View Related
Sep 4, 2014
I have a form called frm002_PAF_MonitoringMAIN and on this form I have a subform called frm002_PAF_Monitoring. The subform has a button to another form for contact details called Contact_Details. The Contact_Details form opens with the details of the person who I have selected on frm002_PAF_Monitoring. There are some fields on frm002_PAF_Monitoring that I would like to be populated on Contact_Details when opened, like FirstName and LastName. How can I do it?I have tried on afterupdate event on Contact_Details below but those don't work:
Me!FirstName=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me!FirstName=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_Monitoring]![FirstName]
Me![FirstName].Value=[Forms]![frm002_PAF_MonitoringMAIN]![frm002_PAF_Monitoring]![FirstName]
View 11 Replies
View Related
Nov 10, 2014
I've been trying : double click a field in a subform and paste the query results into excel. I already have good code to paste in the recordset, but this recordset must reference a control value in a subform.
The query works and shows the proper results manually, but when I run the Sub it throws a "run-time error 3061: Too few parameter. Expected 1."
I can't seem to set the parameters right. Tried EVERYTHING--except the RIGHT thing.
View 4 Replies
View Related
May 8, 2015
I am trying to copy a subform record set from one record to the next record. The data in the main table is copied over using this method;
Code:
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
v1 = Me![Today's Date].Value
v2 = Me!Insured.Value
[Code] ....
Can I integrate the copying of the subform data to the new record with this or do I need something different, and if so what?
It's a many to many relationship and I've tried adding the following line to the code:
v5=Me![Endorsements Umb XS].[UmbID]
same=v5
and it works but it only copies the first record out of the set.
View 14 Replies
View Related
Sep 14, 2013
How it works: You search for a User, and then you search for a Serial.Now, what if I want to edit the table? (inside the subform, the results). I added an openRecordSet, and Parameters, but I still get the error saying that Recordset cannot be updated.Why? I opened and sent parameters but still fails.
View 2 Replies
View Related
Jul 11, 2014
I have a form with a continuous subform whose RecordSource is a predefined query. There are a number of controls on the main form which allow for various filtering on the subform / query and a command button to export the data to Excel.Because I want to export exactly whatever the user has filtered using the form, I take a clone of the subform recordset and pass that as a recordset object to my export function.
I've just noticed, however, that a couple of the fields in the Excel spreadsheet are always blank, even though the corresponding records on the subform show values? I can't figure out why; there is nothing unusual or distinctive about these fields (2 x Text and 1 x Date/Time) other than the fact that the contents don't seem to copy? I do get the field names - just no data for them?
The data is visible in the subform, and when I run the query on its own, the fields are populated in the returned dataset as well? So why aren't they included in the RecordsetClone?
View 14 Replies
View Related
Jun 1, 2006
Problem:
Visual Basic run-time error 7792: You can't open a subform when it is also open in Design view.
I have a subform with properties for SourceObject, LinkMasterFields, LinkChildFields changing according to objects and events in the master form.
The subform was bound to a query before which meant that the subform load event was happening as soon as the master form is loaded. I needed to restrict the subform loading until a certain point so I removed the SourceObject property for the masterform's subform. The subform on the master form is now Unbound.
(This is because I'm now running some code on the FormLoad event for the subform which needs to be restricted until the LinkMasterFields and LinkChild Fields properties have been assigned correctly otherwise it takes ages to load.)
Now I'm getting the above error. Obviously, i do not have the subform open anywhere in design view. No Visual Basic windows are open. I've closed the db, closed access, reopened it and clicked on nothing except the masterform. The error occurs when I raise the event in the master form which assigns the sourceobject property to the subform, i.e.
Me.sfmQryAllOV.SourceObject = "sfmQryAllOV"
Can anyone help me? I've googled this but finding no answers.
View 1 Replies
View Related
Nov 5, 2014
I am adding new record into subform via recordsetclone method. The problem is that record is added but on save it does not appear in the table. If add this record manual using subform everything works. When record added manually update of the record works fine.
C
'Add Wastage value to flooring area section
Private Sub Wastage_AfterUpdate()
Dim rsFlArea As DAO.Recordset
Dim Wastage As Double
Dim Item As String
Set rsFlArea = Me.OrderFloorAreaEdit.Form.RecordsetClone
[Code] .....
View 6 Replies
View Related
Mar 18, 2006
Hi,
I have a main form with 2 subforms.
I am trying to create records in the second subform based on records in the first subform nd the current record in the main form.
at present i can create the new record and paste information, however i cannot seem to advance records in teh first subform to repeat the procedure.
and code or macro i try returns a 'object not open' error
any help would be great.
View 13 Replies
View Related
Feb 2, 2005
Hi All
I have a subform called frmProducts and on it I have 2 command buttons One is to use the current open form to jump to a particular product to make it easier to edit the details and the other is to use the current form and jump to a new record.
Now I have this working when running the form on its own but when I try and run it as a subform it does not work. I am not sure how to adjust the coding to let this happen.
the Main forms name is "MainForm"
Below is my current coding to open the form at a particular record
Private Sub EditProduct_Click()
Dim Msg As String
Dim Title As String
Dim Defvalue As String
Dim Answer As String
Msg = "Enter a Product Name: "
Title = "Open Prouct Form"
Defvalue = ""
Answer = InputBox(Msg, Title, Defvalue)
If Answer <> "" Then
DoCmd.OpenForm "frmProducts", , , "[ProductName]='" & Answer & "' "
Else
DoCmd.OpenForm "frmProducts"
End If
End Sub
Below is my coding to open the form at a new record
Private Sub NewProduct_Click()
DoCmd.GoToRecord , , acNewRec
End Sub
Many thanks in advance for all assistance
Sirch76
View 2 Replies
View Related
Jul 25, 2006
Hello all,
I know I have seen this before and I have searched the forums several times and cannot find it now.
Maybe I am searching the wrong thing. Can someone point me in the right direction?
I need a button on my form that opens a subform or subforms when clicked.
How do I set this up?
Thanks,
Di
View 4 Replies
View Related
Aug 14, 2006
Hi all,
I have a form with a button on it, that when clicked it will open a subform on the same form. But, when I clicked this button, it gave me this error message:
The expression On Click you entered as the event property setting produced the following error: A problem occurred while [Database Name] was communicating with the OLE server or ActiveX Control
For more details on this error message, see the attached file.
Any attempt to help will be highly appreciated.
Sorry for bothering you.
View 2 Replies
View Related
Aug 29, 2006
I have a form with a combobox to select a clients name and that pulls up that client in a subform and shows various records for the client. But it always opens up showing the first record and I want it to open up showing a blank record for keying in more data i.e. after the last record.
Can you help at all on this?
many thanks
View 1 Replies
View Related
Nov 30, 2006
Hi,
I have a form, with a subform in it.
I am trying to add a record through the subform then once added I want to display that record..
at the moment I add the record but the first record is shown.
I have tried adding :
DoCmd.GoToRecord , Forms!frmviewscheme![QScheme_Establishment subform], acLast
but recieve a run-tim error 2498.
can anybody help me ?
regards,
Ja
View 8 Replies
View Related
Mar 22, 2013
I open a form to a blank new record, then when using a combo box to select a record I need the subform to open to the last record. Having trouble doing that. Both form and subform are Single views.
View 1 Replies
View Related
Dec 13, 2005
I want to open Outlook with the recipients name in the To: box by clicking on the email address held in a subform list field...
Any ideas?
Dave E
View 3 Replies
View Related
Mar 9, 2005
I'd like to thank everyone for all the help with a long project that is nearing completion.
I have a query by form where I would like there to be a button that the user could push that would take the values from the query that are in the subform and open them up as an Excel spreadsheet for further analysis. There is a function called analyze it with Excel in the Tools/Office options menu but it only works for the main form. I don't get all the values.
Is there some way I can open the subform as an Excel file or set the code and SQL string that it is based on to values for an Excel sheet?
Thanks
View 1 Replies
View Related
