Add DB field to textboxes and lists..


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Add DB field to textboxes and lists..

  1. #1
    Join Date
    Dec 2006
    Posts
    9

    Unhappy Add DB field to textboxes and lists..

    Hi

    I have managed to populate the list of items from my DB in my combo box...

    But now the field "item_name" which is populating my combo list also has a msp and a shipping field corresponding to it..

    What i want to do is that when a particular Item is selected in the combolist.. the corresponding msp and shipping value should get stored in txtmsp.text and txtship.text which i can display and use later..

    please advice on code

  2. #2
    Join Date
    Oct 2005
    Location
    Maady
    Posts
    1,819

    Arrow

    Ur problem is with using select statment or what ?
    u must make a select msp and ship from database where item_name is the selected from the combo ..
    Please explain more if your problem is with select statment or catch cobobox events .
    Programmer&Cracker CS
    MyBlog:Blog.Amahdy.com
    MyWebsite:www.Amahdy.com

  3. #3
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    When you created the recordset object for the Rowsource of the combobox you need to include and extra fields that you wish to retreive. Then when an item is selected just set the recordsets AbsolutePosition to the index of the Item selected and then set the text boxes to the approprate fields value. Here is an Example where I have used a comboBox control array for multipule conboBoxes and a recordset array for each comboBox's rowsource. This way I can use the Change Event's Index Item to not only select which comboBox but also which recordset to use.
    Code:
    Private Sub cboFields_Change(Index As Integer)
      On Error GoTo Er
      
      If cboFields(Index).DataChanged = True Or mbAddNewFlag = True Then
        If mbEditFlag = False Then cmdEdit_Click
        'Set Copies Textbox to copies from Item selected in ComboBox(0)
        If Index = 0 And (rs(Index).AbsolutePosition <> cboFields(Index).SelectedItem) Then
          rs(Index).AbsolutePosition = cboFields(Index).SelectedItem
          'Only set the value if textbox is blank, so users entered data is not over written
          If cboFields(Index).BoundText <> "" Then
            'Set Copies TextBox(1) to Copies from Item Selected
            txtFields(1) = rs(Index).Fields("L_COPIES").Value
          End If
        End If
      End If
    Done:
      Exit Sub
    Er:
      Resume Done
    End Sub

  4. #4
    Join Date
    Dec 2006
    Posts
    9
    Let me explain more.. The image attached is the simple form ive created



    the DB is : db.mbd
    table : to save order : order_details
    items detials : item_info
    fields in item_info : item_name, msp, shipping

    Now I have populated the list by the following code

    Code:
    Private Sub Form_Load()
    
    ' The below is to save order
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(App.Path & "\db.mdb")
    Set rs = db.OpenRecordset("order_details", dbOpenTable)
    
    'The below is to get the item detials form another field...
    Set ws1 = DBEngine.Workspaces(0)
    Set db1 = ws1.OpenDatabase(App.Path & "\db.mdb")
    Set rs1 = db1.OpenRecordset("item_info", dbOpenTable)
    
    Do Until rs1.EOF
      cmbitem.AddItem rs1!item_name
      rs1.MoveNext
    Loop
    
    End Sub
    Now what i want to do is that when a person click on a particualr item from the list ( cmbitem ).. the MSP and Shipping txt boxes should get populated by the corresponding MSP and shipping from item_info table which is next to the item name..... HOW TO CODE THIS???

    and then when a person fills in everything.. and clicks on add order it gets saved in order_detials Table

    thanks all..

  5. #5
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Try Something like this:
    Code:
    Option Explicit
    Dim ws As Workspace
    Dim db As Database
    Dim rs As Recordset
    Dim rs1 As Recordset
    
    Private Sub Form_Load()
    
      ' The below is to save order
      Set ws = DBEngine.Workspaces(0)
      Set db = ws.OpenDatabase(App.Path & "\db.mdb")
      Set rs = db.OpenRecordset("order_details", dbOpenTable)
      
      Set rs1 = db.OpenRecordset("item_info", dbOpenTable)
      
      Do Until rs1.EOF
        cmbitem.AddItem rs1!item_name
        rs1.MoveNext
      Loop
    
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
      rs1.Close
      Set rs1 = Nothing
      rs.Close
      Set rs = Nothing
      db.Close
      Set db = Nothing
      ws.Close
      Set ws = Nothing
    End Sub
    
    Private Sub cmbitem_Click()
      Dim pos As Long
      pos = cmbitem.ListIndex
      If pos < 0 Then Exit Sub
      rs1.MoveFirst
      rs1.Move pos
      Me.textmsp = Format(rs1!MSP, "Currency")
      Me.textship = Format(rs1!Shipping, "Currency")
    
    End Sub

  6. #6
    Join Date
    Dec 2006
    Posts
    9
    Thanks.. part of code works great..

    Now in EDIT ORDER.. same form structure..

    But I have a problem.. My order_detials has stored a forigen key "item_id" which is the primary key to the table "item_info"

    What I wanna do is that when a person chooses the orderid populated in cmborderid.. the particular item name should be selected in the populated cmbitemname..

    But they are stored in two diff tables.. only relation is the itemId..

    so what i have done is that i made a variable pos1 and stored the item_id from order_detials in that.. Now somehow i have to make it choose the corresponding itemname from item_detials table.. i cannot use listindex as it can change anytime if i add/edit/delete items..

    So what my theory is that i have made another invisible cmbitemid .. and populated that with the itemid from table "item_info" ..

    So all it has to do is that to select the value stored i pos1 in that.. cause the listindex of cmbitemid and cmbitemname WILL BE equal.. so it will automatically select item from item combo

    so how do i make a cmb box choose an particular item whose list index is not known.. .

    eg

    it a combo box has

    1
    2
    3
    4
    5

    OR

    a
    b
    c
    d
    e
    f

    and i need to select "d" from the list automatically but i dont know its position.. how do i make it select that automatically??

    also i need to populate the msp and shipping to the corresponding itemname which is selected..

    hope i made myself clear..

  7. #7
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You have two options. One is that you still have the recordset open so you can find the record by item name and get it from the record, or you can store it within the combobox litst.
    Recordset method:

    'find item_id to save in database by searching for name in combobox
    rs1.FindFirst "item_name = """ & cmbitem.List(cmbitem.ListIndex) & """"
    'set current orders item_id to match item from combobox
    rs!item_id = rs1!item_id

    '...or...

    'search for orders item_id to get item_name and show in combobox
    rs1.FindFirst "item_id = " & rs!item_id
    cmbitem.Text = rs1!item_name

    ComboBox List method:
    So long as your Item_id is a long integer you can store it within the list just like you did with the item_name. Use the ItemData property to store this value. Here is the modified version of your form load routine:
    Code:
    Private Sub Form_Load()
    
      ' The below is to save order
      Set ws = DBEngine.Workspaces(0)
      Set db = ws.OpenDatabase(App.Path & "\db.mdb")
      Set rs = db.OpenRecordset("order_details", dbOpenTable)
      
      Set rs1 = db.OpenRecordset("item_info", dbOpenTable)
      
      Do Until rs1.EOF
        cmbitem.AddItem rs1!item_name
        cmbitem.ItemData(cmbitem.NewIndex) = rs1!item_id
        rs1.MoveNext
      Loop
    
    End Sub
    To save the item_id of the selected item you would index the ItemData list with ListIndex just like you do to find which item selected in the combobox. Like this:
    rs!item_id = cmbitem.ItemData(cmbitem.ListIndex)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center