Unable to move records, first, last,etc.

P

pctutoratwccc

New member
Local time
Today, 03:40
Joined
Nov 25, 2010
Messages
6
Hi,

My first post here.

I have a form filled with employees, Northwind that I've been working on with a student I tutor. The home work was due yesterday, but I am bound and determine to get it working.

I can get the form populated. The last records loaded, stays where it at. I'll click move first and it moves and that is the last of the moving.

I've been stareing at it, re-doing spots, reading books, yes and even googling. I'm so tired that nothing is making any sense.

My student' next chapter involves "Object-oriented programming with Access VBA.

Until now my only experience with VBA has been very sketchy. I did say, "Until now", but that first day was 12 weeks ago. I'm not a child anymore.

Can somebody please tell me what i did wrong? Throw me some hints.

I'll be gone for awile, but I'll be back by 11pm DST.
 

Attachments

The file you have posted, would appear to contain no tables :confused: that would certainly be an impediment to the smooth operation of your DB.
 
i haven't looked, but what do you mean "move first"

this doesn't change the order of your items - it probably just jumps your position in the file to the first item.

in the menu bars, you will see a sort button (A-Z or Z-A). click on any column, and then click A-Z to resort. You can filterdata also.

if you want a complicated default sort order, you need to use a query, not a table, and sort the fields in the query in the order you want.
 
Hi All,

I guess I should have explained.

I "volunteered" to help a student with Michael Vine's " . . VBA for Absolute Beginner". I am some what a neophyte. I have taken the school's both Access classes, but first time with VBA.

A week ago:

Well, the professor through us for a loop. My student informed me, the instructor changed the syllabus, and we are now doing Chapter 10. We are now doing Database Programming with ADO. I've started on it, but if someone could give me hints, suggestions and definitely a few websites I will be greatly appreciative.

Chapter10 is Database Programming with ADO.

My student had to turn, by midnight Wednesday, an incomplete assignment. I'm still determined to make this program work, so here I am.

We used the Northwind.accdb to connect to for the assignment.

Any suggestions will be greatly appreciated.

After populating the form, all of the textboxes are filled with employee ID #9 data.

In straight steps, I click

MovePrevious - Nothing happens
MoveFirst - It moves to #1
MoveNext - Nothing happens
MoveLast - I get the following error message:

"There was an error retrieving information from the database"

Thanks in advance.

Respectfully,

pctutoratwccc
 
i'm out. I don't use ADO.

however, do you have code on the move buttons? If so, you will need to post the code.
 
Hi Dave
i'm out. I don't use ADO. however, do you have code on the move buttons? If so, you will need to post the code.
Click to expand...
I hope it is Ok. I uploaded the program.

I was up all night, but I was able to get it to work, well, sort of. It isn't consistent. I click move next and the record goes to 2 and I get "There was an error retrieving information from the database 3265. Item cannot be found in the collection corresponding to the requested name of the ordinal." Click goes to 3, get error message, etc. Does the same thing through the entire recordset.


I haven't worked on the ADD, UPDATE or DELETE, yet.

Have a few errands to run, be back asapl.
Any hints, suggestions, etc. will be greatly appreciated.

Thanks in advance.

Respectfully,

pctutoratwccc
 
I run 2003 so I can't open the attached file, but I suspect
After populating the form, all of the textboxes are filled with employee ID #9 data.
Click to expand...
may be a clue to your problem. Exactly how are you populating your form? Depending on the method, it's possible that there's only one record in the recordset.

Linq ;0)>
 
After populating the form, all of the textboxes are filled with employee ID #9 data
Click to expand...
I wrote that wrong. I don't know how or why I wrote what I did, but should have been all9 records. see attachment.

Code: 
Option Compare Database
 
