Match The Data

[g.mie]

Hello guys..

I have problem here. How to match the data from ComboBox to Database. Detail information as below:-

1. ComboBox - contain letter from A to Z
2. SQL database - contain a few letter (ex: A, B, F and G)

I want to hide letter A, B, F and G in ComboBox. Because I want to make sure there are no same letter in database when I save it later on.

So, first how to match letter in database to letter in ComboBox and
second hide letter in ComboBox when it match.

Plz somebody help. I'm urgent on it.

TQ

[alex_read]

ooookay, this one ain't tested, but should give the general idea if I've buggered the code :

Dim strSQL as string
Dim intCounter as integer

strSQL = "SELECT * FROM TableName WHERE Column1 =" &  _
               "'" & combo1.list(0) & "'"

For intCounter = 1 to Combo1.listcount - 1
   strSQL = strSQL & " OR " & "'" & combo1.list(intCounter) & "'"
Next intCounter

Will give you a SQL string you can use to match the combobox & the database.

As I understand it, why do you want to put A-Z in the combobox to start with, you could access the database & grab the recordset of the table (RS) then use this type of thing :

Rs.movefirst

Do while not Rs.EOF
   Combo1.additem Rs!Column1
   Rs.Movenext
Loop

[Mark Sreeves]

here is my contribution...

use a table which contains ALL the letters and pick out all those which don't occur on the main table.

[g.mie]

TQ,

But I'll try later. Time to go home now .

See U both later.

Bye

[g.mie]

Hello alex_read

I think it not work. I give more info about my project:

1. Table name : cs_roster_detail
2. Fields name : id, queue(fields to save letter)
3. ComboBox name : cboQueue(contain a list of Letter, A-Z).

And for Mark Sreeves, I can't add more table into my project so it mean that I cannot use your samle. May be U have another idea.

Plz help me on this.

[alex_read]

Ignore mine for the moment as Marks sample's fine to go from -
not every day you produce working code Mark, but this one's !

g.mie, can't see why Mark's don't work on yours make sure you put his database under the "C:" path, or if you have it in the same path as the vb app from the zip file, change the following :

Private Sub Form_Load()
    Set db = DAO.OpenDatabase(App.Path & "\letter.mdb")

    LoadCombo
End Sub

Other than that, check the Project > References menu to see if it has a reference to the Microsoft DAO 3.5 object library.

[Mark Sreeves]

Originally posted by alex_read
Ignore mine for the moment as Marks sample's fine to go from -
not every day you produce working code Mark, but this one's !

I presume that you mean I don't usually post a complete working project!


The dude is saying that he can't add another table to the database.

I don't know why on earth that could be.

[alex_read]

Try using this for your project :

[g.mie]

I'm work on it now. If anything I shout U all back.

[Mark Sreeves]

There's probably a better way of doing this but you could create a "reference" table and delete it afterwards

Option Explicit
Dim db As Database

Private Sub Form_Load()
Set db = DAO.OpenDatabase("c:\letter.mdb")

Dim rst As Recordset
Dim strSQL As String
Dim i As Integer

strSQL = "CREATE TABLE tblChars " _
    & "(ch TEXT);"

db.Execute strSQL

Set rst = db.OpenRecordset("tblChars")

With rst
For i = 65 To 90
  .AddNew
  !ch = Chr(i)
  .Update
Next i
.Close

End With
Set rst = Nothing

LoadCombo
End Sub
Private Sub LoadCombo()
Dim rst As Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCTROW [tblChars].*" & _
" FROM tblChars LEFT JOIN Table1 ON [tblChars].[ch] = [Table1].[f1] " & _
" WHERE ([Table1].[f1] Is Null); "

Combo1.Clear
Set rst = db.OpenRecordset(strSQL)
With rst
  While Not .EOF
    Combo1.AddItem !ch
    .MoveNext
  Wend
  .Close
End With

Set rst = Nothing


Combo1.ListIndex = 0
End Sub

Private Sub Form_Unload(Cancel As Integer)
db.Execute "DROP TABLE tblChars"
db.Close
End Sub