Modules & VBA :: Recordset Referencing Control Value In Subform
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.
I'm trying to check if a control on the subform is null. I've the following line of code:
Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(Form!FrmOrderInfoMain!SubFrmOrderInfo.ItemC ode) Then Msgbox "Empty ItemCode" End If End Sub
..whereby FrmOrderInfoMain is the parent form and SubFrmOrderInfo is the subform. I get an error for this code as Im not sure what is the correct code to use. Thanks for the help.
I have a multi select list box which I am trying to use to open another form and apply a filter based on the user's multiple selections.
List box - lstCatergories Main Form - frmSuppliersSummaryCategories Subform on Main Form - frmSuppliersSummaryCategoriesSubForm
The Subform contains the text box CategoryID
Here's my code which nearly works...
Code: Private Sub cmdFilterSuppliers_Click() On Error GoTo Err_cmdFilterSuppliers_Click
Dim strWhere As String Dim ctl As Control Dim varItem As Variant
[Code] .....
My problem is that the control "CategoryID" is on the subform and I'm having massive problems trying to reference it. This is the part of my code that won't work:
I'm using Access 2003. In "References" (Tools > References > Browse...), I've added in "Microsoft Windows Common Controls 6.0 (SP6)" (c:windowssystem32mscomctl.ocx).
I've then created/inserted an instance of the control "Microsoft ListView Control 6.0 (SP6)" on a Form ("MyForm") and given the listvew control the Name "MyListView".I wanted to decorate MyListView with some custom methods so I've created a new class module ("DecoratedListView") which contains a member field called "lvw".
I want 'lvw' to point/reference to MyListView, but I don't know what reference type to use in its declaration. Importantly, I also want to capture lvw's ColumnClick event.
I've tried:
Code: Public WithEvents lvw As Object Public WithEvents lvw As Control Public WithEvents lvw As MSComctlLib.ListView.2 Public WithEvents lvw As MSComctlLib.ListView Public WithEvents lvw As CustomControl
and none works when I
Code: set lvw = Forms!MyForm.MyListView
The first try (Object) doesn't even compile. I get the exception "Expected: identifier" The second try (Control) doesn't compile either. I get the exception "Object does not source automation events" The third try (MSComctlLib.ListView.2) doesn't compile and throws the exception "Expected: end of statement" The fourth try (MSComctlLib.ListView) compiles but fails at runtime with the exception "Type mismatch". Using TYPENAME() on the control returns "CustomControl". The fifth try (CustomControl) compiles but fails at runtime with the exception "Object or class does not support the set of events".
I can't believe I'm the first person to have tried to capture an ActiveX Control's events in a separate class.
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
I am trying to add a save/close button to a form that will only close if certain conditions are met and if they arnt then display a message box.I got the basics on how to save and close the form, do a simple if statement and display the message I want but the problem im running into is my subform can have multiple records and of those records each records status is set by 3 different checkboxes. I need all of those records status to be same when the form is closed.
if I do an if statement like
If Me!frmDataform1subform.Form!StatusID.Value = 1 then docmd....
then all it looks at is the first record, how do I get my if statement to look at all the records in the subform?
I have a form - purchase, and a subform purchase detail they are linked with a field "puid"..I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.
I'm trying to get the value of a textbox on one of my subforms and can't seem to get it properly referenced.
I have a main form Main with a tabcontrol having two pages. One page is Timesheets. Timesheets has a subform called Detail_data. On the detail_data subform is the text box Time_In that I want to reference. I can get to controls on the timesheet page but not on the detail_data part.
I've distributed a MS 2003 database to another office 1000km away using 2003 as well. The calendar control doesn't seem to be working. I'm assuming that the MSCAL.oxc isn't referenced. Is there a way I can do this programatically without actually having to talk someone through the referencing process over the phone??
I have a form called "FormA" within that form i have a tab control and one of the pages is called "Page1" inside that page1 i have a subform named "subform1" and finally inside subform1 i have a field named "field"
what would be the correct syntax to reference the value of the field???
been messing with this for awhile and am getting a headace, was hoping someone can help
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
I am trying to limit the number of records displaying in a subform [amendedLoans] by selecting only those records that have the same ClientID as the mainform [Customers] and the same loan number as the subforms parent form [ClientAccounts loan changes].[loanno].
I have no difficulty selecting records just using the clientid but as soon as I add the reference to the subform [loanno] field I get no selection of records. I suspect there is an error in the where clause for [Amended Loans].Loan)=[Forms]![customers]![ClientAccounts loan changes].[Form]![LoanNo]) .
I have built the clause using the expression builder and have had many attempts all ending up with the same result. I'm now at a loss as to where to go from here....
I have a form (named frmAddSession) with a combo box (named Band_Name) and lets say i have it displaying the band "Band A"
I have a query where the criteria is [Forms]![frmAddSession]![Band_Name]. when i run the query the results are blank. but if i type ="Band A" it works just fine.
why won't my reference to the combo box on the form work?
In a series of deductions to try and figure this out, my form only has the one combo box, and my query is based on only one table and only has the one field.
I have an Access 2010 Database frontend to SQL Server 2008 Backend. So far there are 3 Tabs in the Navigation System. The first one is the primary Data Entry form which has a tab control with 3 subforms.
Here's the problem. The database is designed to track potential members based on different types of Ads. The primary Data Entry form holds all of the demographic data for the person that called in response to an ad. The first tab has a subform to track how many times that person has contacted us. The second subform tracks which advertisements the person is contacting us about and the third tracks which Events that person attended. There is no correlation between contacts, Ads and Events.
On the Advertisements we have about 7 different types of Ads we do and each ad can be run multiple times on different days. We want to be able to track which Ad the person contacted us about. The Advertisement subform has a comgo box for the Ad type and a combo box for the ad date. the source for the combo box for the ad date includes the combobox for the adtype as the criteria. That criteria is what's giving me the problem. I've used:
I have main form (frmProcedures) that has a tab control with 2 pages. One of the pages I have a subform that contains a listbox control controlled by a query.The subform is a search and display form. When I open the subform on it own, it opens up fine. However when I try to open up the mainform, it always prompts me to enter the parameter value. I have to atleast hit "Enter" for the form to open up.Even after hitting the enter key, when I try to navigate to that page, I keep getting the parameter prompt.
Here is the query that populates the listbox control. And the parameter that is prompted is Forms!frmSearchMulti!SrchText that you see in the query below
SELECT Procedures.ProcedureName, Procedures.BusinessLine, Procedures.Manager, Procedures.ApprovalDate, Procedures.NextApprovalDateFROM ProceduresWHERE (((Procedures.ProcedureName) Like "*" & forms!frmSearchMulti! SrchText & "*")) Or (((Procedures.BusinessLine) Like "*" & forms!frmSearchMulti!SrchText & "*")) Or (((Procedures.Manager) Like "*" & forms!frmSearchMulti!SrchText & "*"));
I do have a searchbox that when updated with search string issues the requery to update the values in the listbox.
I have a main form "KZ_SEARCH" and within this I have a subform which is loaded when a button is pressed and is populated with query results (based on text entered in a text box). The subform is called "KZ NOTICES Query subform". That all works fine and the query modifies based on the text entered every time the button is pressed. The next stage is that when the subform is loaded I want the user to be able to select a line from the subform and to jump to that record in another form (which also needs to be opened from that selection). The reference for the record to select in the newly opened form would be the first column of the selected line highlighted in the subform (control name "KZ NUMBER").
When I run the following code in a module -- to change the value of a label in a subform ("Employees Trained List subform" is a subform of "Contractors") I get an error that says it can't find the form (ie. Contractors). Also, if I eliminate "[Contractors]" the error says it can't find "Employees Trained List subform": Forms![Contractors]![Employees Trained List subform].Form!lblThisYearTrainedDate.Caption = Year(Now()) + 1 & " Trained" Any ideas on how to fix? Thanks
This code checks to see if the last three characters on a ControlSource.Tag property are "Req" and if they are, tests if it's null or = 0 and if it is then it outputs a msg box stating that the control.ControlSource is required. It does it for every control on the main form.
This has worked.
Now, i need to allow for multiple subforms to be located on the main form along with their controls being tested as well. I want to start another loop for each time the conrol on the mainform is a subform.
I have that done so far by using "If TypeOf ctl Is SubForm Then..."
This is the part i need help with. I want to search all of the controls on the subform when it is a subform. I need to search all of the controls on the subform then, and i'm not quite sure of the syntax.
I've tried various somethings like SUBFORMCONTROL.Controls but that hasn't seemed to work...
If someone could help me, that would be wonderful!
Newbie question: I have a datasheet view subform (called "UpdateProjectsSubform") in an "empty" form. I set it up this way because I was unable to get the Switchboard to open the subform in datasheet view.
I would like to have a command button to set filters in the subform. As I cannot have a button in datasheet view, I think my best option is to place a button on the master form that performs the command on the subform. I have tried every which way, but can't seem to get the correct syntax to reference the subform. I have searched this forum as well as other references with no success. Any ideas?
frm_process_ppl (single form) which has the datasource as qry_process_ppl_2 on this form is a subform (continuous) frm_process_ppl_orig
with the same data source linked by [ALINK].I want to run an append query when I click on one of the controls of the subform.On the subform there is a uniqueID called [LNK]
In the append query I have the following criteria for the LNK field [Forms]![frm_process_ppl]![frm_process_ppl2].[LNK]..However it is not working.I think my criteria is wrong.
(This worked fine with just one form but I have had to add an additional subform and the original form was continuous. I cannot add a subform to a continuous form so I decided to creat a single form to house the continuous form) I just cant reference what Im clicking in my query !!
