Help please, it is urgent...

  • Thread starter marco_pb via AccessMonster.com
  • Start date
M

marco_pb via AccessMonster.com

I want to copy the value of Ausbildungsbetreuer (supervisor) based on the
Abteilung (department)
these are the code I have, I wonder why it is not working..
It says type mismatch in: Set rstx = Dbs.OpenRecordset("Abteilungen",
dbOpenTable)
Thank you in advance for your help.

Private Sub Abteilung_AfterUpdate()
Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As Database
Dim rstx As Recordset
Dim Result As String
Dim Ausbilder As String
Stop
On Error GoTo Error_Handling

Stop
On Error GoTo 0
Set Dbs = DBEngine.Workspaces(0).Databases(0)
'Set Dbs = OpenDatabase("W:\Azubis\Praktikum\Marcories\Database\
Praktikanten 2003.mdb")
Set rstx = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
If rst.BOF = False Then
Ausbilder = rst!Ausbildungsbetreuer
Me!Ausbildungsbetreuer = Ausbilder
End If
Dbs.Close

'Me!Ausbildungsbetreuer.RowSource = " "
'Me!Ausbildungsbetreuer.RowSource = "SELECT Ausbildungsbetreuer FROM
Abteilungen WHERE AID='" & Me![abteilung] & "';"
'Me.Refresh
'Stop
'Ausbildungsbetreuer.ListIndex = 0
Exit Sub

Error_Handling:
SaveErr = Err
If Err = 3058 Then
Resume Next
Else
Answer = MsgBox(Error(SaveErr), vbCritical + vbRetryCancel, "Praktianten
DB")
If Answer = vbCancel Then
Resume Next
Else
Resume
End If
End If
End Sub


Private Sub Praktikum_Art_AfterUpdate()
Dim a As String

a = Forms![Praktikanten]!Praktikum_Art

If (a = "Schülerpraktikum") Then
Forms![Praktikanten]!Vergütung.Value = 0
Else
If (a = "Grundpraktikum") Then
Forms![Praktikanten]!Vergütung.Value = 300
Else
Forms![Praktikanten]!Vergütung.Value = 500
End If
End If
End Sub
Private Sub Zurück_Click()
On Error GoTo Err_Zurück_Click


DoCmd.Close

Exit_Zurück_Click:
Exit Sub

Err_Zurück_Click:
MsgBox Err.Description
Resume Exit_Zurück_Click

End Sub
 
K

Keith Wilby

marco_pb via AccessMonster.com said:
I want to copy the value of Ausbildungsbetreuer (supervisor) based on the
Abteilung (department)
these are the code I have, I wonder why it is not working..
It says type mismatch in: Set rstx = Dbs.OpenRecordset("Abteilungen",
dbOpenTable)
Thank you in advance for your help.
Click to expand...

Hi.

What is "Abteilungen"? I don't see it referenced before the offending line
of code, could this be your problem? I would expect to see a reference to a
recordset or a SQL query.

Regards,
Keith.
www.keithwilby.com
 
D

Dirk Goldgar

marco_pb via AccessMonster.com said:
I want to copy the value of Ausbildungsbetreuer (supervisor) based on
the Abteilung (department)
these are the code I have, I wonder why it is not working..
It says type mismatch in: Set rstx = Dbs.OpenRecordset("Abteilungen",
dbOpenTable)
Thank you in advance for your help.

Private Sub Abteilung_AfterUpdate()
Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As Database
Dim rstx As Recordset
Dim Result As String
Dim Ausbilder As String
Stop
On Error GoTo Error_Handling

Stop
On Error GoTo 0
Set Dbs = DBEngine.Workspaces(0).Databases(0)
'Set Dbs = OpenDatabase("W:\Azubis\Praktikum\Marcories\Database\
Praktikanten 2003.mdb")
Set rstx = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
Click to expand...

The may be the very common problem of distinguishing the DAO Recordset
type from the ADODB Recordset type. Try changing this:
Dim rstx As Recordset
Click to expand...

to this:

Dim rstx As DAO.Recordset

If it doesn't recognize the "DAO" type qualifier, open the References
dialog (by clicking Tools -> References...), locate "Microsoft DAO 3.6
Object Library" in the list, and put a check mark in the box next to it.

This problem arises because both the DAO and ADO object libraries define
object types with the same names, so you have to explicitly qualify your
declarations with the library ID (DAO or ADODB). If you're not going to
be using ADO at all, go back into the References dialog and remove the
check mark next to "ActiveX Data Object 2.x Lilbrary". Then you won't
have to worry about confusing the compiler. Otherwise, you'd better
explicitly qualify any of the following objects you declare, since they
occur in both the DAO and the ADODB libraries:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

Note: the following objects exist with the same names in the ADOX and
DAO models as well:

Group
Groups
Index
Indexes
Property
Properties
User
Users
 
M

marco_pb via AccessMonster.com

Thanks, I got no error now, but the value I am seking is not showing..
I have abteilung (as a combo box), so when I klik on one of the choices in
abteilung, I want the value in ausbildungsbetreuer automatically changed as
the query I have made ("SELECT Ausbildungsbetreuer FROM Abteilungen WHERE
AID='" & Me![abteilung] & "';")

I got no result now after I choose something in abteilung combo box.
thank you again in advance for the helps.

Private Sub Abteilung_AfterUpdate()
Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As Database
Dim rst As DAO.Recordset
Dim Result As String
Dim Ausbilder As String
' Stop
On Error GoTo Error_Handling

' Stop
On Error GoTo 0
Set Dbs = DBEngine.Workspaces(0).Databases(0)
'Set Dbs = OpenDatabase("W:\Azubis\Praktikum\Marcories\Database\
Praktikanten 2003.mdb")
Set rst = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
If rst.BOF = False Then
Me!Ausbildungsbetreuer = rst!Ausbildungsbetreuer
End If
Dbs.Close

'Me!Ausbildungsbetreuer.RowSource = " "
'Me!Ausbildungsbetreuer.RowSource = "SELECT Ausbildungsbetreuer FROM
Abteilungen WHERE AID='" & Me![abteilung] & "';"
'Me.Refresh
'Stop
'Ausbildungsbetreuer.ListIndex = 0
Exit Sub

Error_Handling:
SaveErr = Err
If Err = 3058 Then
Resume Next
Else
Answer = MsgBox(Error(SaveErr), vbCritical + vbRetryCancel, "Praktianten
DB")
If Answer = vbCancel Then
Resume Next
Else
Resume
End If
End If
End Sub
Dirk said:
I want to copy the value of Ausbildungsbetreuer (supervisor) based on
the Abteilung (department)
Click to expand...
[quoted text clipped - 19 lines]
Praktikanten 2003.mdb")
Set rstx = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
Click to expand...

The may be the very common problem of distinguishing the DAO Recordset
type from the ADODB Recordset type. Try changing this:
Dim rstx As Recordset
Click to expand...

to this:

Dim rstx As DAO.Recordset

If it doesn't recognize the "DAO" type qualifier, open the References
dialog (by clicking Tools -> References...), locate "Microsoft DAO 3.6
Object Library" in the list, and put a check mark in the box next to it.

This problem arises because both the DAO and ADO object libraries define
object types with the same names, so you have to explicitly qualify your
declarations with the library ID (DAO or ADODB). If you're not going to
be using ADO at all, go back into the References dialog and remove the
check mark next to "ActiveX Data Object 2.x Lilbrary". Then you won't
have to worry about confusing the compiler. Otherwise, you'd better
explicitly qualify any of the following objects you declare, since they
occur in both the DAO and the ADODB libraries:

Connection
Error
Errors
Field
Fields
Parameter
Parameters
Property
Properties
Recordset

Note: the following objects exist with the same names in the ADOX and
DAO models as well:

Group
Groups
Index
Indexes
Property
Properties
User
Users
Click to expand...
 
D

Dirk Goldgar

marco_pb via AccessMonster.com said:
Thanks, I got no error now, but the value I am seking is not showing..
I have abteilung (as a combo box), so when I klik on one of the
choices in abteilung, I want the value in ausbildungsbetreuer
automatically changed as the query I have made ("SELECT
Ausbildungsbetreuer FROM Abteilungen WHERE AID='" & Me![abteilung] &
"';")

I got no result now after I choose something in abteilung combo box.
thank you again in advance for the helps.

Private Sub Abteilung_AfterUpdate()
Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As Database
Dim rst As DAO.Recordset
Dim Result As String
Dim Ausbilder As String
' Stop
On Error GoTo Error_Handling

' Stop
On Error GoTo 0
Set Dbs = DBEngine.Workspaces(0).Databases(0)
'Set Dbs = OpenDatabase("W:\Azubis\Praktikum\Marcories\Database\
Praktikanten 2003.mdb")
Set rst = Dbs.OpenRecordset("Abteilungen", dbOpenTable)
If rst.BOF = False Then
Me!Ausbildungsbetreuer = rst!Ausbildungsbetreuer
End If
Dbs.Close

'Me!Ausbildungsbetreuer.RowSource = " "
'Me!Ausbildungsbetreuer.RowSource = "SELECT Ausbildungsbetreuer
FROM Abteilungen WHERE AID='" & Me![abteilung] & "';"
'Me.Refresh
'Stop
'Ausbildungsbetreuer.ListIndex = 0
Exit Sub

Error_Handling:
SaveErr = Err
If Err = 3058 Then
Resume Next
Else
Answer = MsgBox(Error(SaveErr), vbCritical + vbRetryCancel,
"Praktianten DB")
If Answer = vbCancel Then
Resume Next
Else
Resume
End If
End If
End Sub
Click to expand...

The code you have right now is not going to do what you want. It sounds
like you want to look up the value of Ausbildungsbetreuer from the
record in table Abteilungen that matches the current value of Abteilung,
and set the control named "Ausbildungsbetreuer" to that value. The code
to do that might look like this:

'----- start of revised code -----
Private Sub Abteilung_AfterUpdate()

Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Result As String
Dim Ausbilder As String

On Error GoTo Error_Handling

Set Dbs = DBEngine.Workspaces(0).Databases(0)
Set rst = Dbs.OpenRecordset _
"SELECT Ausbildungsbetreuer FROM Abteilungen " & _
"WHERE AID='" & Me![abteilung] & "'")

With rst
If Not .EOF Then
Me!Ausbildungsbetreuer = !Ausbildungsbetreuer
End If
.Close
End With

Exit_Point:
Set rst = Nothing
Set Dbs = Nothing
Exit Sub

Error_Handling:
Answer = MsgBox( _
Err.Description, _
vbCritical + vbRetryCancel, _
"Praktianten DB")

If Answer = vbCancel Then
Resume Exit_Point
Else
Resume
End If

End Sub
'----- end of revised code -----

That is untested code, but if I guessed right about what you're trying
to do, it should either work or be very close to working.
 
M

marco_pb via AccessMonster.com

Thank you, but I have got an error with this statement:
Set rst = Dbs.OpenRecordset _
"SELECT Ausbildungsbetreuer FROM Abteilungen " & _
"WHERE AID='" & Me![abteilung] & "'") When I combine it to:
Set rst = Dbs.OpenRecordset "SELECT Ausbildungsbetreuer FROM Abteilungen WHERE AID='" & Me![abteilung] & "'")
Click to expand...
It also produced an error..

Thank you again for helping me this far..
thank you..

Dirk said:
Thanks, I got no error now, but the value I am seking is not showing..
I have abteilung (as a combo box), so when I klik on one of the
Click to expand...
[quoted text clipped - 49 lines]
End If
End Sub
Click to expand...

The code you have right now is not going to do what you want. It sounds
like you want to look up the value of Ausbildungsbetreuer from the
record in table Abteilungen that matches the current value of Abteilung,
and set the control named "Ausbildungsbetreuer" to that value. The code
to do that might look like this:

'----- start of revised code -----
Private Sub Abteilung_AfterUpdate()

Dim Answer As Integer
Dim SaveErr As Long
Dim Dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Result As String
Dim Ausbilder As String

On Error GoTo Error_Handling

Set Dbs = DBEngine.Workspaces(0).Databases(0)
Set rst = Dbs.OpenRecordset _
"SELECT Ausbildungsbetreuer FROM Abteilungen " & _
"WHERE AID='" & Me![abteilung] & "'")

With rst
If Not .EOF Then
Me!Ausbildungsbetreuer = !Ausbildungsbetreuer
End If
.Close
End With

Exit_Point:
Set rst = Nothing
Set Dbs = Nothing
Exit Sub

Error_Handling:
Answer = MsgBox( _
Err.Description, _
vbCritical + vbRetryCancel, _
"Praktianten DB")

If Answer = vbCancel Then
Resume Exit_Point
Else
Resume
End If

End Sub
'----- end of revised code -----

That is untested code, but if I guessed right about what you're trying
to do, it should either work or be very close to working.
Click to expand...
 
D

Dirk Goldgar

marco_pb via AccessMonster.com said:
Thank you, but I have got an error with this statement:
Set rst = Dbs.OpenRecordset _
"SELECT Ausbildungsbetreuer FROM Abteilungen " & _
"WHERE AID='" & Me![abteilung] & "'") When I combine it to:
Set rst = Dbs.OpenRecordset "SELECT Ausbildungsbetreuer FROM
Abteilungen WHERE AID='" & Me![abteilung] & "'") It also produced an
error..
Click to expand...
Click to expand...

I accidentally left off the opening parenthesis. Try this:

Set rst = Dbs.OpenRecordset( _
"SELECT Ausbildungsbetreuer FROM Abteilungen " & _
"WHERE AID='" & Me![abteilung] & "'")
 
M

marco_pb via AccessMonster.com

thank you very much...
It works.. It really works... I dont know how to thank you..
hope u have a nice day...
GBU ^^

thank you...

Dirk said:
Thank you, but I have got an error with this statement:
Set rst = Dbs.OpenRecordset _ [quoted text clipped - 4 lines]
Abteilungen WHERE AID='" & Me![abteilung] & "'") It also produced an
error..
Click to expand...
Click to expand...

I accidentally left off the opening parenthesis. Try this:

Set rst = Dbs.OpenRecordset( _
"SELECT Ausbildungsbetreuer FROM Abteilungen " & _
"WHERE AID='" & Me![abteilung] & "'")
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

me.visible doesn't work 3
File in use error 5
help with coding 5
Loop Loopy 2
Type Mismatch on Opening table 4
Update a table based on ComboBox Selection 2
Forms Programming 4
Delete from combo 1

Top