6 Replies - 416 Views - Last Post: 10 February 2013 - 10:22 AM Rate Topic: -----

#1 chocobo162002  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 08-February 13

Sum db of a table to a new table and datacombo problem

Posted 08 February 2013 - 01:28 PM

Hi,

Here's the situation I am in right now (actually, its been 2 weeks) in creating my inventory system. I have 2 forms, one is assigned for my Item_List(frm_ItemList) then the other one is assigned for the ordered_item (frm_OrderedItem) with 1 database named as inventory.mdb. In my Item_List form, I assigned the following fields: Item_Number(textbox), Item_Code(textbox), Item_Name(textbox), Item_Type (combobox), Supplier_Name(combobox), Supllier_Code(combobox), Item_In(textbox), Item_Out(textbox), Item_Remaining(textbox), Supplier_Price(textbox), Item_SRP(textbox), Date_Created (label), Time_Created (label). Said fields are assigned to my table named as add_item in inventory.mdb. For my second form, I have assignd the following fields, Item_Code(datacombo), Item_Name(datacombo), Item_Type (datacombo), Supplier_Name(datacombo), Supllier_Code(datacombo), Order_Qty(textbox), Date_Ordered (label), Time_Ordered (label). Said fields are assigned to my table named as ordered_item in inventory.mdb.


1. For the frm_OrderedItem, if I select an item name in the Item_Name datacombo, the other datacombo fields should reflect the data of records of the item selected. The same goes with Item_Code datacombo. Is there a way to achieve this?
2. How can I sum the value of Order_Qty WHERE Item_Name = "Soap" then the total should be reflected in the field of Item_List.Item_In (connected to add_Item table). The item name soap is selected from the datacombo Item_Name.

I will post the code and attached the database I created once I got home.

Please help... Thanks a lot.

Is This A Good Question/Topic? 0
  • +

Replies To: Sum db of a table to a new table and datacombo problem

#2 chocobo162002  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 08-February 13

Re: Sum db of a table to a new table and datacombo problem

Posted 08 February 2013 - 02:46 PM

Option Explicit

Public conn As ADODB.Connection
Public rs As ADODB.Recordset
Dim Odbase As String
Dim search As Variant


Private Sub Form_Load()

On Error GoTo errhand
    
    Set conn = New ADODB.Connection
    conn.ConnectionTimeout = 5
    Odbase = App.Path & "\inventory.mdb"
    
    conn.Open ("Provider =Microsoft.Jet.OLEDB.4.0; Data Source =" & Odbase & "; Jet OLEDB:Database;")

    Set rs = New ADODB.Recordset
    rs.Open "Select Item_Name, Item_Code, Item_Type, Supplier_Name, Supplier_Code from Add_Item", conn, adOpenStatic, adLockOptimistic
    If rs.BOF And rs.EOF Then
        MsgBox "No Data found", vbCritical, "Error"
    Else
        With rs
            Do While Not .EOF
                cbo_SupplierCode.AddItem !Supplier_Code
                cbo_ItemName.AddItem !Item_Name
                cbo_ItemType.AddItem !Item_Type
                cbo_ItemCode.AddItem !Item_Code
                cbo_SupplierName.AddItem !Supplier_Name
                .MoveNext
            Loop
        End With
    End If
    Set rs = Nothing
    
    Exit Sub
    
    
    
errhand:

    MsgBox Err.Description, vbCritical, "Connection Error"
    Err.clear
    End

End Sub

Private Sub cbo_SupplierCode_Click()
    search = Val(cbo_SupplierCode.Text)
    populatecombo (1)
End Sub
Private Sub cbo_ItemName_Click()
    search = cbo_ItemName.Text
    populatecombo (2)
End Sub
Private Sub cbo_ItemType_Click()
    search = cbo_ItemType.Text
    populatecombo (3)
End Sub
Private Sub cbo_ItemCode_Click()
    search = cbo_ItemCode.Text
    populatecombo (4)
End Sub
Private Sub cbo_SupplierName_Click()
    search = cbo_SupplierName.Text
    populatecombo (5)
End Sub

Private Sub populatecombo(combonum As Integer)
    
    Dim table   As String
    
    Select Case combonum
        Case 1: table = "Supplier_Code"
        Case 2: table = "Item_Name"
        Case 3: table = "Ttem_Type"
        Case 4: table = "Item_Code"
        Case 5: table = "Supplier_Name"
    End Select
    Set rs = Nothing
    Set rs = New ADODB.Recordset
    If combonum = 1 Then
        rs.Open "Select * from Add_Item Where " & table & _
                " = " & search, conn, adOpenStatic, adLockOptimistic
    Else
        rs.Open "Select * from Add_Item Where " & table & _
                " = '" & search & "'", conn, adOpenStatic, adLockOptimistic
    End If
    If rs.BOF And rs.EOF Then
        MsgBox "No Data found", vbCritical, "Error"
    Else
        With rs
                cbo_ItemCode.Text = !Item_Code
                cbo_ItemName.Text = !Item_Name
                cbo_ItemType.Text = !Item_Type
                cbo_SupplierCode.Text = !Supplier_Code
                cbo_SupplierName.Text = !Supplier_Name
                
        End With
    End If

