Creating A Conditional Subform
Jul 19, 2007
Hello, I want to make a form with a conditional subform so that when selecting a specific value in one field, a specific subform appears.
I understand that it needs to go in the 'AfterUpdate' section of the field properties but I am struggling with coding it.
On the main form I have a Combo Box called 'Body System' (from the table '_Study') which can either be 'CV', 'PUL', 'Clinical' or 'CNS'.
When it is 'CV' I want to add the the subform 'Results CV'
When it is 'PUL' - the subform 'Results Pul'
When it is 'CNS' - the subform 'Results CNS'
When it is 'Clinical' - the subform 'Results Clinical'
I would be very grateful if somebody could help me with the coding that I need to be able to do this!
Thanks very much in advance for any help!
Steve
View Replies
ADVERTISEMENT
Jul 19, 2007
Hello, I want to make a form with a conditional subform so that when selecting a specific value in one field, a specific subform appears.
I understand that it needs to go in the 'AfterUpdate' section of the field properties but I am struggling with coding it.
On the main form I have a Combo Box called 'Body System' (from the table '_Study') which can either be 'CV', 'PUL', 'Clinical' or 'CNS'.
When it is 'CV' I want to add the the subform 'Results CV'
When it is 'PUL' - the subform 'Results Pul'
When it is 'CNS' - the subform 'Results CNS'
When it is 'Clinical' - the subform 'Results Clinical'
I would be very grateful if somebody could help me with the coding that I need to be able to do this!
Thanks very much in advance for any help!
Steve
View 1 Replies
View Related
Apr 2, 2012
I use an access db that stores our customer info on a mssql backend. I need to be able to create a mail merge based on a customers renewal date. Now I do know how to set parameters in a query but is there a way I can use a access report to enter a month and day they are set to renew then have it create the mail merge based on their info?
The Layout is fairly simple just a renewal notice with what they have on their plan.
View 6 Replies
View Related
Nov 2, 2006
I have a form named frmtestpkg which has a subform named pkgsubform. The control the subform is in is called pkgsubform1. I have written a macro which works very nicely to hide or show the field [Days2process] if I attach it to "on current" on pkgsubform and open pkgsubform as a form. If I try to open pkgsubform as a subform I get a message stating "The object you referenced in the Visual Basic procedure as an ole object isn't an ole object."
How do I change my macro to refer to the form and controls as a subform?
my Macro looks like this:
condition: [Forms]![Pkgsubform].[CrPkgStatus]<>"Accepted"
Action: SetValue
Item: [Forms]![pkgsubform]![Days2Process].[visible]
Expression: No
condition: [Forms]![Pkgsubform].[CrPkgStatus]="Accepted"
Action: SetValue
Item: [Forms]![pkgsubform]![Days2Process].[visible]
Expression: Yes
View 2 Replies
View Related
Jan 4, 2015
I have a continuous subform on a form. I have applied conditional formatting with expression is.. so that the formatting on one object (field on the subform) depends on whether there is anything in another field on the same subform named "Remarks".
My problem is that I this is not working to my expectations. First of all when the form with the subform is loaded the conditional formatting does not appear immediately. One has to run over the fields with the mouse with the hope that the conditional formatting appears.
Secondly the conditional formatting is not working well as they should.
View 5 Replies
View Related
Apr 10, 2015
I wanted to use a conditional formating when in a subform the field "Field_Name" has the value "Case Status New" and then the field "New_Value" should become orange color.
I guess it can be put togheter like a formula (expression) in the Edit Formating Rule.
View 4 Replies
View Related
Nov 2, 2014
I have a continuous form that is used as a sub form, and one of the fields is a check box which ideally I would like to set not visible; however, I would like the font colour of the other fields to turn red if this check box has a value of false.
View 9 Replies
View Related
Jun 2, 2013
I have a continuous subform with conditional formatting whereby when I select a record the row is highlighted.
It all works fine except that I have to requery the subform to enable the conditional formatting to work.
The Repaint method does not work on the subform.
View 1 Replies
View Related
Aug 28, 2013
I have a continuous subform with multiple controls (textboxes) per row.
I'm trying to set up conditional formatting such that certain textboxes are highlighted based on comparisons with other textboxes within the same row/record.
What's bothering me is, I have managed it for one, but struggling to do it for others!
For example - I want to highlight 'similar' records (i.e. possible duplications) - and have managed this by adding a textbox across the length of the row and applying the following CF expression :
Quote:
DCount("[ID]","[tblMyTable]","[ID] <> " & "txtID" & " And [Field1]=" & "txtField1" & " And [Field2]=" & "txtField2" & " And [Field3]=" & "txtField3" & " And [Field4]=" & "txtField4" & " And [Field5]=" & "txtField5")>0
Works brilliantly - highlights all records where fields 1 thru 5 hold common values.
Now I want to highlight where two fields within the same record are different (i.e. they are driven by different data but should hold the same value)
Am trying this :
Quote:
"txtField3"<>"txtField4"
But it's not working - it's highlighting pairs of fields even though they do hold the same value.
Even tried switching it for a DLookup (which seems like doubling up on effort, but just to see if it worked)
Quote:
DLookUp("[Field3]","[tblMyTable]","[ID]=" & "txtID")<>DLookUp("[Field4]","[tblMyTable]","[ID]=" & "txtID")
But no dice? I can't figure out why the same basic logic which works so well for the first CF, doesn't hold for the second one.
View 5 Replies
View Related
May 15, 2014
I have a continuous subform with a fairly complex snapshot query as its record source. I have controls on the main form to allow criteria parameters for the query and a command button to requery the subform based on the selected criteria. I also have conditional formatting on a number of the continuous controls in the subform (the conditions are fairly straightforward, i.e. highlight if null, true/false etc)
When the subform is (re)queried, it seems to repaint several times (3 or 4 although it appears arbitrary to me) before it eventually settles. And it really louses up the aesthetics as controls flicker between colours; sometimes I even lose the background colour on parts of the detail section, or controls disappear altogether. And this continues as I scroll down the subform (or even click anywhere on it)
I don't mind that the query itself takes some time to run - of the order of a few seconds each time - but I'd prefer for the screen to wait until the query is completed, and all of the CF applied, before refreshing itself, instead of 'sweeping' top to bottom repeatedly. It's a snapshot query so once it's run, there should be no changes to the dataset.
I've tried Application.Echo but it has no effect whatsoever.
View 6 Replies
View Related
Nov 25, 2005
hi guys this is my problem : might be a bit long but here it goes!
tbl 1:
ServiceRecord_ID
veh_no
date in
date out
status
tbl2:
Employee_id
ServiceRecord_ID
task date
action taken
spare parts required
Part_Id
tbl3:
PO_no
part_ID
price
quantity
now i want to create a form to input the datas into tbl one
the layout of the form looks like this
ServiceRecord_ID
vehicle no
date in date out
status
subform:
task history
employee_id task date parts action taken parts required
in the subform , the end user can just input the task history into the subform.
So what are teh steps that i must take>?
View 1 Replies
View Related
Jun 19, 2006
Hi all
Simple problem I'm hoping someone can help with. I have a form that is populated using a query that looks up a table called tblFleet. I can search the query results using a simple search function I downloaded from this site(See previous thread). What I would like to do now is display results from a linked table, tblVCR that match the fleet vehicle I select on the main form. I tried inserting a subform but could not get it to return any results. Do I need to create a separate query to return the reults into the subform or is there code that I need to add.
As always, any help appreciated
Regards
Craig
View 1 Replies
View Related
Jun 19, 2006
Hi all
Simple problem I'm hoping someone can help with. I have a form that is populated using a query that looks up a table called tblFleet. I can search the query results using a simple search function I downloaded from this site(See previous thread). What I would like to do now is display results from a linked table, tblVCR that match the fleet vehicle I select on the main form. I tried inserting a subform but could not get it to return any results. Do I need to create a separate query to return the reults into the subform or is there code that I need to add.
As always, any help appreciated
Regards
Craig
View 1 Replies
View Related
Nov 19, 2004
I desperately need some help with this one.
I have a Form/Subform/subSubform setup, but with all those stacked forms, I don't have room for all the fields on the screen.
How can I open a form (the subSubform) in its own window, but have it behave like a subSubform should?
Each relationship is many-to-one.
View 1 Replies
View Related
Nov 11, 2004
Hi, im having a problem with shring my db over a networkt,
as far as i understand access 97 locks a page (2k) of records when a record is being edited,
does anyone know a way in 97 how i can get access to lock ONLY the EDITED record and not the page?
in Tools/Options/Advanced i have "edited record" locking clicked and each form locks only the record edited but its still locking the page, i know there is "record level locking" feature in Access 2002 but not in 97, is this possible?
thank you
Scott
View 1 Replies
View Related
Jul 2, 2006
Main form is frmPeople
As you can see i have a subform called frmPeopleInsurancesSub
Next to that I have a Edit Command button which open a forms called
PeopleInsurances
When user wants to edit one of the insurances all user has to do is click on on one of the insurance and click edit to take user to that record
But cant make it work
Any Ideas
View 4 Replies
View Related
Dec 6, 2004
I have a form with a subform in it and I would like to print the contents of that form to a report. I am able to print now, using a "print" command button, but it only prints one record, just the one that is being displayed. I want to be able to print a report with all the records that are in the filtered result.
Also, when the press the "print" button I would like to give them an option "Print this record?" "Print All records?". depending on the input, it would print the report.
Can someone please help me with this? Much appreicated.
Thanks.
ronnie
View 1 Replies
View Related
Aug 4, 2005
Hi,
I want a user to be able to create a new record in the main form, after they have finished filling in the boxes in a subform by pressing the Enter key, rather than having to click the 'new record' button on the main form.
I reckon I need a 'default button' on the subform but I have no idea what this should do when clicked to create the new record...
View 1 Replies
View Related
Jul 27, 2004
I'm having difficulty controlling data entry to (disabling) a subform when a new record is being added to the parent form. I can set the subform to be disabled if there is no index value in the parent form (this allows the subform to function for existing records) but then the user has to create the parent record and move off of that record and then return to it in order to add child records to the subform. This is basically a create new records/data entry problem and I'm feeling pretty stupid. There must be a way to handle this...
View 3 Replies
View Related
Jul 6, 2007
I was wondering if what I'm trying to do in Access XP and 2003 is possible. I've looked at countless templates and samples and havn't seen anything like what I want to do to be able to construct it. I've tried manipulating queries, relationships different table joins but to no avail. I'm only a beginner in VBA and know little SQL, I would be truly gratefull if someone can please shed some light on the below.
I've been creating a preventitive fleet maintenance database in access and want to make some changes that will make the database more flexible.
What I have is a main form which has the following main fields from the tblWorkOrder; WorkOrderID, FleetID, StartDate, FinishDate, Odometer, and ServiceTypeID.
Within this I have a subform in datasheet view (tblServiceItems, fields; ServiceItemID, ServiceDescription, ServiceCompleted(checkbox)) and this lists all the service items that belong to the ServiceTypeID in the main form.
I have another table called tblServiceDetails and this has the fields WorkOrderID, ServiceItemID which join the above two tables.
My problem is that I can't get the subform to list all the records that are in the table tblServiceItems, it only shows each record if you go through and select it manually. What I want it to do is to show like a listbox and allow me to go through the list and check of those service items once they have been completed. On top of this I want all those service items for that service type to be recorded against that workorder (regardless of completed or not) so then when I create a report on a WorkOrder It will list all the service items showing the checkbox's of which services have been done.
I've tried to do this with a listbox, but It doesn't show the checkbox, only yes or no. I've also tried using the tblServiceItems as the subform but this only updated the table and didn't create a record in the table tblServiceDetails matching it with the workorder.
Any idea's please...
View 13 Replies
View Related
Feb 12, 2014
Is it possable to create a sub form using a single table. I can create one using 2 tables but am not sure on how to or if it can be done using 1 table. All i can find in forum is about multiple tables.
View 2 Replies
View Related
Jan 20, 2014
I have a form and a subform.
When I click on the command button to create a New Record and then type the customer details in the Main Form and the Order Details in the Subform, the information is not retained
i.e. when I close the Form and then re-open the Form the orders details are not saved but the customer details are.
When I re-enter the order details the information is retained this time, everything seems work ok second time round!!
View 4 Replies
View Related
Apr 12, 2014
I am trying to create a database to manage IT assets, most of the structure is done, I'm now trying to get the details done.
The issue I am currently trying to tackle is to get a control button on one form to create a new record in the sub form that sits in the main form via another pop-up form.
So I have :
>PeripheralsViewForm - that has 4 control buttons (Edit Quantity, Edit Description,Add an Item and Exit)
>> PeripheralsSubForm - (Datasheet) which is referenced to PeripheralsQuery
The Edit Quantity and Edit Description open a separate form which allows the user to update only the quantity or description respectively (I have done it this way rather than allowing edits in the data sheet as I feel that it is too easy to hit a key incorrectly and overwrite data in the datasheet view) Both of these functions work perfectly (to my amazement) - I am using the id field in the subform to link to the pop-up form which then has unbound text input boxes, which I then write back to the subform.
e.g from edit button on click event: DoCmd.OpenForm "perquantform", , , " ID=" & Form.PeripheralsSubForm!ID and then from the pop-up form "perquantform" on the "Exit & Save" button I have :
Forms!peripheralsViewForm.PeripheralsSubForm.Form! Quantity = Me.newquant DoCmd.Close acForm, "perquantform", acSaveYes
This allows the user to select the record in the datasheet form and then click either the change quantity or description button.Also also the other reason I did it this way was because I just couldn't get the subform to requery when I had the two edit popups linked to the table or query.
The problem now is that when I click on the "Add an Item" button, it overwrites whatever record is currently selected !
The code I have is this:
From the "Add an Item" button on click action:
DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form! , acNewRec
DoCmd.OpenForm "addperform"
And from the form "addperform" - "Save & Exit" button on click action:
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerType = Me.pertypedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerMake = Me.permakedrop
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerModel = Me.newmodel
Forms!peripheralsViewForm.PeripheralsSubForm.Form! PerDescription = Me.newdescription
Forms!peripheralsViewForm.PeripheralsSubForm.Form! Quantity = Me.newquantity
DoCmd.OpenForm "PeripheralsViewForm"
DoCmd.Close acForm, "addperform"
--------------------------------------
the line "DoCmd.GoToRecord , Forms!peripheralsViewForm!PeripheralsSubForm.Form! , acNewRec" is what I have added to try to solve the overwrite issue, but when I run this it comes up with the error :
"runtime error 2498 - An expression you entered is the wrong data type for one of the arguments"
I have put this line in the "addperform" exit action and it comes up with the same sort of error.
Using Access 2010.
View 14 Replies
View Related
May 4, 2015
I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records.
Code:
Private Sub cmdNewProject_Click()
'set focus to sbfProject
sbfProject.SetFocus
'navigate to new record
DoCmd.GoToRecord , "", acNewRec
End Sub
View 2 Replies
View Related
Apr 5, 2013
I'm trying to create a record for a table that has 2 attributes:
ContainsTracks
+album
+tracks
I have my main form, where the album is determined by a TextBox. My subform has a textbox that lists the tracks (in datasheet view). I have this set up and working fine.At the moment if I try to add a new record by typing a new entry into the subform I get the error: "Index or primary key can not contain a null value". This is because both album and tracks make the composite primary key of ContainsTracks.
My question is, how can I tell the subform to grab the value from TextBox1 in the main form as the +album when I create a new record.I have provided two screenshot that perhaps explain my predicament a bit better
View 2 Replies
View Related
Nov 24, 2013
I'm trying to create an invoice system with a sub-form within the main form. The issue I'm having is that the data in the sub-form keeps getting overwritten each time a new record is created for the main form. So when I go back to edit an older record, the data in the sub-form is changed to whatever the latest record had in it. Is there a way to lock in the data in the sub-form so that it stays with it's own record?
View 1 Replies
View Related