Navigation with Combo Box values 1

[mxtreme]

I am aiming to use last names of employees to navigate through each employee, i.e. forward, back, first and last. However my form is set up with unbound controls so I can not use the normal navigation buttons.

I was just wondering how I can take the value from my combo box of lastnames, loop through the records and find the next name forward, back, first and last. I am assuming there is some sort of search mixed with a loop to do this but im sure of the syntax and format.


Thanx for any help,

Shaun.

 

[JimFlower]

Dim DB As Database
Dim rst As Recordset
Set DB = CurrentDb
Set rst = DB.OpenRecordset("TableName", dbOpenDynaset)
rst.FindFirst "FeildName ='" & Me.LastName & "'"

Me.x = rst.x
Me.y = rst.y
Me.z = rst.z

rst.Close
Set DB = Nothing

 

[JimFlower]

You may also have the record you want in the combobox just add columns and ref them in code

x = Me.LastName.column(5)

etc

 

[mxtreme]

I assume that is the statement to return the attributes from that current last_name but what I actually want to do is find the first, next, previous and last "name" when each button is pressed. Once the name is found, I run another query to populate the fields on the form so that this stage I just want one variable value.

I have seen that the recordset has FindFirst, FindNext, FindPrevious and FindLast as properties so I thought they might be able to be used but I still cant nail it down to the correct syntax.


Shaun.

 

[mxtreme]

And additionally, the combobox has only one column, which is the attribute "chrName_last".

 

[JimFlower]

Dim DB As Database
Dim rst As Recordset
Set DB = CurrentDb
Set rst = DB.OpenRecordset("TableName", dbOpenDynaset)
rst.MovePrevious
rst.MoveNext
rst.MoveLast
rst.MoveFirst

rst.Close
Set DB = Nothing

TableName can be swaped for SQL

 

[mxtreme]

All, this is the code I get to:

Dim DB As DAO.Database
Dim rs As DAO.Recordset

sSql = "SELECT tblEmployee.intEmployee_id, " & _
"tblEmployee.chrName_last " & _
"FROM tblEmployee;"

Set DB = CurrentDb
Set rs = DB.OpenRecordset(sSql, dbOpenSnapshot)


To that stage i'm fine, but when I go to use findFirst or moveFirst, I'm not sure how to go about it.

sSql is returning 500 records with two columns. So rs(0) will be the value of each employee id and rs(1) will be the value of each last name. This is to ensure each record is unique as there will be no doubt duplicate last names.

the line " rs.moveFirst " does what exactly??? and how can it be used. Likewise for moveLast, moveForward, movePrevious. (My access help doesnt seem to include much about these attributes of the recordset)

Shaun.

 

[RoyVidar]

Help files often come in handy, funny thing, my A97 help files and AXP help files are nearly identical. If you're using 2000, try installing the VBA helpfiles from the office CD. Here's some of the informationg on the find and move methods of the recordset:

On the Find methods of the DAO recordset:
[tt]
Find method Begins searching at Search direction
FindFirst Beginning of recordset End of recordset
FindLast End of recordset Beginning of recordset
FindNext Current record End of recordset
FindPrevious Current record End of recordset
[/tt]
On the move methods:
Move to the first, last, next or previous record in a specified recordset.

Think the initial question relates to the find methods.
There's a long example in the helpfiles.

rs.findfirst "somefield = '" & strSomeTextCriteria & "'"

moves to the first occurence where the criteria matches. If none is found, I think both rs.eof and rs.nomatch would evaluate to true.

rs.findnext "somefield = '" & strSomeTextCriteria & "'"

would continue the search. To avoid moving your recordset to the end if none is found, consider storing the bookmark of the current record in a variable, and if nomatch:

[tt]rs.bookmark = varBokmark[/tt]

- to return to "previous current record"

Roy-Vidar

 

[mxtreme]

The moveLast and moveFirst are working fine as I am using the complete table as a recordset. It just runs an SQL procedure and goes to the first record or last record in the set, (first record or last record in the table). I am just struggling to work out how to pass the current name_last and associate_id from the form and utilising the next and previous moves.

------------------------

Dim intAssoc As Integer
Dim strName_last As String
Dim DB As DAO.Database
Dim rst As DAO.Recordset

