1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Visual Basic in Access

Discussion in 'Software Development' started by allgood4, Feb 12, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. allgood4

    allgood4 Thread Starter

    Joined:
    Nov 15, 2002
    Messages:
    12
    Hi everyone, i am writing a program that basically add, delete and view information from a database. I am having problems with the add. Before i used a loop but did not work
    right, it kept on finding the first record not the right one. What i mean by first record is, you can have more than one SKU in different areas in the warehouse, so when looping it finds the first SKU but it might not be the one u want. So i decided to use a select statement instead. It is working properly capturing the SKU no mater if its in BIN E or A or C, but the problem is iwhen EOF = TRUE meaning that the record was not in the database, now i would like to add a record but it keeps on telling me that the selected record can not be updated. i have checked the locktype and all but still getting the same error msg. Now if EOF = FALSE meaning the record was found, the code updates the rst.quatity (recordset qty) by adding the value in txtQty to the quantity in the recordset (rst.quantity).
    this part works great but i can not add new records. I figured that after the first execute the table probably locked. I need help on trying to add new records with the code below.

    Option Explicit
    Dim pstrItemNumber As String
    Dim pstrColumn As String
    Dim pintRow As Integer
    Dim pintQuantity As Integer
    Dim pintPallet As Integer

    Private Sub Add_New_Record_Click()
    On Error GoTo Err_Delete_Record_Click

    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As New ADODB.Recordset
    Dim pstrSQL As String

    Dim pintQuantity As Integer
    Dim pintQuantityUpdated As Integer

    Set con = CurrentProject.Connection
    Set cmd = New ADODB.Command
    Set rst = New ADODB.Recordset



    pstrSQL = "SELECT itemnumber, column, Quantity FROM ivlocation WHERE ItemNumber = '"
    pstrSQL = pstrSQL & cmbItem
    pstrSQL = pstrSQL & "' AND row = " & txtrow & " AND Column = '" & txtColumn & "'"
    cmd.CommandText = pstrSQL


    pstrItemNumber = cmbItem
    pstrColumn = txtColumn
    pintRow = txtrow
    pintQuantity = txtquantity
    pintPallet = txtPallet
    cmd.CommandType = adCmdText


    Set cmd.ActiveConnection = con

    rst.Open "ivlocation", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

    Set rst = cmd.Execute


    With rst



    If cmbItem = "" Then
    Exit Sub

    Else




    If .EOF = True Then

    .AddNew

    rst.Fields("itemnumber") = pstrItemNumber
    rst.Fields("column") = pstrColumn
    rst.Fields("row") = pintRow
    rst.Fields("pallet") = pintPallet
    rst.Fields("quantity") = pintQuantity

    rst.Update





    Else

    pintQuantityUpdated = rst("Quantity") + txtquantity

    cmd.CommandText = "UPDATE ivLocation SET Quantity = " & pintQuantityUpdated & " WHERE ItemNumber = '" & cmbItem & "' AND row = " & txtrow & " AND Column = '" & txtColumn & "'"

    Set rst = cmd.Execute

    Set rst = Nothing




    End If
    End If

    End With
    Set rst = cmd.Execute

    Set rst = Nothing

    Exit_Delete_Record_Click:
    Exit Sub
    Err_Delete_Record_Click:
    MsgBox Err.Description

    Resume Exit_Delete_Record_Click

    End Sub



    :confused:
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Should read...

    If rst.EOF = True Then

    rst.AddNew

    instead of.....
    If .EOF = True Then

    .AddNew
     
  3. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    Give me a day. I am studying your code and will surely try to sort out your problem.
     
  4. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    There's one problem that I clearly see here. You are doing variable type conversion here.

    Somewhere in the code, I see the following lines.

    pintRow = txtrow
    pintQuantity = txtquantity
    pintPallet = txtPallet

    Now if you have entered 12 in txtrow, after these lines get executed, pintRow will contain "12" and not 12. It will contain a string made up of 1 & 2. The corresponding field in the recordset will have an integer value and you are assigning a text value to it. This is likely to generate an error.

    What you could have done is as follows
    pintRow = val(trim(txtrow.text))

    However, this may not be the real problem and you will need to send me the entire project for me to be able to understand it properly. Send me all the .frm files, .vbp and also your Access Database. Remeber to zip it and send it as attachment.

    Check you private message for my e-mail address. If its not there, send me a private message with your e-mail address.
     
  5. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    From what I have seen from your Database Application, I fear there are many errors in it. I tried to run the application by opening the main form. It did open, but clicking on various buttons gives errors. I am not too sure about the workability of designing as well.

    What exactly are you trying to do with this datbase application? What is it for?

    Secondly, is it a project for educational purposes or is it something that you are doing professionally? Answer to these questions may vary my response to your problem because in a project being done professionally, there are many more situations in coding that you need to take care of. If its only for some educational purposes, then as long as it does the main thing, its okay.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/118453

  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