Unbound Subform Counts Toward Two Subform Depth Limit?
Jul 26, 2006
I didn't know that Access is supposed to only have 2 subform deep. On my form, I have three subform deep. However, the master form is unbound, not linked to subforms, and nothing more than a container for tab pages which contains their own subforms and has no code directly associated with data. There is a code to move subform to new record when tabs changes, but that's all.
Does that mean there is really only two subforms deep from the topmost form on the tab page or the subform depth has been exceeded whether the master form has no role in data transaction?
I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code: Private Sub Accounting_Unit_Enter() 'find where AUs match. only works for first Subform2 record If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then msgbox "Match" End If End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
I have three tables; tblPallet PalletId DateDespatched
TblBox BoxId PalletID
TblKitting KITID BoxID Model
Process The users will be adding a kitting item to a box. Only 10 items are allowed in a box (no more no less) and the box needs to have its own Id Number and then the box is placed on a pallet. 40 boxes are allowed on a pallet (again no more no less) and the pallet is despatched. Then a new pallet is set up.
I am trying to think of the easiest way to do this as I am a little new to Access. Any tips in the right direction would be greatly appreciated. I thought about a series of subforms from a main pallet form but am not sure how to introduce a limit to the number of records in a subform. Perhaps Ive bitten off more than I can chew (I hope not)
I can't get this to work! I have a module for it and I think (?) the correct code.
'Limit Records On Subform. OnCurrentEvent of Main Form ' Main form is frmTimeCards LimitRecords Me.frmPayrollEntry.Form, 7 ' Allow at most 7 records Scenario: Main Form/Subform Subform is a Continuous form. Enter Payroll via Subform Via Combo, select a Employee on Subform Enter Hours Finish,Select a New Employee Using CarryOver for Employee
Problem: Can't add new Record for a new Employee.Because of the Limit Records Code.?
Is it possible to hide previously added records which have been added to a subform? For example a user adds their selections and closes the database. They then re-open the database and rather than displaying their previous selection - its blank ready to start again?
I am looking for a way to limit the number of rows that are added to the subform of a main form. Is there anyway that the allowable number of rows be defined using a field on the main form.
I am currently trying to figure out how to limit the entries into a nested sub form to just one.
for example im making wine collection database, I have a PURCHASE table, a WINE table containing the FK of the PURCHASE PK, and an INVENTORY contain the FK of the WINE PK.
a purchase can contain many wines, but one wine can only be in one inventory location.
I am trying to add all this information with one form. I fill out the purchase details, enter the info of X bottles of wine were in the purchase, and then with one of those wines selected in their subform, i enter the inventory details for that wine. I go select the next wine in the first subform and the inventory nested subform doesnt allow another entry.
I have this code loaded into the On Current event for the main form:
Private Sub Form_Current() With Me![INVENTORY Subform].Form If .Recordset.RecordCount = 0 Then .AllowAdditions = True
I used the sample of the Unbound Form from this form for my application and it works perfectly. Now I try to have 2 subforms in a Main form(Unbound form) and want to use the idea of unbound form to design these two subform. But I don't know how to start.
Any suggestion to lead would be appreciated. Thanks.
My Main looks similarly to the Order form in NorthWind db sample which has 2 subform.
I've got a main form with a subform on it. I am in the process of converting all the forms from bound to unbound. Currently the subform is populated by a link from Employee ID. How do I link the subform to the main form when the main and subforms become unbound?
A couple of questions about an unbound subform.....
1) I have an "Add New" form with a large number of controls. It has an unbound subform with two columns (a person's name and their role - both combo boxes). It's unbound because I don't want to save the data until they save the new main record.
With the unbound subform, I get one blank row. When I add data to both controls, I don't get a new blank row to allow me to enter another. I've tried adding a "New Contributor" button with this in the event:
It sets the focus properly, but doesn't add a new row.
Recordset type is Dynaset. Allow edit, deletion and addition are set to yes.
Any ideas?
2) I'll be getting the entered data and saving it with a SQL insert statement. What is the syntax to reference a particular control in a particular row?
Since I only have one row currently, I'm able to get it this way:
Me.[Contributor Subform].Controls![contributor]
Once the first question in this post is solved, I hope to have muliple rows. How do I reference them?
I have an unbound text box, that is determined from a users Environ details, and i would like to link this to a subform the subform is made from a cross tab query and basically i only want the subform to show the users details which is in the text box.
Something that's been bothering me. I have a unbound subform which acts as a placeholder for other subforms rotating on it (by changing the SourceObject), that way all the relevant subforms appear in the right place when required. It works great except, it's this great big white box and there's no option to change the background colour anywhere. I would like it to be either transparent or have an option to change it to the same colour as my main form it sits on.
Please see the attached images (screen shots from my db).
I'll start by explaining the function of this form. Invoices are raised through a different area of the system - they then appear in the outstanding invoice list in my payment processing screen while awaiting payment. When a payment is received the user creates a payment using the controls at the top of the form. When created - payments will appear in a list box at the bottom of the screen. I then want the user to be able to highlight a payment from the list and type the amount to allocate against each line in the outstanding invoices subform. Then (once happy with the allocation) press the "Allocate" button which will cycle through the allocations the user has made inserting rows into my Transaction table - allocating the payment against multiple invoices.
The fields displayed in the outstanding invoices subform are selected in a query which calculates the amount still outstanding. In trying to illustrate my aim I've added an unbound text box to the subform (the column Allocate). What I'd like to be able to do is type a different amount against each outstanding invoice. I understand this is not the way that unbound controls work in continuous forms - i.e. that they are multiple instances of the same control hence when I type 100 against the first item in the list it appears against every item in the list. I can't use a bound control because the query is not updateable - and in any case, the entry won’t exist in the transaction table until it has been inserted anyway.
I'm pretty competent with Access and VBA but haven't really explored recordsets - I'm usually able to achieve what I want by binding my forms to stored queries. This, however, is a bit more of a complex problem than I'm used to dealing with.
So, to the point then. I guess my first question is - is it possible to achieve this? I'm sure it must be. If so please can someone try to shed some light?
Secondly, I have an idea but have absolutely no idea of how to implement it. I seems to me what I would need to do is build a temporary recordset for the form and add a field to it i.e. the "Allocate" field I've been talking about. The user would then be able to manipulate this recordset because the form wouldn't be directly accessing a query. Nor would there be any unbound controls on the form. Once the user had typed in all of the allocation figures they are happy with they would be stored in my temp recordset. They could then hit the "Allocate" button which would cycle through the recordset inserting rows into my Transaction table (as long as various validation rules were passed).
Does this sound possible? If so can anyone give me any help or point me in the direction of any material they know of that might be of any help?
Thanks in advance for your help.
To give a flavour of how I originally built this form I've attached another image - PaymentProcessingOld.gif. It's a similar kind of setup. Payments are created at the top of the screen and appear in the list box bottom left. The outstanding invoice subform serves only as a point of reference. The user actually allocates payments by selecting the item from a combo box and typing the amount. This builds the transaction I've talked about above. It's quite straight forward when there are only a few payments and allocations, but in practice payments are usually large and split across up to 100 different invoice lines. That makes it a very tedious task.
I have form named SearchForm and there is unbound subform named Search_blank. Depending on button in menu is used source object of Search_blank. One of the source objects is form named MeasureForm and it has subforms named MeasureForm_sf1 and MeasureForm_sf2. How can I reference public sub in MeasureForm_sf1 from SearchForm? How can I reference public sub in MeasureForm_sf1 form MeasureForm_sf2?
There might be a problem with unbound form. I tried reference public sub named GetLastVal from SearchForm that is in MeasureForm:
Code : Me!Search_blank.Form.GetLastVal
and it was working. Than I tried reference public sub named GetLastVal2 from SearchForm that is in MeasureForm_sf1:
As the unbound parent list box record selection is changed, I want the sub form to refresh. Do I put code in the Subform Current Record event?
Master Form Name: frmsr_NewWellEntry Unbound Listbox - when record selected the primary key populates txtNavWellID (unbound) on parent form
subform Container fsubsrNavSHLBHL Link Master Fields =Forms![frmsr_NewWellEntry].[txtNavWellID]
The read-only form in the subform Record Source is something like: select * from vsrNavigatorSHLBHL where Well_ID =90243..Key The Form used as the subform above will be re-used in multiple parent forms. The parent form data is form SQL Server, the subform from Oracle.
I have a Continuous subform based on a query based on few linked tables.One of those tables has a Yes/No field which shows in the subform.
I want to be able to change the status of this field for each record independently, so I placed an unbound check box in the subform Detail section and a Command button on the subform Header section. However when I click one record check box, all of them change, I did not expect that. How do I move forward to get this done?
I have a main form (start_protocol) with an unbound subform (placeholderform).
Using a button in mainform I open a search form (search_protocol) inside the unbound form, by the code: ------------------------------------------------------------------- [placeholderform1].Visible = False [placeholderform].Visible = True Me.placeholderform.SourceObject = "SEARCH_PROTOCOL" -------------------------------------------------------------------- The search form (has a run query button) is build from a query named: search_protocol.
I want to be able, as soon as I pass the button, to have the results (from the query) inside the unbound subform, of the main form (Start_protocol).
I have a form with a textbox which when users enter a unique number(barcode) I want to run a query which pics up the barcode number, checks against the product id and fills the subform with the name of the product and price.
The basic details of the product table is like:
product id (Autonumber) prdoduct name (text) price barcodeId (number)
The subform where i want the result to go is the order details fields product name and price.
I am trying to figure out how to do this but my mind keeps going blank, its been a long while since i used ms access.
Pls. help!!!, the attached document is the application I want to programmatically control the data entry:
1.) Parent Form is unbounded fields, and set a record source in my table "BidTabTransaction". If I want to retrieve record, I just populate the table by querydef and sql into recordset and put all the table fields into form fields. The same thing if I want to save the record, and edit the record in the form.
2.) My problem is the child form. I set a record source in my table "BidTab Details". I set the child form to allowaddition=Yes; data entry=No;Allowdeletion=No;AllowEdits=No then it just simply display the records which is equal to Bid Tab No. because it is bounded the form fields into record source fields of "BidTabDetails" Table.
How could I retrieve a record from parent form and display the details in the child form in both unbounded form fields? (FrmBidTabTransaction & FrmBidTabDetails)
I would be grateful if someone out there attends immediately with my problem because I am in deadlock deadline to submit at least a partial running application. Thank you for your kindness...
Will be waiting. You can email me at jrb_ph@yahoo.com. God Bless!!!
have set up a navigation form which contains 5 unbound subforms that are made visible or not visible thru the on click event of command buttons on the main form, the subforms have a command button with an embedded macro that searches for a record based on what has been keyed to an unbound text box on the subform, this works fine when the sub forms are opened independently of the navigation form but when the search is done from within the main form, the error message is, "....cannot find the referenced from frmAQIFAList" subform, I tried referencing the main form name infront of the subform name but this also doesn't work, perhaps this would be better with code ? or is it because the subforms are unbound ?
I have a listbox "lbxMP" that holds names of forms and a subform "ctrlMP" that opens selected form (ctrlMP.sourceobject = lbxMP). I also have some various master cbx that control criteria in each form. I am trying to create a button that will not only clear/null all the cbx values (which works fine), but I am having a hard time telling me.ctrlMP to go back to null or unbound (i want it to blank out the subform to make it look fresh). I have tried requry and also me.ctrlMP = null and ctrlMP=unbound with no luck.
I have placed an unbound subform on a form. I have the following code which loads in different datasheet to the subform:
'Loads in NewQuery Me.data.SourceObject = "query.NewQuery"
'Loads in NewQuery2 Me.data.SourceObject = "query.NewQuery2"
etc.... this works fine to show these queries but i want to know how to reference the fields on the datasheet.
for example one of the fields in NewQuery is "ID"... When clicked i want to run code... normally on bound subform i could go for example ID_Click()... msgbox(me.ID)
how can i reference these other fields on unbound???
The unbound Parent form has a listbox (SQL Server) , the selection updates the SubForm with a primary key ID. The Subform is a ReadOnly view from an Oracle Linked table. It populates with a Select * From Oracleview where ID = listboxID The code is at the link shown below update: The subform uses textboxes linked to the read-only record.
The main form can populate, no problem. However the SubForm data source is either populated OR is Null.
If it is Null, the subform stays blank. Two Objectives:
1. Set a boolean variable flag in the parent (main form) that subform record exist or that it is null. e.g. Parent obtain recordset count property from Sub form 2. Display the subform with blanks.
I have a tabbed form from which the user can select a contact's record from a subform on the first tab, click a edit command button, and the unbound text boxes on the top of that tab populate. The user can then click the second tab with employment history which has blank unbound text boxes and another subform which is linked by the contact id to the first tab.
The user can select a record in the second tab, click a command button and the text boxes populate no problem. The problem comes when the user changes the contact on the first tab, and then tries to edit a record on the second tab. Then I get a run-time error '-2147352567 (80020009)' saying the value you entered isn't valid for this field. Why it would work the first time by not the second?
