Saving Problem

[darrel]

Hi there vb masters i have a problem regrading the saving procedure in my program:

Here my code:

Private Sub cmdSave_Click()
Dim rsShowRec As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
rsShowRec.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " '"
rsShowRec.Open , cnn, adOpenStatic, adLockOptimistic
 
    If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
        MsgBox ("Cannot Save blank entries"), vbCritical
        rsShowRec.Close
        cnn.Close
        Exit Sub
    Else
 
        If rsShowRec.BOF = True Then
 
                rsShowRec.AddNew
 
                If IsNull(txtCode(x).Text) Then rsShowRec.Fields("SUBJECT CODES") = "" Else rsShowRec.Fields("SUBJECT CODES") = txtCode(x).Text
                If IsNull(Combo1(x).Text) Then rsShowRec.Fields("TimeStart") = "" Else rsShowRec.Fields("TimeStart") = Combo1(x).Text
                If IsNull(Combo2(x).Text) Then rsShowRec.Fields("TimeEnd") = "" Else rsShowRec.Fields("TimeEnd") = Combo4(x).Text
                If IsNull(Combo4(x).Text) Then rsShowRec.Fields("ROOM") = "" Else rsShowRec.Fields("ROOM") = Combo2(x).Text
                If IsNull(Combo3(x).Text) Then rsShowRec.Fields("DAYS") = "" Else rsShowRec.Fields("DAYS") = Combo3(x).Text
                If IsNull(txtBlock.Text) Then rsShowRec.Fields("BLOCK SECTION") = "" Else rsShowRec.Fields("BLOCK SECTION") = txtBlock.Text
                If IsNull(txtSY.Text) Then rsShowRec.Fields("SCHOOL YEAR") = "" Else rsShowRec.Fields("SCHOOL YEAR") = txtSY.Text
 
                rsShowRec.Update
                rsShowRec.Requery
            Set rsShowRec = Nothing
        End If
 
 
 
    End If
    Next
        MsgBox ("Data Saved!"), vbOKOnly
 
 
End Sub
 

My problem is like this everytime i try to save some records in the table called "RECORDS", it saves it stores all the data in the table.. but the first row of the my table "RECORDS", that consist of field namely "TimeStart" , "TimeEnd", "ROOM", "DAYS". Everytime it save it always put an empty row in the first row of my fields,,

I dont know how i can prevent it, i dont want to have empty rows in my fields in any location. Please help me...

I hope someone can crack whats wrong with my code or how can i code it...

Thank you very much.

[jeffbroodwar]

Hi,

I saw your code..... i think there's a problem with the "If BOF == true then rs.addnew... you see if it reaches the (Beginning Of File) the addnew method of your recordset doesn't add the new record in that position.... meaning, the addnew method always adds a new record into the next row.

ex :

if your at row 3 of your database

1
2
3 rs position..... if you try to invoke the addnew method here it'll save data at position 4 of your database. leaving 3rd position blank.... though i didn't try to paste your code in vb6 ide and tried it. I hope my suggestion will solve your problem...

Best Regards,
Jeff

[nairda]

Hi there vb masters i have a problem regrading the saving procedure in my program:

Here my code:

Private Sub cmdSave_Click()
Dim rsShowRec As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
rsShowRec.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " '"
rsShowRec.Open , cnn, adOpenStatic, adLockOptimistic
 
    If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
        MsgBox ("Cannot Save blank entries"), vbCritical
        rsShowRec.Close
        cnn.Close
        Exit Sub
    Else
 
        If rsShowRec.BOF = True Then
 
                rsShowRec.AddNew
 
                If IsNull(txtCode(x).Text) Then rsShowRec.Fields("SUBJECT CODES") = "" Else rsShowRec.Fields("SUBJECT CODES") = txtCode(x).Text
                If IsNull(Combo1(x).Text) Then rsShowRec.Fields("TimeStart") = "" Else rsShowRec.Fields("TimeStart") = Combo1(x).Text
                If IsNull(Combo2(x).Text) Then rsShowRec.Fields("TimeEnd") = "" Else rsShowRec.Fields("TimeEnd") = Combo4(x).Text
                If IsNull(Combo4(x).Text) Then rsShowRec.Fields("ROOM") = "" Else rsShowRec.Fields("ROOM") = Combo2(x).Text
                If IsNull(Combo3(x).Text) Then rsShowRec.Fields("DAYS") = "" Else rsShowRec.Fields("DAYS") = Combo3(x).Text
                If IsNull(txtBlock.Text) Then rsShowRec.Fields("BLOCK SECTION") = "" Else rsShowRec.Fields("BLOCK SECTION") = txtBlock.Text
                If IsNull(txtSY.Text) Then rsShowRec.Fields("SCHOOL YEAR") = "" Else rsShowRec.Fields("SCHOOL YEAR") = txtSY.Text
 
                rsShowRec.Update
                rsShowRec.Requery
            Set rsShowRec = Nothing
        End If
 
 
 
    End If
    Next
        MsgBox ("Data Saved!"), vbOKOnly
 
 
End Sub
 

My problem is like this everytime i try to save some records in the table called "RECORDS", it saves it stores all the data in the table.. but the first row of the my table "RECORDS", that consist of field namely "TimeStart" , "TimeEnd", "ROOM", "DAYS". Everytime it save it always put an empty row in the first row of my fields,,

I dont know how i can prevent it, i dont want to have empty rows in my fields in any location. Please help me...

I hope someone can crack whats wrong with my code or how can i code it...

Thank you very much.

Hi Darrel,
I think you don't need the BOF

If you want to put a new data in your database table, you don't have to look for your table's BOF or EOF.
Example:

With rs
                        .AddNew
                        !TIMESTART = Text1.Text
                        !TIMEEND = Text2.Text
                        !ROOM = Text3.Text
                        !DAYS = Text4.Text
                        .Update
                    End With

It would be different if you want to UPDATE your data, you have to search for your data until EOF is reached

I hope this would help

NAIRDA