Modules & VBA :: Referencing All Records In Subform
Feb 23, 2015
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?
View Replies
ADVERTISEMENT
Nov 10, 2014
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.
View 4 Replies
View Related
May 2, 2015
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])
View 5 Replies
View Related
Jan 16, 2015
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Me.lstFacilities = ListBox
Me.cboMeasure = ComboBox
Me.cboYesNo = ComboBox
Me.txtTarget = TextBox
Code:
Private Sub cmdAddMet_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Set DB = CurrentDb
[Code] .....
This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:
Code:
Set DB1 = OpenDatabase("serverotherdb.accdb")
strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN 'serverotherdb.accdb'"
Set RS1 = DB1.OpenRecordset(strSQL1)
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Code:
For i = 0 To lstFacilities.ListCount - 1
If lstFacilities.Selected(i) = True Then
RS.AddNew
RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
RS!MEASUREMENT_PERIOD = Me.cboMeasure
[Code] .....
Is it even possible to do this?
View 10 Replies
View Related
Apr 15, 2014
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
View 7 Replies
View Related
May 27, 2014
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?
View 4 Replies
View Related
Oct 28, 2007
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
View 2 Replies
View Related
Feb 16, 2007
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!
View 4 Replies
View Related
Oct 24, 2005
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
View 6 Replies
View Related
Sep 2, 2004
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
View 7 Replies
View Related
Aug 12, 2015
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 !!
View 2 Replies
View Related
Mar 9, 2005
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!
View 3 Replies
View Related
Jun 24, 2015
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.
View 4 Replies
View Related
Oct 11, 2007
Hello everyone,
I am pretty new to access and I am trying to reference records by date and time. I don't know how to do it. I am keeping track of aircraft flight time for our Fire Department Helicopter. When the crew gets back from a run they enter the ending Hours or 'HOBBS' time. I've queried the HOBBS to subtract from the previous HOBBS with the following lines:
PrevHobbs: (Select Max(Hobbs) from AircraftRecord Where ([Serial Number] = Aircraft1.[Serial Number] and [Hobbs]< Aircraft1.[Hobbs]))
and
Flight Time: [Hobbs]-[PrevHobbs]
-Aircraft1 is an Alias-
We have more than one helicopter so I needed to pick out the serial number as well. I modified this line from something I found on the internet and it works most of the time. It does not work when the crew gets a call and they don't run up any hours on the helicopter. If they enter the same hobbs time as the last run it will skip the last run and subtract from two runs prior, on account of the "<" in the 'PrevHobbs' line.
I cannot reference a run by the "Run Number" (autonumber) because people will occasionally forget to enter runs and come back days later to enter them. The run numbers are then no longer consecutive. The only thing that consistantly defines a consecutive run is Date and Time.
Any help on this matter would be greatly appreciated. Thank you in advance for your time and effort.
Adam
View 1 Replies
View Related
Mar 11, 2008
Another problem has thrust its ugly head into my personal universe.
I have a report to make that is to show a break down of employees hired in each month since Oct. '05 and to show what percentage of the total for that month remain.
I have created one query that grabs each of the relevant personnel;
SELECT Right(Str(Year([dbo_personnel]![originalapptdate])),4)+" -"+Str(Month([dbo_personnel]![originalapptdate]))+" : "+MonthName(Month([dbo_personnel]![originalapptdate]),True)+" '"+Right(Str(Year([dbo_personnel]![originalapptdate])),2)+" Hires" AS hire_month, [dbo_personnel]![lname]+"; "+[dbo_personnel]![fname]+IIf(IsNull([dbo_personnel]![mname]),""," "+Left([dbo_personnel]![mname],1)+".") AS name, dbo_personnel.badge, dbo_personnel.originalapptdate, IIf(IsNull([dbo_personnel]![dateoftermination]),[dbo_personnel]![assignedorg],"EOS'd") AS EOS
FROM dbo_personnel
WHERE ((Not ((dbo_personnel.badge) Like "R*" Or (dbo_personnel.badge) Like "9*")) AND ((dbo_personnel.originalapptdate)>=#10/1/2005#))
ORDER BY dbo_personnel.originalapptdate;
I've saved this query as "2-yr Service Check Baseline."
And then this query that does the count;
SELECT [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") AS EOS, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS
FROM [2-yr Service Check Baseline]
GROUP BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active")
ORDER BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active");
The output from the second query stores the Active count on odd numbered records and the terminated ones on the even numbered records.
So, to get the percent left, I'd need to sum the two count fields from the two relevant records and then divide this into the Active count.
But, in the report, how do I reference a field from a record other than the current one?
Thanx in advance for any help!
View 3 Replies
View Related
Jun 5, 2005
Hi,
nuther question from the forum dunce I'm afraid,
I want to ensure the user of the form cannot enter the same [cboPlayerName]
twice for the same [txtMeetDate] (thanks for the advice trubolotta, I changed
the name of the [Date] field.) The user will select the PlayerName from a combo
box. Can I have the list in the combo box diminish by each player as they have
been selected for a [txtMeetDate] I suspect a monstrous amount of code &
intellectual input will be required to achieve this. Basically, what I'm asking
(this time !) is how can I have my program search my tblScratchScores for
duplicate names for the [MeetDate] that I am currently inputting Data for?
View 1 Replies
View Related
Oct 23, 2013
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"?
View 4 Replies
View Related
Sep 22, 2005
I tried the idea sugested in post (http://www.access-programmers.co.uk/forums/showthread.php?p=423080#post423080) but it didn't seem to give the cross referencing that I had hoped for .... example 1 is related to 4,3, and 5 while 6 is related to 1 thus implying an extended relationship to 4,3, and 5.
In a standard one-to-many I'd look at record 1 and see that it is related to 4,3,5 but if I were to look at record 6 I'd only see that it is related to 1. How would I set up the table relationships to drill further to see that 1 is also related to 4,3,5?
How do you do a many-to-many from one table back to that same table?
tblEvent
EventID(AutoNumber) EventTitle(Memo)
1 memo content
2 data
3 more memo content
4 some info
5 more stuff
6 other text
tblEventRelationships
EventParrent EventChild
1 4
1 3
1 5
6 1
3 2
In addition ... how would one set up the integrity to prevent loops from forming?
View 6 Replies
View Related
Dec 12, 2006
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
View 2 Replies
View Related
Nov 19, 2014
I have recently upgraded to Access 2013 and this has updated all my object library references to 15.0. The problem is that my colleages are still using Access 2010 and when they try to use the database they are receiving a missing reference error.
I have removed the Excel reference but this gives me the 'object type not recognized' error when running the code.I have tried to download the 14.0 reference libraries in order to add them back in but for some reason the installer doesn't work.
View 3 Replies
View Related
Jun 26, 2015
I'm using Access 2003. In "References" (Tools > References > Browse...), I've added in "Microsoft Windows Common Controls 6.0 (SP6)" (c:windowssystem32mscomctl.ocx).
I've then created/inserted an instance of the control "Microsoft ListView Control 6.0 (SP6)" on a Form ("MyForm") and given the listvew control the Name "MyListView".I wanted to decorate MyListView with some custom methods so I've created a new class module ("DecoratedListView") which contains a member field called "lvw".
I want 'lvw' to point/reference to MyListView, but I don't know what reference type to use in its declaration. Importantly, I also want to capture lvw's ColumnClick event.
I've tried:
Code:
Public WithEvents lvw As Object
Public WithEvents lvw As Control
Public WithEvents lvw As MSComctlLib.ListView.2
Public WithEvents lvw As MSComctlLib.ListView
Public WithEvents lvw As CustomControl
and none works when I
Code:
set lvw = Forms!MyForm.MyListView
The first try (Object) doesn't even compile. I get the exception "Expected: identifier"
The second try (Control) doesn't compile either. I get the exception "Object does not source automation events"
The third try (MSComctlLib.ListView.2) doesn't compile and throws the exception "Expected: end of statement"
The fourth try (MSComctlLib.ListView) compiles but fails at runtime with the exception "Type mismatch". Using TYPENAME() on the control returns "CustomControl".
The fifth try (CustomControl) compiles but fails at runtime with the exception "Object or class does not support the set of events".
I can't believe I'm the first person to have tried to capture an ActiveX Control's events in a separate class.
View 1 Replies
View Related
Jun 16, 2014
I have a form which has a subform that holds records from a table. When in the table i can move through the records with my up and down arrows but when I am in the subform i can not move in the same manner with up and down arrows. What needs to be done on subform to allow user to move through records using up and down arrows.
View 1 Replies
View Related
Mar 5, 2015
I have a form with a subform. I would like a control in my parent form to uncheck a checkbox control in my subform when that field value is deleted . I can get that to work if there is only one record in my subform but it doesn't uncheck the rest. how to I reference all the records in my subform so all of them will uncheck when I delete that field value?
View 2 Replies
View Related
Nov 16, 2013
I have created three forms using three tables tblUser, tlbMeasure and tblSubMeasure. The tblUser is linked to the tblMeasure table via UserLogin and tblMeasure is linked to tblSubMeasure via MeasureID. The forms are created with frmMainMeasure being the main form and frmMeasure6 a subform and frmSubMeasure a sub with frmMeasure6.
I want to be able to add sub measures that will be linked to each MeasureID and each MeasureID link to each user via UserloginID. The forms load the data in the sub forms but when I try to add sub measures using a button I added on the frmMeasures6. The button works when I try work on the form fine but when I try via the main form I am not able to add or view other records within the table.
When I click the Add button I want to be able to load back the same MeasureID number in fmrSubMeasure to ensure that it is linked to the same measure and the MeasureID is linked to the same UsrrLoginID.
The code is included below and I've attached the file as well.
Private Sub cmdAdd_Click()
If Me!frmSubMeasure.Form.Dirty = False Then
End If
Me!frmSubMeasure.SetFocus
DoCmd.GoToRecord , , acNewRec
[Code] ....
View 1 Replies
View Related
Sep 9, 2013
I have button the when pressed will insert a record (via an insert into query) to the frmMain and several records to the frmSub.The problem is when a new record is created in the main form the auto number primary key advances. This is fine however the linked Child field which is a foreign key of the main form is not added and the value is blank. I thought this would happen automatically and the Master field would force the child field to be update but this is not happening.Do I have to determine the max number used in the auto number then add 1 to it?
View 7 Replies
View Related
Sep 12, 2013
i want to Count recrods by a pressing a button from a subform.
After counting the records from the subform, it should compare the amount of the records with a number.
The user can put in the number.
The field for the number is called number.
The button is in the form.
I've created the following code.
The Name of the subform ist Abfrage_Vergutungssatze_Unterformular
Code:
If DCount("*", Me.Abfrage_Vergutungssazte_Unterformular) > Me.number + 1 Then
MsgBox "Please recheck!"
I've get an error.
error message: errors when compiling
View 4 Replies
View Related
