Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    73

    Talking Unanswered: Updating results in listbox..

    Hi Kind Souls,

    I faced error when selected value in Combo box, values arent able to display in the listbox provided.

    And also btnSMedia is suppose to collect the input text from txtMedia and using this to search all the relevant records which the relevant field value contain the same input text and finally display all the other fields and records results on listbox.
    However, no results were shown.

    My connection was actually not faulty.Its just that i cant display what i wanted in the listbox.

    Im currently using the visual basic editor behind Microsoft Access 2000.

    Any guidance will be much appreciated..

    thankyou very much!!!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    What action do you require to occur after a value from the Combo box is selected.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2005
    Posts
    73

    Talking

    When combo box is selected under the click event, results are suppose to be display.. This is roughly my codes when the combo box is selected..

    Private Sub cmbCountry_Click()
    cmbCountry.SetFocus


    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    Dim txtCountry As String
    txtCountry = cmbCountry.Text

    'Create an ADO SQL Recordset
    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.open "c:/.../MergeDb.mdb"

    Dim mySQL As String
    mySQL = "Select * from MIXMODEviaDACs where Country = '" & txtCountry & "'"
    rs.open mySQL, conn, adOpenStatic, adLockOptimistic

    lstDisplay.SetFocus
    lstDisplay.Requery

    Me.lstdisplay ---> ERROR: Invalid use of property

    End Sub

    I really do not know why there arent any results shown on listbox.. have been trying for sometime but to no avail..
    Kind souls, please help me!!
    Last edited by shanshan; 03-08-05 at 23:36.

  4. #4
    Join Date
    Jan 2005
    Posts
    73
    Oops ooopsss oooopsss...

    I need it urgent for my proj to be submitted nextweek..
    Anyone can help... PLease please please....

    I tried it for some weeks already..still recv no results.. Why!!! WHY!!!

    *Sob sob*

    Almost all the listbox properties i had tried... but none of them seem to work.. WHY!!! It jus cat retrieve the results out for displaY!!!! Nothing wrong with my sql statment nor my db conection.. but what the hell the listbox cant display!!!!!!!!!!! haiz..

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Just for the heck of it....try this:

    Make sure your ListBox RowSource Type is set to Table/Query.
    Code:
    Private Sub cmbCountry_Click()
    ' cmbCountry.SetFocus --- Not Needed (Allready has focus)
     
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
     
    Dim txtCountry As String
    txtCountry = cmbCountry.Text
     
    'Create an ADO SQL Recordset
    ' Set conn = New ADODB.Connection --- Not Needed (Allready Done above)
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.open "c:/.../MergeDb.mdb"
     
    Dim mySQL As String
    mySQL = "Select * from MIXMODEviaDACs where Country = '" & txtCountry & "'"
    rs.open mySQL, conn, adOpenStatic, adLockOptimistic
     
    Me.lstDisplay.RowSource = mySQL
    End Sub
    You may need to play with the BoundColumn, ColumnCount, and ColumnWidths properties for this to display properly.

    To get an idea of what this may do, Set the ColumnCount property to the Total number of columns within the MIXMODEviaDACs table + 1. Set the ColumnWidths property accordingly (i.e: 1";1";1";1"....for all columns to display). Set the BoundColumn property to 1.

    Modify the ListBox's RowSource property SQL statement so as to query and display whatever you want. But don't forget to also modify the ColumnCount and ColumnWidths properties accordingly.

    PS: You should put code in to close your ADODB connection as well within this routine (at the end before the End Sub).

    Last edited by CyberLynx; 03-09-05 at 03:00.

  6. #6
    Join Date
    Jan 2005
    Posts
    73

    Help Me!!!

    Oh my GOD!!! Thank you so muchhhh...Thankyou.. sososso sososo much...CyberLynx.. I really dunnoe how to thank you enough...

    I had been trying and fiddling it for ages yet nothing came out of it!!! ArghH! This time i really got to thank you..

    But now sadly, i still have another prob with my btnMedia.. In this case the codes are below.. As usual.. Nothing came out in the listbox...


    Private Sub btnSMedia_Click()
    On Error GoTo Err_btnSMedia_Click
    ADOOpenRecordset
    Exit_btnSMedia_Click:
    Exit Sub
    Err_btnSMedia_Click:
    MsgBox Err.Description
    Resume Exit_btnSMedia_Click

    End Sub


    Private Sub ADOOpenRecordset()

    'MsgBox ("in ADOOpenRecordSet")

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim fld As ADODB.field
    Dim strOutput, strMedia As String
    Dim mySQL As String
    Dim i, max As Integer

    Dim sConnect As String

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.open "c:/.../MergeDb.mdb"

    txtMedia.SetFocus
    strMedia = txtMedia.Text

    mySQL = "Select * from MIXMODEviaDACs where Media = '" & strMedia & "'"
    rs.open mySQL, conn, adOpenStatic, adLockOptimistic, adCmdText


    If rs.RecordCount = 0 Then
    MsgBox ("No record found")
    ExitSub
    End If

    lstDisplay.Requery
    Me.lstDisplay

    rs.Close
    conn.Close

    Set rs = Nothing
    Set conn = Nothing

    End Sub

    is there anything wrong with my logic? Data just don't be good and display out the results i wanted... *scratch* scratch*

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmmm.....

    Code:
    Private Sub btnSMedia_Click()
       On Error GoTo Err_btnSMedia_Click
    
       Call ADOOpenRecordset
       
    Exit_btnSMedia_Click:
       Exit Sub
    
    Err_btnSMedia_Click:
       MsgBox Err.Description
       Resume Exit_btnSMedia_Click
    End Sub
    
    
    Private Sub ADOOpenRecordset()
       'MsgBox ("in ADOOpenRecordSet")
       Dim conn As ADODB.Connection
       Dim rs As ADODB.Recordset
       Dim fld As ADODB.field
       Dim strOutput As String, strMedia As String
       Dim mySQL As String
       Dim i As Long, max As Long
       Dim sConnect As String
    
       Set conn = New ADODB.Connection
       Set rs = New ADODB.Recordset
    
       conn.Provider = "Microsoft.Jet.OLEDB.4.0"
       conn.open "c:/.../MergeDb.mdb"
    
       If IsNull(Me.txtMedia) Then 
    	  Goto ExitThisRoutine
       Else
    	  strMedia = Me.txtMedia
       End If
       
       mySQL = "Select * FROM MIXMODEviaDACs WHERE [Media] = '" & strMedia & "'"
    rs.open mySQL, conn, adOpenStatic, adLockOptimistic, adCmdText
    
    
    If rs.RecordCount = 0 Then
       MsgBox ("No record found")
       Exit Sub
    End If
    
    Me.lstDisplay.RowSource = mySQL
    
    ExitThisRoutine:
    rs.Close
    conn.Close
    
    Set rs = Nothing
    Set conn = Nothing
    
    End Sub
    As mentioned in the earlier post....you may need to modify your ListBox's RowSource SQL statement to suit your needs. I am simply using the mySQL string as a sample. You may want to create an entirely different SQL string so as to query and display exactly what you want within your ListBox.


  8. #8
    Join Date
    Jan 2005
    Posts
    73

    Cool

    Ohh..Thankyou thankyou so much.. It works!!!! Ahh.. Finally solved my month headaches.. Jus couldnt get the codes to display the results on the stupid listbox..
    Well.. admit im stupid *slap* Geezz.. Anyway, i finally get my long research problem solve!!!! Thankyou!!

    arghh.. i think i gotta stump into another problem again.. My editting.. Do youguys know a simple way out of it.. As in when you select the record from the listbox.. It leads you to the edit page..

    And do i need to decare one txtbox by one txtbox to save all the new values... If i have 20 fields.. i mus declare 20 txtboxes to read the value and update to the recordset??!!!

    And by the way... i dunnoe how it is going to past the old values to the edit page.... *stumped*

  9. #9
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by shanshan
    Ohh..Thankyou thankyou so much.. It works!!!! Ahh.. Finally solved my month headaches.. Jus couldnt get the codes to display the results on the stupid listbox..
    Well.. admit im stupid *slap* Geezz.. Anyway, i finally get my long research problem solve!!!! Thankyou!!

    arghh.. i think i gotta stump into another problem again.. My editting.. Do youguys know a simple way out of it.. As in when you select the record from the listbox.. It leads you to the edit page..
    errr... use Private Sub Listboxname_BeforeUpdate(cancel As Integer) to start up the edit page?

    And do i need to decare one txtbox by one txtbox to save all the new values... If i have 20 fields.. i mus declare 20 txtboxes to read the value and update to the recordset??!!!
    Errr, not sure what you mean.. You dont need to declare txtboxes at all? You need to declare VARIABLES, but not textboxes afaik

    And by the way... i dunnoe how it is going to past the old values to the edit page.... *stumped*
    And... the edit page is a new form? Then use something like...

    Code:
    Var = Forms!MainFormName!txtField
    ...on your editform (class module) to assign the value from txtfield (on your main form named 'MainFormName') to 'var'...

  10. #10
    Join Date
    Jan 2005
    Posts
    73
    Ooopss!! Yah.. its 20 variables to read my txtfield input...Bizzz..

    Thanks.. But...Ehh.. Sorry.. i dun quite understand this..
    Var = Forms!MainFormName!txtField
    on your editform (class module) to assign the value from txtfield (on your main form named 'MainFormName') to 'var'...
    Actually to be specific enough, i have 20 columns of data.. When search result is displayed in the listbox.. User gets to select one record.. and by pressing the edit button..

    User will be lead to the edit page with all the 20 columns of the selected record in listbox. Old data is copied to the respective textfields in the edit form ready for user to edit.

    And when btnSave is clicked, records will be updating the old records in the database... Do u think this can be done??

    Ahhhh... and how am i suppose to go about doing it?? *puzzled*

    Please help me!!!
    Last edited by shanshan; 03-09-05 at 21:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •