try cut & pasting this code. (there are some comments within it that
might help):

Public Sub DoLogin()
Dim cnnConn As ADODB.Connection
Dim cmmQuery As ADODB.Command
Dim rstReturned As ADODB.Recordset
Dim strSQL As String
Dim myError As Error

On Error GoTo DBError

Set cnnConn = New ADODB.Connection

cnnConn.ConnectionString = "DSN=Holiday"
cnnConn.Open

'Your Code:
'strSQL = "SELECT * from User"
'strSQL = strSQL & "WHERE (" & "'" & Username = CStr(m_strUserName) And
'Password = CStr(m_strPassword) & "')"

'New Code: Assuming that User is your table, UserName & Password are
'          database fields within that table, and m_strUserName &
'          m_strPassword are module-level variables that have previously
'          been defined and had the value set.
'set a breakpoint and check the value of strSQL AFTER it is set
'in the immediate window.  you should get a valid SQL statement that
'can be cut & paste into a sql window in access to produce a
'recordset.  if not, you will probably have to play with the quotes.
'the goal is to produce a valid sql string for strSQL from the
'concatenation.
strSQL = "SELECT * FROM User WHERE (UserName = """ & _
         CStr(m_strUserName) & """) AND Password = """ & _
         CStr(m_strPassword) & """)"

Set cmmQuery = New ADODB.Command
Set rstReturned = New ADODB.Recordset
'you had a new connection and new command object, but were missing a
'new recordset.  effect, you had a valid object declared for the
'recordset, but had not created it before you attempted to use it.

With cmmQuery
.ActiveConnection = cnnConn
.CommandTimeout = 30
.CommandText = strSQL
.CommandType = adCmdText
'.Execute
Set rstReturned = cmmQuery.Execute
End With

'youve already called cmd execute in the with-end with block.
'Set rstReturned = cmmQuery.Execute

'if you comment out the if-end if block for the bof/eof.  you WILL get
an error if you attempt to movefirst an empty recordset.
'' If Not (rstReturned.BOF And rstReturned.EOF) Then
'rstReturned.MoveFirst
'' End If
'try this instead.  Unless you are marshalling some security from
'the recordset, if a record returns, they are a valid user.
if Not (rstReturned.BOF or rstReturned.EOF) Then
    'Do Something VALID User.
else
    'Do Something Else NOT VALID User.
endif

DBError:

For Each myError In cnnConn.Errors
MsgBox myError.NativeError & ": " & myError.Description
Next
'cmm
End Sub

hope this helps or at least gets you farther along.
dav