[RESOLVED] Associate button index with record id in database

[Nightwalker83]

Hi,

How would I associate record in a database the the index of a command button? This is the I use at the moment

 If rs.RecordCount > 0 Then
   'Check if the recordset contain records'
   If Not rs.RecordCount = 0 Then
   'Move to the first record'
      rs.MoveFirst
   'Loop till the recordset return EOF(end of file)'
      Do While Not rs.EOF And Not rs.BOF And Not i > rs.RecordCount
       Command1(i).Caption = rs.Fields("Name")
       Command1(i).BackColor = &H80FF&
       Entry.FillFields
   'Move to the next record'
      rs.MoveNext
      i = i + 1
     Loop
     rs.MoveFirst
     i = 0
    End If
 End If

However, the record is progressed before the button index is changed and thus clicking the button populates the wrong data into the form compared to the associated record in the database.

Edit:

The attached project is a project I am helping forum member elRuffsta with.

Edit II:

if you make a choice after, the first two then it will display the previous choice rather than the current choice.

Thanks,


Nightwalker

[SamOscarBrown]

Well, without looking at the project, "I" would create a new field in the table and call it something like 'buttonIndex' and when I inserted something into the table based upon the click event of the cmdbtn, I'd insert the index of the button right there. Then it will always be available, and at form load one could always sort the initial query by buttonIndex to put the correct caption on the correct cmdbtn.
Make sense?

[DataMiser]

There is also some code there that should not be there

If rs.RecordCount > 0 Then
   'Check if the recordset contain records'
   If Not rs.RecordCount = 0 Then ' This will never be 0 when this line executes
   'Move to the first record'
      rs.MoveFirst
   'Loop till the recordset return EOF(end of file)'
      Do While Not rs.EOF And Not rs.BOF And Not i > rs.RecordCount
   'No need for  those ands bof will never be true and i will never be > recordcount

I agree with Sam that index should be added to the record when the record is added.

[SamOscarBrown]

