Dropdown loosing it's value

[abenitez77]

I have a dropdown that once I select a value, it runs an event on the After Update Event. Once the code finishes it resets the drop down to the first value, even though I selected the second value. Here is the code and the data for the dropdown. So if I select ID = 5, when the after update code finishes, it does everything fine but in the form, it leaves me on ID = 4. Why and how can i prevent/fix this?

' ***** Properties for Dropdown ***************'
' Row Source
SELECT ProjectsID, ProjectName, AuditYear FROM Projects ORDER BY ProjectName, AuditYear, ProjectType;

' Row Source Type
Table/Query

' Bound Column
2


' ******** Data for Dropdown **************************************
ProjectsID ClientName ProjectName ProjectType AuditYear SQL_Server SQL_Database DateEntered
1 CoOp Pricing Pricing 2009 UKHOST04 COOPPRICING2009 23/07/2013 15:28:00
2 CoOp Pricing Pricing 2010 UKHOST04 COOPPRICING2010 23/07/2013 15:28:00
3 CoOp Trade Discount Trade Discount 2010 UKHOST04 COOPTRADEDISCOUNTS 23/07/2013 15:28:00
4 CoOp Scan Scan 2009 UKHOST04 COOPMULTISAVES2009 23/07/2013 15:28:00
5 CoOp Scan Scan 2010 UKHOST04 COOPMULTISAVES2010 23/07/2013 15:28:00


' **************** After update code on dropdown: ******************************
Private Sub cboClaimConcept_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim myProjects As String
Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim str_ProjectsID As Integer
Dim str_ProjectType As String
Dim str_SQLServer As String
Dim str_SQL_Database As String
Dim str_SQL_TableName As String
Dim str_LinkedTableName As String
Dim str_LinkType As String
Dim str_sqODBC As String

Set db = CurrentDb

str_ProjectsID = Me.cboClaimConcept.Column(0)
myProjects = Me.cboClaimConcept.Column(1)

DeleteAttachedNONSYSTables 'Function to remove Linked NON System tables

If Len(RTrim(LTrim(GBL_strDriverVersion))) < 1 Then
Call Init_Globals
End If

strSQL = "Select DataListID,SQL_Server,SQL_Database,SQL_TableName,LinkedTableName,"
strSQL = strSQL & "ProjectsID,Rows,LinkType From DataList "
strSQL2 = "Where ProjectsID = " & str_ProjectsID & " "
strSQL2 = strSQL2 & "ORDER BY SQL_Server,SQL_Database,SQL_TableName "

strSQL3 = "Select ProjectType From Projects Where ProjectsID = " & str_ProjectsID

Set rs = db.OpenRecordset(strSQL & strSQL2, dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset(strSQL3, dbOpenDynaset, dbSeeChanges)
X = rs2.RecordCount

' Get ProjectType From Projects Table
If rs2.EOF = True And rs2.BOF = True Then
GoTo Clean_UP
Else
rs2.MoveLast
rs2.MoveFirst
str_ProjectType = IIf(IsNull(rs2![ProjectType]), "", rs2![ProjectType])
End If

If rs.EOF = True And rs.BOF = True Then
MsgBox ("There are NO Tables setup for linking.")
GoTo Clean_UP
Else
rs.MoveLast
rs.MoveFirst
n = rs.RecordCount
Do Until rs.EOF = True
str_SQLServer = rs![SQL_Server]
str_SQL_Database = rs![SQL_Database]
str_SQL_TableName = rs![SQL_TableName]
str_LinkedTableName = rs![LinkedTableName]
str_LinkType = rs![LinkType]

If str_LinkType = "PassThru" Then

GBL_strConnection = "ODBC;DRIVER={" & GBL_strDriverVersion & "};SERVER=" & str_SQLServer & ";DATABASE=" & str_SQL_Database & ";TRUSTED_CONNECTION=Yes;"

str_sqODBC = str_SQL_TableName ' Table name in SQL DB.

' Select statement to run in SQL server.
strSQL = "Select * From [" & str_SQL_TableName & "]"

If (str_LinkedTableName <> str_SQL_TableName) And (Len(RTrim(str_LinkedTableName) > 1)) Then
str_sqODBC = str_LinkedTableName
End If

'Create a link to the selected table.
Call LinkSelectedTables(str_SQLServer, str_SQL_Database, str_SQL_TableName, str_LinkedTableName, str_sqODBC, strSQL)

End If

If str_LinkType = "LinkedTable" Then

GBL_strConnection = "ODBC;DRIVER={" & GBL_strDriverVersion & "};SERVER=" & GBL_strSQLServer & ";DATABASE=" & GBL_strSQLDatabase & ";TRUSTED_CONNECTION=Yes;"

str_sqODBC = str_SQL_TableName ' Table name in SQL DB.

' Select statement to run in SQL server.
strSQL = "Select * From [" & str_SQL_TableName & "]"

If (str_LinkedTableName <> str_SQL_TableName) And (Len(RTrim(str_LinkedTableName) > 1)) Then
str_sqODBC = str_LinkedTableName
End If

'Create a link to the selected table.
Call AttachDSNLessTable(str_SQL_TableName, str_LinkedTableName, str_SQLServer, str_SQL_Database, "", "")

End If
rs.MoveNext
Loop
'Me.cboClaimConcept = Me.cboClaimConcept.ItemData(str_ProjectsID)
End If


Clean_UP:
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
db.Close
Set db = Nothing
End Sub

 

[dhookom]

Without reading through your code, is your combo box bound to a unique field? You have the bound column as 2. Is this column guaranteed to be unique? Why not bind the combo box to the primary key which I presume is column 1 the ProjectsID field.

Duane
Hook'D on Access
MS Access MVP

 

[abenitez77]

The field it is bound to is not currently unique. ProjectID is unique. I will try your suggestion.