saving input data in MS Access

Discussion in 'Visual Basic ( VB )' started by viv345, Mar 7, 2010.

  1. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    I am new bee in programming. i have created a Small program in VB & I want 2 save it in MS Access 2007. getting problem which is not recognized by me where i am doing wrong.
    Hence i have attached my programme as project1.zip Pl tell me where giving wrong.
    Thanks
     

    Attached Files:

  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    Please do not attach your code and ask to get it answered. I doubt you will do the same for others.

    Explain what is not working and we would be more than happy to answer.
     
  3. virxen

    virxen Active Member

    Joined:
    Nov 24, 2009
    Messages:
    387
    Likes Received:
    90
    Trophy Points:
    28
    try the changes below

    Code:
    
    
    [COLOR=Red]Option Explicit
      Dim conn As New ADODB.Connection
      Dim cmdCommand As New ADODB.Command
      Dim rec As New ADODB.Recordset[/COLOR]
    Private Function connect()
      conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        [COLOR=Red]App.Path & "\" & "db1.mdb;Mode=Read|Write"[/COLOR]
      [COLOR=Red]conn.CursorLocation = adUseClient
      conn.Open
          cmdCommand.ActiveConnection = conn
          cmdCommand.CommandType = adCmdText
          rec.CursorType = adOpenDynamic
          rec.CursorLocation = adUseClient
          rec.LockType = adLockOptimistic[/COLOR]
    End Function
    
    Private Sub Form_Load()
    connect
    End Sub
    
    Private Sub Command1_Click()
    [COLOR=Red]cmdCommand.CommandText = "SELECT * FROM table1;"
    rec.Open cmdCommand[/COLOR]
    rec.AddNew
    rec.Fields(1) = Text1.Text
    rec.Fields(2) = Text2.Text
    rec.Fields(3) = Text3.Text
    rec.Fields(4) = Text4.Text
         rec.Update
    [COLOR=Red]     'If Not rec.EOF Then rec.MoveNext
         MsgBox "record stored"
         rec.Close[/COLOR]
    
    End Sub
    
    [COLOR=Red]Private Sub Form_Unload(Cancel As Integer)
    conn.Close
    End Sub[/COLOR]
    
    Private Sub Text2_Click()
    Form1.Text2.Text = Val(Form1.Text1.Text) * 1#
    End Sub
    
    Private Sub Text3_Click()
    Form1.Text3.Text = Val(Form1.Text1.Text)
    End Sub
    Private Sub Text4_Click()
    Form1.Text4.Text = Val(Form1.Text2.Text)
    End Sub
    
    
    
    
    
     
  4. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    Re: saving move around data in MS Access

    In my previous Project i have add some new commands like Next; Previous and Delete.
    How I can code to move around my saved data.
     

    Attached Files:

  5. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    Re: saving move around data in MS Access

    In my previous Project i have add some new commands like Next; Previous and Delete.
    How I can code to move around my saved data. i am getting problem. When click on next, Highlights Private Sub Command2_Click () and on End Sub, is this wrong.
    Pl Help
     
  6. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    THANKS

    Can data saved in access remain constant after quitting the programme. and if delete/clear button is clicked how i can delete data in access
     
  7. virxen

    virxen Active Member

    Joined:
    Nov 24, 2009
    Messages:
    387
    Likes Received:
    90
    Trophy Points:
    28
    i have made some changes in your code.

    1) code for deleting records (delete button)
    2) code for next,previous (considering error situations,with appropriate messages)
    3) button NEW added (press this to empty text boxes,add data and press save to store in DB)
    4) label showing current record out of how many records

    and many more.

    check the code.

    for more questions post again.
     

    Attached Files:

  8. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
  9. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    I want know one more thing that if their are more forms in a project viz., Form 1, Form2 etc etc then what will be the coding.
    AND
    In this project on this command it is giving error
    Run-time error '3704'
    Operation is not allowed when the object is closed


    Private Sub Command8_Click() 'save button
    rec.AddNew
    rec.Fields(1) = Text1.Text
    rec.Fields(2) = Text2.Text
    rec.Fields(3) = Text3.Text
    rec.Fields(4) = Text12.Text
    rec.Fields(5) = Text13.Text
    rec.Update
    MsgBox "record stored"
    currentRecord = currentRecord + 1
    Label3.Caption = "Record " + CStr(currentRecord) + " of " + CStr(rec.RecordCount)

    End Sub
     
  10. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    You could have used [​IMG] button instead.
     
    Last edited: Jan 21, 2017
  11. virxen

    virxen Active Member

    Joined:
    Nov 24, 2009
    Messages:
    387
    Likes Received:
    90
    Trophy Points:
    28
    from the code above i assume that you run your code with the changes i did,
    not my project.

    check your code again,somewhere in your code you must have left something like
    rec.close remove it and it should be ok.


     
  12. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    Yes i tried to change the fields. Just to see that, if I change the boxes or text.box what will happen will it work or not. If not where i am wrong.
    Thanks for your help.
     
  13. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    Sir,
    No such (rec.close) is given in code
    Only code for centering form and go back is given viz

    Private Sub Form2_Load()
    Me.Left = (Screen.Width - Me.Width) / 2
    Me.Top = (Screen.Height - Me.Height) / 2
    End Sub

    AND

    Private Sub Command7_Click()
    Form1.Show
    Form2.Visible = False
    End Sub


    FOR (GOBACK) is given
     
  14. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    For example if their two forms with same text and labels can i use
    this for loading, centering etc etc

    Private Sub Form2_Load ()
     
  15. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    Getting Problem when I write code :-
    Private Sub Form_Load()
    deleting = False

    connect
    Label2.Caption = "Record " + CStr(currentRecord) + " of " + CStr(rec.RecordCount)


    If Not rec.EOF Then

    Text3.Text = rec.Fields(1).Value
    Text2.Text = rec.Fields(2).Value
    Text12.Text = rec.Fields(3).Value
    Text13.Text = rec.Fields(4).Value
    End If
    End Sub

    It gives error in :-

    Private Sub Command8_Click() 'save button
    rec.AddNew

    rec.Fields(1) = Text3.Text
    rec.Fields(2) = Text2.Text gives error
    rec.Fields(3) = Text12.Text
    rec.Fields(4) = Text13.Text
    rec.Update
    MsgBox "record stored"
    currentRecord = currentRecord + 1
    Label2.Caption = "Record " + CStr(currentRecord) + " of " + CStr(rec.RecordCount)
    End Sub

    If I use :-

    [/B]Private Sub Form2_Load()[/B]
    deleting = False

    connect
    Label2.Caption = "Record " + CStr(currentRecord) + " of " + CStr(rec.RecordCount)


    If Not rec.EOF Then

    Text3.Text = rec.Fields(1).Value
    Text2.Text = rec.Fields(2).Value
    Text12.Text = rec.Fields(3).Value
    Text13.Text = rec.Fields(4).Value
    End If
    End Sub

    It Gives error in :-

    Private Sub Command8_Click() 'save button
    rec.AddNew gives error here

    rec.Fields(1) = Text3.Text
    rec.Fields(2) = Text2.Text
    rec.Fields(3) = Text12.Text
    rec.Fields(4) = Text13.Text
    rec.Update
    MsgBox "record stored"
    currentRecord = currentRecord + 1
    Label2.Caption = "Record " + CStr(currentRecord) + " of " + CStr(rec.RecordCount)

    End Sub

    Please tell me where I am wrong?
     
  16. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    OK
    I found my mistake

    Thanks
     
  17. viv345

    viv345 New Member

    Joined:
    Feb 22, 2010
    Messages:
    76
    Likes Received:
    0
    Trophy Points:
    0
    Problem Resolved
    THANK YOU VERY VERY MUCH FOR YOUR HELP
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice