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?
Thanks in advance,
Recall.
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 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 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,
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:
Me.[Contributor Subform].SetFocus
DoCmd.GoToRecord , , acNewRec
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?
Thanks!
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.
View 1 Replies View RelatedHow do i create an unbound subform on an unbound form? I want to bring data in from another table/query if the data exists.
I am pullind residential property data, if there are recent sales i would like to pull that too.
I know this is a pretty broad question, but any help would be appreiciated, exmaples would be awsome to. Thanks.
Hi Friends,
I want to create unbound subform. As per your exprience can some one help me. subform must be Datasheet veiw. Thanks
Hi guys,
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.
Any way to do this?
Hello,
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:
Code : Me!Search_blank.Form!MeasureForm_sf1.Form.GetLastVal2
and I got RunTime error 2465, cannot find 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.
Working with Access 2000
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!!!
Sincerely,
Rody
Ps. I created this using MS ACCESS 2003.
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 ?
View 4 Replies View RelatedI 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.
View 1 Replies View RelatedI 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.
[URL]
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?
How to update unbound textbox on main form from unbound textbox in subform afterupdate.
that is when amount paid is updated it automatically updates total paid, balance etc.
My database has Main form and a Sub form. On main form i place one unbound text box named investigations. In subform of which datasheet there is a column named TestCode. I want unbound text box (Investigations) to display row values which selected in a column (TestCode) of subform.for example:Investigation field should display "CBC,HB,ALP".
View 5 Replies View RelatedTo make it simple, I have a list of contact names and their email. I want to have a form with no control source and have 2 simple drop down boxes for their Name and their Email. What I would like to happen is the user choose a name from the drop down in field 1 and then the field below auto-populate their email in field 2. I understand I can use conditional IF/THEN and list out each email, but the contact list is ever changing, so I want to first drop down to link to the TblContacts, have the user pick the name from the list and then have the 2nd field autopopulate from that same TblContacts with their email respective to the Name entered in field 1. My form has two field [Traveler] and [Email]...The tblContacts two fields are [Name] and [Email]
Code:
DLookup("[Email]", "tblContact", "[Name] = [frmEmail]![Traveler]")
I am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.
I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.
Ive been looking through the posts to see the merits of using bound and unbound forms. I have the following scenario and think that a unbound form would be best solution, what does anyone else think?
I have carried out normalisation of the system and have ended up with two identical tables called Broad themes, one of these tables relates to a documents theme, one to a organisations theme. Organisations and documents are both directly related in the table structure making it impossible to just use one Broad themes table (Circular).
I want to give the user an option form to enter the relevant criteria into unbound text boxes then select using an option group which table to enter information into or option to insert into both tables.
Is this where a unbound form would be most appropriate?