Full Version: Find First
UtterAccess Forums > Microsoft® Access > Access Forms
sithlord2008
Hi All,
I am trying to write a code that will find the first case Id in a record set and then place that number in a field on my form.
So far I have tried writing:
Dim intResponse As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("QryAutoAssignLog", dbOpenDynaset)


With rst
rst.FindFirst !ClientID
rst.Edit
Forms!frmContactHomePage!NextCase = ClientID

End With
Does anyone know what I am doing wrong.
Thanks
Lee
Alan_G
Hi Lee
indFirst doesn't do what you're thinking it does. It finds the first record that matches some criteria that you supply - eg
rst.FindFirst "ClientID " = & Me.txtClientID
Also, when you use With, you don't reference the variable or object again - eg
With rst
.FindFirst "ClientID = " & Me.txtClientID
.Edit
End With
Now, to answer your question, I don't think you don't need to open a recordset. Either DFirst() or possibly DMin() depending on what you're doing should get you what you want.........
Forms!frmContactHomePage!NextCase = DFirst("ClientID","QryAutoAssignLog")
or
Forms!frmContactHomePage!NextCase = DMin("ClientID","QryAutoAssignLog")
sithlord2008
H Alan,
Thanks for the tip. I have used the code, but it is not puling out the next record.
I have a query that only shows cases based on my criteria, which all works fine, but when I use the code to look up the first Client ID, it show the very first Client ID based on number ie 85225 is before 85226, but the first client based on my criteria and order is 85226,
How can I fix this.
Thanks
Lee
Alan_G
Hi
I'm not sure what you mean by
<<but it is not puling out the next record>>
Can you give an example of the ClientID's returned by your query and the one you're wanting to get......
sithlord2008
Hi Alan,
I have a query that shows the following Results
Client ID Date Due
1 18/09/08
2 18/09/08
3 16/09/08
4 16/09/08
5 05/09/08
6 18/09/08
The query sorts by Date due so the clients would be displayed in the following order
5,3,4,1,2,6
So my first record in the query is 5. This is the client ID I require, but the code brings back 1 as this is the lowest number in the table.
I think I need to refer to the date due column and then client ID, but still return the client ID to my field on the form.
I hope that helps.
Thanks
Lee
sithlord2008
Hi Alan,
orget my last post, I have changed my query to display the top value on the date field and it all works fine now. I have tested it through and it is bringing up the next correct case.
Thanks for all the help.
Thanks
Lee
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.