Dim remoteConnection As New ADODB.Connection
Dim rsEmployees As New ADODB.Recordset
Public Sub SetRecordset()
Dim x As Integer
Dim i As Integer
Dim Nim As Integer
    Dim sql As String
 
    'DoCmd.GoToRecord , , AcNewRecord
    On Error GoTo DbError
 
    sql = "select * from Employees"
 
    rsEmployees.CursorType = adOpenKeyset
    rsEmployees.LockType = adLockReadOnly
 
    rsEmployees.Open sql, remoteConnection, _
        , , adCmdText
    'rsEmployees.MoveNext
    If rsEmployees.RecordCount > 0 Then
        x = 0
        'Do While x < rsEmployees.RecordCount
        Do Until rsEmployees.EOF = True
        Me.txtID = rsEmployees!ID
        Me.txtCompany = rsEmployees!Company
        Me.txtFirstName = rsEmployees.Fields.Item("First Name")
        Me.txtLastName = rsEmployees.Fields.Item("Last Name")
        Me.txtEmail = rsEmployees.Fields.Item("E-Mail Address")
        Me.txtJobTitle = rsEmployees.Fields.Item("Job Title")
        Me.txtBusinessPhone = rsEmployees.Fields.Item("Business Phone")
        Me.txtHomePhone = rsEmployees.Fields.Item("Home Phone")
        Me.txtMobilePhone = rsEmployees.Fields.Item("Mobile Phone")
        Me.txtFaxNumber = rsEmployees.Fields.Item("Fax Number")
        Me.txtAddress = rsEmployees!Address
        Me.txtCity = rsEmployees!City
        Me.txtStateProvince = rsEmployees.Fields.Item("State/Province")
        Me.txtZipPostCode = rsEmployees.Fields.Item("Zip/Postal Code")
        Me.txtCountryRegion = rsEmployees.Fields.Item("Country/Region")
        Me.txtWebPage = rsEmployees.Fields.Item("Web Page")
        Me.txtNotes = rsEmployees!Notes
        Me.txtAttachments = rsEmployees!Attachments
        DoCmd.GoToRecord , , acNewRec
        rsEmployees.MoveNext
 
            Nim = Val(Me.txtID)
            x = x + 1
            txtRecordCount.Value = rsEmployees.RecordCount
            MsgBox "ID Number is " & Nim
 
        Loop
 
    End If
 
 
    Exit Sub
DbError:
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub
Private Sub cmdAdd_Click()
    Dim sql As String
    Dim rsAdd As New ADODB.Recordset
 
    On Error GoTo DbError
    'Assign updatable cursor and lock type properties.
    rsAdd.CursorType = adOpenDynamic
    rsAdd.LockType = adLockOptimistic
   'Open the Recordset object.
    rsAdd.Open "Employees", remoteConnection, , , adCmdTable
   'Add the record based on input from the user
    '(except for the AutoNumber primary key field).
    With rsAdd
        .AddNew
        .Fields.Item("Company") = Me.txtCompany
        .Fields.Item("First Name") = Me.txtFirstName
        .Fields.Item("Last Name") = Me.txtLastName
        .Fields.Item("E-Mail Address") = Me.txtEmail
        .Fields.Item("Job Title") = Me.txtJobTitle
        .Fields.Item("Business Phone") = Me.txtBusinessPhone
        .Fields.Item("Home Phone") = Me.txtHomePhone
        .Fields.Item("Mobile Phone") = Me.txtMobilePhone
        .Fields.Item("Fax Number") = Me.txtFaxNumber
        .Fields.Item("Address") = Me.txtAddress
        .Fields.Item("City") = Me.txtCity
        .Fields.Item("State/Province") = Me.txtStateProvince
        .Fields.Item("Zip/Postal Code") = Me.txtZipPostCode
        .Fields.Item("Country/Region") = Me.txtCountryRegion
        .Fields.Item("Web Page") = Me.txtWebPage
        .Fields.Item("Notes") = Me.txtNotes
        .Fields.Item("Attachments") = Me.txtAttachments
        .Update
        .Close
     End With
 
    MsgBox "Record Added.", vbInformation
 
    'Close the form-level Recordset object and refresh
    'it to include the newly updated row.
 
    rsEmployees.Close
    SetRecordset
 
    Exit Sub
DbError:
    MsgBox "There was an error adding the record." _
        & Err.Number & ", " & Err.Description
End Sub
Private Sub cmdDelete_Click()
    Dim sql As String
    Dim rsDelete As New ADODB.Recordset
 
        On Error GoTo DbError
 
        'Build dynamic SQL statement based on
    'record selected by the user.
 
    sql = "select * from Employees where ID = " & _
        Val(Me.txtID.Value)
 
        'Assign updatable cursor and lock type properties.
    rsDelete.CursorType = adOpenDynamic
    rsDelete.LockType = adLockOptimistic
        'Open the Recordset object.
    rsDelete.Open sql, remoteConnection, , , adCmdText
        'Don't try to delete the record, if the
    'recordset did not find a row.
    If rsDelete.EOF = False Then
              'Update the record based on input from the user.
        With rsDelete
            .Delete
            .Update
            .Close
        End With
    End If
        MsgBox "Record deleted.", vbInformation
        'Close the form-level Recordset object and refresh
    'it to include the newly updated row.
    rsEmployees.Close
    SetRecordset
        Exit Sub
DbError:
    MsgBox "There was an error deleting the record." _
        & Err.Number & ", " & Err.Description
