Caption & Adodc.Recordset

[greysquirl]

I have two questions. . . .

1.) I am using (4) buttons to navigate the recordset. They are cmdFirst, cmdPrevious, cmdNext & cmdLast. The code I am using is AdodcMaster.Recordset.MoveFirst,Previous etc.
The problem I am having is that if I navigate to the very last record or the very first record and then keep hitting that button I recieve an error: Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record. How do I keep this from happening. Perhaps an error message stating that they have reached the end???

2.) I am wanting to put a label on my app that reflects what record the user is curerntly working on. I.E. Employee 40 of 536. . . . I know that the code is similar to AdodcMaster.Recordset.Caption, but I am not sure what code to use to get this effect (Employee # of #).

[vbuser1976]

Okay, in answer to your 1st question, in order to not get that BOF/EOF error you need to add an OnError goto handle in the sub where it accesses the recordset. Here's an example:

Private Sub cmdSvNew_Click() 
On Error GoTo Errhandler
    'Process Data Here
Exit Sub
Errhandler:
    Dim errornew As Integer
    errornew = MsgBox("Invalid! Beginning/End of record", vbOKOnly + vbExclamation, Title:="Error")
End Sub

And that should do it.

Now for your 2nd question, try this:first of all, all of your records should have an ID number, such as an Autonumber that just keeps track of all the records.

dim CurrRec as integer
dim TotalRec as integer
dim answer as string
'open the recordset here
CurrRec = Recordset.IdNumber.Text
TotalRec = Recordset.IdNumber.Count
answer = "Record # " & CurrRec & " of " & TotalRec & ""

Then you can assign answer to the caption.

I hope this helps. You might have to make changes on the code though in order for it to suit your needs. Good Luck!

[honeybee]

To answer your first question of navigating:

I think the EOF/BOF is triggered only if you move past the last or the first record. So you can try this:

rsRecordSet.MoveNext
If rsRecordSet.EOF Then
   rsRecordSet.MoveFirst
Endif

And a similar code for MovePrev.

This code wraps around to the first record, which you can easily modify to suit your needs.

[greysquirl]

I just wanted to thank you very much for the help. Your code examples helped tremendously and I was able to finish the project I was working on. But as long as your helping. . .I have another question, lol.

Anyways, I have several comboboxes on my prog and each access the database with a seperate Adodc (AdodcRace, AdodcSex, AdodcSupervisor.....etc.). I am having trouble with updating. When I make a change in the database to say....the supervisor i.e. change one of the supervisors last name because she got married; it updates in the combobox with her new name, but the problem I am having is that it doesn't update all of her employees with the correct name that has been updated. Does this make sense? If I didn't explain it right please let me know. Again thank you for your help before and in advance for help with this one.

[vbuser1976]

from what I understand, It looks like you need to either refresh/requery if the tables are linked or you might need to do a mass update of the tables. Let me know if this is what you need.

Good Luck!

[greysquirl]

When you ask if the tables are linked, do you mean are they related to each other in Access (Relationships)? They are. The code your talking about sounds like what I am trying to do, but I have no idea where o start. Thanks

[vbuser1976]

Are the fields that you need to update in any way linked in the relationships? Not the tables but the fields you want to update(in other words, are they copies of the main field or a pointer to the main field). If it is a copy of the main field, then what should work is using a UPDATE command in SQL, but it might have to be multiple updates. I was thinking of something like this:

UPDATE Field1 from table1
where field1 = @fieldrefer

but you would have to create a separate UPDATE for each table you want to update. Hopefully, some other forum buddy might have a better solution. This one could be a little time consuming.

Good luck!

PS-Or you can try passing parameters to a procedure to process the updates ByRef

[Edited by vbuser1976 on 11-20-2000 at 11:18 AM]

[greysquirl]

I'm kinda understnading where your going with this but maybe I can give you the names of one of the tables and you can clrify this for me.

Database: FEDS.mdb
Table in Access: Supervisor (Relationship: SupervisorID in Table Supervisor linked to SupervisorID in Master Table)
VB6: ComboBox is called cboSupervisor
Adodc's: AdodcMaster - drives most of the app
AdodcSupervisor - drives cboSupervisor

I hope this helps.

Thanks

[vbuser1976]

As you might already know, you can update the combobox by adding this command line at the end of adding an update:
[code]cboSupervisor.Refresh

Now for the records that are already in the master, you can try this(IN SQL, as a stored proc in VB or as a string command in VB):

strSQL = "UPDATE SupervisorID from MasterTable
Where Supervisor.SupervisorID LIKE " & left(Supervisor.SupervisorId, LengthOfFirstName) & ""

left() is a function in VB which brings back a string of characters moving from left to right. the syntax is like this:

Left(String, Length As Long)

I hope this gets you started and Good luck!

[greysquirl]

Thanks for the help. . . it was exactly what I needed

[greysquirl]

OK, I thought I had it down. . . .I guess I was wrong.

I tried the refresh thing and that didn't work.

If I wanted to use the Sql statement where would I place the code at?

Here is what I get. . . .

RowSource: AdodcSupervisor
ListField: Supervisor
DataSource: AdodcMaster
DataField; SupervisorID

I placed a DataCombo on the app and linked it to the database using the above mentioned settings. When I run the app and select a supervisor it gives me this error when I try to update or move to the next record.

Run-Time error '-2147217842 (80040e4e)

The change was cancelled during notification; no columns are changed

Sorry for any inconvenience this problem may be causing you, and thanks for th help

[mpSmooth]

when your trying to move through the list, and you using your own command buttons or clicking inside of the list box. Throw in the bookmark property, this is also very helpful..

[Edited by mpSmooth on 11-21-2000 at 10:24 AM]

[jess]

Hi greysquirl,
I'm running into the same kind of problem as you describe when I'm trying to update some values that are connected to the adodc control. Did you ever figure out the problem? I would really appriciate if you could let me know how you solved it.

Thanks

[greysquirl]

Here is the code I used for my four navigation buttons. I didn't really fix the problem, I just went around it sorta speak. What I did was make it do a big loop; in other words when it gets to the end it just reverts back to the beginning. The last few lines of code on bottom is the update stuff I used. Hope this helps.

Private Sub cmdFirst_Click()
On Error GoTo FirstErr
AdodcMaster.Recordset.MoveFirst
Exit Sub
FirstErr:
MsgBox ("You Are At The Beginning.")
End Sub

Private Sub cmdLast_Click()
On Error GoTo LastErr
AdodcMaster.Recordset.MoveLast
Exit Sub
LastErr:
MsgBox ("You Are At The End.")
End Sub

Private Sub cmdNext_Click()
AdodcMaster.Recordset.MoveNext
If AdodcMaster.Recordset.EOF Then
AdodcMaster.Recordset.MoveFirst
End If
End Sub

Private Sub cmdPrevious_Click()
AdodcMaster.Recordset.MovePrevious
If AdodcMaster.Recordset.BOF Then
AdodcMaster.Recordset.MoveLast
End If
End Sub

Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr
AdodcMaster.Recordset.UpdateBatch adAffectCurrent
Exit Sub
UpdateErr:
MsgBox ("Record Could Not Be Updated.")
End Sub

[paulw]

Hi greysquirl,

You really shouldn't use error handling like this. What happens if there is an error unrelated to your position in the recordset?

You should use the EOF and BOF properties to ensure that those errors cannot occur. The best way is to disable the move buttons that are inappropriate. I have some code at home to do this but you won't get it until Monday.

As to the Updates, why not enable Cascading updates in the data. This will automatically deal with the updates necessary. If the relationships cannot handle that then you will have to issue a series of UPDATE SQL commands in a batch to update all the dependent records.

Later,

Cheers,

P.