How to add data to a table from a list box in Ms Access

V

vixinho

New member
Local time
Today, 06:50
Joined
May 11, 2020
Messages
8
Hi,
I have the following code to feed data to two related tables. The mother table is tblInvoice and the child is invoicejoin, Therefore I cant feed data to the child before feeding to the mother table. The mother table is to be fed with data from specific textboxes in my form while the child table receives data from the listbox in my form.
The code below saves data to the mother table but brings a run time error message saying OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET. Kind help me solve this error.
Code: 
Option Compare Database
Option Explicit
Dim Db As Database
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cnb As New ADODB.Connection
Dim sql As String
Dim ID As Long
Dim Status As String
Dim Image As Office.FileDialog
Dim ImagePath As Variant
Dim ImageName As String
Dim S1 As String
Dim i As Integer
Dim intItemsInList As Integer
Dim intCounter As Integer
Dim num1, num2, num3, num4, num5, num6, num7, num8

Public Sub Reset()
   txtInvoiceID.Value = AutoNumberReturn("tblInvoice", "InvoiceID")
    txtInvoiceNo.Value = "AplNr-" & AutoNumberReturn("tblInvoice", "InvoiceID")
     txtInvoiceDate.Value = Date
      cboSales.Value = ""
       txtSalesManID.Value = ""
        txtsalesmanName.Value = ""
      txtCustomerID.Value = Null
        txtRemarks.Value = Null
     cboCustomerName.Value = ""
     cboProduct.Value = ""
      txtGroup.Value = ""
       txtContactNo.Value = Null
     listBox1.RowSource = ""
     txtCrop.Value = ""
         txtGreenhouseNr.Value = ""
         txtPropagator.Value = ""
  btnSave.Enabled = True
btnNew.Enabled = True
btnDelete.Enabled = False
btnUpdate.Enabled = False
btnPrint.Enabled = False
Clear
End Sub

Public Sub Clear()
txtProductID.Value = ""
txtPID.Value = ""
cboProduct.Value = ""
txtPropagator.Value = ""
txtQTY.Value = "0"
txtGreenhouseNr.Value = ""
'txtCrop.Value = ""

End Sub

Private Sub btnAdd_Click()
Dim D As String
    Dim p1, p2, p3, p4, p5, p6
    p1 = "'" & txtPID.Value & "'"
    p2 = "'" & txtProductID.Value & "'"
    p3 = "'" & txtProductName.Value & "'"
    p4 = "'" & txtPropagator.Value & "'"
    p5 = "'" & txtGreenhouseNr.Value & "'"
    p6 = "'" & txtQTY.Value & "'"
     If IsNull(txtProductID.Value) Then
        MsgBox "Please Retrieve Product ID", vbInformation, "Information"
        txtProductID.SetFocus
        Exit Sub
      End If
     If IsNull(txtQTY.Value) Then
        MsgBox "Please Enter Quantity", vbInformation, "Information"
         txtQTY.SetFocus
         Exit Sub
     End If
      If IsNull(txtQTY.Value = 0) Then
        MsgBox "Quantity can not be zero", vbInformation, "Information"
         txtQTY.SetFocus
         Exit Sub
     End If
    D = p1 & ";" & p2 & ";" & p3 & ";" & p4 & ";" & p5 & ";" & p6 & ";"
    listBox1.AddItem D
   ' Call clean(Me)
    txtQTY.SetFocus
    Clear
End Sub

Private Sub btnNew_Click()
Reset
End Sub

Private Sub btnPurchaseList_Click()
DoCmd.OpenForm "frmSalesList", acNormal
End Sub

Private Sub btnRemove_Click()
 Dim ii As Integer
    With listBox1
     For ii = 0 To .ListCount - 1
            If .Selected(ii) = True Then
           .RemoveItem ii
           End If
     Next
     If .ListCount <= 0 Then
       ' btnRemove.Enabled = False
        btnAdd.Enabled = True
       End If
    End With
End Sub

