I cannot get this to work no matter what I do.
Form![MainForm].Form![Subform1].Form![Subform2]![Field]
The query containing this statement doesn't return anything and I get no prompts or error messages.
TIA
I have a form with four unlinked sub forms on - I am hoping to
1) add a value from one subform to another and more importantly
2) update 1 subform when the other is amended...how exactly do I reference subform 2 from subform1.
I have tried me.parent!form2 and forms!mainform!form2.requery etc but can't get it right
On a main form i have a continuous subform listing Jobs.
On the Click Event i want to display the personnel and equipment separately in two additional subforms linked to the Job i have clicked on by JobID.
I have tried all sorts of references and currently have this:-
Forms!PersonnelAndEquipmentMovementFrm.PerEquipMov Jobs.JobID = Form!EmployeeSubform.JobID.Text
Which does not work?
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
Hello,
I need some help with a piece of code for a db.
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?
Thanks!
Nancy
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.
Swee
I Open my Form
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 !!
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?
Hi,
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers]
but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
Thanks very much!
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 have a form and subform with the following names:
frmBasic
sfrmCustomers
I have a unbound txt field called txtCustName on the mainform
I want to populate this field from a field on the subform called CustName when the record is selected.
I also have an unbound combo box with the cust ids using the row source from the tables record source from the main form.
I tried the following Code:
Private Sub cboCustNum_AfterUpdate()
Me.Filter = "[Cust_ID]=" & cboCustNum
Me.FilterOn = True
Me.txtCustName = Forms.frmBasic.sfrmCustomers.Form.CustName
End Sub
This didn't work. So I put "= [Forms].[frmBasic].[sfrmCustomers].Form.[CustName]" in the control source of the text box on the main form I want populated.
The name doesn't appear in the field. I get #Name? in the field.
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 used the control source code:
=DMax("[forms]![purchase]![purchasedetailds]![pdID]","[forms]![purchase]![purchasedetailds]","[Forms]![purchse]![purchasedetailds]![puid]=" & [Forms]![purchase]![puid])
and the simpler:
=DMax("purchasedetailds]![pdID]","[purchasedetailds]","[purchasedetailds]![puid]=" &[puid])
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:
Code:
DoCmd.OpenForm "frmSuppliersSummaryCategories", acNormal, , "CategoryID IN(" & strWhere & ")"
However; if I open the SubForm directly it works perfectly. I.e.
Code:
DoCmd.OpenForm "frmSuppliersSummaryCategoriesSubform", acFormDS, , "CategoryID IN(" & strWhere & ")"
To summarise... How do I reference the control "CategoryID" on the subform "frmSuppliersSummaryCategoriesSubform"?
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
2 Subform problems
I have a data entry subform that is only supposed to show an empty record ready to be populated, and a display records subform that is supposed to show all the records. The subforms are both on the same tab of a tab control on my main form.
Problem 1:
The data entry subform shows all the records rather than a blank record. Something on my main form is causing it to show the records when it should not. Any ideas? The Data Entry is set to Yes.
To try to isolate the problem, I created a new form and added the subform to it where it behaves properly:confused:
I then added Me.DataEntry = True to the form open to see if that would solve my problem but it still sets the data entry to no.
If I have the properties box open when in form view of my main form, I can set the data entry to Yes and it works fine until I move to the next record of the main form when it resets to no. Teraing my hair out here.:mad:
My final attempt was to search the entire project to see if there is a "DataEntry = False" somewhere but there isn't. What is setting this property? Any ideas where I should look?
Problem 2:
After entering data in the first subform (data entry form), I want to re-query the second subform but I just can't get the syntax right. I have wrestled with the "Syntax for subs" document downloaded from http://www.mvps.org/access/forms/frm0031.htm (Microsoft MVP site) but to no avail.
My main form is called fdlgPrjDetails, the data entry is via fsubPrjCommentsUsersDataEntry and the subform I wish to requery is fsubPrjCommentsUsers.
None of the attempts below worked giving a cannot find control error.
Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler
Me.Requery
'Me!fsubPrjCommentsUsers.Requery
'Me!fsubPrjCommentsUsers.Form.RecordSource.Requery
'DoCmd.Requery ([fsubPrjCommentsUsers])
'DoCmd.Requery [fsubPrjCommentsUsers]
ExitHere:
Exit Sub
ErrHandler:
MsgBox Err.Number & " - " & Err.Description & Chr(13) _
& Chr(13) & "Error in fsubPrjCommentsUsersDataEntry: Err 003"
Resume ExitHere
End Sub
Any Ideas?
Both problems have me stumped so I'll be grateful to anybody with a scoobie on this.:)
im having problems getting a subform to calculate another subform's total and display it as it keeps coming up with the #NAME?.Basically my database is like this
tblCustomer
- tblOrders
- tblItems
and this is displayed on a form. so you can flick through clients, then flick through the orders and its broken down into the items inside each order.i have seen many answers that are only about calculating something on a subform and displaying on a form but this does not work when displaying on a 'parent' subform. the form structure looks like this
frmCustomers
- sFrmCustomerItems
- sFrmCustomerOrders
i have a calculated control textbox in the footer of sFrmCustomerOrders with sums up all the prices using =sum([Price])and in my sFrmCustomerItems, i have a field called Amount (the total amount of all the items) and the Control Source formula i was using was =[Forms]![sFrmCustomerOrders]![sFrmCustomerItems]![txtTotalPrice] but i get a #NAME? error.
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 almost have this form done.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
:confused:
I have a form that is almost complete
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
i have a form with 2 subforms. when the user selects a record in subform one. the date of that record filters subform 2. in the source query of subform 2 i have this under criteria
Code:
[Forms]![frmeventbuild]![frmBUILD]![EVENT_DATE]
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX
I have two sub forms on a main form.Subform 1 displays information which comes from a query, filtered using two combo boxes on the same form.The active record on the data sheet has two field values output to hidden text boxes on the form.
These text boxes then provide the values used for the query displayed on the 2nd sub form.When I highlight a new record in subform 1, the text box values change, but the values in sub form 2 do no.Which would be the best method to use to get this to requery?
All I am trying to do is get a subform to requery after choosing a name from a combobox on the subform itself, thus updating the records on the subform. The main form name is "BasicTestOneF" the subform name is "ICminiBasicF" and the combobox name is "Combo4"
I have the combobox requery on After Update, but I still get the "Enter Parameter Value" popup. I've spent the last hour and a half just trying to get this simple thing to work and have gone through I don't know how many sites and forum posts, tried every combination of VBA code I've found, and I still can't get it to work.
The forms in the code are subforms on a TabControl on a main form.
I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.
At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.
Private Sub FloorNumber_AfterUpdate()
If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then
[Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area]
End If
End Sub