sSql = "SELECT tblEmployee.chrName_last, tblEmployee.intEmployee_id " & _
"FROM tblEmployee " & _
"ORDER BY tblEmployee.chrName_last;"

Set DB = CurrentDb
Set rst = DB.OpenRecordset(sSql, dbOpenSnapshot)
rst.MoveLast 'rst.MoveFirst
strName_last = rst(0)
cboEmployee_id.Value = rst(1)

-------------------------------

I now need to take the values in, find the record in the query and rst.MovePrevious or rst.MoveNext. At this stage a WHERE clause tacked onto the query won't work as it will obviously only return one record.

Thanx for the help so far Roy, it has been much appreciated.

Shaun.

 

[RoyVidar]

Don't understand. Think I've covered the find syntax in my first post. JimFlower has also demonstrated how to use .FindFirst with the value from a control on the form named "LastName". In my code, just substitute the strSomeTextCriteria with a reference to the form control holding the value you'd like to find.

Just to clarify, to find records, use one of the find methods, to move to next, previous record from the current record, or move to the first or last record use one of the move methods.

Roy-Vidar

 

[mxtreme]

Yeh, the findFirst and findLast is fine, the code in my last post works fine. Even if that wasn't what was instructed, it still works.

It is the move previous and move next commands that I cant get to work as I wrote last time. As the .move method doesnt take parameters, i cant tell it what value to move from. I must have missed something or my cranium isnt working today so I will leave it at that.

This is one of those tasks that will just get the delagation stick.

Thanx for ya help guys, my vba skills need to be tuned I think.

 

[RoyVidar]

As stated "Move to the first, last, next or previous record in a specified recordset." - and yes, this doesn't require a parameter.

Aren't you trying to find records? Why use the move methods? They just move, not find.

Having a recordset where the bold is the current record:
[tt]
...
101 Will Brown
102 Jane Doe
105 John Doe
25 Martin Doe
987 Jason Schmidt
...[/tt]

The move methods does just that.
movenext would make " 25 Martin Doe" the current record. Another movenext would make "987 Jason Schmidt" the current record. MovePrevious from "105 John Doe" would make "102 Jane Doe" the current record, another moveprevious would make "101 Will Brown"

To find the next or previous, use the find methods. With the above current record using a .FindNext <criteria thingie> with Doe, would result in making &quot;25 Martin Doe&quot; the current record. Issuing another .Findnext would (in this recordset) result in rs.nomatch, whilst the movenext method would have continued thru the recordset.

So, again/repeating, using the move methods will change currentrecord according to the direction, nomatter what values resides in the recordset. Using the find methods will take the criteria into consideration, and change currentrecord based on the criteria. The find methods are used to find records in the specified search direction with the provided criteria.

BTW - there's a typo in my first reply, the .FindPrevious methods search direction is Beginning of recordset, not End of recordset.

Roy-Vidar

 

[mxtreme]

Finally got it out. Now all navigation works. Lucky I'm not a charity or no-one would donate to this cause....

----------------------------
Dim intEmp As Integer
Dim strName_last As String
Dim DB As DAO.Database
Dim rst As DAO.Recordset

aSql = &quot;SELECT tblEmployee.chrName_last, tblEmployee.intEmployee_id &quot; & _
&quot;FROM tblEmployee &quot; & _
&quot;ORDER BY tblEmployee.chrName_last;&quot;

Set DB = CurrentDb
Set rst = DB.OpenRecordset(aSql, dbOpenSnapshot)

If Not rst.BOF Then
rst.MoveLast
Do While Not (rst(0) = cboName_last.Value And rst(1) = cboEmployee_id.Value)
rst.MovePrevious
Loop
Else
MsgBox (txtName_first.Value & &quot; &quot; & cboName_last.Value & &quot; is the first employee in the database.&quot
End If

rst.MovePrevious

If rst.BOF = True Then
MsgBox (txtName_first.Value & &quot; &quot; & cboName_last.Value & &quot; is the first employee in the database.&quot
Exit Sub
End If

-----------------------------------

Thanx again for the help guys. I probably confused you too many times to mention.

Shaun.