OpenRecordset type mismatch

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a DB "School.mdb" with three tables:
Students: Student_key, Last, First....
Students Actitities: Student_key, Activity
Activity: Activity_Name

I also have a form with two combo boxes where the user first selects an
Activity from "Activity_Name" and then a student from "Students". When the
student is selected I want to add a record to the "Students Activities"
table. Using the following code:

Private Sub cmbFindStudent_BeforeUpdate(Cancel As Integer)

Dim dbsSchool As Database
Dim rstStudents As Recordset

Set dbsSchool = OpenDatabase("School.mdb")

Set rstStudents = dbsSchool.OpenRecordset("Students Activities",
dbOpenDynaset)

AddName rstStudents, Student_Key, Activity_Name

End Sub

This code came from the NorthWind database. I get a "Type mismatch" error
on the "Set rstStudents..." line. Ingore the line wrap above. Can anyone
tell me why?

thanks,
 
I believe you need "[Students Activities]". Its best not to have embedded
spaces in any name of an Access table, form, query etc. as it introduces lots
of problems, call it "StudentsActivities" then you wont have the problem.
- David
 
Thanks David,

I did take the space out of the name and with the [] I no longer have a Type
Mismatch error. However I now get a "database is locked" error on the Set
dbsSchool = OpenDatabase("School.mdb")line.

Maybe I'm not taking the right approach for what I want to do. I've
collected the "Activity_Name" and the "Student_Key" from the combo boxes and
all I want to do is add a record to the StudentsActivities table. Is there
an easy way to do this?

Thanks again,




mscertified said:
I believe you need "[Students Activities]". Its best not to have embedded
spaces in any name of an Access table, form, query etc. as it introduces lots
of problems, call it "StudentsActivities" then you wont have the problem.
- David

Phil said:
Hi,

I have a DB "School.mdb" with three tables:
Students: Student_key, Last, First....
Students Actitities: Student_key, Activity
Activity: Activity_Name

I also have a form with two combo boxes where the user first selects an
Activity from "Activity_Name" and then a student from "Students". When the
student is selected I want to add a record to the "Students Activities"
table. Using the following code:

Private Sub cmbFindStudent_BeforeUpdate(Cancel As Integer)

Dim dbsSchool As Database
Dim rstStudents As Recordset

Set dbsSchool = OpenDatabase("School.mdb")

Set rstStudents = dbsSchool.OpenRecordset("Students Activities",
dbOpenDynaset)

AddName rstStudents, Student_Key, Activity_Name

End Sub

This code came from the NorthWind database. I get a "Type mismatch" error
on the "Set rstStudents..." line. Ingore the line wrap above. Can anyone
tell me why?

thanks,
Click to expand...
Click to expand...
 
Hopefully, you have Access help installed, so you should be able to display
information on various statements like the OpenDatabase statement. That would
normally be used to open a separate databse than the one you are in (and you
would need the full file path, not just what yoiu have). What is the
School.mdb database? If that is the database you are in, there is no need to
open it as it is already open.
What you need is:
Set dbsSchool = CurrentDB()
Don't take too much notice of the code that the Northwind database has in
it, that stuff was written years ago for an ancient version of Access.

Provided that your form controls are bound to your tables (control source
property), updating should happen automatically when you move to a new record
or exit your form. Your form needs to be based on the Student Activities
table if that is the table you need to update.

- David

Phil said:
Thanks David,

I did take the space out of the name and with the [] I no longer have a Type
Mismatch error. However I now get a "database is locked" error on the Set
dbsSchool = OpenDatabase("School.mdb")line.

Maybe I'm not taking the right approach for what I want to do. I've
collected the "Activity_Name" and the "Student_Key" from the combo boxes and
all I want to do is add a record to the StudentsActivities table. Is there
an easy way to do this?

Thanks again,




mscertified said:
I believe you need "[Students Activities]". Its best not to have embedded
spaces in any name of an Access table, form, query etc. as it introduces lots
of problems, call it "StudentsActivities" then you wont have the problem.
- David

Phil said:
Hi,

I have a DB "School.mdb" with three tables:
Students: Student_key, Last, First....
Students Actitities: Student_key, Activity
Activity: Activity_Name

I also have a form with two combo boxes where the user first selects an
Activity from "Activity_Name" and then a student from "Students". When the
student is selected I want to add a record to the "Students Activities"
table. Using the following code:

Private Sub cmbFindStudent_BeforeUpdate(Cancel As Integer)

Dim dbsSchool As Database
Dim rstStudents As Recordset

Set dbsSchool = OpenDatabase("School.mdb")

Set rstStudents = dbsSchool.OpenRecordset("Students Activities",
dbOpenDynaset)

AddName rstStudents, Student_Key, Activity_Name

End Sub

This code came from the NorthWind database. I get a "Type mismatch" error
on the "Set rstStudents..." line. Ingore the line wrap above. Can anyone
tell me why?

thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top