Yes.....Indices change (well, they don't CHANGE), but say you delete a record, and then add one in its place. That new record will NOT retain the original ID (index), hence a new field makes a whole lot more sense.

[LaVolpe]

There is also some code there that should not be there

For completeness, probably should not be relying on rs.RecordCount either. Depending on provider and/or method returning the recordset, the RecordCount property may be -1 when a populated recordset is returned.

ADO Recordset Property
The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

Maybe something like this?

If rs.State = adStateOpen Then
   Do Until rs.EOF = True
      ....

      rs.MoveNext
   Loop
End If

[IkkeEnGij]

look here post #43
http://www.vbforums.com/showthread.p...ase-help/page2

[techgnome]

Well, without looking at the project, "I" would create a new field in the table and call it something like 'buttonIndex' and when I inserted something into the table based upon the click event of the cmdbtn, I'd insert the index of the button right there. Then it will always be available, and at form load one could always sort the initial query by buttonIndex to put the correct caption on the correct cmdbtn.
Make sense?

There is also some code there that should not be there

If rs.RecordCount > 0 Then
   'Check if the recordset contain records'
   If Not rs.RecordCount = 0 Then ' This will never be 0 when this line executes
   'Move to the first record'
      rs.MoveFirst
   'Loop till the recordset return EOF(end of file)'
      Do While Not rs.EOF And Not rs.BOF And Not i > rs.RecordCount
   'No need for  those ands bof will never be true and i will never be > recordcount

I agree with Sam that index should be added to the record when the record is added.

so.... you'd add UI data to the data? Bleh... I'd go the other way around... stuff the record's ID value into the Tag property of the button... then I don't care one wit if something is added, deleted, shuffled, etc. I also then don't need to muck with the originating data.

Also something to consider, if I'm reading the loop right, it will stop when the max count of buttons is reached... so if there are 10 records, and only 5 buttons, only the first 5 buttons get linked. Assuming then there is a way to paginate to the second set, I'd first have to clear out the new magic field in the first 5 rows before I can move on to the next 5.

-tg

[DataMiser]

I think that if the data is to be matched to a button index then adding the index of the button as an indexed field in the DB is the best way to go.
It doesn't matter if something is added, removed nor changed the index field will match the index of the control. Seems extremely simple to me.

Yes you could use the tag but why bother?

The way I understand it is each record is linked to a button so there should not even be a changing number of records, 1 record for each button, record index= button index no problem

[IkkeEnGij]

@DataMiser
one would think that is the most logical thing to do, simply because it is so extremely logical
but on september 14th at 01:30 AM, i posted a link that would one enable to do just that in an extremely easy way
and on september 20th at 02:49 PM, i even clearly explained how to do just such a thing
i even posted 2 lines of code that did just that (because it only takes 2 lines of code)
but probably that is to simple and to logical

[Nightwalker83]

Well, without looking at the project, "I" would create a new field in the table and call it something like 'buttonIndex' and when I inserted something into the table based upon the click event of the cmdbtn, I'd insert the index of the button right there. Then it will always be available, and at form load one could always sort the initial query by buttonIndex to put the correct caption on the correct cmdbtn.
Make sense?

There is already a field called "ID" in the database which holds the button index. The ID field is set as the primary key.

look here post #43
http://www.vbforums.com/showthread.p...ase-help/page2

What is SlotNr? I already have already set the primary key for the database.

I think that if the data is to be matched to a button index then adding the index of the button as an indexed field in the DB is the best way to go.
It doesn't matter if something is added, removed nor changed the index field will match the index of the control. Seems extremely simple to me.

Yes you could use the tag but why bother?

The way I understand it is each record is linked to a button so there should not even be a changing number of records, 1 record for each button, record index= button index no problem

So you are saying I need separate fields, one to hold the button index and one for the item number?

[DataMiser]

I don't know what you mean by item number nor where I said anything about a separate field for it, but if there is an item number and it is different than the index of the button then yes it should be a separate field.

Each piece of data should have a field for it.

If the data were index and caption then that would be two fields one for the index, the other for the caption

Populating the controls would be simple, just loop through the data

command1(rs("Index")).Caption=rs("Caption")

If you want to update a record just use the button index as the criteria for the update or whatever.

Granted I have not looked at the project but I did skim over the thread where this question came from and it seemed extremely simple.

[IkkeEnGij]

Granted I have not looked at the project but I did skim over the thread where this question came from and it seemed extremely simple.

yes, it IS extremely simple

[IkkeEnGij]

What is SlotNr? I already have already set the primary key for the database.

start microsoft access
make a new database call it 'elruf.mdb' or whatever you want
make a new table call it 'SeedTray'
add a field call it 'SlotNr'
give it a datatype of longinteger
make it the primary key (and call the index msaccess creates for you 'SlotNr')
give it a validationrule of: >0 And <73
give it a validationtext of: the slot number has to be from 1 to 72 both included
Default Value= nothing(simply remove whatever there is) probably it was 0
required=yes
indexed=yes(no duplicates)

-----
edit,in that topic 'forgot' tp mention that the field called 'Memo' , is indeed a memo field
my bad

[Nightwalker83]

This is what I have at the moment

If rs.RecordCount > 0 Then
   'Move to the first record'
      rs.MoveFirst
   'Loop till the recordset return EOF(end of file)'
      Do While Not rs.EOF
       Command1(rs.Fields("ID")).Caption = rs.Fields("Name")
       Command1(rs.Fields("ID")).BackColor = &H80FF&
       
         'Checks if we are at the first or last record.
        Entry.Caption = Entry.Caption & " Record Exists!"
        Entry.Label2.Caption = rs.Fields("Name") 'Saves the Seed name to the database
        Entry.Label7.Caption = rs.Fields("Qty") 'How many seeds
        Entry.Label8.Caption = rs.Fields("Date")  'The date the seed was planted
        Entry.Label10.Caption = rs.Fields("Plant Type")   'The type of seed (example: vegtable)
        Entry.Option1(index).Caption = rs.Fields("Spice")  'The how hot the seed is
        Entry.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
        If Not (rs.BOF = True And rs.EOF = True) Then Entry.Command2.Enabled = True
   'Move to the next record'
      rs.MoveNext
     Loop
    End If

I have tried using rs.find to find the record matching the Command1(rs.Fields("ID")) that is stored in the database! However, I keep receiving

run-time error '3021'

Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record.

I suspect it is because the rs does not like the data type of the ID stored in the database although, I have tried both integer and string but it does not make a difference.

[SamOscarBrown]

OK....I DO see how the ID field is 'somewhat' related to the index of the command button....When you insert a new record, the code assigns the cmdbtn index to the ID field...GREAT!!! However, when you re-start your project, the following loop does not look at the ID field...IOW, it just loops through each record and assigns each record to the commandbutton index starting with zero through the number of records.
For example, if you have ID;s of 0,1,14,16,2,4 (six records), and then start the project, the ID is ignored and (using i in the code) will set the captions OF THE FIRST SIX cmdbuttons (0 through 5) to the name field of each record, so numbers 14 and 16 will still say "Empty" even though they were originally changed by adding an item to that position.

'Loop till the recordset return EOF(end of file)'
      Do While Not rs.EOF And Not rs.BOF And Not i > rs.RecordCount
       Command1(i).Caption = rs.Fields("Name")
       Command1(i).BackColor = &H80FF&
       Entry.FillFields
   'Move to the next record'
      rs.MoveNext
      i = i + 1
     Loop

[Nightwalker83]

The reason I do not use name as the primary key is because elRuffsta could have to entries for Cayanne Peppers but the name would not be unique. I figured the only identifier that would be unique is the command button index number.

[SamOscarBrown]

I have no problem with using ID as PK. I was just pointing out ONE thing wrong with this program (there are lots more). As the table keeps the ID correct, upon form load (or refresh), elRuffsta simply needs to make sure that the proper cmdbutton is assigned the proper caption (table field-Name) based upon the index/ID. That's all.

I know you know how to code.....seen some of your projects and code for quite a while.....why don't you just rewrite the entire thing for el....(as was mentioned by Ikky, its a very simple project....just designed kinda wierd.)? Not sure of el's capability after looking at the project.....:-)

