[RESOLVED] Changing the recordset

[Piglet]

Hello

How do you change the recordset from one access table to another

within the same form? i want to test a query in an If statment within

a Select Case for two separate tables. Here is part of my code...

Select Case rstRecordSet!Difficulty

Case "Easy"
MsgBox "Your level is set to easy"

If txtUser.Text = rstRecordSet!Username Then

Msgbox "test"

Else
sqlstring = "INSERT INTO tbleasy (Username) VALUES ('" &

txtUser.Text & "')"

conConnection.Execute sqlstring, , adCmdText + adExecuteNoRecords

End If


rstRecordSet!Difficulty is in one table and I want rstRecordset!

Username to relate to another table.

Thanks in advance

Piglet

[Hack]

Are you using Bound Controls?

If not, then it would be a simple matter of creating two recordset objects.

[Piglet]

I have added another recordset but how do i let it know which table to refer to? It is still referring to the same table as the first recordset.

Thanks

[Hack]

Show me what you are doing.

[Piglet]

Here is the relevant part of my code:


Private Sub Command1_Click()

Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset

Dim rstRecordSet2 As New ADODB.Recordset


conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\" & "calculations.mdb;Mode=Read|Write"

conConnection.CursorLocation = adUseClient


conConnection.Open


With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tblRegister;"
.CommandType = adCmdText
End With


With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With

With rstRecordSet2
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With


'tests each record in the table
Dim bMatch As Boolean

bMatch = False
Do While bMatch = False


If (txtUser.Text = rstRecordSet!Username) And (txtPwd.Text = rstRecordSet!password) Then
bMatch = True
Else
rstRecordSet.MoveNext

If rstRecordSet.EOF = True Then

Exit Do
End If
End If
Loop


If bMatch Then

Dim sqlstring As String
Dim bMatch2 As Boolean

Select Case rstRecordSet!Difficulty

Case "Easy"

MsgBox "Your level is set to easy"

bMatch2 = False
Do While bMatch2 = False

If (txtUser.Text = rstRecordSet2!Username) Then
bMatch2 = True
Else
rstRecordSet2.MoveNext

If rstRecordSet2.EOF = True Then

Exit Do
End If
End If
Loop

If bMatch2 Then 'the record matches

MsgBox "Already in tblEasy"

Else

sqlstring = "INSERT INTO tbleasy (Username) VALUES ('" & txtUser.Text & "')"

conConnection.Execute sqlstring, , adCmdText + adExecuteNoRecords

MsgBox "Just added"

End If


I have 4 tables, tblRegister, tblEasy, tblMedium & tblHard. The primary key for tblRegister is 'Username' which is the foreign key in each of the other tables. The Easy, Medium & Hard tables each have their own primary key called '<difficulty>_ID'. Im unsure how to make rstRecordSet2 refer to tblEasy. Any suggestions?

Thank you!

[Hack]

Did you post everything? Without the vbcode tags, it made no sense, so I copied it in to a command button, and then did some indenting, and I'm amazed that this doesn't produce errors.

Private Sub Command1_Click()
Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command
Dim rstRecordSet As New ADODB.Recordset
Dim rstRecordSet2 As New ADODB.Recordset
 
 
conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\" & "calculations.mdb;Mode=Read|Write"
 
conConnection.CursorLocation = adUseClient
 
conConnection.Open
 
With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tblRegister;"
.CommandType = adCmdText
End With
 
With rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmdCommand
End With
 
With rstRecordSet2
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open "Select * From tblEasy "    [b]<===== try this[/b]
End With
 
 
'tests each record in the table
Dim bMatch As Boolean
 
bMatch = False
Do While bMatch = False
 
If (txtUser.Text = rstRecordSet!Username) And (txtPwd.Text = rstRecordSet!password) Then
    bMatch = True
Else
    rstRecordSet.MoveNext
    If rstRecordSet.EOF = True Then
        Exit Do
    End If
End If
Loop
 
If bMatch Then    [b]<====== There is no End If for this[/b]
 
        Dim sqlstring As String
        Dim bMatch2 As Boolean
        
        Select Case rstRecordSet!Difficulty  [b]<===== Where is End Select?
             If you only have one Case, why use this instead of an If/Then[/b]
        
        Case "Easy"
            MsgBox "Your level is set to easy"
            
            bMatch2 = False
                Do While bMatch2 = False
                
                If (txtUser.Text = rstRecordSet2!Username) Then
                    bMatch2 = True
                Else
                    rstRecordSet2.MoveNext
                
                    If rstRecordSet2.EOF = True Then
                     Exit Do
                    End If
                End If
                Loop
            
            If bMatch2 Then 'the record matches
                MsgBox "Already in tblEasy"
            Else
                sqlstring = "INSERT INTO tbleasy (Username) VALUES ('" & txtUser.Text & "')"
                conConnection.Execute sqlstring, , adCmdText + adExecuteNoRecords
                MsgBox "Just added"
            End If
 
End Sub

[Piglet]

Sorry...I do have other cases, they're the same as the first one but for 'Medium' and 'Hard'

From 'End Select' I have...


End Select

Else: MsgBox "Sorry, your details are invalid, please try again"

End If


conConnection.Close

Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing


End Sub

[Hack]

Sorry...I do have other cases, they're the same as the first one but for 'Medium' and 'Hard'

From 'End Select' I have...


End Select

Else: MsgBox "Sorry, your details are invalid, please try again"

End If


conConnection.Close

Set conConnection = Nothing
Set cmdCommand = Nothing
Set rstRecordSet = Nothing


End Sub

I kind of figured but I wanted to make sure. Anyway, if I were doing this I would differ from what you have by just a bit.

'this is wrong
Dim rstRecordSet As New ADODB.Recordset
Dim rstRecordSet2 As New ADODB.Recordset
'should be
Dim rstRecordSet As ADODB.Recordset
Dim rstRecordSet2 As ADODB.Recordset
Set rstRecordSet = New ADODB.Recordset
Set rstRecordSet2 = New ADODB.Recordset
'then I would do
Dim sSQLReg As String
Dim sSQLEasy As String
sSQLReg = "SELECT * FROM tblRegister"
rstRecordSet.Open sSQLReg, conConnection
'now I have all of tblRegister in one recordset
sSQLEasy = "SELECT * FROM tblEasy "
rstRecordSet2.Open sSQLEasy, conConnection
'now I have all of tblEasy in another recordset