[RESOLVED] Loading data into Combox

[BlackRiver]

My Code:

Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider= Microsoft.JET.OLEDB.3.51;" & "Data Source=" & App.Path & " \Index.mdb"
  cn.Open

  Set rsIndex = New ADODB.Recordset


  rsIndex.Open " select Ime,Prezime from StudentDetalji order by Ime",cn,adOpendynamic,adLockOptimistic

  Do until rsIndex.EOF
  Combo1.AddItem rsIdex("Ime")
  Combo1.AddItem rsIndex("Prezime")
  rsIndex.MoveNext
Loop

What is does: Takes data from field1(Ime) places it into the combobox,then takes data from field2(Prezime) and places it under fileld1.I would it to place that data after field1.
So I would have:
Field1 Filed2

NOT
Filed1
Field2

[dee-u]

Try

Combo1.AddItem rsIdex("Ime") & vbTab & rsIndex("Prezime")

[BlackRiver]

Yeah,that works.But now,what if I want to delete the selected item?
Would this be the correct syntax?

rsIndex.open " Delete from TableName Where FieldName1= ' " & Combo1.Text & " ' "

[BlackRiver]

Yes,this also works,as long as I load only ONE field from DB to ComboBox.
But,if I load TWO fields then I cant say
FieldName1= ' " & Combo1.Text & " ' " AND FieldName2 = ' " & Combo1.Text & " ' "

Help!

[dee-u]

What are the values for IME?

[BlackRiver]

Both IME and PREZIME are text values.To translate into English IME=first name,PREZIME=last name

So I need to check if the first part of the combobox item = IME, and the second part = PREZIME, if so DELETE it from the database,refresh the listview.

Or I could just merge those two fields in the DB,and call the FULLNAME,but then I would have to reorganise my entire program.

[Siddharth Rout]

here is an alternative...

Change Dee's code in post 2 to

'-- Instead of Tab use "#" as firstname or lastname might have a space between them
StrName = rsIdex("Ime") & "#" & rsIndex("Prezime")
Combo1.AddItem = StrName

Later you can use split to get the first name and last name which you can use to delete the data in the table for example

Dim StrName As String, MyArray() As String
'
'Your other code
'
'-- Instead of Tab use "#"
StrName = rsIdex("Ime") & "#" & rsIndex("Prezime")
Combo1.AddItem = StrName
'
'Your other code
'
MyArray = Split(StrName, "#")
 
Trim (MyArray(0)) '-- This will give you the first name
Trim (MyArray(1)) '-- This will give you the last name

[BlackRiver]

I followed you example,but it doesnt work in my code.

Do Until reIndex.EOF
strIme = rsIndex("Ime") & "#" & rsIndex("Prezime")
Combo1.AddItem = strIme
rsIndex.MoveNext
Loop

   MyArray = Split(strIme, "#")
   Trim (MyArray(0))
   Trim (MyArray(1))
   
   end sub

That part is under FormLoad.I open a connection with the DB before that,and select fields,as shown in my post num.1.

So the error is ARGUMENT NOT OPTIONAL
And .AddItem is highlited

[Siddharth Rout]

Combo1.AddItem = strIme

my apologies. it should be

Combo1.AddItem strIme

[BlackRiver]

Yeah,I noticed that too.Fixed it.
Is this the correct syntax to delete from DB?

rsIndex.open "Delete from StudentDetalji Where Ime= 'MyArray(0)' AND Prezime= 'MyArray(1)' "

If I run it like that,it says Subscript out of range

And I read that an array subscript is not valid because it falls outside the allowable range.

Trim (MyArray(0)) is highlited.

[Siddharth Rout]

try storing MyArray(0) and MyArray(1) in a variable, say something like this (doing this from memory... so ignore typos if any )

Temp1= MyArray(0)
Temp2=MyArray(1)
rsIndex.open "Delete from StudentDetalji Where Ime= '" & Temp1 & "' AND Prezime= '" & Temp2 & "'"