[Nightwalker83]

I know you know how to code.....seen some of your projects and code for quite a while.....why don't you just rewrite the entire thing for el....(as was mentioned by Ikky, its a very simple project....just designed kinda wierd.)? Not sure of el's capability after looking at the project.....:-)

I was wondering whether I should or not? From what I remember from the original project available in the thread Ikky linked to in post #6 the code was beyond elRuffsta's level of understanding. I was wondering why he wanted 72 command buttons on the main form surely there is an easier method of doing what he wants to achieve? Although, that being said I do not really have the time to redesign the program for him and I am now eager to see what I can achieve this using the above method.

[SamOscarBrown]

I was wondering why he wanted 72 command buttons on the main form surely there is an easier method of doing what he wants to achieve? .

AYUP, a grid would suffice, and much easier to set up. Anyway, I'm outa here.....made my comment about making sure the cmdbuttons' indices matched the ID field---that is the first thing I woulda fixed for el.
Have a great day!
Sami

[IkkeEnGij]

I do not really have the time to redesign the program

it took me 2 minutes to design a perfectly workable database
with a table who's fields were not in blatant disacord with each other
it took me 2 minutes to "write" a program that dit everything what was needed
and that permitted the eventual users to use it as they have always used a seedtray
that's 4 minutes
all that was left to do was graphical embellischment
it took me maybe 10 minutes to explain what i had done
so that's 14 minutes
but i admit 14 completely wasted minutes

I was wondering why he wanted 72 command buttons

probably because he tought 72 command buttons where a perfect imitation of a seedtray
probably because he tought the eventual users would not recognise a picture of a seedtray as a seedtray ?

It sounds like you need one-on-one help, and the place to request that sort of assistance is probably the Project Requests forum. Once you have a volunteer or two you can coordinate through a project thread in the related Project Communication Area.

but that also would have been to easy, i suppose

Nightwalker83 let me give you some advice
1) if someone gives you a database that can only be labelled as 'garbage'
trow it where it's place is :the garbage can

2) if someone gives you a program that can only be labelled as 'garbage'
trow it where it's place is :the garbage can

according to your actions, i think you are a saint
but:
not even a saint can recycle complete garbage in something usable

[Nightwalker83]

Ikky,

I don't think it is the database to blame here because I redesigned it adding renamed "ID" to "ButtonID" and adding a "RecordID" column! The data is saved in the database correctly and this is the code I use to save the ids

          rs.Fields("ButtonID") = Main.Command1(cmdindex).index
          rs.Fields("RecordID") = Main.Command1(cmdindex).index

now given that all the information is being pulled from the database there should NOT be a problem with

 rs.MoveNext

However, I still encounter the same error as above.

[DataMiser]

What error? Where is it happening?
Why two fields containing the same thing?

[Nightwalker83]

What error?

Run time error '3001'

Arguments of wrong type, are out of range, or are in conflict with one another.

Where is it happening?

 rs.MoveNext

Why two fields containing the same thing?

In case the program was getting confused somewhere along the line.

[IkkeEnGij]

Ikky,

