Referencing "Current" From In Query
Dec 14, 2005
Hi guys, I think that I am being dumb but here goes.
I need the query associated with a Combo Box row-source to filter records based on another field on the form.
I can get this to work just fine if I explicitally include the form name in the query, ie SELECT Client.Client FROM Company INNER JOIN Client ON Company.Company = Client.Company WHERE (((Company.Company)=[forms]![allcalls]![company])); My form name is AllCalls.
However, I will be having many similar forms so do not want to reference the form by name. I am sure that the form can be referenced by me! or something like that but try as I might I cannot get it working.
Am I making sense? and does anyone have a solution?
Cheers
Wayne
View Replies
ADVERTISEMENT
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
Oct 24, 2006
Hello,
I am trying to write a simple query that references calculations in the same query - but I keep getting the "enter parameter value" box.
Basically, I have some columns in a query calculating average and sum values for a column. I would then like to multiply some of those columns together in a new column, using an expression like this: =[AvgOfColumn1]*[SumOfColumn2]. Access doesn't want to do this for me. It seems very simple so I'm sure I'm missing something!
Thanks!
View 3 Replies
View Related
Jul 24, 2005
Hi,
My situation is hard to explain but i will give it a shot. What i have is a db with employees and its being used to track training sessions. These employees are split into 6 teams and those teams are split into 7 teams each.
I have written two queries, first asks the user what team to display. This data is used in the second query. The second query asks for what training item we are searching for. The result of the 2nd query is displayed in a form. Displaying of the information on a form is important. This way the user can verify that the members on this list are the ones he is updating. Now I wrote a third query that works off of the 2nd and updates the datecompleted field with todays date.
Problem is when i post a button on the form to show all these employees complete it referes back to the first query and asks all the filtering questions again.
I would like to know if there is any way to run an update query from a form that has already been filtered. Or if there is a way to reference these records in a new query so as to avoid all the filtereing again
hope i explained it well
Thanks
Ricky
View 7 Replies
View Related
Mar 10, 2006
Hey All,
I have a question regarding referencing a form using dates between.
Currently I have it setup as:
Between [Start Date] And [End Date]
on the query.
I want to reference a form, which i figured would be like this
Between [Forms]![frmSBReports]![txtStartDate] And [Forms]![frmSBReports]![txtEndDate]
But this doesnt work! :confused: What did I do wrong here? Any help as always is greatly appreciated!
Thanks!
Mateo
View 1 Replies
View Related
Dec 4, 2014
I have a split database that is working pretty well, but I've been asked to add a functionality to it. I have a table, product1, that contains basic product information. One of the values is a "class", in that if a product was created at a certain time, it fits into a class; the other value in this table that is applicable is the time the product was created.
I have another table, class1, that lists the beginning and end times of the class periods and the class numbers.
So, my users will "ingest" a product, including the time the product was created. I need the query (i think so anyway) to fill in the "class" value in product1, by determining which class it is in by seeing which class beginning and end time it is between in class1 and using the class number found in class1.
View 1 Replies
View Related
Aug 13, 2007
Hello - am hoping someone can help with the below query.
I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.
I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.
I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?
Thanks!
View 2 Replies
View Related
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
Aug 27, 2013
What I really need is for when the form opens, it looks at todays date, then matches current user and then goes to that record for today, if no current user there, then will goto new record..
i know, sounds complicated, and probably is really easy, but my heads not with it today, as about to get drunk as its my 40th, and got people ringing and texting and still trying to get this done....
I've included a copy of this database, named Timecards..
View 1 Replies
View Related
Oct 3, 2007
I know this should be very simple, but I've already tried every possible entry from the site given in the FAQ section of this website (http://www.mvps.org/access/forms/frm0031.htm), but it keeps access keeps asking for a parameter value!!
i have a subform(frmSub2) on a subform(frmSub1) on frmMaster.
I have a combo box (cbo2) on frmSub2 that grabs its value from the value chosen from another combo box (cbo1). Here's what I have tried in the query builder (none work!! why?????):
-[forms]![frmMaster]![frmSub1].[form]![frmSub2].[form]![cbo1]
-[forms]![frmMaster]![frmSub1].[form]![frmSub2].[form].[cbo1]
-[forms]![frmMaster]![frmSub1].[form]![frmSub2]![cbo1]
-[forms]![frmmaster]![frmSub1].[form]![frmSub2].[cbo1]
Please help!!
View 3 Replies
View Related
Feb 28, 2006
Hi there, I am trying to put a query on my form which selects the most recent date of a members attendance from an attendance table and displays it in a text box, the code I am using is:
Private Sub Text41_BeforeUpdate(Cancel As Integer)
SELECT MAX(Attendance.AttendDate) as "LastAttended"
FROM Attendance
WHERE Attendance.membershipNo = Forms!Customer.sbfAttendance.Form.membershipNo
End Sub
Customer is my main form and Attendance is my subform. I think the mistake is that I dont have a field called last attended (you are not supposed to store data like this are you) and I need it to display in my text box (text41) on the main form
Thanks in advance,
Jon
View 3 Replies
View Related
Jun 10, 2007
hi all, i create a form which have a button to append the current entry to another table but i only want to append the current entry tat is open. So how do u set the query to append one entry(the current open entry on the form) using the criteria.
i notice the query only have sum, aver, +- ...etc..
tks.
View 7 Replies
View Related
Sep 18, 2007
Maybe a simple question, but i do not know how to put the current date in the WHERE clause of a SQL query.
The query i have so far:
select [table1].*, [table2].* from [table1], [table2] where [table1].[field] = [table2].[field] and [table1].[enddate] is null
After this there has to be something like
and [table1].[begindate] < [Date] (where [date] is the expression)
I know that with Date() or Now() the expression can be made, but i do not know how, please advise..
Thanks in advance
View 4 Replies
View Related
Apr 6, 2008
I want to be able to reference a variable dependant on where I am within a loop. I have myct1, myct2 and myct3 looping then a mycount loop and I want to add a record to a table taking the relevant myct value dependanct on the current mycount value. Any ideas anyone?
Here is my code:
for myct1 = 1 to 3
for myct2 = 1 to 3
for myct3 = 1 to 3
for mycount = 1 to 3
mytable.addnew
myvalue = <if mycount = 1 then myct1 etc>
mytable.update
next mycount
next myct3
next myct2
next myct1
Any help most appreciated.
View 1 Replies
View Related
Mar 7, 2005
I have code on a subform that is referencing another form that is still open (not the parent form, but the form which's button openned the form that the subform is on). I'm trying to get the value from a combobox that contains the data that will filter the subform in question. See below.
==========================================
Dim strForm As String
Dim strSQL1 As String, strSQL2 As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String
strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON tblMainData.ResponsibleParty = tsubPermissionList.FullName "
strWhere = "WHERE(((tsubPermissionList.UserID) = [Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)))"
strSQL1 = strSelect & strFrom & strJoin
strSQL2 = strSelect & strFrom & strJoin & strWhere
If IsNull([Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
==========================================
I can't resolve the error:
==========================================
Runtime error '3085':
Undefined function
'[Forms]![frmMainEntry].Form.[cboSelectUser].Column' in expression.
==========================================
Somehow access thinks that the references to the combobox is a function. Can someone help me with this?
Thanks,
PC
View 4 Replies
View Related
Apr 5, 2005
When clicking on a command button on a form in order to open another form and go to a specified record, i used to use a code similar to the following:
DoCmd.OpenForm "frmName", , , "[FieldName]=" & Me.ControlName
Is it in the same format the above statement if the form to be opened has a tab control ?
Thanks
View 5 Replies
View Related
Dec 6, 2005
I'm trying to get the value of a textbox on one of my subforms and can't seem to get it properly referenced.
I have a main form Main with a tabcontrol having two pages. One page is Timesheets. Timesheets has a subform called Detail_data.
On the detail_data subform is the text box Time_In that I want to reference. I can get to controls on the timesheet page but not on the detail_data part.
Thanks in advance....
View 1 Replies
View Related
Jul 10, 2007
Hi all
I've distributed a MS 2003 database to another office 1000km away using 2003 as well. The calendar control doesn't seem to be working. I'm assuming that the MSCAL.oxc isn't referenced. Is there a way I can do this programatically without actually having to talk someone through the referencing process over the phone??
Your ideas are appreciated.
View 2 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
Apr 27, 2007
I used to know how to get the path for the current database by running a query. I forgot how to do it. I am using Access 2003. I have tried querying the msystem tables without success. I have searched forums and the web without success. Anyone? Thanks!:)
View 1 Replies
View Related
May 10, 2005
I have a form with a subform in Datasheet view. I want the user to double click a record in that subform and a query to ammend it to another table.
I want to trap the double click event of the subform but
how do i run a query based on a current record in a subform?
View 3 Replies
View Related
Sep 9, 2005
Hello,
Can anybody see what I'm doing wrong here. I have the following query
SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName
FROM Contacts
WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK)
ORDER BY Contacts.ContactNr;
The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.
View 3 Replies
View Related
Oct 3, 2005
Below is the Field data I have in a query. This allowed me to get a count of forms for the current month. I think it is giving me a count of forms for both October 2004 & October 2005. Up until this month I did not have any data covering the same month of both years. Could this be what is happening and if it is how do I change my field data to ensure it is current month and current year only?
FORMS: DCount("[Date_of_Change]","all_trucks_table","[FORM #]=true AND Month([Date_of_Change]) =month(now)")
View 1 Replies
View Related
Nov 28, 2006
Guys,
I am trying to put together a query that will give me a calculation of days between the currect date and a date in the future.
How do I tell a query that I want to display the current date, and then calculate this?
View 1 Replies
View Related
Jun 12, 2007
It is possible to refer in a SQL statement to the "current" or "actual" record?I want to base the querry in the present value of two related tables. I will try to show you what I have to do, maybe you can give an alternative solution.These are the tables:tblTeam (idTeam, TeamName, DayCreated)tblTeamPerson (idPerson, idTeam)tblPerson (idPerson, PersonName, MusicPreference)Every person have a music preference, and the teams have to be formed by people with similar music preference.So, one way to filter that is to use the following method: everytime I create a team, I choose a person, ok. That firts guy is the one that will give the team his "MusicalPreference". The next members with different MusicalPreferences will be excluded from the query.But in order to do that, I need to refer to the current team that have 1 member, and use the value of the field MusicPreference in that Person in particular.I hope you have understand what I have to do, and I hope you can help me with this.P.S.: I´m using a combo-box with a special query in a subform to select the team members. In the main form I create the team. So it would be great to be able to use that "idTeam" value in relation with the firts team member of that team.
View 6 Replies
View Related
Aug 23, 2007
I am designing a contact management system and have hit a bit of a wall. I am farily new to Access so please advise if I am going about this the totally wrong way.
I have three tables; Contacts, Companies, Company_Addresses
Contacts:
ContactID (P)
CompanyID
OfficeName
Companies:
CompanyID (P)
Company_Addresses:
CompanyID
OfficeName (P)
What I want is when adding a contact, to have a drop down list in the CompanyID field showing all the companies in the Companies table, and then in the OfficeName field, have a list of only the offices related to the company selected in the previous field.
I can do the first list, but cant work out how to list only data related to the company selected in the previous field. Any help would be greatly appreciated.
Sorry if this is in the wrong forum.
Thanks. James
View 3 Replies
View Related