Private Sub btnSave_Click()
If txtSalesManID = "" Then
MsgBox "Please Retrieve Sales Man ID.", vbInformation, "Choose SalesMan"
txtSalesManID.SetFocus
Exit Sub
End If
DoCmd.runSQL "INSERT INTO tblinvoice(InvoiceID,InvoiceNo,InvoiceDate,CustomerID,SalesManID,Remarks) VALUES('" & txtInvoiceID & "','" & txtInvoiceNo & "','" & txtInvoiceDate & "','" & txtCustomerID & "','" & txtSalesManID & "','" & txtRemarks & "')"

  For i = 0 To listBox1.ListCount - 1 Step 1
     Db.Execute "INSERT INTO InvoiceJoin(InvoiceID,ProductID,Propagator,GreenhouseNr,Qty)  VALUES('" & txtInvoiceID & "', '" & listBox1.Column(0, i) & "' , '" & listBox1.Column(3, i) & "' , '" & listBox1.Column(4, i) & "' , '" & listBox1.Column(5, i) & "')"
       Next
         MsgBox "Successfully done", vbInformation, "Sales"
         btnPrint.Enabled = True
 
Hi and welcome to AWF!

You appear to have only posted half of your btnSave_Click() sub.

Please post the rest of it.
 
Hi Cheeky, the only part I omitted is
Code: 
End Sub
 
vixinho said:
OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET
Click to expand...
In sub btnSave_Click() you have not set the object variable db before you use it. Amend to:
Code: 
' ...
  DoCmd.runSQL "INSERT INTO tblinvoice(InvoiceID,InvoiceNo,InvoiceDate,CustomerID,SalesManID,Remarks) VALUES('" & txtInvoiceID & "','" & txtInvoiceNo & "','" & txtInvoiceDate & "','" & txtCustomerID & "','" & txtSalesManID & "','" & txtRemarks & "')"
  Set db = CurrentDb
  For i = 0 To listBox1.ListCount - 1 Step 1
    Db.Execute "INSERT INTO InvoiceJoin(InvoiceID,ProductID,Propagator,GreenhouseNr,Qty)  VALUES('" & txtInvoiceID & "', '" & listBox1.Column(0, i) & "' , '" & listBox1.Column(3, i) & "' , '" & listBox1.Column(4, i) & "' , '" & listBox1.Column(5, i) & "')"
  Next
  Set db = Nothing
' ...
 
When performing inserts in code I find it easier to avoid relying on grabbing the mother ID from the form.

You can re-write your btnSave_Click() procedure like:
Code: 
Private Sub btnSave_Click()

  Dim strSQL As String, strInvID As String

  If txtSalesManID = "" Then
    MsgBox "Please Retrieve Sales Man ID.", vbInformation, "Choose SalesMan"
    txtSalesManID.SetFocus
    Exit Sub
  End If
  With CurrentDb
    strSQL = "INSERT INTO tblinvoice(InvoiceID,InvoiceNo,InvoiceDate,CustomerID,SalesManID,Remarks) VALUES('" & txtInvoiceID & "','" & txtInvoiceNo & "','" & txtInvoiceDate & "','" & txtCustomerID & "','" & txtSalesManID & "','" & txtRemarks & "')"
    .Execute strSQL, dbFailOnError
    strSQL = "SELECT @@IDENTITY;"
    With .OpenRecordset(strSQL)
      strInvID = .Fields(0)
      .Close
    End With
    For i = 0 To listBox1.ListCount - 1 Step 1
      strSQL = "INSERT INTO InvoiceJoin(InvoiceID,ProductID,Propagator,GreenhouseNr,Qty)  VALUES('" & strInvID & "', '" & listBox1.Column(0, i) & "' , '" & listBox1.Column(3, i) & "' , '" & listBox1.Column(4, i) & "' , '" & listBox1.Column(5, i) & "')"
      .Execute strSQL, dbFailOnError
    Next
  End With
  MsgBox "Successfully done", vbInformation, "Sales"
  btnPrint.Enabled = True

End Sub

(If InvoiceID is a numeric datatype you should remove the single quotes from around its value in the INSERT statement)

hth,

d
 
Wauh Wauh Wauh! This solved my problem which I have been trying to solve alone for over a month now. Thank you very very much @cheekybuddha What could I have done without you. Now I can proceed with my coding to enhance other functionalities.
I will be back here for your help again in case I get more challenges
 
Le
cheekybuddha said:
When performing inserts in code I find it easier to avoid relying on grabbing the mother ID from the form.