I don't think it is the database to blame here because I redesigned it adding renamed "ID" to "ButtonID" and adding a "RecordID" column! The data is saved in the database correctly and this is the code I use to save the ids

lets see if there is nothing wrong with the database
there is a field 'ID' of type string
is there not supposed to be a 1 on 1 relationship between that field and a button index ?
so there has to be a 1 on 1 relationship between a string and an integer
possible ? sure, clever ? surely not
that 'ID' field is the primary key...ok
on that 'ID' field are 2 indexes
the one that makes it the primary key, named 'PrimaryKey'...and thus unique
the one that is there for reasons known to nobody,named 'ID'...not unique
if anyone knows how an index can at the same time be unique and not unique...please let me know
there is a field 'Date' what that field stands for i can not even ques,surely not a date type...for it is a string
there is a field 'Qty' i suppose that is for the number of seeds that has been planted in a specific slot ?
so there can be more then 1 (because there is a quantity field)
but since there are fields spice,plant type, that implies al the seeds need to be the same spice,plant type,
but if there are the fields Status and Died,that means al the seeds need to have the same status and if 1 dies,all the others die of symphaty ?
but all that i already made very clear in that other topic
but other than that,there there are only minor things that could need more thought,such as the Notes field can only take 50 characters
all that makes it very clear that the genius who disigned that database has not the faintest idea what people do with seedtray's
nor does he/she have the faintest idea of the environment seedtray's are used in...not the ideal environment to use a computer in

[Nightwalker83]

lets see if there is nothing wrong with the database

Here is the project as it was on my last attempt.

[IkkeEnGij]

as i am not a saint, i bail out
i already gave all the info needed to make a seedtray system that can be used to full satisfaction by the millions of seedtray users
and whats more, it only needs 2 lines of code (in the ide, as an exe it needs 3)

[DataMiser]

Well the first thing I noticed was that the program hangs while the splash screen is showing due to a missing movenext

 If Not rs.RecordCount = 0 Then
   Do Until rs.EOF
    For i = 0 To rs.RecordCount
       Command1(i).Caption = rs.Fields("Name")
       Command1(i).BackColor = &H80FF&
    Next i
    rs.MoveNext
   Loop

The missing move next results in an endless loop

Also have no idea why there is a loop inside the loop. This would just keep setting the caption and color over and over and over as many times as there are records but always to the same values. Absolutely no reason to do this and there is no reason to have the If Statement above the Do.

Try something like this

   Do Until rs.EOF
       Command1(Val(rs.Fields("buttonid"))).Caption = rs.Fields("Name")
       Command1(Val(rs.Fields("buttonid"))).BackColor = &H80FF&
       rs.MoveNext
   Loop

Ideally you should have the ID field as a number and drop the val() statement but it is what it is

Also ideally there should be one record in the table for each button. They should not be deleted nor missing but have the data updated as needed to empty or whatever the case may be so all the program needs to do is read the values and/or update the values

[Nightwalker83]

Also ideally there should be one record in the table for each button. They should not be deleted nor missing but have the data updated as needed to empty or whatever the case may be so all the program needs to do is read the values and/or update the values

Thanks! That solves the problem on the buttons lining up and the captions but the rest of the data is still out of sync.

[IkkeEnGij]

Also ideally there should be one record in the table for each button. They should not be deleted nor missing but have the data updated as needed to empty or whatever the case may be

great idea, if anyone wants to know the easiest way to do it, see post#6 in this topic

so all the program needs to do is read the values and/or update the values

true
if anyone wants to know how to do that without a single line of code, see post#6 in this topic
whether it is a good idea to do it without a single line of code is debatable...but possible

[Nightwalker83]

great idea, if anyone wants to know the easiest way to do it, see post#6 in this topic

true
if anyone wants to know how to do that without a single line of code, see post#6 in this topic
whether it is a good idea to do it without a single line of code is debatable...but possible

How do you give the field a validation rule and validation text?

Edit:

I managed to solve the problem using a workaround!

Option Explicit
 Dim i As Integer
Private Sub Command1_Click(index As Integer)
 If Command1(cmdindex).Caption = "Empty" Then
     With Add
     Command1(cmdindex).Caption = .ShowModalAndGetNewCaption(Me, Command1(index).Caption)
  End With
 Else
 If Command1(index).index = rs.Fields("ButtonID") Then
 Entry.Show
 Command1(index).Enabled = False
 End If
 End If
End Sub

