Solved VBA coming up with compile error (1 Viewer)

markdooler

Member
Local time
Today, 21:37
Joined
Nov 25, 2020
Messages
58
Hi All

The below code is coming up with a compile error as screen shot below

Access 2016 is being used.

Any ideas?

Code:
Private Sub btnLogin_Click()
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)

rs.FindFirst "UserName='" & Me.txtUserName & "'"

If rs.NoMatch = True Then
Me.lblWrongUserName.Visible = True
Me.txtUserName.SetFocus
Exit Sub
End If
Me.lblWrongUserName.Visible = False

If rs!Password <> Me.txtPassword Then
Me.lblWrongPassword.Visible = True
Me.txtPassword.SetFocus
Exit Sub
End If

Me.lblWrongPassword.Visible = False
DoCmd.OpenForm "frmTest"
DoCmd.Close acForm, Me.Name

End Sub

Error:

1611052057343.png
 

Minty

AWF VIP
Local time
Today, 21:37
Joined
Jul 26, 2013
Messages
10,355
The only thing I can see (and it doesn't generate that error) is the use of dbReadOnly which with a dbOpenSnapshot is unnecessary.

Can you highlight the line that the debugger is showing?
 

markdooler

Member
Local time
Today, 21:37
Joined
Nov 25, 2020
Messages
58
The only thing I can see (and it doesn't generate that error) is the use of dbReadOnly which with a dbOpenSnapshot is unnecessary.

Can you highlight the line that the debugger is showing?


Hi, thanks for the reply

Please see screenshot below:

1611053309906.png
 

Minty

AWF VIP
Local time
Today, 21:37
Joined
Jul 26, 2013
Messages
10,355
In the VBA editor click on Tools and screenshot the references dialogue that comes up I suspect you have a missing one or two.
 

markdooler

Member
Local time
Today, 21:37
Joined
Nov 25, 2020
Messages
58
In the VBA editor click on Tools and screenshot the references dialogue that comes up I suspect you have a missing one or two.
I have been googling this but cant find the right reference.

This is what i have

1611053840700.png
 

Minty

AWF VIP
Local time
Today, 21:37
Joined
Jul 26, 2013
Messages
10,355
I think you also need the M$ Office Accsess Database engine objects library as well:
1611054157030.png
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:37
Joined
Sep 12, 2006
Messages
15,614
Don't you need the microsoft DAO library? (3.6?) for recordsets.
It would need to be moved above the microsoft active data objects library.
 

Minty

AWF VIP
Local time
Today, 21:37
Joined
Jul 26, 2013
Messages
10,355
@markdooler That isn't the one I suggested, the one I have selected is Microsft Office Access Database Engine Objects Library
 

markdooler

Member
Local time
Today, 21:37
Joined
Nov 25, 2020
Messages
58
@markdooler That isn't the one I suggested, the one I have selected is Microsft Office Access Database Engine Objects Library
I do apologise, you are completely right.

Actually making the change you suggested (and not my incorrect one lol) has fixed the issue :)

Thanks for that.
 

Minty

AWF VIP
Local time
Today, 21:37
Joined
Jul 26, 2013
Messages
10,355
Glad it fixed it - sometimes the error codes don't always give you much clue.
Good luck with the rest of your project.
 

markdooler

Member
Local time
Today, 21:37
Joined
Nov 25, 2020
Messages
58
Glad it fixed it - sometimes the error codes don't always give you much clue.
Good luck with the rest of your project.
Thank you. Your help is greatly appreciated.

And im sure i will be back.....loads lol.
 

markdooler

Member
Local time
Today, 21:37
Joined
Nov 25, 2020
Messages
58
Don't you need the microsoft DAO library? (3.6?) for recordsets.
It would need to be moved above the microsoft active data objects library.
Hi

Thanks for the reply

I dont think i need DAO as i am using a later version of access.

Minty has solved it for me. :)
 

isladogs

MVP / VIP
Local time
Today, 21:37
Joined
Jan 14, 2017
Messages
18,186
I would recommend you specify the recordset type to avoid potential issues with the ADODB library:
Code:
Dim rs As DAO.Recordset
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:37
Joined
Feb 19, 2013
Messages
16,553
suggest you specify the type of recordset to avoid ambiguity. There are two, DAO and ADO. So

Dim rs as DAO.Recordset
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:37
Joined
Sep 12, 2006
Messages
15,614
Hi

Thanks for the reply

I dont think i need DAO as i am using a later version of access.

Minty has solved it for me. :)

Out of interest, I am looking at a database now

It's showing
Access 16.0 library THEN
DAO 3.6 library

I can't copy the Access version, but its Access for MS 365, 16.0, 32bit
I tried taking out the DAO reference and it wouldn't compile

Dim outputdbs As Database 'failed here
Dim outputrst As Recordset

I put DAO back in, and it failed to compile again but this time on a different code line, rst.edit

I believe this is because DAO is now listed AFTER the ADO reference, and presumably rst.edit isn't legal in ADO.
I moved DAO up to it's current position, which fixed it, and the dbs complied again.
 

isladogs

MVP / VIP
Local time
Today, 21:37
Joined
Jan 14, 2017
Messages
18,186
In ACCDB files, the Microsoft DAO 3.6 reference has been superceded by the Microsoft Office xx.0 Access database engine Object library which contains additional functionality. You don't need and indeed cannot load both of these at once as the references will conflict due to overlapping content

NOTE: The Microsoft Access xx.0 Object Library is different from the Microsoft Office xx.0 Access database engine Object library but both of these are loaded by default in any new ACCDB file
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:37
Joined
Sep 12, 2006
Messages
15,614
aah. Thanks Colin. My database is indeed an mdb, not an accdb.
 

Users who are viewing this thread

Top Bottom