End Sub

Private Sub cmd_SaveOrder_Click()
On Error GoTo errhand
    rs!Item_Qty = txt_ItemQty.Text
    conn.Execute ("INSERT INTO Ordered_Item VALUES ('" & rs!Item_Code & "', '" & rs!Item_Name & _
                  "', '" & rs!Item_Type & "', '" & rs!Supplier_Name & "', '" & rs!Supplier_Code & "')")
    MsgBox "Item Code " & rs!Item_Code & " was successfully save", vbOKOnly, "Data Save"
    Exit Sub
errhand:
    MsgBox Err.Description, vbOKOnly, "Error"
End Sub





From here I get the message "Number of query values and destination fields are not the same." This is the first time I've seen this error. Or should I really consider this as error? I really cannot find the problem with this.

I tried attaching the database. However, an error always prompting.
Was This Post Helpful? 0
  • +
  • -

#3 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,354
  • Joined: 02-June 10

Re: Sum db of a table to a new table and datacombo problem

Posted 08 February 2013 - 02:55 PM

You can't attach the database. The site won't allow it.

When you post the code be sure to use the code tags.

Then confirm you are doing this in a language that has been effectively dead for 10 years. Should this be moved to VB.NET?

Quote

if I select an item name in the Item_Name datacombo, the other datacombo fields should reflect the data of records of the item selected. The same goes with Item_Code datacombo. Is there a way to achieve this?


In other words:


Q: ...get Form/class 'A' to make a change or talk to Form/class 'B'

NOTE: Don't try to access GUI controls across forms. Its wrong. Nobody will hire you if you do this sort of crap. It violates every guideline for 'black box' programming, Separation of Responsibility, loose binding of components, and event driven programming. Read the tutorials and learn to do it right the first time so you don't develop bad habits that you just have to un-learn later.
A:
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,538
  • Joined: 12-December 12

Re: Sum db of a table to a new table and datacombo problem

Posted 08 February 2013 - 03:10 PM

Quote

From here I get the message "Number of query values and destination fields are not the same." This is the first time I've seen this error. Or should I really consider this as error?

Er, yes. I assume it relates to this statement:

"INSERT INTO Ordered_Item VALUES ('" & rs!Item_Code & "', '" & rs!Item_Name & _
    "', '" & rs!Item_Type & "', '" & rs!Supplier_Name & "', '" & rs!Supplier_Code & "')"

and the number of values doesn't reflect the number of fields you have in this table (and they need to be in the correct order).

I don't know if this link is relevant to your enquiry.
Was This Post Helpful? 0
  • +
  • -

#5 chocobo162002  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 08-February 13

Re: Sum db of a table to a new table and datacombo problem

Posted 08 February 2013 - 03:15 PM

Thanks for the tip Master. I will read the links you post for me to learn properly. I am a newbie in vb6.

I did put the codes in code tag.
Was This Post Helpful? 0
  • +
  • -

#6 chocobo162002  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 08-February 13

Re: Sum db of a table to a new table and datacombo problem

Posted 08 February 2013 - 03:30 PM

View Postandrewsw, on 08 February 2013 - 03:10 PM, said:

Quote

From here I get the message "Number of query values and destination fields are not the same." This is the first time I've seen this error. Or should I really consider this as error?

Er, yes. I assume it relates to this statement:

"INSERT INTO Ordered_Item VALUES ('" & rs!Item_Code & "', '" & rs!Item_Name & _
    "', '" & rs!Item_Type & "', '" & rs!Supplier_Name & "', '" & rs!Supplier_Code & "')"

and the number of values doesn't reflect the number of fields you have in this table (and they need to be in the correct order).

I don't know if this link is relevant to your enquiry.


The reason why the number of values/field reflected on my actual code versus the number of field mentioned in the table ordered_item is not the same because the table is just a sample. However, once I find out the problem with my error and I manage to make it worked, I will immediately add the field to my actual database.

Hopefully, I can pinpoint where I am wrong with this code.

I check your link Sir.

Thank you.
Was This Post Helpful? 0
  • +
  • -

#7 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Sum db of a table to a new table and datacombo problem

Posted 10 February 2013 - 10:22 AM

Quote

From here I get the message "Number of query values and destination fields are not the same." This is the first time I've seen this error. Or should I really consider this as error? I really cannot find the problem with this.

Sometimes the error you get actually describes the problem. This is a very common error in SQL. Let's say you have a very simple table called Name:

LastName
FirstName
MiddleName

Now, SQL INSERT takes this syntax in its simplest form
INSERT INTO Name VALUES (Rodes, Robert, Emmet)
This code works, because I'm providing a value for each of the fields in the table. Now, suppose I do this:
INSERT INTO Name VALUES (Rodes, Robert)
Now, the "number of query values an destination fields are not the same." We have three fields in the table and are only providing two values. How does SQL know which fields to insert the values into? It doesn't. To fix this, we can (and usually do) tell it which fields to put our values into, so:
INSERT INTO Name (LastName, FirstName) VALUES ("Rodes, "Robert")
Now we've told it where to put our values, so no error.

Now you know. :)

This post has been edited by BobRodes: 10 February 2013 - 10:23 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1