Modules & VBA :: Referring To Tab Control On Sub Form
Jun 9, 2013
I have the following setup:
A main form - named [formMain]
On [formMain] I have a tab control - named [tabMain]
[tabMain] has 3 pages named [pageOne], [pageTwo] and [pageThree].
On [pageThree] I have created a subform named [formSub].
On [formSub] there is a tab control named [tabSub].
Now what I would like is to make [tabSub] (in)visible if the user switches from [pageOne] or [pageTwo] to [pageThree].
Code:
Sub tabMain_Change()
How do I refer to [tabSub] from within this subroutine? I am trying something like:
I have a form with a subform which resides in a tabbed control. In that subform, I have a textbox in the footer that sums values in the detail section of the subform.
I have a textbox on my main form that should display the same data that is in my subform footer textbox, but it is giving me the #name? error.
This is the expression in my subform footer textbox (which returns the correct result):
TxtSubtotalHQPCalc - =Sum([ActualHQPCalc])
And the expression in my main form textbox (which returns #name?):
I have verified that my subform name and textbox names are all accurate. This is very frustrating because I am using this exact same method in a different database with no problems!
I believe I did this before awhile ago but for some reason I keep getting an error.
I have a Mainform (frmMain) that has a Subform (frmSub). On frmSub I have 2 comboxes (strCom1 & strCom2) one is set to invisible (strCom2.Visible = No).
So using the "On Open Event" of frmMain I want make strCom2 visible if strCom1 = "Read Only". Ofcourse I will also need to place the vba on the On After Update event. Below is what I have so far but doesnt work.
I get Run-time Error 2427 "You enetered an experssion that has no value"
Code:
Private Sub Form_Open(Cancel As Integer) If Me!frmSub.Form!strCom1.Value = "Read Only" Then Me!frmSub.Form!strCom2.Visible = True Else Me!frmSub.Form!strCom2.Visible = False End If End Sub
I know how to refer to a control on a form by using [forms]![formname]![controlname], but can anyone help with referring to a subform.
I have a subform called subPrograms, on which is a combo box whose contents need to be filtered depending on the selection from another combo box on the main form.
The combo box on the main form is cboDivisions. The combo box on the subform is cboPurchasers Main form is called frmMain Subform is called subPrograms
The user is supposed to select a Division, and then depending on that selection the contents of cboPurchasers will change to only show those that are within that same division.
I would also like to know how to update the cboPurchasers when the cboDivision has been selected? Can someon help?
I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click() ' UserInit is global variable Me!Notes.SetFocus Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _ " -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String) 'varInitials, varFormName, varMemoName are global variables Forms!varFormName.Controls!varMemoName.SetFocus ' Error here 'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.
my record set is
Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)
The calculated filed I have added in is Round(Price/7,2) AS Daily_rate
If I let it run just with this it runs fine.
and then I try to use this calculated field:
Do Until rstPrice.EOF Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37 rstPrice.MoveNext Loop rstPrice.Close ' Tidy up Set rstPrice = Nothing
They it stops running and it all seems to be because of the Daily_rate field
I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1
Start with summary --================ Part Qty 111000 2 222000 3
End result all Qty=1 --================ Part Qty 111000 1 111000 1 222000 1 222000 1 222000 1 --================
I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.
For n = 1 To [Forms]![MainScreen]![Text7] DoCmd.OpenQuery "qry_Update_Qty" 'DoCmd.GoToRecord , , acNewRec Next n
How can I refer to a form by using its index number? Also how can I retrieve an index number of a form?
(I'm opening multiple instances of the same form and read that the only way to refer to those instances is by using their index number. I found some solutions that use hWnd as well but I can't get any further support on it as far as referring to a form by using the window handle so I'm trying to capture and then use the index number)
The [ItemsID] is actually on the form as a txt box. but i dont really need it there for any other purpose other than the vba above.
In the form i have a field that is in the form as a txt box that has a relationship to the ItemsID. i have attached a image of the relationship.
The field that is in the form is tblOrdersItems.OrdersItemsID and i also have tblItems.Items. so rather than adding another meaningless txt box to the form(ItemsID) i would like to be able to use the relationships to get the correct ItemID ...
I have a form named frmMain that contains five buttons that lead to subforms, one of which is named frmDeceased.
In frmDeceased, I needed to create a combo box that relied on selections from the combo box before it. I was able to do this successfully in frmDeceased, but then today when I opened the database and opened frmMain, I realized that the query fails when frmDeceased is opened via frmMain. It works perfectly if I just open frmDeceased directly.
The first combo box is ROHE and the second is Iwi (which is dependent on the Rohe selected).
The query for Iwi has this criteria: Forms!frmDeceased!cbo_ROHE
When I open frmDeceased through frmMain, I get the 'enter parameter value' message showing "Forms!frmDeceased!cbo_ROHE".
I tried changing this query to: Forms!frmMain!frmDeceased!cbo_ROHE, but it didn't work.
I have an Access 2010 application and I'm having trouble referring to properties in my subform withon a Navigation form. Based on a selection in a Combo Box, I need to change the filter on the subform. The relevant forms are:
Main Navigation Form = frmAdminNav "Standard" Navigation Subform = NavigationSubForm My Subform = frmShowInventory
How do I refer to the Filter property on frmShowInventory?
Any way to list all currently opened Access databases? It feels like this must be possible by referring to the databases collection, but I just don't know how.
Also, is it possible to refer to controls on a form in one open database from code in another database? (and obviously if so, how?)
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.
Go to frmInvoice, and you'll see a Net Total box (txtNetTotal) . It's control source is linked to a textbox in the subform fsubInvoiceDetails2 called txtStocktotal. It basically just pulls up all the costs associated with that InvoiceID.
The reference mechanism is as follows: =[fsubInvoiceDetails2].[Form]![txtStockTotal].Now...sometimes this works and sometimes it doesn't! Sometimes i've had to use: =forms!fsubInvoiceDetails2!txtStockTotal.
It seems to be very temperamental at times and i'm not fully confident if this can be explained.By way of note, I use express builder normally to input these statements: I go to Forms > ALL FORMS > fsubInvoiceDetails2 > txtstocktotal.
I need to duplicate a number of controls on a form. They are all acCommandButton types, but they have different images, sizes etc.
Anyway, my thought process was if I have a "template" acCommandButton then when I create my new controls if I could use this template then the code I have hanging off them (I have a function being called on the onClick event, borders are set to hairline, themes turned off and various other settings) would be copied. Save me trying to set them all individually.
So, my question is, can I "copy" a control in form design through VBA. Once I create the copy of the control I can then change the couple of properties I need to and "hey presto" I have skipped a load of settings that I don't need to set.
At the end of the process when I rename my form I can delete my template control and I have a working form.
How I might use a value selected by a user on a database opening menu (which remains open), as a default value for records created programmatically in other tables?
I wonder if I need to write a function to repeat the value - but I cannot see how to use a form value outside of the form's own code. Some of my existing code inserts values into tables using SQL converted into VBA and I do not really want to start fiddling with that - I would rather for now use the default value of the table for the field.
My variable which will change depending upon which set of records a user is working on is a string "FullAccession".
When a user creates records in a table called tblGroups, I need the string "FullAccession" to be the default value in the tblGroups.FullAccession field. A unique integer in the tblGroups is "GroupNo". "GroupNo" and "FullAccession" are joined in a unique index for tblGroups. There is a separate PK autonumber.
My scenario is that I am using a one time table that is a copy of my real transaction table (called GLAcTranLine) to enter general ledger transactions. Both my form and my sub-form are unbound. The form is called GLFI50 and the sub form is called GLFI50TranSub on disk and inside the main form. That is, both the sub form and its description inside the form (in the label that appears when I build the sub form with the wizard) bear that name.
So in the load for the main form I carry out these job steps.
1. I take a "No Data" copy of the transaction table GLAcTranLine, to get the structure. That works fine. The name I give to it is prefaced with GLFI50 and ends with a random number. That works fine too.
2. I insert a blank record into the new table. That also works fine.
3. Assign the control source to the Sub form and to its individual objects. That just dies in a crumpled heap. Neither the form not the individual columns code works.
The error I get (in both cases) is the Run-time error '2465' Application-defined or object-defined error.
I believe this means that it just cannot work out what idiotic error I have made. The code all passes the compiler test.
Here is the SUB.
Private Sub Form_Load()
'STEP 1. I take a "no data" copy of Create the Work Table and assign it as a control source to the sub form '================================================= ======================
'(Copies the structure of the source table to a new target one. It isn't a temporary table so I need to 'find a way of assigning a temporary name and then using that name on this prog. 'The True on the end says "Do not copy the data)"
'GLFI50Work is the Global variable that holds the temporary table name. Dim RandomInt As Single
I am trying to use the string value from a form control as the criteria for a query. I first need to replace the "," in the string with "AND".
So far I have a module using the Replace function, but it doesn't seem to be working. I am not sure I can reference the string inside the form control directly....if I can, I might have a syntax error.
Here is my code so far:
Dim result As String result = Replace("Forms!Processing!Dataset_Acreage_Query", ",", "AND")
I am not experienced and having trouble finding the search terms to answer this question.
I have a from that has information icons that opens a separate form to support users make the right choice on a form. With each information form users are able to choose "Yes" or "No" and then it returns them back to the form they started. I am having trouble setting the focus to the control the user original left the form from.
Form A Control 1 Control 2 Control 3
Form B Information for Control 1
Form C Information for Control 2
Form D Information for Control 3
For Forms B - C I would like the form to close, open Form A and then set focus to the control that the form was providing information for.For example, if you are on Form C and you make a decision, I want Form C to close, Form A to open and focus to be set to Control 2.
Code: Private Sub image5_Click() Dim sWHERE As String Criminal_Background.Value = "Yes"
I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.
I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).
I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.
Things I have tried to no avail: Putting a Iif statement in the query criteria: gives an error that criteria is too complex Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run
I've got a form, user inputs a whole bunch of data and the data is stored into 2 different tables (using a form and subform), however i also need to copy a few fields from the subform into another table (as a new record) the table i intend to insert this data into (tblAdjustments) has four fields (that im interested in) Type, Reason, Quantity and Product (not the exact names) Type and reason are both just straight strings and will be the same each time, regardless of what the other two variables are, so i need type to equal "Finished Goods" and Reason to equal Produced further to that, the subfrom has 2 fields, batchPackedQTY (which will be quantity in the new table) and fgID (product in the new table
so i need an insert into statement that does this, ive tried INSERT INTO tblAdjustment (adjType, adjReason, fgID, adjQTY) ("Finished Goods", "Produced", Forms!SubFormBatches.fgID, Forms!SubformBatches.batchPackedQTY)
Basically I have a continuous form with each record having a textbox and a checkbox. There can only be one checkbox ticked per record but what I want to do is to stop the other checkboxes from being ticked if one is already ticked.
Is there a way to set the control source for an entire form through VBA? I have a database that was set up just as an archive file, not intending to have any forms or reports in it. It was simply to house tables containing data from previous years. My main database just copies the table over to the archive file and adds the year to the table name. I have just been told that the users would like a form and a series of reports set up in this database.
I would like to set the main form up so that before it will allow them to do anything, they have to enter the year of the data they are looking for. After the year has been entered, I would like to adjust the control source of the form to reflect the appropriate table. In other words, when a user opens the archived database, he or she will see a field for the year an nothing else. Once they enter the year they are looking for, the form will populate with data from the corresponding table (the table names look like this - tblPM_Completed_2013). Is this possible?
Essentially I have a form_A with several tab's and then one list control box in each of those tabs. At present if you double click on any item within the control boxes your taken to another form_B with info about that item and when you close that from down again if refreshes form_A.
Is it possible to only refresh the specific list control box that is active instead of refreshing the whole form ?
I'm using form with subform. Main form header contain a combobox. I need to check the combobox is null or not before update a subform filed. If combobox is null then its back to Main form combobox from subform. The code below that I'm trying to:
Code: Private Sub AssignTo_BeforeUpdate(Cancel As Integer) If IsNull(Forms!frmDepartmentReview!cboDepartment) Then MsgBox "You must select Department first", vbInformation Forms![frmDepartmentReview]![cboDepartment].SetFocus End If End Sub
Then I'm getting 2110 run time error
Access can't move the focus to the control cboDepartment.