Private Sub Form_Load()
 table = "Tray"
 DB = "DATA"
 Update = False
 'instantiate the connection object
 'LocalHost
 Set cn = New ADODB.Connection
 cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\" & DB & ".mdb"

 cn.Open cn 'open the connection
  
 'instantiate the recordset object
 Set rs = New ADODB.Recordset
    'open the recordset
 With rs
  .Open table, cn, adOpenKeyset, adLockPessimistic, adCmdTable
 End With
    'Check if the recordset contain records'
   If Not rs.RecordCount = 0 Then
    Do Until rs.EOF
       Command1(Val(rs.Fields("buttonid"))).Caption = rs.Fields("Name")
       Command1(Val(rs.Fields("buttonid"))).BackColor = &H80FF&
       rs.MoveNext
   Loop
   rs.MoveFirst
        If Not (rs.BOF = True And rs.EOF = True) Then Entry.Command2.Enabled = True
 End If
  For i = 0 To 71
   If Command1(i).Caption = "Empty" Then Command1(i).Enabled = False
 Next i
End Sub

However, that will the disable the ability to add new records and the records would have to be accessed in the order they were entered into the database. Also, if you don't disable the "Empty" buttons thus keeping the ability to add more records and you access the previous records in order, then click the "Empty" buttons you will eventually receive the same error mentioned in post #14.

[DataMiser]

Not sure what you mean about data being out of sync

Did you understand the concept of the one to one button to record.

If you have 72 buttons then create 72 records each with an id=button index so every record is linked to a button

[DataMiser]

I was just having another look at the code from the zip you posted and see another major issue

Private Sub showEntry(index As Integer)
 For i = 0 To rs.RecordCount
        rs.MoveFirst
        
   If rs.Fields("ButtonID") = index Then
          'Checks if we are at the first or last record.
        Entry.Caption = Entry.Caption & " Record Exists!"
        Entry.Label2.Caption = rs.Fields("Name") 'Saves the Seed name to the database
        Entry.Label7.Caption = rs.Fields("Qty") 'How many seeds
        Entry.Label8.Caption = rs.Fields("Date")  'The date the seed was planted
        Entry.Label10.Caption = rs.Fields("Plant Type")   'The type of seed (example: vegtable)
        Entry.Option1(index).Caption = rs.Fields("Spice")  'The how hot the seed is
        Entry.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
        End If
        rs.MoveNext
     Next i
      Entry.Show
End Sub

This will always being processing the first record in the recordset. Why on earth would anyone put a .movefirst in a loop that is supposed to iterate through a recordset?

Why use an indexed for loop? It would seem that rs.Find would be the way to go here.

So far I have only looked at the code of two routines and both have loop issues, I wonder how many more of these there are?

[DataMiser]

Looking a little deeper.
This code makes no sense

Public Function ShowModalAndGetNewCaption(OwnerForm As Object, CurCaption As String) As String
  mCaption = CurCaption
   Show vbModal, OwnerForm
  ShowModalAndGetNewCaption = mCaption
End Function

It is returning the same thing that it is passed ???

This code

Private Sub Command1_Click(index As Integer)
 If Command1(cmdindex).Caption = "Empty" Then
     With Add
     
     Command1(cmdindex).Caption = .ShowModalAndGetNewCaption(Me, Command1(index).Caption)
  End With
 Else
  showEntry (Command1(cmdindex).index)
 End If
End Sub

Also has issues cmdindex should be Index so it would actually react to the button that was actually clicked and not some unknown value stored in cmdindex

The edit form also has some issues, no code behind the save button and the delete button I guess deletes the current record from the DB, didn't look far enough to see if it was working with the right record but based on some of the other code my guess would be that it just deletes the second record if there are more than 1 record.

My opinion, most of the code needs to be scrapped and start over.

[IkkeEnGij]

How do you give the field a validation rule and validation text?

in table design view
in the upper part you design the fields
in the lower part you choice additional properties of the fields
among these properties there is Validation Rule and Validation Text
note that the validationrule is not strictly needed,it just insured that there can only be records with a primary key from 1 to 72
or in your case 0 tp 71 ?

[IkkeEnGij]

and the records would have to be accessed in the order they were entered into the database.

FYI a table is an unordered collection of records
the order records are entered in a table is completely irrevelant

[MarkT]

I don't know if this is what you are after but it's what I came up with after quick pass to clean up the code. I aproached things a bit differently.

[Nightwalker83]

Not sure what you mean about data being out of sync

Say I click on button 5 the record being shown does not match the record 4 in the database.

