Subform Recordcount Problem
Aug 25, 2006
I have several bound subforms whose recordsources are based on querydefs...
I use the following code in the OnCurrent event of the main form to count the number of records on a subform,
Me.mysubformname.Form.RecordsetClone.RecordCount
but for some reason, one particular subform won't give me the correct count... the only difference I can see between the problem subform and the others is that it is populated by a union query... any reason why that would be causing the problem? Any other thoughts?
Thanks in advance,
Scott
View Replies
ADVERTISEMENT
Jul 14, 2006
I am trying to count how many records has a table with a special value (SolicitaçãoID.Text) but I keep getting error:
Compile Erro:
User-Defined not defined
And it goes to this line: Dim db As DAO.Database
This is the command:
Private Sub CmdAmostra_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Set db = CurrentDb
SolicitaçãoID.SetFocus
strSQL = "SELECT COUNT (*) As RecordCount FROM Protocolo_O where SolicitaçãoID=" & SolicitaçãoID.Text
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
intResult = rs("RecordCount")
MsgBox intResult
Amostras.SetFocus
Amostras.Text = intResult
rs.Close
db.Close
End Sub
Well i have no clue what that error means and why.
Thanks for the Help
Art
View 9 Replies
View Related
Oct 6, 2006
Thanks for looking at my post. What I have done is this, i have built a custom search form for my access database. What i need is a textbox that shows how many records will show up if the user clicks generate form.. I want this textbox to update every time the person changes his search criteria. For example
the person wants to search by address, by typing in
Gold into the address textbox the recordcount textbox would show the user if he were to press generate form he would get
1200 records
now if he changes the address to
GoldStone -- the recordcount textbox would then update to show only
14 records would be shown if he/she generated the form...
Here is my current code
***********
Set db = CurrentDb
Set qdf = db.QueryDefs("MainDB_Search")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenDynaset)
rsCount = rs.RecordCount
MsgBox (rsCount)
txtGenerateReport.Value = rsCount
************
Now the problem with this code is that it works the first time the user types in search criteria, however when he/she tries to change it from Gold to Goldstone -- nothing happens, it runs the code above and returns 1200 records instead of the correct 14...
Please help :)
Thanks
Jason
View 4 Replies
View Related
Feb 28, 2005
The company I work for remains on the 2.6 version of the various MDAC components. Unfortunately, upgrading is not an option that I can control. So....
When I open a recordset which DOES contain records, I often return a RecordCount of -1. EOF is false, so that is working, and I am able to use that to get around part of my problem. Where I need the count after iterating through the recordset, I am able to use an iCounter variable to get that. I have tried using MoveLast, MoveFirst, etc. before calling for RecordCount, but that does not help.
In many instances having the count BEFORE going through the recordset would be very helpful. Has anyone else ever encountered this problem? Anyone devise a workaround?
Thanks in advance for any help!
John
View 3 Replies
View Related
Nov 4, 2005
Rich helped Latex88 a little further down and the advise given was:
"=Count(*) in the control source of a textbox in the subform footer will do it, no vba is needed"
I tried this out out on a few of my subforms and it worked on all but one. The recordsource of the one that it does not work on is based on a query. When I changed the recordsource to a table then the recordcount works.
Curious as to why and is there a work around?
Thanks a head of time,
Shane
View 9 Replies
View Related
May 21, 2013
For small tables (<10,000 rows), which technique is faster, more efficient ?
View 11 Replies
View Related
Sep 16, 2014
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.
View 7 Replies
View Related
Jan 26, 2014
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.
View 2 Replies
View Related
Dec 5, 2006
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.:)
View 10 Replies
View Related
Jun 20, 2013
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.
View 5 Replies
View Related
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?
View 1 Replies
View Related
Sep 1, 2006
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:
View 1 Replies
View Related
Sep 1, 2006
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!
View 1 Replies
View Related
Oct 8, 2013
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]
View 3 Replies
View Related
Feb 11, 2014
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
View 6 Replies
View Related
Sep 3, 2013
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?
View 10 Replies
View Related
Jul 26, 2013
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.
View 14 Replies
View Related
Jan 10, 2015
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
View 1 Replies
View Related
Aug 8, 2014
I have a main form that has two subforms, subform1 and subform2, both connected to the main form. When I enter data into a field in subform1 I want subform2 to requery and update to show the calculated results from the new or changed data in subform1.
I found that if I do a refresh it works when changing the data but not when entering a new line of data in subform1. I tried some code in the after update field of on subform1, but cannot seem to get anything to work.
Me.[subform2].Form.Requery
Does not work. It errors and says it cannot recognize subform2 as a field. I have tried a field name on subform2 but I still got the same error.
View 1 Replies
View Related
Jun 25, 2007
I have a subform within a main from which switches by entry. The subform is a datasheet. I have two dropdowns in the datasheet and I want to limit one off of the other. I keep getting a prompt. The prompt is Forms!CLTS Subform!Environment. To my understanding it looks like it can't find Environment. Does anyone know the proper format.
This is my code for the second drop down in the subform.
Also special note Environment is what I am referencing to and it also is in the subform.
SELECT PackageName.PackageNameID, PackageName.PackageName, PackageName.EnvironmentID
FROM PackageName
WHERE (((PackageName.EnvironmentID)=[Forms]![CLTS Subform]![Environment]))
ORDER BY PackageName.PackageName;
View 2 Replies
View Related
Feb 5, 2005
I have a main form which has a subform control in it. when the main form is opened the sub form control contains a list of events in a sub form (a summary view), for the person selected in the main form.
I want to change the subform to a different one which shows the whole event when i click in a field on the subform, based on the event i clicked in. ( i can do it by opening a new form but not with a new subform)
I can make the form change using the following code:
Private Sub Notes_DblClick(Cancel As Integer)
Me.Parent.subformhere.SourceObject = "eventsfullrecord"
End Sub
What code do i need to make it open to the record i clicked in the summary form, rather than just opening to the first record every time?
Not very good at passing recordsets
Thank you
View 4 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
Aug 19, 2015
I often move sub forms up and down within a form using something like.
Code:
forms!MyMainForm!MySubform.form.top = 500
However, I am having trouble moving a subform within a subform of a form.
Code:
Forms!MyMainForm!!MySubform!MySubformSubForm.form.top = 500
View 1 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
Mar 29, 2014
I use to Navigation form called "frmMain" and it's subform is "Transactions" and "frmTransactionsDetail". I just want to pass value from a field in "Transactions" Subform to other field in "TransactionsDetail" Subform by command button with vba syntax.
And Other one thing I want that, when I click a button Subform will refresh with vba syntax. How to refer.
View 1 Replies
View Related
May 26, 2015
I know how to use openargs to pass values between forms but im not sure how to go about passing all the values from one subform to another.
To be specific I have an order form (frmCustomerOrders) that has fields like CustomerName, EmployeeName, OrderDate and so forth, the subform (frmCustomerOrdersSubform) contains information on the Products the customer is ordering which usually has multiple records with fields like ProductName, PartNumber, Quantity and ItemNumber. I want to pass the values from the CustomerOrdersSubform form to another subform (TransferSubform) My initial thought was to use something like the openform command through openargs but im not sure if that will work or if there is another method I should use.
View 9 Replies
View Related
