i'm having a bit of trouble with listindex...
i have a combobox containing two columns which is sourced from a table with two fields as it's primary key.
thus when i try to fill in a combobox from the database i must ensure that the value i set for the combobox is matches up with both fields (eg. first name and last name).
say i'm after Sarah Jones and i have several Sarah's in list, like:
- Rylie Coles
- Sarah Bills
- Sarah Farrington
- Sarah Jones
- Tom Harley
the table is in the alphabetical order of first name then last name.
this is the part of code i have to check for the right person...
1 With rstPerson
2 Me.cmbName.SelText = Trim(![fName])
3 While Trim(Me.cmbName.Column(0)) = Trim(![fName]) And _
4 Me.cmbName.Column(1) <> Str(![lName])
5 cmbName.ListIndex = cmbName.ListIndex + 1
7 End With
however, MS Access fails to update the combo box with the appointed ListIndex item and automatically updates it with the very first item of the list of matching items ie Sarah Bills.
i gathered this as when i replace line 5 with
cmbName.ListIndex = cmbName.ListIndex +3
it returned "Tom Harley" and exited the loop...
is this a bug or am i just not using it right???
ps. when testing the code above, set a break to not fall into a forever loop...
JTRockville - hmm... but i CAN actually set the listindex... i can set the listindex to the names 'Rylie Coles', 'Sarah Bills' and 'Tom Harley' in the example above, just not 'Sarah Farrington' or 'Sarah Jones'...
lansing - the lines before the example code read:
Set Dbh = CurrentDb
Set rstPerson = Dbh.OpenRecordset(sqlStatement, dbOpenDynaset)
so what i do is, when 'personal details form' is opened, i open up the record of a person and update the fields from that recordset.
the combo box has its row source from 'person table' displaying first names and last names of people
the combo box stores the value of people's first names
Person table contains three fields: id, fName, lName
to answer your question, i guess i update it from a recordset using the first name, then check that it's the right person by comparing the last names, and if it's not the right person then i update the combo box to the next person in the combo box list by incrementing the listindex value and repeat the checking process..
if what i am trying to do is not possible... then how do i create a combo box of people's full names and have it updated, given that people can have the same first name or the same last name but not the same full name???
Ok, I see that your linking the table data to the combo box. Anyways, say that the combo box name is cboNames. Ok, do a cboName.Refresh, this will refresh the sql query. Anyways, for the duplicate names. There is a possibility that two people has the same first name and last name. So you either have to set the ID, First, and last name as the primary key unless the ID is there social security number, so you just set that as the primary key.
To do a search if there is a possiblity of the same name. Do a filter property like Tablename.Filter = "FirstName = ' " & txtFirstName & "' And LastName = '" & txtLastName &" ' "