Visual Basic in Access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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:
 
Joined
Jul 29, 2001
Messages
21,334
Should read...

If rst.EOF = True Then

rst.AddNew

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

.AddNew
 
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.
 
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.
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top