Recordset problem

I am creating a DB and table etc. on the fly but in the function that I ma testing to see if the user is in the DB it bombs out! It throws an error: "Object variable or block varialbe not set"

Any ideas?


'Creating DB on the fly......

Dim cat As New ADOX.Catalog

'create an Access 97 Database
cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;" & "Data Source=" & App.Path & ".\mydata.mdb"

'DB is locked until cat object is released
Set cat = Nothing
'
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim sSql As String

'Open connection to the new db
Set con = New ADODB.Connection
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & App.Path & ".\mydata.mdb;Persist Security Info=false"
con.Open



'run Sql query to create table
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
sSql = "CREATE TABLE Students (LoginID TEXT (50), Password TEXT (50), FirstName TEXT (50), LastName TEXT (50), Department TEXT (50),"
sSql = sSql & "CONSTRAINT MyFieldConstraint PRIMARY KEY(LoginID, Password));"

....................................................................

'called in another function
If FindUser(sUserID, sPassword) = True Then
'change where am i
sWhereAmI = "DISPLAYUSER"
Else
iResult = MsgBox("There are no users with the ID and Password you entered, Would you like to add them", vbYesNo + vbQuestion, "Log In")
End If

...........................................................................
'called by the above code

Function FindUser(sUserName As String, sUserPW As String) As Boolean
Data1.Recordset.Index = "MyFieldConstraint"
Data1.Recordset.Seek "=", sUserName, sUserPW
'So that No match = True
FindUser = Not Data1.Recordset.NoMatch

End Function

you need a 'set something = new something' somewhere :\

Sorry not following that...

On what line you are getting this error?

Data1.Recordset.Index = "MyFieldConstraint"

Make sure that "MyFieldConstraint" is an index in the table that you searching.
One more thing. The "Seek" method works only if RecordsetType is a Table (0). So try this:

VB Code:

Function FindUser(sUserName As String, sUserPW As String) As Boolean 
 
With Data1
   .DatabaseName = Path_To_The_Database_here
   .RecordSource = Table_Name_Here
   .RecordsetType = 0 'Table
   .Refresh
   .Recordset.Index = "MyFieldConstraint" 
   .Recordset.Seek "=", sUserName, sUserPW 
   'So that No match = True 
   FindUser = Not  .Recordset.NoMatch 
End With
 
End Function

Thanks that seems to work!

Just ouit of curiosity. Why do you use DataControl to verify user?
You are already using ADO in your program.
I suggest to you this way. Make separate fields UserName and UserPassword within User table, then just use this

VB Code:

Function FindUser(sUserName As String, sUserPW As String) As Boolean 
Dim rs, strSQL, conn
 
   conn="Provider=Microsoft.Jet.OLEDB.3.51;" & "Data Source=" & App.Path & ".\mydata.mdb"
   strSQL="SELECT * FROM [User] WHERE [UserName]='" & sUserName & "' AND UserPAssword='" & sUserPW & "'"
   
   Set rs = CreateObject("ADODB.Recordset")
   rs.open strSql, conn
   FindUser = Not  rs.EOF
   rs.Close
   Set rs = Nothing
 
End Function