Filtering For Current Record
Dec 9, 2005
Hi,
I have two combo boxes where the first lists values in one table. On selecting one I want a second combo populated with records in another table where foreign key = primary key.
Obviously the query is
select location from client,location
where client.client = location.client
However I can not get to the current record.
Alternatively, I could populate a list programmatically on the afterupdate event but I am not sure how to do this or if this is the best way. Any advice would be most appreciated.
Thanks John.
View Replies
ADVERTISEMENT
Dec 2, 2014
I currently have a form that only shows data based on a specific record id. I have placed a list into that form though that has multiple record ID's listed ( It's a log). How do I get a List to Filter to just the Record ID that corresponds to the current form Record.
What I have are as follows:
A form that has client information : Address, phone numbers, Etc.
On that form I have Pages. one of the pages contains a List.
That List however contains information from multiple clients based on Point of Contact ( Call Log)
What I want is a way to narrow the List to only show those that corresponds to the Current Client Record.
Is this possible? If my approach is wrong, which is a better way?
View 1 Replies
View Related
Nov 16, 2014
The aim of what I am doing is to create a monthly statement to give to our intermediaries that shows the commission they will receive each month for the deals they have referred. I have managed to create this report, HOWEVER I can't figure out how to filter out which month I need, so I a report for Jan, Feb Mar etc... The idea is that at the end of each month I need to run the report so only the latest month shows...
View 3 Replies
View Related
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
May 6, 2015
I'm trying to make a form that shows what the last record was next to the empty space where you enter a new record.
This is so the user knows that what they are entering is roughly in line with what has come before.
So for example if I was recording temperature every May, I would like a form that has a field called temperature and next to that field I would like to see last year's temperature.
Records:
Date | Temp
2014 | 20.5
2013 | 18.5
2012 | 19.0
2011 | 22.7
2010 | 15.2
So when I enter the record for 2015 I have a box that says: Temp and next to that box is "Last year was 20.5" or something like that.
View 2 Replies
View Related
Aug 8, 2014
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")
View 3 Replies
View Related
Sep 15, 2005
In my query, i want to automatically display the value of another field from the previous record in a field in my current record. i.e.
Name Value Previous
Record 1 1
Record 2 2 1
Record 3 7 2
Record 4 1 7
Is it possible to create some kind of simple expression to refer to data in another record?
Cheers,
Ben
View 6 Replies
View Related
Apr 14, 2007
I need to write a query which populates an empty field in the current record with information from a specific field in the next sequential record. Any ideas?:confused:
View 3 Replies
View Related
May 1, 2013
I have a query to bring in values, I need to select 2 rows of data but the criteria is as such:
x= starting value on form
now the row of data must match the following criteria previous row to current row(ref temp)<=x And Current row(ref temp)>xnext row to current row(ref temp)>=x And Current row(ref temp)<x
I have dealt with SQL before but how to do the above.The isolated 2 rows of data will then go into unbound boxes on a form from which I will do intercept and gradient calculations.
View 5 Replies
View Related
May 29, 2012
The filter output from a recordset in the following code is 1. But this is incorrect: How can this be corrected?
Private Sub Command43_Click()
Dim curDatabase As Database
Set curDatabase = CurrentDb
Dim rs3 As Recordset
Dim t As Recordset
Set rs3 = curDatabase.OpenRecordset("Select * from [Courses under Programs]")
rs3.Filter = "ProgramCode = 'ANS.CT'"
Set t = rs3.OpenRecordset
t.MoveLast
t.MoveFirst
MsgBox t.OpenRecordset.RecordCount
End Sub
View 2 Replies
View Related
Sep 5, 2006
Hi all,
Another problem bothering me. Wish someone can help me to solve it. I am doing the 'Edit' Option right now ... As a user friendly propuse, I had try to use a combo box to let user to choose my employee record with all the detail on but the problem is the program 'JUST' list the record they can not go to the current record!! When i try to edit my record, the program is not allow (for sure because they can't have a duplicate record on it). The reason is because the record just stay in the FIRST record but can not go to the current record. I do believe is the problem of i using the way of listing the record as below:
Me.EmployeeName = Me.EmployeeName.Column(0)
Me.IC = Me.EmployeeName.Column(1)
Me.Nationality = Me.EmployeeName.Column(2)
Me.Race = Me.EmployeeName.Column(3)
Me.Sex = Me.EmployeeName.Column(4)
Me.FMU = Me.EmployeeName.Column(5)
Me.Position = Me.EmployeeName.Column(6)
Me.SectionField = Me.EmployeeName.Column(7)
Me.DOEmploment = Me.EmployeeName.Column(8)
Me.DOResign = Me.EmployeeName.Column(9)
Me.Remarks = Me.EmployeeName.Column(10)
So, Could someone help me to solve it like when i can use the combo box to select the Employee record with the details are on as the same time can go to current record... Thanks alot!!!
i do have the confident if i can go to the current record then my edit option can be working properly.
P/S: I had attach the picture... showing my program problem (with the red circle)
View 8 Replies
View Related
May 12, 2005
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.
I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working).
But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.
I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.
If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.
Thanks
View 2 Replies
View Related
Dec 5, 2013
Is there a way to search for a term within a form and goto a record that matches that term assuming there was only one matching result (Unique ID, for example).
But NOT filter the results so that user can still navigate as usual after the search has been carried out.
View 1 Replies
View Related
Jun 9, 2014
I work in psychological testing and I have created a database to store some data for our patients' test scores. I have a main form with demographics and then 5 buttons on the main form that open into 5 other forms (one form for each test). I also have created a button on the main form to run a Report; however, I would like to filter the report to only show the current record (e.g., Patient ID #1 only).
(Can this be done?) How should I go about filtering the report to show only the open record? Expression is preferred - I'm not very good with code.
I'm also assuming that since each form is linked by the primary key of Patient ID, I should be able to see all data from each form (main + 5 others) in the report for that specified Patient ID... (?)
View 4 Replies
View Related
Apr 28, 2014
I am using the Filtering a listbox method from this post [URL] .....
It works great apart from when i type too many characters and no search results can be found i get a run time error '2105' you cant go to the specific record.
I think it may be because my form has a row source. When the example uses an unbound from?
View 1 Replies
View Related
Feb 26, 2015
I have a form and subform where i have table data in the subform and 03 Combo box in the main form header. I need to do some filtering using combox box1 and out of that filtered records i need to do one more filtering using combobox2. then again another filtering by Combobox3. ( same way we are doing in Excel)
Subform is running on a query where i have following fields;
Vessel
Voyage,
POL
POD
MLO
by Combo box 1 i need to filter Voyage
by Combo box 2 i need to filter POL ( out of the data filtered by above )
by combo box 3 i need to filter POD ( out of the data filtered by both above )
View 3 Replies
View Related
Jul 1, 2005
I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.
I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like
CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject
I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross
:confused: :confused: :confused:
View 6 Replies
View Related
Feb 23, 2006
hello,
I have faxing setup through the XP wizard, I setup my report in Access to use the Fax as specific printer. So When I select a record, it generates the Fax wizard which then prompts for the senders name Fax # etc. and away it goes.
that is ok but It would be nice if I could use the Fax number from the record I am sending, either current record or all. I guess the main trick is how would I pass the fax number to the Fax Printer interface?
Is it possible? I've read a few articles on Microsoft Fax for workgroups to work with Outlook, Outlook help says to go to Office update and download it but I can't find it there? Also do the newer versions of Outlook/Access have a better means of doing this? I am running office 2000, but I can get 2002 installed if that is the case.
thanks
Ziggy
View 1 Replies
View Related
Dec 12, 2005
I have a query that pulls all orders out of 3 tables that fit specific criteria.
tblLotInfo.WOSD = Between ([tblLotInfo].[WOSD])<=Date() And [Forms]![frmRaisedPanelLots].[EndingDate]
tblDelivery.Status = <>"On Floor" And <>"Floor Hold" And <>"On Floor LV Losee" And <>"On Floor LV N. 5th" And <>"On Floor ONT Cucamonga" And <>"On Floor ONT Locust" And <>"Shipped" And <>"Installed" And <>"Invoiced" And <>"Completed" And <>"Cancelled" And <>"Returned to Floor LV Losee" And <>"RETURNED TO FLOOR N. 5th" And <>"Returned to Floor ONT Cucamonga" And <>"Returned to Floor ONT Locust"
tblLotInfo.DoorStyle = Like "*eagle*" Or Like "*H/E*" Or Like "*RP*" Or Like "*F/E*" Or Like "*CC-23R*" Or Like "*AR-756*" Or Like "*Deco*"
This query works fine through 12/29/05. Anything date entered after that gives me "NO CURRENT RECORD". I don't know why. We have orders through Feb of next year scheduled. Any help would be greatly appreciated!!
View 2 Replies
View Related
Jun 26, 2006
I need help to get current record with different ids:
Dim strDocName As String
Dim strWhere As String
strDocName = "16kanaler"
strWhere = "[skjema_16_gr1_ID]=" & Me!skjema_16_gr1_ID
DoCmd.OpenReport strDocName, acViewPreview, , strWhere
How can I use more Ids in the wherecondition? I want to get the current record for skjema_16_gr1_ID and skjema_16_gr2_ID, not only the first.
Please help me...
Håvar
View 2 Replies
View Related
Oct 27, 2005
Hi, I am a complete newbie to Access. I want to be able to double click on the field in the table, form or a query and have, as a result, another form pop-up with all the data from that record displayed. I have tried macros, but the doubleclick property only has things like goto first, last, etc. records. and always brings the first record in the table. Any hep in the right direction is greatly appreciated. Thanks.
View 2 Replies
View Related
Jan 5, 2006
I have got a tricky problem, for which I can find no answer - but then I am a bit of a novice here.
I have created a (probably too-) complicated relational database but what I really need is to be able to make abutton on the page of my address book form, so that when I hit it it creates a report of an address label for that current record on the screen only.
I hope this makes sense. If this is possible, I then need to extrapolate this technique to print a report of a subform (of samples sent) of a current client record.
All help gratefully received
Xenia
View 4 Replies
View Related
Feb 14, 2006
I have a form with data in formview. When I select a record I want it to highlight with a specific color.
How can I do that?
View 1 Replies
View Related
Apr 9, 2006
I am creating a query based on two tables: 1) tblClient (only one primary key "ClientID") and 2) tblContactDate (with two primary keys "ClientID" and "ContactDate").
Each client may have more than one contact date.
In the query, I only want the records from the tblContactDate showing for the MOST RECENT contact date of the client only; however, right now the query is producing more than one record for those clients with more than one contact date.
Not sure if I need add some special code to the "criteria" area in the Design View, or somewhere else. Your help is appreciated. :confused:
View 5 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
