Sort order issue with Combo box

[elcunliffe]

Hi
I've inherited a database and I'm learning (very slowly) access so I can edit it.
The form header contains a number of combo boxes that display columns from Table queries. They're linked via macros so that your choice in the first one updates the next, so I can't just delete them and use the original table instead of the table query as I've no idea how to recreate the macro/ code to relink them.

The first box is a drop down list of 22 rows, (set to sort alphabetic ascending which is fine, but it always defaults to displaying row 10 as the default, not row 1.
All the others default to row 1 as you would expect.


It doesn't matter, but it bugs me as I can't figure out why?
Any advice?

Cheers

[andy49]

Look at the row source in properties of combobox. May give you a clue as to why


Sent from my iPhone using Tapatalk

[elcunliffe]

It tells me it's linking to the (correct) database query and when I check it, it's set to sort alphabetically. Which it is: it just displays in the middle of the list automatically.

[andy49]

I wonder if the rowsource changes when it's updated? Is there an onupdate or an onchange event in the properties of the combobox.




Sent from my iPhone using Tapatalk

[elcunliffe]

On Change is blank
After Update says [Event procedure]?

[andy49]

Click on that and see what it says


Sent from my iPhone using Tapatalk

[elcunliffe]

Option Compare Database
Public Current_Filter
Private Sub Form_Load()
DoCmd.SelectObject acForm, "F_Database"
DoCmd.Maximize
Forms!F_Database!TabOptions.Pages("*major_ID*").Vi sible = False
Edit_Mode_Button.Caption = "Click to Start EDIT MODE"
Select_Period.RowSource = ""
Select_Dataset.Value = "SHR"
Call Select_Dataset_AfterUpdate
Call PopulateDateListBox
ApplyFilter ("All")
End Sub


Sub GoRequery()
Date_Graph_Large.Requery
Date_Graph_Small.Requery
List_Place_Name.Requery
List_Place_Name.Value = List_Place_Name.ItemData(0)
End Sub


Sub Reset_All()
MsgBox ("Reset ALL")
Select_Site.RowSource = Select_Site.RowSource
Select_Site.Value = Select_Site.ItemData(0)


Select_Major_ID.RowSource = Select_Major_ID.RowSource
Select_Major_ID.Value = Select_Major_ID.ItemData(0)


Select_Minor_ID.RowSource = Select_Minor_ID.RowSource
Select_Minor_ID.Value = Select_Minor_ID.ItemData(0)




SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource
Select_Category.RowSource = Select_Category.RowSource
Select_Data_Type.RowSource = Select_Data_Type.RowSource
Label_Major_ID.Caption = Select_Minor_ID.Value




End Sub








Private Sub Box_Significance_Limit_AfterUpdate()
Call PopulateDateListBox
End Sub






Private Sub DropDown_Category_Change()
Selected_Category = DropDown_Category.Value
DropDown_Data_Type.Requery
DropDown_Data_Type.Value = DropDown_Data_Type.ItemData(0)
Selected_Data_Type.Value = ""
End Sub


Private Sub DropDown_Data_Type_Change()
Selected_Data_Type = DropDown_Data_Type.Value
End Sub


Private Sub DropDown_Period_Change()
Selected_Period = DropDown_Period.Value
End Sub


Private Sub DropDown_Observations_Change()
TheCriteria = "Observation_Key =" & DropDown_Observations
Me.SF_Observations.Form.Recordset.MoveFirst
Me.SF_Observations.Form.Recordset.FindFirst TheCriteria
Selected_Category = Me.SF_Observations.Form.Category
Selected_Data_Type = Me.SF_Observations.Form.Data_Type
Selected_Period = Me.SF_Observations.Form.Period_LINK
ResetDropDowns ("All")
End Sub




Private Sub Date_Graph_Small_Click()
Forms!F_Database!TabOptions.Pages("Date Graph").SetFocus
End Sub


Private Sub Edit_Mode_Button_Click()


If TabOptions.Pages("*Major_ID*").Visible = True Then


TabOptions.Pages("Overview").SetFocus
TabOptions.Pages("*Major_ID*").Visible = False
Edit_Mode_Button.Caption = "Click to Start EDIT MODE"


Else


Edit_Mode_Button.Caption = "Click to Exit EDIT MODE"
TabOptions.Pages("*Major_ID*").Visible = True
TabOptions.Pages("*Major_ID*").SetFocus


F_Edit.Form.RecordSource = "Q_SF_Major_IDs_All"
F_Edit.Form.Modify_Button.BackColor = RGB(50, 50, 50)
F_Edit.Form.Add_Button.BackColor = RGB(100, 100, 250)
F_Edit.Form.Modify_Select_Label.Visible = False
F_Edit.Form.Select_To_Edit.Visible = False




End If


End Sub


Private Sub Filter_Comments_Click()
SF_Observations.Form.RecordSource = "Q_SF_Observations_Comments"
ApplyFilter ("Comments")
End Sub


Private Sub Filter_Files_Click()
SF_Observations.Form.RecordSource = "Q_SF_Observations_Files"
ApplyFilter ("Files")
End Sub


Private Sub Form_AfterUpdate()
TheSmallImage.Requery
TheFullscreenImage.Requery


End Sub








Sub ResetDropDowns(WhichOne)
If Len(WhichOne) = 0 Then WhichOne = "All"


If WhichOne = "Category" Or WhichOne = "All" Then
Select_Category.Value = Selected_Category
For I = 0 To DropDown_Category.ListCount
If DropDown_Category.ItemData(I) = Selected_Category Then
DropDown_Category.Value = DropDown_Category.ItemData(I)
DropDown_Data_Type.Requery
DropDown_Data_Type.Value = DropDown_Data_Type.ItemData(0)
Selected_Data_Type.Value = ""
Exit For
End If
Next
End If


If WhichOne = "Data_Type" Or WhichOne = "All" Then
Select_Data_Type.Value = Selected_Data_Type
For I = 0 To DropDown_Data_Type.ListCount
If DropDown_Data_Type.ItemData(I) = Selected_Data_Type Then
DropDown_Data_Type.Value = DropDown_Data_Type.ItemData(I)
Exit For
End If
Next
End If


If WhichOne = "Period" Or WhichOne = "All" Then
Selected_Data_Type.Value = Selected_Data_Type
For I = 0 To DropDown_Period.ListCount
If DropDown_Period.ItemData(I) = Selected_Period Then
DropDown_Period.Value = DropDown_Period.ItemData(I)
Exit For
End If
Next
End If


End Sub


Private Sub NextObs_Click()
'MsgBox (DropDown_Observations.ListIndex)
DropDown_Observations.ForeColor = RGB(0, 0, 0)
LastObs.Visible = True


TheValue = DropDown_Observations.ListIndex
TheValue = TheValue + 1

If TheValue < (DropDown_Observations.ListCount - 1) Then
DropDown_Observations.Value = DropDown_Observations.ItemData(TheValue)
Call DropDown_Observations_Change
Else
DropDown_Observations.Value = DropDown_Observations.ItemData(TheValue)
Call DropDown_Observations_Change
DropDown_Observations.ForeColor = RGB(255, 0, 0)
LastObs.SetFocus
NextObs.Visible = False
End If


End Sub
Private Sub LastObs_Click()
'MsgBox (DropDown_Observations.ListIndex)


DropDown_Observations.ForeColor = RGB(0, 0, 0)
NextObs.Visible = True


TheValue = DropDown_Observations.ListIndex
TheValue = TheValue - 1

If TheValue > -1 Then
DropDown_Observations.Value = DropDown_Observations.ItemData(TheValue)
Call DropDown_Observations_Change
Else
DropDown_Observations.ForeColor = RGB(255, 0, 0)
NextObs.SetFocus
LastObs.Visible = False
End If


End Sub


Private Sub Select_Dataset_AfterUpdate()
Select_Site.RowSource = Select_Site.RowSource
Select_Site.Value = Select_Site.ItemData(0)


Select_Major_ID.RowSource = Select_Major_ID.RowSource
Select_Major_ID.Value = Select_Major_ID.ItemData(0)


Select_Minor_ID.RowSource = Select_Minor_ID.RowSource
Select_Minor_ID.Value = Select_Minor_ID.ItemData(0)


Select_Category.RowSource = Select_Category.RowSource
Select_Category.Value = Select_Category.ItemData(0)


Select_Data_Type.RowSource = Select_Data_Type.RowSource
Select_Category.Value = Select_Data_Type.ItemData(0)




If IsNull(Select_Minor_ID.Value) Then
MsgBox ("Check your data tables - there is some error in T_Major_ID/T_Connections for: " & Select_Major_ID.Value)
Else
Label_Major_ID.Caption = Select_Minor_ID.Value
End If
Select_Observation_Period.RowSource = Select_Observation_Period.RowSource


SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource
'SF_Observations_Small.Form.RecordSource = SF_Observations.Form.RecordSource
DropDown_Observations.RowSource = DropDown_Observations.RowSource
DropDown_Observations.Value = DropDown_Observations.ItemData(0)


Call PopulateDateListBox
Call GoRequery
End Sub












Private Sub Select_Site_AfterUpdate()


Select_Major_ID.RowSource = Select_Major_ID.RowSource
Select_Major_ID.Value = Select_Major_ID.ItemData(0)


Select_Minor_ID.RowSource = Select_Minor_ID.RowSource
Select_Minor_ID.Value = Select_Minor_ID.ItemData(0)


SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource
'SF_Observations_Small.Form.RecordSource = SF_Observations.Form.RecordSource
DropDown_Observations.RowSource = DropDown_Observations.RowSource
DropDown_Observations.Value = DropDown_Observations.ItemData(0)


Select_Category.RowSource = Select_Category.RowSource
Select_Data_Type.RowSource = Select_Data_Type.RowSource
Label_Major_ID.Caption = Select_Minor_ID.Value
Select_Observation_Period.RowSource = Select_Observation_Period.RowSource




Call PopulateDateListBox
Call GoRequery


End Sub


Private Sub Select_Major_ID_AfterUpdate()


Select_Minor_ID.RowSource = Select_Minor_ID.RowSource
Select_Minor_ID.Value = Select_Minor_ID.ItemData(0)


SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource
'SF_Observations_Small.Form.RecordSource = SF_Observations.Form.RecordSource
DropDown_Observations.RowSource = DropDown_Observations.RowSource
DropDown_Observations.Value = DropDown_Observations.ItemData(0)




Select_Category.RowSource = Select_Category.RowSource
Select_Data_Type.RowSource = Select_Data_Type.RowSource


If IsNull(Select_Minor_ID.Value) Then
MsgBox ("Check your data tables - there is some error in T_Major_ID/T_Connections for: " & Select_Major_ID.Value)
Else
Label_Major_ID.Caption = Select_Minor_ID.Value
End If
Select_Observation_Period.RowSource = Select_Observation_Period.RowSource
Call PopulateDateListBox
Call GoRequery


End Sub


Private Sub Select_Minor_ID_AfterUpdate()
SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource
'SF_Observations_Small.Form.RecordSource = SF_Observations.Form.RecordSource
DropDown_Observations.RowSource = DropDown_Observations.RowSource
DropDown_Observations.Value = DropDown_Observations.ItemData(0)


Select_Category.RowSource = Select_Category.RowSource
Select_Data_Type.RowSource = Select_Data_Type.RowSource
Label_Major_ID.Caption = Select_Minor_ID.Value
Select_Observation_Period.RowSource = Select_Observation_Period.RowSource
Call PopulateDateListBox
Call GoRequery
End Sub




'---------------------------------------------------------------------------------------------------
' OVERVIEW SCREEN - Changing Selection Boxes -------------------------------------------------------
'---------------------------------------------------------------------------------------------------


Private Sub Select_Category_AfterUpdate()


Selected_Category = Select_Category.Value
Selected_Data_Type = ""
Select_Data_Type.RowSource = Select_Data_Type.RowSource
''SF_Observations.Form.RecordSource = "Q_SF_Observations_Category"
End Sub


Private Sub Select_Data_Type_AfterUpdate()
Selected_Data_Type = Select_Data_Type.Value
'SF_Observations.Form.RecordSource = "Q_SF_Observations_Data_Type"
End Sub


Private Sub Select_Observation_Period_Click()
Selected_Period = Select_Observation_Period.Value
End Sub


'---------------------------------------------------------------------------------------------------
' OVERVIEW SCREEN - Double Click Selection Boxes -------------------------------------------------------
'---------------------------------------------------------------------------------------------------


Private Sub Select_Observation_Period_DblClick(Cancel As Integer)
Call Filter_Period_Click
End Sub


Private Sub Select_Category_DblClick(Cancel As Integer)
Call Filter_Category_Click
End Sub


Private Sub Select_Data_type_DblClick(Cancel As Integer)
Call Filter_Data_Type_Click
End Sub




'------------------------------------
' Button Clicks
'------------------------------------
Private Sub Filter_All_Click()
ApplyFilter ("All")
End Sub


Private Sub Filter_Category_Click()
If Len(Selected_Category) > 0 Then
ApplyFilter ("Category")
Else
Filter_Category.Caption = "Select a Category!"
Filter_Category.BackColor = RGB(255, 0, 0)
End If
End Sub




Private Sub Filter_Period_Click()


If Len(Selected_Period) > 0 Then
ApplyFilter ("Period")
Else
Filter_Period.Caption = "Select a Period!"
Filter_Period.BackColor = RGB(255, 0, 0)
End If


End Sub




Private Sub Filter_Data_Type_Click()
If Len(Selected_Data_Type) > 0 Then
ApplyFilter ("Data_Type")
Else
Filter_Data_Type.Caption = "Select a Data Type!"
Filter_Data_Type.BackColor = RGB(255, 0, 0)
End If


End Sub
Sub PopulateDateListBox()
'POPULATE RECORDSET----------------------------------------------------------------------------------------
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

TheLimitValue = 0
TheLimitValue = Box_Significance_Limit.Value
If IsNull(TheLimitValue) Then
TheLimitValue = 0.5
Else
TheLimitValue = TheLimitValue * 1
End If
TheSite = Forms![F_Database]!Select_Minor_ID.Value

TheQuery = "Select * FROM [Q_Dates_pt1] WHERE Major_ID = '" & TheSite & "'"
'Thequery = "'Select * FROM [_Q_Dates];'"
'Comments_Label.Value = CurrentProject.Connection

rs.Open TheQuery, CurrentProject.Connection

ReDim DatePeriods(2, 1)

DateCount = 0

While Not rs.EOF
'MsgBox (rs.Fields.Count)
For I = 0 To rs.Fields.Count - 1
FieldName = rs.Fields(I).Name
FieldValue = rs.Fields(I).Value
If FieldName <> "Major_ID" And FieldValue >= TheLimitValue Then
DateCount = DateCount + 1
ReDim Preserve DatePeriods(2, DateCount)
DatePeriods(1, DateCount) = Mid(FieldName, 6, 10000)
DatePeriods(2, DateCount) = FieldValue
End If
Next

rs.MoveNext
Wend
rs.Close
Set rs = Nothing

'MsgBox (DateCount)

Forms![F_Database]!Select_Period.RowSource = ""


For I = 1 To DateCount

ToAdd = DatePeriods(1, I) & "," & DatePeriods(2, I)
Forms![F_Database]!Select_Period.AddItem ToAdd

Next I
'POPULATE RECORDSET----------------------------------------------------------------------------------------




End Sub


Private Sub TabOptions_Change()
'MsgBox ("Change is in the air")
SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource
Call ResetDropDowns("All")


End Sub


Private Sub TabOptions_Click()
ErrorMessage.BackColor = RGB(255, 255, 255)
End Sub


Private Sub TheSmallImage_Click()
'MsgBox (TheSmallImage.Picture)
TheFullscreenImage.Requery


Forms!F_Database!TabOptions.Pages("Image").SetFocu s
End Sub


Sub ApplyFilter(TheButton)
'MsgBox (TheButton)
SelectedColor = RGB(50, 100, 150)
NormalColor = RGB(150, 150, 150)


Filter_All.BackColor = NormalColor
Filter_Category.BackColor = NormalColor
Filter_Data_Type.BackColor = NormalColor
Filter_Period.BackColor = NormalColor
Filter_Files.BackColor = NormalColor
Filter_Comments.BackColor = NormalColor


Filter_All.Caption = "Show ALL Records"
Filter_Category.Caption = "Filter by Category"
Filter_Data_Type.Caption = "Filter by Data Type"
Filter_Period.Caption = "Filter by period"
Filter_Files.Caption = "Associated Files"
Filter_Comments.Caption = "Comments"


Select Case UCase(TheButton)


Case "ALL"
Filter_All.BackColor = SelectedColor


Case "CATEGORY"
Filter_Category.BackColor = SelectedColor


Case "DATA_TYPE"
Filter_Data_Type.BackColor = SelectedColor


Case "PERIOD"
Filter_Period.BackColor = SelectedColor


Case "FILES"
Filter_Files.BackColor = SelectedColor


Case "COMMENTS"
Filter_Comments.BackColor = SelectedColor
End Select


SF_Observations.Form.RecordSource = "Q_SF_Observations_" & TheButton
SF_Observations.Form.RecordSource = SF_Observations.Form.RecordSource


TheFilterQuery = "Q_SF_Observations_" & TheButton


SelectWhat = "SELECT " & TheFilterQuery & ".[Observation_KEY], " & TheFilterQuery & ".[T_Observations].[Major_ID], " & TheFilterQuery & ".[Category], " & TheFilterQuery & ".[Data_Type] FROM " & TheFilterQuery & " ORDER BY [Category] , [Data_Type];"
DropDown_Observations.RowSource = SelectWhat
DropDown_Observations.Value = DropDown_Observations.ItemData(0)


End Sub

[andy49]

What is the name of the combobox you're dealing with.


Sent from my iPhone using Tapatalk

[elcunliffe]

Select Dataset
row source = Q_Select_Dataset
(The query table contains 3 columns: Dataset_Code (which is the one that displays), and then also Datase_who and Dataset_where

[Bulzie]

In the properties of the combobox, on the data tab, in the Default box, is there a value? If not put the value you wish it to be.

Also sometimes you want the list to sort other then alphabetic. You can add a 2nd field to the table that as the values and call it SortOrder. In this column, number the values as you want them to sort. Then use that SortOrder field in your combobox query to sort as needed.

[elcunliffe]

It was empty so I added in the value of row 1 ("BON") so that it would start there. Which it did, so adding a default value fixed the problem, but now means the linked queries that update
from Box 1 no longer work as they are still defaulting to the old value.

And then when I closed the form and re-opened it, although it now says "BON" in the default value, the form still re-opened displaying Row 10. And it's all correctly sorted, it just...???
I don't think this is going to be possible. Never mind. I really appreciate you trying to help, though.

[Bulzie]

Could there be any code in an event of the form that assigns that value to that combobox? Another idea would be to create a new combobox, set it to the query and see if it fixes the issue. Sometimes these issues occur with no logic and lots of times deleting the bad object and creating a new one does the trick.

[elcunliffe]

That's probably it.
I tried to make a new box, but I have zero SQL knowledge, and I failed to relink the boxes correctly, and just got yet more error messages so I think it's one to leave.
Reverse engineering is not my forte.