Did you understand the concept of the one to one button to record.

If you have 72 buttons then create 72 records each with an id=button index so every record is linked to a button[/QUOTE]

Yes, I that is how it is suppose to work but in practice sometimes things do not work out the way you intended.

I was just having another look at the code from the zip you posted and see another major issue

Private Sub showEntry(index As Integer)
 For i = 0 To rs.RecordCount
        rs.MoveFirst
        
   If rs.Fields("ButtonID") = index Then
          'Checks if we are at the first or last record.
        Entry.Caption = Entry.Caption & " Record Exists!"
        Entry.Label2.Caption = rs.Fields("Name") 'Saves the Seed name to the database
        Entry.Label7.Caption = rs.Fields("Qty") 'How many seeds
        Entry.Label8.Caption = rs.Fields("Date")  'The date the seed was planted
        Entry.Label10.Caption = rs.Fields("Plant Type")   'The type of seed (example: vegtable)
        Entry.Option1(index).Caption = rs.Fields("Spice")  'The how hot the seed is
        Entry.MousePointer = vbNormal 'sets the mouse pointer to the normal arrow
        End If
        rs.MoveNext
     Next i
      Entry.Show
End Sub

This will always being processing the first record in the recordset. Why on earth would anyone put a .movefirst in a loop that is supposed to iterate through a recordset?

The rs.MoveFirst keep causing an error when it was before the "For Next".

Why use an indexed for loop? It would seem that rs.Find would be the way to go here.

Yes, I know! I have used a find in the passed and it works great! However, in this project it keeps giving me the same message as in post #23.

Looking a little deeper.
This code makes no sense

Public Function ShowModalAndGetNewCaption(OwnerForm As Object, CurCaption As String) As String
  mCaption = CurCaption
   Show vbModal, OwnerForm
  ShowModalAndGetNewCaption = mCaption
End Function

It is returning the same thing that it is passed ???

This code

Private Sub Command1_Click(index As Integer)
 If Command1(cmdindex).Caption = "Empty" Then
     With Add
     
     Command1(cmdindex).Caption = .ShowModalAndGetNewCaption(Me, Command1(index).Caption)
  End With
 Else
  showEntry (Command1(cmdindex).index)
 End If
End Sub

Yes, it does what Schmidt said it did! That is his code.

in table design view
in the upper part you design the fields
in the lower part you choice additional properties of the fields
among these properties there is Validation Rule and Validation Text
note that the validationrule is not strictly needed,it just insured that there can only be records with a primary key from 1 to 72
or in your case 0 tp 71 ?

Ah ok. thanks!

FYI a table is an unordered collection of records
the order records are entered in a table is completely irrevelant

The information is being retrieved like that because the button index matches the id in the database! I do not know why it is looping like that? It is suppose to show the record of the button I click on independent of which non-Empty button I click on.

I don't know if this is what you are after but it's what I came up with after quick pass to clean up the code. I aproached things a bit differently.

Ah ok. Thanks! I will take a look at the attachment.

[DataMiser]

Say I click on button 5 the record being shown does not match the record 4 in the database.

Probably due to that move first. It could only find a match if it were the first record

Yes, I that is how it is suppose to work but in practice sometimes things do not work out the way you intended.

It would definitely work, should not be any issue at all but it is not coded that way.

The rs.MoveFirst keep causing an error when it was before the "For Next".

What error? If you want to loop through a recordset starting at the first record then the movefirst must be before the loop and never under any condition would it go inside the loop. As I mentioned before you would always be looking at the first record, it there were 72 records then you would be looking at the first record 72 times and when the loop exits you would be on record #2

Yes, I know! I have used a find in the passed and it works great! However, in this project it keeps giving me the same message as in post #23.

Which means either you coded the find wrong or the problem is not related to the find.

Yes, it does what Schmidt said it did! That is his code.

Yeah, but it really makes no sense to create a function that returns exactly what it has been passed. I have no idea why such a function would be created.

[Nightwalker83]

What error?

The same one mentioned in post #14.

Edit:

MarkT, That project is exactly what I am after.

[DataMiser]

run-time error '3021'

Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record.

That error is pretty self explanatory, indicating that there is no current record, possibly dealing with an empty RS. Moving the .MoveFirst from above the For statement to just after it would not change anything related to the error as it would not make records appear nor would the current record or absence thereof be any different a few lines later unless there was some other code in play that populated the recordset in between. Moving the movefirst inside the loop would just make your loop useless.