Sorting in form

[jyrilepisto]

I have listbox in form that collects data from one table. Table name in tblTuotantoHuolto. Data collection has made in VBA and everytime when form activates (Event Procedure).

Collection has made in sql.
strSQL2 = "SELECT tblTuotantoHuolto.Nro, tblTuotantoHuolto.TuotantoID, tblTuotantoHuolto.Alue, tblTuotantoHuolto.Sijainti, tblTuotantoHuolto.Date, tblTuotantoHuolto.Ready, tblHuolto.Tyyppi " & _

"FROM tblHuolto INNER JOIN tblTuotantoHuolto ON tblHuolto.HuoltoID = tblTuotantoHuolto.HuoltoID " & _

"WHERE tblTuotantoHuolto.Date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _

" ORDER BY tblTuotantoHuolto.Date, tblTuotantoHuolto.TKoodi, tblTuotantoHuolto.Nro;"

How could this be made if i wan´t that this date would be sorted from one other listbox in same form. The listbox name is Selectalue and data that there is tblTuotantoHuolto.Nro.

If you have even understanded a little bit what i mean, it is great.

Thank you for advance, BR Jyri

 

[spikepl]

date would be sorted from one other listbox

Huh? Try to explain again or show an example to illustrate what you mean.

 

[jyrilepisto]

Sorry for all that.

I´ll try again.

I have a form that has calendar. And data is getted in VBA written below.

Private Sub PopulateCalendar()
On Error GoTo Err_PopulateCalendar
Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
Dim strEvent As String
Dim lngSystemDate As Long
Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean
Dim strSQL As String
Dim lngFirstDateInRange As Long
Dim lngLastDateInRange As Long
Dim lngEachDateInRange As Long
Dim strStartTime As String

lngSystemDate = Date
intMonth = objCurrentDate.month
intYear = objCurrentDate.year
lstEvents.Visible = True
lblEventsOnDate.Visible = True
lblMonth.Caption = MonthAndYear(intMonth, intYear)
strFirstOfMonth = "7/" & Str(intMonth) & Str(intYear)

bytFirstWeekdayOfMonth = WeekDay(strFirstOfMonth)
lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
lngLastOfMonth = lngFirstOfNextMonth - 1
lngLastOfPreviousMonth = lngFirstOfMonth - 1
bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)

Set db = CurrentDb

'28.6.2014 TOIMIVA =WORKING
strSQL = "SELECT HuoltoB3.Nro, HuoltoB3.Koodi, HuoltoB3.Date, HuoltoB3.Tunnus " & _
"FROM HuoltoB3 " & _
"WHERE HuoltoB3.Date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
" ORDER BY HuoltoB3.Tunnus, HuoltoB3.Koodi;"


Set rstEvents = db.OpenRecordset(strSQL)
Do While Not rstEvents.EOF
lngFirstDateInRange = rstEvents![Date]
If lngFirstDateInRange < lngFirstOfMonth Then
lngFirstDateInRange = lngFirstOfMonth
End If
lngLastDateInRange = rstEvents![Date]
If lngLastDateInRange > lngLastOfMonth Then
lngLastDateInRange = lngLastOfMonth
End If

For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore


If astrCalendarBlocks(bytBlockCounter) = "" Then
astrCalendarBlocks(bytBlockCounter) = Format$(rstEvents![Tunnus]) & ", " & _
Left$(rstEvents![Koodi], 12) & ""
Else
astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
Format$(rstEvents![Tunnus]) & ", " & _
Left$(rstEvents![Koodi], 12) & ""

End If
Next lngEachDateInRange

rstEvents.MoveNext
Loop

For bytBlockCounter = 1 To 42 'blank blocks at start of month
Select Case bytBlockCounter
Case Is < bytFirstWeekdayOfMonth
astrCalendarBlocks(bytBlockCounter) = ""
ReferenceABlock ctlDayBlock, bytBlockCounter
'ctlDayBlock.BackColor = 12632256
ctlDayBlock.BackColor = 8421440
ctlDayBlock = ""
ctlDayBlock.Enabled = False
ctlDayBlock.Tag = ""
Case Is > bytBlankBlocksBefore + bytDaysInMonth 'blank blocks at end of month
astrCalendarBlocks(bytBlockCounter) = ""
ReferenceABlock ctlDayBlock, bytBlockCounter
'ctlDayBlock.BackColor = 12632256
ctlDayBlock.BackColor = 8421440
ctlDayBlock = ""
ctlDayBlock.Enabled = False
ctlDayBlock.Tag = ""
ctlDayBlock.Visible = Not (bytBlankBlocksAfter > 6 And bytBlockCounter > 35)
Case Else
bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
ReferenceABlock ctlDayBlock, bytBlockCounter
lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
If bytBlockDayOfMonth < 10 Then
ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
vbNewLine & astrCalendarBlocks(bytBlockCounter)
Else
ctlDayBlock = bytBlockDayOfMonth & _
vbNewLine & astrCalendarBlocks(bytBlockCounter)
End If

'If this block is the system date, change its color (CFB 1-25-08)
If lngBlockDate = lngSystemDate Then
ctlDayBlock.BackColor = RGB(0, 0, 255)
ctlDayBlock.ForeColor = QBColor(15)
Set ctlSystemDateBlock = ctlDayBlock
blnSystemDateIsShown = True
Else
ctlDayBlock.BackColor = QBColor(15)
ctlDayBlock.ForeColor = 8388608
End If
ctlDayBlock.Visible = True
ctlDayBlock.Enabled = True
ctlDayBlock.Tag = lngBlockDate
End Select
Next

'If the system date is in this month, show its events (CFB added 1-25-08)
If blnSystemDateIsShown Then
PopulateEventsList ctlSystemDateBlock
End If

Call PopulateYearListBox
Exit_PopulateCalendar:
Exit Sub
Err_PopulateCalendar:
MsgBox Err.Description, vbExclamation, "Error in PopulateCalendar()"
Call LogErrors(Err.Number, Err.Description, "Tuotanto", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
Resume Exit_PopulateCalendar
End Sub



I would like to get data also from another table (like HuoltoB2 and same atributed (HuoltoB2.Nro and so on)

How could this be made?

And after that is working i would need help how to sort this data when i click on textbox that has in informatio *.Nro.

Hopefully this helps you to understand little bit more.