You can re-write your btnSave_Click() procedure like:
Code: 
Private Sub btnSave_Click()

  Dim strSQL As String, strInvID As String

  If txtSalesManID = "" Then
    MsgBox "Please Retrieve Sales Man ID.", vbInformation, "Choose SalesMan"
    txtSalesManID.SetFocus
    Exit Sub
  End If
  With CurrentDb
    strSQL = "INSERT INTO tblinvoice(InvoiceID,InvoiceNo,InvoiceDate,CustomerID,SalesManID,Remarks) VALUES('" & txtInvoiceID & "','" & txtInvoiceNo & "','" & txtInvoiceDate & "','" & txtCustomerID & "','" & txtSalesManID & "','" & txtRemarks & "')"
    .Execute strSQL, dbFailOnError
    strSQL = "SELECT @@IDENTITY;"
    With .OpenRecordset(strSQL)
      strInvID = .Fields(0)
      .Close
    End With
    For i = 0 To listBox1.ListCount - 1 Step 1
      strSQL = "INSERT INTO InvoiceJoin(InvoiceID,ProductID,Propagator,GreenhouseNr,Qty)  VALUES('" & strInvID & "', '" & listBox1.Column(0, i) & "' , '" & listBox1.Column(3, i) & "' , '" & listBox1.Column(4, i) & "' , '" & listBox1.Column(5, i) & "')"
      .Execute strSQL, dbFailOnError
    Next
  End With
  MsgBox "Successfully done", vbInformation, "Sales"
  btnPrint.Enabled = True

End Sub

(If InvoiceID is a numeric datatype you should remove the single quotes from around its value in the INSERT statement)

hth,

d
Click to expand...
Let me try your mentioned preference although the first one already worked. I am really humbled. You may not know how much you have helped me coz I have been really struggling to figure out the error
 
Proper indentation can help finding errors as well.?
I use this now and again for code I have copied and need to modify.

VBA Code Indenter

How to indent your vba code inyour Excel modules.
www.add-ins.com

HTH
 
Hello @cheekybuddha when using this code you edited it gives an error message saying you cannot add or change a record because a related record is required in table tblInvoice. It however save data to the mother table but doesn't to the InvoiceJoin table. Kindly guide me on this.
cheekybuddha said:
When performing inserts in code I find it easier to avoid relying on grabbing the mother ID from the form.

You can re-write your btnSave_Click() procedure like:
Code: 
Private Sub btnSave_Click()

  Dim strSQL As String, strInvID As String

  If txtSalesManID = "" Then
    MsgBox "Please Retrieve Sales Man ID.", vbInformation, "Choose SalesMan"
    txtSalesManID.SetFocus
    Exit Sub
  End If
  With CurrentDb
    strSQL = "INSERT INTO tblinvoice(InvoiceID,InvoiceNo,InvoiceDate,CustomerID,SalesManID,Remarks) VALUES('" & txtInvoiceID & "','" & txtInvoiceNo & "','" & txtInvoiceDate & "','" & txtCustomerID & "','" & txtSalesManID & "','" & txtRemarks & "')"
    .Execute strSQL, dbFailOnError
    strSQL = "SELECT @@IDENTITY;"
    With .OpenRecordset(strSQL)
      strInvID = .Fields(0)
      .Close
    End With
    For i = 0 To listBox1.ListCount - 1 Step 1
      strSQL = "INSERT INTO InvoiceJoin(InvoiceID,ProductID,Propagator,GreenhouseNr,Qty)  VALUES('" & strInvID & "', '" & listBox1.Column(0, i) & "' , '" & listBox1.Column(3, i) & "' , '" & listBox1.Column(4, i) & "' , '" & listBox1.Column(5, i) & "')"
      .Execute strSQL, dbFailOnError
    Next
  End With
  MsgBox "Successfully done", vbInformation, "Sales"
  btnPrint.Enabled = True

End Sub

(If InvoiceID is a numeric datatype you should remove the single quotes from around its value in the INSERT statement)

hth,

d
Click to expand...
 
Hi,

Without examining your database I'm not sure what is causing this issue.

My guess is that it is to do with the form having a lock on the table or not having noticed the newly inserted record yet.

My advice would be to revert to the code which worked.
 
Sure, Let me revert to the working code and study on your code some other time. Thank you bro
 
Hello, I am back again yet with a new challenge. I have a form which I use to feed data to two related tables. One table receives data direct from textboxes while the other receives from a list box. Is the any VBA code can I use for a searchbox to give details in textboxes and list box according to the keyup?
 
You must log in or register to reply here.

Similar threads

N
Solved How to force MS Access display a primary key on the forms after entering data in the first field
Replies
6
Views
229
Pat Hartman
P
N
Solved Saving one field from Json String received in MS Access
Replies
6
Views
516
saketaco
S
N
Solved How to grab the last updated record in a table
Replies
10
Views
323
June7
June7
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
229
lbcurrier
L
S
Solved Boolean values in table controlled by list box
Replies
17
Views
477
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom