Updating a table using VB 1

[MarkWaddington]

Hi,

I am using the following code to try and update a table named "staff" with the current username:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo ErrorHandler

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Staff", dbOpenDynaset)

.AddNew
.Fields("StaffName&quot = CurrentUser()
.Update


ExitHere:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:

MsgBox "A " & Err.Number & " error has occurred. Please contact the admin."

Resume ExitHere

End Sub


However when the code runs I get the following error, "Compile Error - user defined type not defined"

And the line "Dim db As Database" is highlighted in yellow. I figure I have to tell the database this defined type, but I don't know how.

I am still trying to get to grips with VB so please answer in simple language for me!

Thanks in advance,

Mark Waddington.

 

[GHolden]

While in the code to up to the tools - references and make sure you have a reference to Microsoft DAO object library.

There are two ways to write error-free programs; only the third one works.

 

[MarkWaddington]

Hey thanks!

However now i'm getting a "type mismatch" error, and I can't see any problems? Here's the code:

Private Sub Form_Open(Cancel As Integer)

On Error GoTo ErrorHandler

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Staff", dbOpenDynaset)

With rs
.AddNew
.Fields("StaffName&quot = CurrentUser()
.Update
End With

ExitHere:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:

MsgBox Err.Description

Resume ExitHere


Any ideas?

Thanks in advance,

Mark Waddington.

 

[GHolden]

Can you tell me which line is giving you the error?

There are two ways to write error-free programs; only the third one works.

 

[MarkWaddington]

It doesn't tell me which line, the box comes up saying type mismatch and when you click ok, the form loads, but the table isn't updated.

How can I find out which line it's referring to?

 

[GHolden]

If you go to tools-options-advanced set error trapping (bottom right hand corner of the tab) to 'break on all errors'

Currently your code goes to the error trap, so you can't see what line is giving the error.

Once your program is debugged you can set this back to 'break on unhandled errors'

There are two ways to write error-free programs; only the third one works.

 

[Namlop]

This line is probably the one the generates the problem:
Fields("StaffName&quot = CurrentUser()

If the field StaffName is of the type text, you'll need to convert CurrentUser() to text as well

 

[Namlop]

You can test if this is the problem by replacing CurrentUser() by something like "James"

 

[MarkWaddington]

Cool! You learn something every day!

The following line is flagged -

Set rs = db.OpenRecordset("Staff", dbOpenDynaset)

Any ideas why this line is throwing a type mismatch error?

Cheers,

Mark Waddington.

 

[MarkWaddington]

>>>
You can test if this is the problem by replacing CurrentUser() by something like &quot;James&quot; <<<<

No I tried that and I got the same error, there doesn't seem to be a problem with that line.

 

[GHolden]

It would imply that you dont actually have a table called Staff

There are two ways to write error-free programs; only the third one works.

 

[MarkWaddington]

But I do have a table called staff. I even changed the name of the table and it came up with a different error message saying it couldn't find it, so I know that that isn't the problem. The problem is it can't update the table with the information I pass to it for some reason.

What are all the other options for in the line I pasted? Perhaps something else needs to go in the brackets in this line:

Set rs = db.OpenRecordset(&quot;Staff&quot;, dbOpenDynaset)


Thanks,

Mark Waddington.

 

[GHolden]

That line should work fine as it is...

In your rs dim change it to

Dim rs As DAO.Recordset

There are two ways to write error-free programs; only the third one works.

 

[MarkWaddington]

Hey that works!

Thanks so much, i'm not worthy

Not sure why it works, but it does.. and i'm not complaining!

Once again, thanks for taking time out to help me.

Mark Waddington.

 

[GHolden]

Glad to be of assistance...



There are two ways to write error-free programs; only the third one works.