Modules & VBA :: Counting Input In A Subform?
Sep 11, 2013
i have a field in the form where you can choose how many vehicles you have in the contract.
the field is called
amount_vehicle
Then i have a subform where you put the vehicle in.
So there is a 1 to n relation between the form and the subform
The id of the vehicle is called ID vehicle
I want that e.g a user chooses 2 vehicles in the form. Now it is not possible to put in 3 vehicle in the subform.
I�ve created the following code, but it doesnt work.
[CODE]
If DCount("*", "subform", "ID vehicle = " & Me.Parent!ID vehicle) >= Nz(Me.Parent!amount_vehicle, 0) Then
MsgBox "no vehicle"
Cancel = True
Me.Undo
End If]
View Replies
ADVERTISEMENT
Nov 13, 2006
Hi I have a form showing me a list of records in datasheet view, every record can be expanded showing other records related to the main record also in a datasheet view, the subform records contain a field called "family" wich are A1, A2, C3. What I need is to show on the main form the number of records that belong to the A1 family, A2 family and C3 family, this result have to appear on 3 different text boxes on the main form!
View 1 Replies
View Related
Feb 7, 2014
I have subform (datasheet) with a column for gender indicating "Male" or "Female" I wish to place the totals for each gender (and total overall) on the main form footer. I have tried objects of every type with calculated controls using conditional count formulas such as
=Count(IIf([Gender]="Male",0))
But I get #error on everything.
View 12 Replies
View Related
Feb 18, 2014
I have set up a database to record several different sets of records for a volunteer group that I run. The types of records that I am keeping are hours on duty, uniform issued and attendance at training. The attendance form is a subform which contains a list of dates and the two fields of checkboxes showing if they attended or sent their aplogises.
What I am trying to do is to count the number of times that members attended training. I have set up a 'unbounded' text box in the footer and would just like to add a formula in to the command box of the box properties. Anything more i.e. coding then it goes over my head very quickly.
View 3 Replies
View Related
Sep 15, 2014
I am a novice and have been searching to no avail for a solution to my problem. I have a main form and a subform (datasheet view) of a table. In my main form I have various calculations which calculates the data from the subform (when auto-filtered). What I am trying to do is count the distinct [Call Number] reflecting the data from the subform into a textbox in the main form (when subform is auto-filtered).
View 14 Replies
View Related
Dec 19, 2014
I built a query "WplatyKlienci" which sum all of payments "Sumawplat" from my clients "klient".
Code:
SELECT SUM(tblWycenaZakonczone.cenanetto) AS Sumawplat, tblKlienci.nazwaSkrocona AS klient
FROM tblWycenaZakonczone INNER JOIN (tblZleceniaZakonczone INNER JOIN TblKlienci ON tblZleceniaZakonczone.id_k = tblKlienci.id_k) ON tblWycenaZakonczone.id_zlecenia = tblZleceniaZakonczone.id_zlecenia
WHERE tblWycenaZakonczone.cenanetto is not null
GROUP BY tblKlienci.nazwaskrocona;
Now I need to know 2 things and I have problem with counting in sql:/
1) what percentage of the total amount represents a particular client.
2) I want to create a graph, so I need to do another query, which shows values form no. 1 only for 10 clients with the largest payments + one position named "others" which represents rest of clients
View 5 Replies
View Related
Jun 22, 2005
One form, two subforms in a new db. The three work well together, except when it comes to a new record.
When I want to input details of a new record, the main form and a subform work ok, but the third subform won't accept anything.
Any guidance, please, gurus?
View 14 Replies
View Related
Aug 15, 2013
I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.
Option Compare Database
Global GBL_Category As String
Global GBL_Icount As Long
Public Function Increment(ivalue As String) As Long
If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
GBL_Icount = GBL_Icount + 1
' MsgBox icount
Else
GBL_Category = ivalue
GBL_Icount = 1
End If
Increment = GBL_Icount
End Function
View 1 Replies
View Related
Sep 24, 2013
In my database when an item is currently being loaned the item's loaned field will have a 1 to indicate this. When the item is returned the value is 0. Only so many items are available and when I use the DCount function it is counting the 0s as 1s.
View 1 Replies
View Related
Nov 13, 2014
I have a
- form called "Hauptformular" and a
- subform called "qry_tracking_Unterformular". Then there is a
- button called "cb_filialname".
Now when opening the Database, the form appears with the subform containing the
- query "qry_tracking"
When hitting the button "cb_filialname" the subform receives the contents of the
- query "qry_tracking_FilName", that has a filtering option on the Column "Filiale": "Like [Filialname?]"
Now the filtering works fine with the following Code:
PHP Code:
Private Sub cb_filialname_Click()Me!qry_Tracking_Unterformular.Form.FilterOn =
FalseMe!qry_Tracking_Unterformular.Form.RecordSource = "qry_tracking_FilName"End Sub
-> when you hit the button, the inputbox
Filialname?
appears.
The problem now is, that after having used the button, when I click the "Exit" cross right up there, the inputbox appears again.
View 2 Replies
View Related
Aug 15, 2014
I have a table that contains names of employees and the name of awards they received.
tblEmployeeAwards
EmployeeName, AwardName
John,Nobel
John, Pulitzer
Michael, Fulbright
Jane, Nobel
Jane, Fulbright
I would like to know the following:
1. How many employees received more than one award (answer: 2).
2. How many employees who received the Nobel prize also received any second prize (Answer: 2).
3. How many employees who received the Nobel prize also received the Fulbright (answer: 1).
View 10 Replies
View Related
May 14, 2013
On my main form, I have a subform to input multiple dates, using date picker.
I also have a text box on the main form to input single dates, again using Date Picker.
On entering both the single date text box and the subform, I don't want the (default) date to be visible. I only want the date to be visible once I have selected a date from the Date Picker.
I have figured out how to do this with the single date text box. I have simply defaulted the forecolor to be white so that you can't see the font against the white backcolor. Then on the Change Event, I set the forecolor to be black. Works like a charm.
However... This same approach will not work with the subform. I've tried playing around with a bunch of different events, but so far nothing allows me to re-create what I am able to do with the single date text box on the main form.
View 2 Replies
View Related
Jun 6, 2014
Suppose I have a table with a variety of fields; one autonumber, some text fields and some date fields.
I need code to calculate the number of fields that are NOT date fields and then code to count how many of those fields (the ones that are not date fields) have data in them.
Following is the code to count how many fields in a table had data and this works well, but now I need to find a way to ignore the date fields. How can this code be modified to do the same but ignore the date fields?
Code:
Public Function PercentCompleted(Table As String, IDProject As Long, TotalFields As Single) As Double
Dim sampO As DAO.Recordset, iCtr As Long, strSQL As String, Percent As Double
'Takes the fields based on the projectID
strSQL = "SELECT *" & _
"FROM " & Table & " WHERE IDProject = " & IDProject
Set sampO = CurrentDb.OpenRecordset(strSQL)
[code]....
View 13 Replies
View Related
May 1, 2015
I have to count the number of occurrences of Line feed (vbLf) in a long string. The code I am using is:
MAX = TLine.Split(vbLf).Length - 1
It does not work.
View 5 Replies
View Related
Jan 26, 2007
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies
View Related
Jun 12, 2014
How I could do a multiple value InputBox in VBA, if this is even possible... This is so I can prompt the end user to enter a range (with two string values as start and end parameters) for a report I'm working on.
If this is possible, I would like to include this as an additional parameter option within the code I have below:
Code:
Private Sub EmployeesBadges_Click()
Dim soa As Byte
Dim emp, sEB As String
soa = MsgBox("Do you wish to print a single employee badge or all?", vbYesNo + vbQuestion)
Select Case soa
Case vbYes
[Code] ....
I'll just create a form to do this.
View 3 Replies
View Related
Aug 5, 2014
I have a textbox where I need an afterupdate event, which should check that input is 3 letters followed by 3 numbers. The total is 6 characters, but always 3 letters + 3 numbers. If condition not met, a msgbox will be shown, for the user to input correctly, before being able to continue.
View 1 Replies
View Related
Jan 13, 2015
We receive hundreds of items at the time capturing the item barcode and the userID. I want to improve the process capturing the UserID once and updated the table with this entry on all empty records on the table. I have created the following script but I doesn't work.
Private Sub Command7_Click()
Dim strSQL As String
Dim strUser As String
strUser = InputBox("Scan your badge")
strSQL = "UPDATE " & tblGER_ReceivingLog & " SET " & tblGER_ReceivingLog & _
"." & UserID & "='" & strUser & "' WHERE ([" & tblGER_ReceivingLog & "]. & UserID & is null);"
DoCmd.RunSQL strSQL
End Sub
View 3 Replies
View Related
Mar 1, 2015
I'm having trouble executing a SQL command in VB... I want it to find the the value of the input box in TBL-Purchases and Delete all related values. Here's my code.... I get an error on the line I've highlighted in green...
Private Sub Command31_Click()
Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)
[Code] .....
View 9 Replies
View Related
Jun 24, 2013
I need to create a form to input records (or modify records) however im unaware of the methods that could be used to actually put the data into the tables..my current method is using:
Code:
Dim dbs as DAO.Database
Set dbs As CurrentDB
Dim InsertSQL As String
InsertSQL = "INSERT INTO [Table] (Columns) VALUES (Values)"
dbs.Execute InsertSQL
I am not sure if this is the most effecient or best or if it has issues that other methods or ways to use but doing this seems to do the trick.
View 3 Replies
View Related
Jan 20, 2014
i have being requested to create a from that displays the number of files in different folders. I can accomplish this somewhat with the code below. In the example below it is only showing the code for the first 3 folders. I have 7 folders in total to count.
1.Each week the location of the data will change. In the example below it is showing 2014-W03, the following week it will be 2014-W04 etc. etc. I was thinking of having an input box in the form that the user type the date into, to trigger the code, but how would I go about changing the code to include this variable?
2.I need to repeat this code for 9 other products, so my form will be displaying data for 10 products in total. I know I could copy this code 9 times and modify it accordingly, but is it possible to loop it in some way?
Code:
' Returns the total number of fails
Private Sub Form_Load()
Dim file As Object
Dim fileCount As Long
With CreateObject("Scripting.FileSystemObject").getfolder(" estmachineProduct1 FAILS2014-W03")
For Each file In .Files
[code].....
View 11 Replies
View Related
Jul 23, 2013
how to input alarm in my access database? I have data that should be insert over and over in 4 hours duration, when I put first data, I get local time and I need timer that could count 4 hours and after to make notification (massage) that the time for the Product of the database are over and need to input clean Product.
something like in first-->
ProductTime is column from Access Database
i=j=ProductTime;
for (i=0;i>= j+4hours, i++)
{
If(i=j+4h)
{
msgbox("ALARM- TIME is OVER");
}
}
View 1 Replies
View Related
Aug 18, 2015
I'm trying to update the account number via an input box with a do loop but I don't have the code right. The at the input box, the user will type in a 4-8 digit number and it will update all the account number fields in a table that are null.
View 1 Replies
View Related
Jan 24, 2015
I try to validate the input in a form combobox. In my table it works okay with a validation rule
validation rule: Like "[A][B]"
validation text: The input should be two numbers with A or B with two numbers Example: 01A01 or 21B43
But when i go to my form i can still input other letters than A or B. It will not save but there is no warning that the input is wrong.
View 4 Replies
View Related
Aug 20, 2013
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Code:
Beep
strBegindatemsg = "Enter the beginning date." & vbCrLf & vbLf
strBegindate = InputBox(Prompt:=strBegindatemsg, Title:="Begin Date")
Beep
strEnddatemsg = "Enter the beginning date." & vbCrLf & vbLf
strEnddate = InputBox(Prompt:=strEnddatemsg, Title:="End Date")
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code:
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
View 3 Replies
View Related
Nov 23, 2013
I want to save input into an Array, and then show this information.To enter data I am using a textBox. After "Enter" it should save info into the Array.If textBox is empty, it should show information. I would like to use a textbox too, that shows all the Array after pressing Enter.
textbox on the Form: txtInput, txtOutput..I have the following code, but I am not able to get it work:
Code:
Option Compare Database
Public Sub Form_Load()
Dim Data(10) As Integer
Dim i As Integer
Dim j As Integer
[code]...
View 4 Replies
View Related