End Sub
Private Sub cmdMoveFirst_Click()
On Error GoTo DbError
    'Move to the first record in the result set.
 
        rsEmployees.MoveFirst
 
        Me.txtID = rsEmployees!ID
        Me.txtCompany = rsEmployees!Company
        Me.txtFirstName = rsEmployees.Fields.Item("First Name")
        Me.txtLastName = rsEmployees.Fields.Item("Last Name")
        Me.txtEmail = rsEmployees.Fields.Item("E-Mail Address")
        Me.txtJobTitle = rsEmployees.Fields.Item("Job Title")
        Me.txtBusinessPhone = rsEmployees.Fields.Item("Business Phone")
        Me.txtHomePhone = rsEmployees.Fields.Item("Home Phone")
        Me.txtMobilePhone = rsEmployees.Fields.Item("Mobile Phone")
        Me.txtFaxNumber = rsEmployees.Fields.Item("Fax Number")
        Me.txtAddress = rsEmployees!Address
        Me.txtCity = rsEmployees!City
        Me.txtStateProvince = rsEmployees.Fields.Item("State/Province")
        Me.txtZipPostCode = rsEmployees.Fields.Item("Zip/Postal Code")
        Me.txtCountryRegion = rsEmployees.Fields.Item("Country/Region")
        Me.txtWebPage = rsEmployees.Fields.Item("Web Page")
        Me.txtNotes = rsEmployees!Notes
        Me.txtAttachments = rsEmployees!Attachments
 
   Exit Sub
 
DbError:
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub
Private Sub cmdMoveLast_Click()
'rst.Fields("[FVH PLAN]")
On Error GoTo DbError
        'Move to the last record in the result set.
    If rsEmployees.AbsolutePosition > 1 Then
            rsEmployees.MoveLast
        Me.txtID = rsEmployees!ID
        Me.txtCompany = rsEmployees!Company
        Me.txtFirstName = rsEmployees.Fields.Item("[First Name]")
        Me.txtLastName = rsEmployees.Fields.Item("[Last Name]")
        Me.txtEmail = rsEmployees.Fields.Item("E-Mail Address")
        Me.txtJobTitle = rsEmployees.Fields.Item("[Job Title]")
        Me.txtBusinessPhone = rsEmployees.Fields.Item("[Business Phone]")
        Me.txtHomePhone = rsEmployees.Fields.Item("Home Phone")
        Me.txtFaxNumber = rsEmployees.Fields.Item("Fax Number")
        Me.txtMobilePhone = rsEmployees.Fields.Item("[Mobile Phone]")
        Me.txtAddress = rsEmployees!Address
        Me.txtCity = rsEmployees!City
        Me.txtStateProvince = rsEmployees.Fields.Item("[State/Province]")
        Me.txtZipPostCode = rsEmployees.Fields.Item("[Zip/Postal Code]")
        Me.txtCountryRegion = rsEmployees.Fields.Item("[Country/Region]")
        Me.txtWebPage = rsEmployees.Fields.Item("[Web Page]")
        Me.txtNotes = rsEmployees!Note
        Me.txtAttachments = rsEmployees!Attachment
      'Do Until yourrecordset.EOF
'Loop
 
     End If
 
DbError:
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
 
End Sub
Private Sub cmdMoveNext_Click()
Dim Nim As Integer
Dim Nimmer As Integer
    On Error GoTo DbError
 
        'Move to the next record in the result set if the cursor is not
        'already at the last record.
        'Move to the last record in the result set.
 
 
        Do Until rsEmployees.EOF = True
        rsEmployees.MoveNext
 
        Me.txtID = rsEmployees!ID
        Me.txtCompany = rsEmployees!Company
        Me.txtFirstName = rsEmployees.Fields.Item("[First Name]")
        Me.txtLastName = rsEmployees.Fields.Item("[Last Name]")
        Me.txtEmail = rsEmployees.Fields.Item("[E-Mail Address]")
        Me.txtJobTitle = rsEmployees.Fields.Item("[Job Title]")
        Me.txtBusinessPhone = rsEmployees.Fields.Item("[Business Phone]")
        Me.txtHomePhone = rsEmployees.Fields.Item("[Home Phone]")
        Me.txtFaxNumber = rsEmployees.Fields.Item("[Fax Number]")
        Me.txtMobilePhone = rsEmployees.Fields.Item("[Mobile Phone]")
        Me.txtAddress = rsEmployees!Address
        Me.txtCity = rsEmployees!City
        Me.txtStateProvince = rsEmployees.Fields.Item("State/Province]")
        Me.txtZipPostCode = rsEmployees.Fields.Item("[Zip/Postal Code]")
        Me.txtCountryRegion = rsEmployees!Country / Region
        Me.txtWebPage = rsEmployees.Fields.Item("[Web Page]")
        Me.txtNotes = rsEmployees!Note
        Me.txtAttachments = rsEmployees!Attachments
 
        Loop
 
 
 
    Exit Sub
 
DbError:
    MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
End Sub
Private Sub cmdMovePrevious_Click()
    On Error GoTo DbError
 
        'Move to the previous record in the result set, if the
    'current record is not the first record.
 
    If rsEmployees.AbsolutePosition > 1 Then
            rsEmployees.MovePrevious
        Me.txtID = rsEmployees!ID
        Me.txtCompany = rsEmployees!Company
        Me.txtFirstName = rsEmployees.Fields.Item("First Name")
        Me.txtLastName = rsEmployees.Fields.Item("Last Name")
        Me.txtEmail = rsEmployees.Fields.Item("E-Mail Address")
        Me.txtJobTitle = rsEmployees.Fields.Item("Job Title")
        Me.txtBusinessPhone = rsEmployees.Fields.Item("Business Phone")
        Me.txtHomePhone = rsEmployees.Fields.Item("Home Phone")
        Me.txtMobilePhone = rsEmployees.Fields.Item("Mobile Phone")
        Me.txtFaxNumber = rsEmployees.Fields.Item("Fax Number")
        Me.txtAddress = rsEmployees!Address
        Me.txtCity = rsEmployees!City
        Me.txtStateProvince = rsEmployees.Fields.Item("State/Province")
        Me.txtZipPostCode = rsEmployees.Fields.Item("Zip/Postal Code")
        Me.txtCountryRegion = rsEmployees.Fields.Item("Country/Region")
        Me.txtWebPage = rsEmployees.Fields.Item("Web Page")
        Me.txtNotes = rsEmployees!Notes
        Me.txtAttachments = rsEmployees!Attachments
 
    End If
 
       Exit Sub
 
DbError:
   MsgBox "There was an error retrieving information " & _
        "from the database." _
        & Err.Number & ", " & Err.Description
 
End Sub
Private Sub cmdUpdate_Click()
End Sub
Private Sub Form_Load()
    Connect
    SetRecordset
 
End Sub
Public Sub Disconnect()
 
    On Error GoTo ConnectionError
 
    rsEmployees.Close
    remoteConnection.Close
    Exit Sub
 
ConnectionError:
    MsgBox "There was an error closing the database." & _
        Err.Number & ", " & Err.Description
 
End Sub
Private Sub Connect()
    On Error GoTo ConnectionError
    With remoteConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open "X:\Cpt. 208\MS Access 2007 VBA ABS Beginnr\chapter10\Northwind 2007.accdb"
    End With
 
    Exit Sub
 
ConnectionError:
 
    MsgBox "There was an error connecting to the database. " & _
        Chr(13) & Err.Number & ", " & Err.Description
 
End Sub
Private Sub Form_Unload(Cancel As Integer)
    Disconnect
End Sub

Still need to get the ADD, UPDATE AND DELETE.

I'm bound and determined to see this to all the way to the end with a working program.

I also started to work on Chapter 11 Object-Oriented Programming with Access VBA.

Looks like I'll be up all night again.

This semester is almost over and I don't know what subjects I'll be tutoring in the Spring.

These are the database subjects left.
CPT 271—ORACLE SQL I 3-0-3
This course will provide students with an introduction to the SQL
query language as it is used in an Oracle database environment.
The course content will include creating and modifying queries, the
design of effective queries and query programming. Prerequisite(s):​

CPT 196 or CPT 235

CPT 235—DATABASE MANAGEMENT SYSTEMS 3-0-3
Explore the theory behind data management through the use of a
database management system and stressing the importance of good
database design. Topics include controlling redundancy, entity-relationship
diagrams, normalization and an introduction to SQL. Prerequisite(​

s): CPT 151 or CPT 196

What do "You" recommend? Or some other database class?

I have the Database Mgmt. Sys.book and I should have no problem picking up the Oracle.

Thanks in advance.

Respectfully,

pctutoratwccc
 

Attachments

Hi All,

I surrender. Can somebody please point me in the right direction.

Long story, short weekend and I don't know wich end is up.

I discovered, with all of the Move*, the only field moving was the ID #. I thought I needed a Loop, but it doesn't make sense. How do you combine all fields into one and move? Only the text field is moving.

I'm working on the next chapter. This one should be fum.

Attached is the latest copy. Any help will be greatly appreciated.

Thanks in advance.

Respectfully,

pctutoratwccc
 

Attachments

You must log in or register to reply here.

Similar threads

A
Solved In a continuous form : Disable / lock a textbox on a specific record on clicking a button within that record. Any way it can be done ?
Replies
6
Views
465
alan2013
A
J
Just sayin hello
Replies
6
Views
295
Isaac
Isaac
Uncle Gizmo
Misleading Grouping Claim
Replies
19
Views
429
LarryE
LarryE
jwcolby54
Using ChatGPT as a personal editor
Replies
0
Views
419
jwcolby54
jwcolby54
GPGeorge
  • Question Question
Migrating Northwind Developer Edition to PowerApps
Replies
1
Views
610
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom