Modules & VBA :: Open Form To Record With Latest Date In A Field
May 12, 2014
I am trying to make an on-click event that would open a form showing the last inspection done on a site.
Unfortunately, I cannot even first create a dlookup function to use, so I haven't even attempted the rest!
The data needed to reference is in one table, and I just...can't... quite get it.
Here is my last attempt (which at this point probably isn't my best )
Code:
=DLookUp("InspectionID","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "' AND InspectionDate = #" & DMax("InspectionDate","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "'") & "#")
After breaking it apart I'm pretty sure the DMax function (and using date?) is the culprit, but I can't figure out why.
View Replies
ADVERTISEMENT
Jul 11, 2015
If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
View 2 Replies
View Related
May 10, 2007
I am trying to create a query or series of queries that will identify the latest record of a field called CheckInDate. The table will have multiple entries. For example.
Book#__________CheckOutDate_____________CheckInDat e
__1_____________01/01/2007_______________03/01/2007
__1_____________04/01/2007_______________05/01/2007
__1_____________01/01/2007_______________
__2_____________01/01/2007_______________02/01/2007
__2_____________02/05/2007_______________04/27/2007
__3_____________01/01/2007_______________03/01/2007
__3_____________05/01/2007_______________07/01/2007
I need the results to be:
#1(ALL) will not be listed, as there is a book still checked out
#2 will show the record with the 04/27/2007(ONLY), as I want the last time the book was checked out.
#3 will show the record with the 07/01/2007(ONLY), as I want the last time the book was checked out.
I currently have a somewhat flat table where this is concerned. Please advise on what I would need to do to create this. Thanks!
View 1 Replies
View Related
Apr 17, 2008
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
View 4 Replies
View Related
Sep 21, 2007
Hello All...
I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
Thanks!
View 1 Replies
View Related
Aug 10, 2015
I'm trying to create a query that has a calculated field that shows a maximum value from multiple fields.
As far as I can find, this is not built into Access, so I've used this code from a Microsoft page:
Code:
Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
' Set the variable currentVal equal to the array of values.
[code]....
The problem I'm having is, well this doesn't work for me.I'm a bit of an beginner VBA coder, but I understand this code and don't know why it won't work.It only displays the value from the field within the brackets.The values used are Dates, so I need to display the latest date from multiple fields.
View 13 Replies
View Related
Sep 28, 2014
In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.
View 4 Replies
View Related
Jul 21, 2015
I would like to open a form to a specific date that is taken from another form. Date field format is short date and looks like yyyy.mm.dd
I have figured it out that it only works if date format is yyyy/mm/dd
I know format function can do this, but i do not know how to write the code with correct syntax.
For example i have tried:
DoCmd.OpenForm "TreatmentsTB1", , , "DOV= #"& Format(2015.01.11,"yyyy/mm/dd")&"#"
it does not work.
View 3 Replies
View Related
Mar 27, 2014
I have a form that is opened as hidden when my startup form loads. Data gets entered into it from other forms so whenever I switch records the hidden form needs to be opened to the matching record.
To accomplish this, I have been using the DoCmd.OpenForm in the OnCurrent event with the WHERE clause matching the PK/FK. I have had no luck with the SearchFor method.
It seems silly to have to keep reopening a form to go to a specific record when the form is always open already. Plus, I am wondering if it is affecting the forms' load times.
Is there a better way to do this (there must be)?
View 1 Replies
View Related
Dec 17, 2013
In my Access adp, when I open a certain form (frmVertebrates), the databinding occurs in the Load event for the form, for various reasons. I have not specified any datasource in the form's design view.
Databinding for frmVertebrates:
Code:
Me.Form.RecordSource = "select * from dbo.vertebrates where catalogID=3"
This works great when just opening the form. However, when opening with a filter from a button on another form it won't work, the form displays all records.
Button code:
DoCmd.OpenForm frmVertebrates, , , "vertID=123"
Obviously, this makes sense, since the record source is explicitly set in the Load event.
Is there a way to capture the filter "vertID=123" so it can be added to the Form.RecordSource sql?
When breaking in the Load-code and watching the 'form' variable, I can't spot the filter condition anywhere...
View 4 Replies
View Related
May 7, 2014
What I need to do is press a button on a form. When the button is pressed, I want to create a new record in a table not already open and populate some fields with data from the form I am looking at. Then call up a new form with the record I just created on it.
Basically it is a work-order entry issue, the user scrolls through to find the correct piece of equipment when they do they click on "Create work-order" a work order is created and populated and the user can then fine tune the new work-order as required.
View 13 Replies
View Related
Sep 4, 2014
I am using a datasheet view with dbl click code to open a form to a selected record. I was able to use pbaldy's code and it worked perfectly.
DoCmd.OpenForm "Asset Status", , , "[Project Number] = '" & Me.Project_Number & " ' "
Then I realized I really want to be able to go to other records after I have gone to this form and tried this:
Dim rs As Object
Dim lngBookmark As Long
'set variable to the current record
lngBookmark = Me.Project_Number
'open new form
[code]....
But to no avail. Project_Number is a text field.
View 12 Replies
View Related
Apr 15, 2015
i've been working on a sample database today that someone has written some vb code into, which enables the database to merge with some dialler software. basically it's just a hang up and dial button on a form for surveying people.
i've done ok extracting the code for the dial and hangup buttons, referencing the external file and getting my test database to interface with the dialler but it currently allows multiple users to edit the same record, which i need to stop it doing.
the guy that's done the coding says he's more of a vb programmer and not much use in access (he's definitely better than me!) but i should be able to insert some code that will skip to the next record if the record is being worked on with someone else.
i think the piece of code that i need is
Forms("Form1").RecordLocks = 2
the only problem is i don't know what else to type with it or where in the code to insert it.
View 5 Replies
View Related
Apr 15, 2013
I have a form with numerous fields on it, with the one I'm concerned about being a date field.
I want a different form to open automatically if the above date field is in the past (this will act as a warning to the user).
I've created the warning form as a pop-up view but just want to know the best way to have it open automatically.
Can this be done via a macro in the OnOpen Property (or maybe OnLoad) or will this have to written in vba.
View 7 Replies
View Related
Feb 4, 2014
I've noticed that occasionally when pressing a button I've made to 'create a review record', it will overwrite the very first 'review' in the table even though it should be opening in the form acFormAdd.
View 2 Replies
View Related
Aug 5, 2013
Is there a way to have a date field populate automatically when a form opens but be able to change that date if need be?
View 4 Replies
View Related
Oct 29, 2013
I have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOpenPatientRecord"
[Code] ....
This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.
View 14 Replies
View Related
Apr 19, 2015
How do I reference a subform DS field to open up a tabbed form on another subform.
Here's what I have.
field one on the Sub DS opens up tab one on another form, as does field two on DS open tab two on another form.
but..
how can I click on "any" field on the DS, run an IF statement, and it checks if that record on the Datasheet, contains information in regards to, IF field one OR field two contains any data, and opens the tabbed form respectively on the other form?
View 3 Replies
View Related
Jun 21, 2005
Hi All,
I am looking for some help with a project I am working on where I need to automatically print a report from my database every time a new record is added to the table. The table contains 13 fields and the report needs to display 12 of them, the other being the index which is set to Autonumber.
The table is being updated solely by ODBC, this is working OK.
There is the potential for records to be added to the table very quickly via the ODBC link, so I need to safeguard that the report is being populated with correct information from the record that triggered the print event. Also, should multiple records be added in close succession, a report needs to be correctly generated/printed for each of one. The DB is to have no user intervention, and will just run on the PC at startup with all access menus/controls locked out.
Any information on how you think I should structure this, or any examples of helpful code that you might have would be very much appreciated.
Thanks in advance, :)
Jon.
View 7 Replies
View Related
Aug 16, 2014
I am trying to make use of the OpenForm command to open a form to a record where a textbox matches the value of a text box on another form.
On Form1, I have a textbox called txtRtnRef, and it contains a reference number in a similar format to SWR-9
On Form2, I have a textbox called txtReOrderRef (control source ReOrderRef) which also contains a reference number in the same format (SWR-9)
I am trying to have a button next to the text box on Form1 that has an OnClick Event that opens Form2 to the record containing the same text reference number.I have tried, to no avail, a number of variations of the OpenForm command, the most recent being
Code:
DoCmd.OpenForm "Form2", , , "ReOrderRef = '" & Me.txtRtnRef & "'"
The form opens, but to a blank record?
View 6 Replies
View Related
Oct 21, 2005
I've run several searches and can't seem to find what I'm looking for, so here goes:
Have a table with several (maybe as many as 30) dates related to each employee, such as:
NAME-------CLASS 1-----CLASS 2----CLASS 3
J.Johnson.....10/16/05 ......12/17/05...12/25/05
S.Smith.........09/05/05......10/15/05
Not all employees will have entries in every class.
Need a query to result in most recent Date for each employee, such as:
--------------
J.Johnson......CLASS 3........12/25/05
S.Smith.........CLASS 2.......10/15/05
Any suggestions?
View 3 Replies
View Related
Nov 15, 2005
I have a table that shows me 3 fields:
PROCESS
EFFECTIVE_
OPERATION_
My issue is each process and operation may be there multiple times due to multiple Effective dates. I only need to see each Process and Operation one time based on the latest Effective date. Below is what I have NOW:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-01 10
1/010/1-8TCOWLFEEDER 2005-11-01 20
1/010/1-8TCOWLFEEDER 2005-11-01 30
1/010/1-8TCOWLFEEDER 2005-11-01 40
1/010/1-8TCOWLFEEDER 2005-11-03 10
1/010/1-8TCOWLFEEDER 2005-11-03 20
1/010/1-8TCOWLFEEDER 2005-11-03 30
1/010/1-8TCOWLFEEDER 2005-11-03 40
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
This is what I need:
PROCESS EFFECTIVE_ OPERATION_
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40
Out of the records above I would only want to see the records with the 2005-11-09 date. The dates can vary based on processes so I really need something that selects the the latest date for each Process and Operation. I would like to do this in a query or multiple queries.
Thanks for any help.
View 2 Replies
View Related
Jan 17, 2005
I am hoping someone may be able to help me with this one. I have a set of dates that I would like to query for the next latest date based on certain criteria. Not sure if I explain clearly, if more info is required, please advise. Thanks so much in advance for your help.
View 4 Replies
View Related
Jul 21, 2005
I have a simple table in Access 2003:
Case# (numeric)
CaseOpen (Date)
CaseClose (Date)
Item# (numeric)
ItemOpen (Date)
ItemClose (Date)
Some of the date fields may be null; If null then that date may NOT be selected
I am trying to write a query that returns:
Case#
Item#
Only the most recent date in any of the four date fields
The heading that matches the selected date field
ie:
Case# = 251
Item# = 4756
Most Recent Date = 7/8/2005
Matching Heading = "ItemOpen"
I have been searching and reading for two days-
Help, please?
TIA
View 1 Replies
View Related
Feb 9, 2006
I'm trying to sort dates by the latest date when the query returns multiple IDs with different results. Ex.
ID1 1/1/2006
1/8/2006
ID2 1/2/2006
1/9/2006
In this example I would want ID1 with the date of 1/8/2006 and ID2 with the date of 1/9/2006 since they are the latest date. I will have many IDs that I need to run a query on that will all return the latest date. TIA
View 1 Replies
View Related
Mar 21, 2008
I need to find the LATEST date in field Recdate that is 45 days or older in a query. I have tried <Date()-45, etc. but it returns all dates not just the last one. Does this require a range of dates to do this ? If not, how would I id the last date input ?
Thanks
View 14 Replies
View Related
