Results 1 to 2 of 2

Thread: Run Time Error

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

    Unanswered: Run Time Error

    I have come up with the following code, but it gives me "run time error 91 - object variable or with block variable not set" at the .AddNew statement. Any clues as to what is going wrong?

    Option Compare Database
    Dim rs As ADODB.Recordset
    Dim recfound As Boolean



    Private Sub cmdSave_Click()
    With rs
    If recfound = True Then
    .Fields("S_REF") = txtS_REF
    .Fields("Applicant_Number") = txtApp_No
    .Fields("Term_Applied") = txtTerm
    .Fields("Age_at_Sept2003") = txtAge
    .Fields("Second_Level_of_Study_Attended_") = txtSecond_level
    .Fields("Department") = txtDept
    .Fields("Course_Title") = txtCourse_Title
    .Fields("Course_Code") = txtco_code
    .Fields("Repeating_year_at_NWIFHE") = txtRepeating
    .Fields("Education_Level") = txtEducation_Level
    .Update
    Else
    .AddNew
    .Fields("S_REF") = txtS_REF
    .Fields("Applicant_Number") = txtApp_No
    .Fields("Term_Applied") = txtTerm
    .Fields("Age_at_Sept2003") = txtAge
    .Fields("Second_Level_of_Study_Attended_") = txtSecond_level
    .Fields("Department") = txtDept
    .Fields("Course_Title") = txtCourse_Title
    .Fields("Course_Code") = txtco_code
    .Fields("Repeating_year_at_NWIFHE") = txtRepeating
    .Fields("Education_Level") = txtEducation_Level
    .Update
    End If
    End With
    End Sub

    Private Sub Form_Load()
    Set cn = CurrentProject.Connection
    Set rs = New Recordset
    rs.Open "Additional Personal Details", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
    End Sub

    Private Sub txtS_REF_AfterUpdate()
    With rs
    If .EOF Then
    Exit Sub
    End If
    .MoveFirst
    .Find "S_REF" & Me.txtS_REF
    If Not .EOF Then
    txtApp_No = .Fields("Applicant_Number")
    txtTerm = .Fields("Term_Applied")
    txtAge = .Fields("Age_at_Sept2003")
    txtSecond_level = .Fields("Second_Level_of_Study_Attended_")
    txtDept = .Fields("Department")
    txtCourse_Title = .Fields("Course_Title")
    txtco_code = .Fields("Course_Code")
    txtRepeating = .Fields("Repeating_year_at_NWIFHE")
    txtEducation_Level = .Fields("Education_Level")
    recfound = True
    Else
    txtApp_No = .Fields("Applicant_Number")
    txtTerm = .Fields("Term_Applied")
    txtAge = .Fields("Age_at_Sept2003")
    txtSecond_level = .Fields("Second_Level_of_Study_Attended_")
    txtDept = .Fields("Department")
    txtCourse_Title = .Fields("Course_Title")
    txtco_code = .Fields("Course_Code")
    txtRepeating = .Fields("Repeating_year_at_NWIFHE")
    txtEducation_Level = .Fields("Education_Level")
    recfound = False
    End If
    End With
    End Sub
    Take 'er handy!

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Run Time Error

    Originally posted by Coopstar
    I have come up with the following code, but it gives me "run time error 91 - object variable or with block variable not set" at the .AddNew statement. Any clues as to what is going wrong?

    Option Compare Database
    Dim rs As ADODB.Recordset
    Dim recfound As Boolean



    Private Sub cmdSave_Click()
    With rs
    If recfound = True Then
    .Fields("S_REF") = txtS_REF
    .Fields("Applicant_Number") = txtApp_No
    .Fields("Term_Applied") = txtTerm
    .Fields("Age_at_Sept2003") = txtAge
    .Fields("Second_Level_of_Study_Attended_") = txtSecond_level
    .Fields("Department") = txtDept
    .Fields("Course_Title") = txtCourse_Title
    .Fields("Course_Code") = txtco_code
    .Fields("Repeating_year_at_NWIFHE") = txtRepeating
    .Fields("Education_Level") = txtEducation_Level
    .Update
    Else
    .AddNew
    .Fields("S_REF") = txtS_REF
    .Fields("Applicant_Number") = txtApp_No
    .Fields("Term_Applied") = txtTerm
    .Fields("Age_at_Sept2003") = txtAge
    .Fields("Second_Level_of_Study_Attended_") = txtSecond_level
    .Fields("Department") = txtDept
    .Fields("Course_Title") = txtCourse_Title
    .Fields("Course_Code") = txtco_code
    .Fields("Repeating_year_at_NWIFHE") = txtRepeating
    .Fields("Education_Level") = txtEducation_Level
    .Update
    End If
    End With
    End Sub

    Private Sub Form_Load()
    Set cn = CurrentProject.Connection
    Set rs = New Recordset
    rs.Open "Additional Personal Details", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
    End Sub

    Private Sub txtS_REF_AfterUpdate()
    With rs
    If .EOF Then
    Exit Sub
    End If
    .MoveFirst
    .Find "S_REF" & Me.txtS_REF
    If Not .EOF Then
    txtApp_No = .Fields("Applicant_Number")
    txtTerm = .Fields("Term_Applied")
    txtAge = .Fields("Age_at_Sept2003")
    txtSecond_level = .Fields("Second_Level_of_Study_Attended_")
    txtDept = .Fields("Department")
    txtCourse_Title = .Fields("Course_Title")
    txtco_code = .Fields("Course_Code")
    txtRepeating = .Fields("Repeating_year_at_NWIFHE")
    txtEducation_Level = .Fields("Education_Level")
    recfound = True
    Else
    txtApp_No = .Fields("Applicant_Number")
    txtTerm = .Fields("Term_Applied")
    txtAge = .Fields("Age_at_Sept2003")
    txtSecond_level = .Fields("Second_Level_of_Study_Attended_")
    txtDept = .Fields("Department")
    txtCourse_Title = .Fields("Course_Title")
    txtco_code = .Fields("Course_Code")
    txtRepeating = .Fields("Repeating_year_at_NWIFHE")
    txtEducation_Level = .Fields("Education_Level")
    recfound = False
    End If
    End With
    End Sub
    I suspect it's with the recordset or connection object.

    Did you intend to declare the connection object first?

    I noticed you tried to set it but not declare. I usually use Option Explicit with my code and declare everything prior to use.

    If you have DAO and ADO referenced, you might also want to instantiate the recordset as ADODB.Recordset.

    Have you tried stepping through the code to see which line it fails on?

    Gregg

Posting Permissions

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