Error 3075 and Others

[Lrn2Code]

Hi! I'm relatively new to the programming world and definitely need some help.

Am working on an application and trying to call a recordset to compare data that was entered into a form to data that is in a table making sure the "new" entry won't be a duplicate of what's already in the table. I keep getting strange errors that I can't figure out - one is the 3075 error and another is "Object variable or with block variable not set".

I have no clue what this means but I cannot get my "set rst =" phrase to actually work. The rst variable keeps showing up as "= nothing". And once it hits the set rst = dbs.OpenRecordset(strSQL) the program jumps down to the error line.

My code is pasted below - any guidance you can provide is greatly appreciated, and if you need more information just let me know. Thank you!

strSQL = "Select * from tblschoolclasses where coursename = '" & strName & "' and coursecat = '" & strcoursecat & "' and POSID = '" & strPSID & ""
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
MsgBox "This course name already exists at this school."
Exit Sub
End If

 

[DCrake]

Have you dimmed the Rst variable?

Dim Rst As DAO.Recordset

Set Rst = CurrentDb.OpenRecordset(".....")
If Not Rst.EOF And Not Rst.BOF Then
   MsgBox ...
   Rst.Close
End If
   
Set Rst = Nothing

David

 

[Lrn2Code]

Yes, it is listed as you wrote - Dim rst As DAO.Recordset

At the top of the sub the following are declared -

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strName As String
Dim lngID As Long
Dim strSQL As String
Dim strPSID As String
Dim strcoursecat As String
Dim txtCourseCat As String
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Dim strCoursename1 As String
Dim strcoursecat1 As String

 

[DCrake]

What values have you for your strcoursecat?
If you do a Debug.Print strSQL after the line do it look syntactically correct?

 

[Lrn2Code]

Yes the values are listed correctly as they appear in the form it's running from -

Select * from tblschoolclasses where coursename = 'GRADE 1' and coursecat = 'Elementary Education-General (K-6)' and POSID = 'PS241

 

[DCrake]

So what is the problem you are encountering? If you cut and paste the sql into a new query does that work or does access thow an error?

Noticed no closing single quote on your thread is this a typo or missing from your code?
David

 

[gemma-the-husky]

if all you want to know is "have i entered this already" use dlookup instead

dim foundit as long
foundit = nz(dlookup("classid","tblschoolclasses","coursename = '" & strName & "' and coursecat = '" & strcoursecat & "' and POSID = '" & strPSID & ""),0)

this will return the classid of (the first) any matching record if it exists.

-------
or if the coursename/coursecat/posid is a unique key, then you could just try to insert the record which will fail (error 3022? offhand) and you can trap it that way.

 

[Lrn2Code]

I missed the last quote in my copy/paste of the code.

The most recent errors I'm getting are still the 3075 error (with the original code I pasted yesterday) and now (that I've created different code using CreateQueryDef I'm getting error 91).

strSQL = "Select * from tblschoolclasses where coursename = '" & strName & "' and coursecat = '" & strcoursecat & "' and POSID = '" & strPSID & "'"
Debug.Print (strSQL)
Set qdf = dbs.CreateQueryDef("CheckCourseList", strSQL)
Debug.Print (qdf)
If qdf.MaxRecords > 0 Then
MsgBox "This course name already exists at this school."
Exit Sub
End If
​

My code kept jumping over the "SET RST =" part and I can't figure out why because it looks like the SQL statement is populating correctly. Of course now it's jumping over the "SET QDF =".

Also, if a user doesn't want to update the course information in the form they can just return to the main menu but I get an error 2467 on that -

Private Sub cmdClose_Click()
On Error GoTo errClose

If X = 0 Then
If MsgBox("You have not updated this course name and/or category. Do you want to exit anyway?", vbYesNo) = vbNo Then
Exit Sub
Else
DoCmd.Close '(frmUpdateSchoolClasses1)
End If
End If
​

I really wish I understood this stuff better...it's so confusing. Plus I'm trying to learn PL/SQL code too. No wonder my head is going to explode!

 

[Lrn2Code]

if all you want to know is "have i entered this already" use dlookup instead

dim foundit as long
foundit = nz(dlookup("classid","tblschoolclasses","coursename = '" & strName & "' and coursecat = '" & strcoursecat & "' and POSID = '" & strPSID & ""),0)

this will return the classid of (the first) any matching record if it exists.

Running this gives me error 3078. I changed classid to courseid (that's what's listed in the table) and I'm getting the error. I've probably messed something up royally.

-------
or if the coursename/coursecat/posid is a unique key, then you could just try to insert the record which will fail (error 3022? offhand) and you can trap it that way.

Unfortunately there is no unique key.

 

[Lrn2Code]

Figured out part of it - needed to have the rst reference the CurrentDB - Set rst = CurrentDb.OpenRecordset(strSQL). This got rid of the Error 91.

Don't know why it wasn't automatically doing that as there's no other DB I'm dealing with. Now am trying to pinpoint the number of EXACT matches - just got a record count of 25 which is every record in the table for that POS. All I care about is an exact match.

Thanks for your guidance - got me on the right track! (Was tired of being derailed.)