Getting error while entering the first record in recordset

[sukhjinder]

I am a new to ms access. Please help me i am not able to enter my first record into the database.

[andy49]

We're gonna need more info here. What do you want to happen.


Sent from my iPhone using Tapatalk

[andy49]

Have you considered binding your form to the tblbattery. That way the changes that you make would automatically update.

This may be useful
http://stackoverflow.com/questions/1...s-in-ms-access




Sent from my iPhone using Tapatalk

[sukhjinder]

[QUOTE=andy49;341533]We're gonna need more info here. What do you want to happen.


sir please look into the attachment

[sukhjinder]

sir i donot want to bind the text boxes i want to enter the data through VBA programming. I know my program will work if it had some data in tblbattery.but how to initially enter the data into the table thorough vba

[Bob Fitz]

Perhaps something like:

Private Sub Form_Load()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_battery", dbOpenDynaset, dbSeeChanges)
    
    If Not rs.BOF Then
    
        Me.txtid = rs!id
        Me.txtsname = rs!sname
        Me.txtoem = rs!oem
        Me.txtcontno = rs!contno
        Me.txtallotslno = rs!allotedslno
        Else
        Exit Sub
    End If
End Sub

[Micron]

This isn't a comment on the previous post, just an explanation of the error for the benefit of the OP.
MoveFirst is an action. It has no Null or other such properties. You would have to MoveFirst, then check if a specific field in that record is Null or not.
Your attempts to move first/previous/next will generate errors if there are no records/you are at the first/you are at the last record of a recordset because you haven't trapped for those errors.

[sukhjinder]

Perhaps something like:

Private Sub Form_Load()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_battery", dbOpenDynaset, dbSeeChanges)
    
    If Not rs.BOF Then
    
        Me.txtid = rs!id
        Me.txtsname = rs!sname
        Me.txtoem = rs!oem
        Me.txtcontno = rs!contno
        Me.txtallotslno = rs!allotedslno
        Else
        Exit Sub
    End If
End Sub

Thanks
sir your code really help me but i have improved a little bit more in editing and getting data i want to share it with you

Option Compare Database
Option Explicit


Dim db As Database
Dim rs As Recordset


Private Sub cmdclose_Click()
DoCmd.Close
End Sub


Private Sub cmddelete_Click()
Dim msg As String
msg = "Are you sure that you want to delete this record?"

If MsgBox(msg, vbYesNo, "Are you sure?") = vbOK Then
rs.Delete
End If

End Sub


Private Sub cmdmovenext_Click()
If Not rs.EOF Then
rs.MoveNext
refreshdata
End If

If rs.EOF Then
rs.MovePrevious
End If
End Sub


Private Sub cmdmoveprevious_Click()
If Not rs.BOF Then
rs.MovePrevious
refreshdata
End If

If rs.BOF Then
rs.MoveNext
End If

End Sub


Private Sub cmdnew_Click()
Me.txtid = ""
Me.txtsname = ""
Me.txtoem = ""
Me.txtcontno = ""
Me.txtallotslno = ""
Me.txtsname.SetFocus
End Sub


Private Sub cmdsave_Click()
If Me.txtid = "" Then
rs.AddNew
Else
rs.Edit
End If
rs!SNAME = Me.txtsname
rs!oem = Me.txtoem
rs!containerno = Me.txtcontno
rs!allotedslno = Me.txtallotslno
rs.Update
End Sub


Private Sub Form_Load()
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_battery", dbOpenDynaset, dbSeeChanges)
refreshdata
End Sub


Private Sub refreshdata()
If Not rs.BOF And Not rs.EOF Then
Me.txtid = rs!id
Me.txtsname = rs!SNAME
Me.txtoem = rs!oem
Me.txtcontno = rs!containerno
Me.txtallotslno = rs!allotedslno
End If
End Sub


but i want some more help of you sir every battery consist of 5 cells each
how to enter five records differently in the record set

[Bob Fitz]

every battery consist of 5 cells each
how to enter five records differently in the record set

Sound as though you need a table for the batteries and another related table for the details of the 5 cells.

[Micron]

agreed. If you already have that, then I think as long as you're using recordsets, you'll have to create another one that gives you the values you need, and when writing one set of values, you have to loop through the second and get those values and do whatever with them. That loop has to be nested within the code that gives you access to the first recordset. Quite often, simple queries based on a set of form controls are much easier to deal with and do the job. Not sure, but I see no reason here why the complicated route of using recordsets has been chosen.

[ssanfu]

You should also make these changes:

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset

Private Sub cmdclose_Click()
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   DoCmd.Close acForm, Me.Name

End Sub

[sukhjinder]

You should also make these changes:

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim rs As DAO.Recordset

Private Sub cmdclose_Click()
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   DoCmd.Close acForm, Me.Name

End Sub

yours code really helped a lot in the closing part of the database. thanks

Next step i am working on the table of cells as suggested by

Sound as though you need a table for the batteries and another related table for the details of the 5 cells.

[ssanfu]

You really should step back and design your table structures using pencil & paper, cardboard, whiteboard, etc.
It doesn't seem like you know where you are headed with the dB. test the design manually. Once it seems to work, then and only then should you open Access and start creating objects.

Why don't you want to use bound forms? Easier and less programming....

You know this will probably require another table??

Please describe what you are trying to do without using Access jargon.

[sukhjinder]

My road map in creating the database is clear and i am determined that i will complete this. I have checked and come with a workable design that i will finish this with VBA. The database is further

You know this will probably require another table??

beyond requirement another table. it will do analysis and give the report individually. Where i was stuck up is little help in minimizing coding errors. i am trying and adding beauty to the database. thanks

[sukhjinder]

Sound as though you need a table for the batteries and another related table for the details of the 5 cells.

how to enter all the five records at a time through VBAbattery.zip