How to add data to a table from a list box in Ms Access (1 Viewer)

V

vixinho

New member
Local time
Tomorrow, 01:24
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
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 23:24
Joined
Jul 21, 2014
Messages
2,272
Hi and welcome to AWF!

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

Please post the rest of it.
 
V

vixinho

New member
Local time
Tomorrow, 01:24
Joined
May 11, 2020
Messages
8
Hi Cheeky, the only part I omitted is
Code: 
End Sub
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 23:24
Joined
Jul 21, 2014
Messages
2,272
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
' ...
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 23:24
Joined
Jul 21, 2014
Messages
2,272
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
 
V

vixinho

New member
Local time
Tomorrow, 01:24
Joined
May 11, 2020
Messages
8
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
 
V

vixinho

New member
Local time
Tomorrow, 01:24
Joined
May 11, 2020
Messages
8
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
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 23:24
Joined
Sep 21, 2011
Messages
14,231
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
 
V

vixinho

New member
Local time
Tomorrow, 01:24
Joined
May 11, 2020
Messages
8
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...
 
cheekybuddha

cheekybuddha

AWF VIP
Local time
Today, 23:24
Joined
Jul 21, 2014
Messages
2,272
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.
 
V

vixinho

New member
Local time
Tomorrow, 01:24
Joined
May 11, 2020
Messages
8
Sure, Let me revert to the working code and study on your code some other time. Thank you bro
 
V

vixinho

New member
Local time
Tomorrow, 01:24
Joined
May 11, 2020
Messages
8
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

R
  • Question
Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames.
2
Replies
32
Views
901
spaLOGICng
S
N
Solved How to avoid the domain functions in Ms Access
Replies
13
Views
552
nector
N
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
223
tvanstiphout
T
N
Solved How to pass parameters on a pass through query in ms access
Replies
18
Views
838
nector
N
N
Solved How to filter a single form in MS Access with VBA
Replies
12
Views
455
nector
N

Users who are viewing this thread

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