Solved VBA coming up with compile error

[markdooler]

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:

 

[Minty]

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]

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:

 

[Minty]

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]

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

 

[Minty]

I think you also need the M$ Office Accsess Database engine objects library as well:

 

[markdooler]

I think you also need the M$ Office Accsess Database engine objects library as well:
View attachment 88338

No it must be a different one as i still get the same error with these selected.

If i select the activex one the error moves to the highlighted below

 

[gemma-the-husky]

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]

@markdooler That isn't the one I suggested, the one I have selected is Microsft Office Access Database Engine Objects Library

 

[markdooler]

@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]

Glad it fixed it - sometimes the error codes don't always give you much clue.
Good luck with the rest of your project.

 

[markdooler]

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]

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]

I would recommend you specify the recordset type to avoid potential issues with the ADODB library:

Dim rs As DAO.Recordset

 

[CJ_London]

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]

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]

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]

aah. Thanks Colin. My database is indeed an mdb, not an accdb.