[BlackRiver]

I have tried that too,but still doesnt work.Again the same error.
I am really out of ideas.

[Siddharth Rout]

post your complete code...

[BlackRiver]

Private Sub Form_Load()
Dim strIme as String
Dim cn as Connection
Dim rsIndex as Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider= Microsoft.JET.OLEDB.3.51;" & "Data Source=" & App.Path & " \Index.mdb"

  cn.Open

  Set rsIndex = New ADODB.Recordset


  rsIndex.Open " select Ime,Prezime from StudentDetalji order by Ime",cn,adOpendynamic,adLockOptimistic

  Do until rsIndex.EOF
  strIme = rsIndex("Ime") & " " & rsIndex("Prezime")
  Combo1.AddItem strIme
  rsIndex.MoveNext
Loop

 end sub

Private sub cmdBrisanjeOK_Click()
Dim MyArray as String, x1 as String, x2 as String

MyArray = Split (strIme, " ")
x1 = MyArray(0)
x2 = MyArray(1)

rsIndex.open "Delete from StudentDetalji where Ime = '" & x1 & "' AND Prezime= '" x2 & "'", cn, adOpenDynamic,adLockOptimistic
cn.Close

Unload Me
frmIndex.Show

End Sub

I'm not sure if I'm wrong here or not,but I opened the connection with DB only once,in form load.Do I have to open it again in Button Click,or is it OK to leave it like that?
It works fine until I click the button to delete the selected item in combobox.

[Siddharth Rout]

Okay I have cleaned your code and my comments are in green. You will now understand why the errors were happening...

Hope this helps...

'-- You had incorrectly declared the array
Dim MyArray() As String, x1 As String, x2 As String
 
Private Sub Form_Load()
    Dim cn As New ADODB.Connection, rsIndex As New ADODB.Recordset
    Dim DBFullName As String, strQuery As String, strIme As String
    
    '-- Not the best way but a very clean way of handling your code
    DBFullName = App.Path & " \Index.mdb"
    cn.ConnectionString = "Provider= Microsoft.JET.OLEDB.3.51;" & "Data Source=" & DBFullName
    cn.open
    strQuery = "Select Ime,Prezime from StudentDetalji Order by Ime"
    
    rsIndex.open strQuery, cn, adOpenDynamic, adLockOptimistic
 
    Do Until rsIndex.EOF
        '-- Like I suggested do not use space as full names
        '-- can have more than once space
        strIme = rsIndex("Ime") & "#" & rsIndex("Prezime")
        Combo1.AddItem strIme
        rsIndex.MoveNext
    Loop
    
    '-- It is good to close the connection instead of keeping it open
    '-- unnecessarily
    rsIndex.Close
    Set rsIndex = Nothing
    
    cn.Close
    Set cn = Nothing
End Sub
Private Sub cmdBrisanjeOK_Click()
    'MyArray = Split(strIme, "#") '--- Where is the value of strIme coming from????? and hence the error
    'if you are picking the value from combo then try something like this in lieu of the above
    MyArray = Split(Trim(Combo1.Text), "#")
    
    x1 = Trim(MyArray(0))
    x2 = Trim(MyArray(1))
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '-- Open the connection once again here before performing the below action as shown above
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    rsIndex.open "Delete from StudentDetalji where Ime = '" & x1 & "' AND Prezime= '" _
    & x2 & "'", cn, adOpenDynamic, adLockOptimistic
    
    '''''''''''''''''''''''''''''''''''''
    '-- Close connecton here
    '''''''''''''''''''''''''''''''''''''
    
    Unload Me
    
    frmIndex.Show
End Sub

[BlackRiver]

Thank you very much,the code you wrote is excellent and works like a charm!

I would like to ask you a question.You seem to know very much when it comes to VB.How did you learn it so well?Tutorials?Forums like this one?Books?
I'm a real begginer in this and I know I lack the basics to become a bit more advanced.

Once again thank you!