Results 1 to 5 of 5

Thread: Any Takers??

  1. #1
    Join Date
    Oct 2003
    Location
    Derry, Northern Ireland
    Posts
    18

    Unanswered: Any Takers??

    I have attached the code i came up with to add student additional personal info once it has been entered, it is supposed to update the info if there changes to be made later! it doesn't! If anyone out there can help i will put you on my crimbo card list!!lol


    Option Compare Database
    Private cn As Connection
    Private pers_tb As Recordset
    Dim rec As Boolean



    Private Sub cmdSave_Click()
    Set cn = CurrentProject.Connection
    Set pers_tb = New Recordset
    pers_tb.Open "Additional Personal Details", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
    ct = 0
    'pers_tb.MoveFirst
    'ct = pers_tb.RecordCount

    If rec = False Then
    With pers_tb
    .AddNew
    ![S_REF] = Me.S_REF
    ![Applicant_Number] = txtApp_No
    ![Term_Applied] = txtTerm
    ![Age_at_Sept_2003] = txtAge
    ![Second_Level_of_Study_Attended_] = txtSecond_level
    !Department = dept
    !Course_Title = txtCourse_Title
    !Course_Code = co_code
    ![Repeating_year_at_NWIFHE] = txtRepeating
    ![Education Level] = txtEducation_Level
    .Update
    End With
    MsgBox "Student Record added to file"

    Else

    With pers_tb
    ![S_REF] = Me.S_REF
    ![Applicant_Number] = txtApp_No
    ![Term_Applied] = txtTerm
    ![Age_at_Sept_2003] = txtAge
    ![Second_Level_of_Study_Attended_] = txtSecond_level
    !Department = dept
    !Course_Title = txtCourse_Title
    !Course_Code = co_code
    ![Repeating_year_at_NWIFHE] = txtRepeating
    ![Education_Level] = txtEducation_Level
    .Update
    End With
    MsgBox "Student record updated"
    End If

    End Sub

    Private Sub Form_Load()
    Dim ct As Integer
    Set cn = CurrentProject.Connection
    Set pers_tb = New Recordset
    pers_tb.Open "Additional Personal Details", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
    ct = 0
    rec = False


    If pers_tb.RecordCount > 0 Then
    With pers_tb
    Do
    If Me.S_REF = !S_REF Then
    txtApp_No = ![Applicant_Number]
    txtTerm = ![Term_Applied]
    txtAge = ![Age_at_Sept_2003]
    txtSecond_level = ![Second_Level_of_Study_Attended_]
    dept = !Department
    txtCourse_Title = !Course_Title
    co_code = !Course_Code
    txtRepeating = ![Repeating_year_at_NWIFHE]
    txtEducation_Level = ![Education_Level]
    rec = True
    Exit Do
    End If
    Loop While !EOF = False
    End With
    End If
    End Sub
    Take 'er handy!

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Any Takers??

    Originally posted by Coopstar
    I have attached the code i came up with to add student additional personal info once it has been entered, it is supposed to update the info if there changes to be made later! it doesn't! If anyone out there can help i will put you on my crimbo card list!!lol


    Option Compare Database
    Private cn As Connection
    Private pers_tb As Recordset
    Dim rec As Boolean



    Private Sub cmdSave_Click()
    Set cn = CurrentProject.Connection
    Set pers_tb = New Recordset
    pers_tb.Open "Additional Personal Details", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
    ct = 0
    'pers_tb.MoveFirst
    'ct = pers_tb.RecordCount

    If rec = False Then
    With pers_tb
    .AddNew
    ![S_REF] = Me.S_REF
    ![Applicant_Number] = txtApp_No
    ![Term_Applied] = txtTerm
    ![Age_at_Sept_2003] = txtAge
    ![Second_Level_of_Study_Attended_] = txtSecond_level
    !Department = dept
    !Course_Title = txtCourse_Title
    !Course_Code = co_code
    ![Repeating_year_at_NWIFHE] = txtRepeating
    ![Education Level] = txtEducation_Level
    .Update
    End With
    MsgBox "Student Record added to file"

    Else

    With pers_tb
    ![S_REF] = Me.S_REF
    ![Applicant_Number] = txtApp_No
    ![Term_Applied] = txtTerm
    ![Age_at_Sept_2003] = txtAge
    ![Second_Level_of_Study_Attended_] = txtSecond_level
    !Department = dept
    !Course_Title = txtCourse_Title
    !Course_Code = co_code
    ![Repeating_year_at_NWIFHE] = txtRepeating
    ![Education_Level] = txtEducation_Level
    .Update
    End With
    MsgBox "Student record updated"
    End If

    End Sub

    Private Sub Form_Load()
    Dim ct As Integer
    Set cn = CurrentProject.Connection
    Set pers_tb = New Recordset
    pers_tb.Open "Additional Personal Details", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
    ct = 0
    rec = False


    If pers_tb.RecordCount > 0 Then
    With pers_tb
    Do
    If Me.S_REF = !S_REF Then
    txtApp_No = ![Applicant_Number]
    txtTerm = ![Term_Applied]
    txtAge = ![Age_at_Sept_2003]
    txtSecond_level = ![Second_Level_of_Study_Attended_]
    dept = !Department
    txtCourse_Title = !Course_Title
    co_code = !Course_Code
    txtRepeating = ![Repeating_year_at_NWIFHE]
    txtEducation_Level = ![Education_Level]
    rec = True
    Exit Do
    End If
    Loop While !EOF = False
    End With
    End If
    End Sub
    You haven't specified what error message you get, but when I look at the code I can see three potential problems:

    You haven't set your connection string correctly. I can't see
    Set cn = New ADODB.Connection
    any where in your code. You could actually set this as part of the recordset using the ActiveConnection property

    You should qualify the recordset,connection or command with ADODB so there is no potential confusion with DAO

    You also have !EOF=False instead of .EOF=False - EOF is a property of the recordset and not a field.

  3. #3
    Join Date
    Oct 2003
    Location
    Derry, Northern Ireland
    Posts
    18
    at the moment it just keeps adding a new record and doesn't update if the details need to be altered! i will make the changes you sugested and take it from there! thanx
    Take 'er handy!

  4. #4
    Join Date
    Oct 2003
    Location
    Derry, Northern Ireland
    Posts
    18
    I dont have the ADODB connection just yet as i am working with test data, so i would adjust the connection once i get it to enter and then update the records. It still just re-enters data and doesn't update!
    Take 'er handy!

  5. #5
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by Coopstar
    I dont have the ADODB connection just yet as i am working with test data, so i would adjust the connection once i get it to enter and then update the records. It still just re-enters data and doesn't update!
    have you stepped through the code in debug to see exactly what it is doing? It may never reach the update code as the condition is never met!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •