List box problem

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

I am trying to sort out a problem with a list box. The
following code works fine but will cause problems if I
don't sort it out. When items in the list box are selected
and saved it saves to a table 'software' as text,
'SoftwareIDNo' as the numeric ID and a form ID from the
main form which I also need to hold. The problem is that
this code, when I reopen the list box and the previous
items are shown as selected, does a check with the table
and highlights the items it should but does the check on
the 'software' and not on the 'softwareIDNo'. What I really
need it to do is look at the ID No and if there is a match
highlight the text. I don't now how to make it look at the
ID and highlight the text which is a different column
basically. I know there is quite a lot of code here but not
much of it is applicable to what needs to be changed.
Problems will occur if the 'software' name is changed in
the list for some reason, hence I need to use the 'IDNo'
instead and can then not saave the 'software' in the table
at all. Hope this makes sense, please post back if it
doesn't and I'll try to explain another way.

Any help greatly appreciated.

The rowsource for the list box to select form is
SELECT [tblSoftware].[Software],
[tblSoftware].[SoftwareIDS] FROM tblSoftware ORDER BY
[tblSoftware].[Software];


Private Sub Form_Load()
Dim dyntblTempSoftware As Recordset
Dim intCurrTable As Integer
' Open the table containing previously selected choices
Set dyntblTempSoftware =
CurrentDb.OpenRecordset("tblTempSoftware", dbOpenDynaset)
' Loop through the selected choices
Do Until dyntblTempSoftware.EOF
' For each of the items in the listbox, see if it matches
the
' current table name.
For intCurrTable = 0 To Me!lboSoftwareToSelect.ListCount -
1
' If there is a match, mark it in the list box as selected
If (dyntblTempSoftware!Software =
Me!lboSoftwareToSelect.ItemData(intCurrTable)) And
(dyntblTempSoftware!TempID =
[Forms]![frmTemp]![txtTempIDS]) Then
Me!lboSoftwareToSelect.Selected(intCurrTable) = True
Exit For
End If
Next intCurrTable
dyntblTempSoftware.MoveNext
Loop

'********show software selected*******************
Function ShowSoftwareSelected() As String
Dim varTable As Variant
Dim strTemp As String
' for each of the items in the ItemsSelected collection
For Each varTable In Me!lboSoftwareToSelect.ItemsSelected()
' If not the first item, put a carriage return line feed
' in front of the item
If Len(strTemp) <> 0 Then
strTemp = strTemp & vbCrLf
End If
' Grab the current item
strTemp = strTemp &
Me!lboSoftwareToSelect.ItemData(varTable)
Next
' Assign the final string pass it back
ShowSoftwareSelected = strTemp
End Function

**************Save software chosen ******************
Private Sub cmdSavesoftwareChosen_Click()
Dim dyntblTempSoftware As Recordset
Dim intCurrTable As Integer
Dim varTable As Variant
CurrentDb
' Delete the previous choices from the table
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * FROM tblTempSoftware WHERE
tblTempSoftware.TempID=[Forms]![frmTemp]![txtTempIDS];"
DoCmd.SetWarnings False
'Open the TablesChosen table
Set dyntblTempSoftware =
CurrentDb.OpenRecordset("tblTempSoftware", dbOpenDynaset)
'For each of the items in the ItemsSelected collection
'Add a new record in the TablesChosen table.
For Each varTable In Me!lboSoftwareToSelect.ItemsSelected()
dyntblTempSoftware.AddNew
dyntblTempSoftware!Software =
Me!lboSoftwareToSelect.ItemData(varTable)
dyntblTempSoftware!TempID = [Forms]![frmTemp]![txtTempIDS]
dyntblTempSoftware!SoftwareIDNo =
Me!lboSoftwareToSelect.Column(1, varTable)
dyntblTempSoftware.Update
dyntblTempSoftware.Requery
Next varTable
 
Have worked it out... mostly, but have one more problem I
can's solve. Everything is saving to the table ok but i
can't get it to highlight the selected tems when i re-open
the form. In the following code i believe these two lines
are the problem.

If (dyntblTempSoftware!SoftwareIdNo =
Me!lboSoftwareToSelect.ItemData(intCurrTable)) And
(dyntblTempSoftware!TempID =
[Forms]![frmTemp]![txtTempIDS]) Then
Me!lboSoftwareToSelect.Selected(intCurrTable) = True

I added a msgbox to follow what numbers were going through
but even when the dyntblTempSoftware!TempID =
[Forms]![frmTemp]![txtTempIDS] and
dyntblTempSoftware!SoftwareIdNo=Me!lboSoftwareToSelect.Item
Data(intCurrTable) it still does not select the item. If i
put the msgbox above the 'if' statement and watch the
numbers as it loops through they look like they match at
different points and so should select, but if I put the msg
box after the 'then' statement I see nothing, as if it
doesn't ever match. I can't work it out!

Private Sub Form_Load()
Dim dyntblTempSoftware As Recordset
Dim intCurrTable As Integer
' Open the table containing previously selected choices
Set dyntblTempSoftware =
CurrentDb.OpenRecordset("tblTempSoftware", dbOpenDynaset)
' Loop through the selected choices
Do Until dyntblTempSoftware.EOF
' For each of the items in the listbox, see if it matches
the
' current table name.
For intCurrTable = 0 To Me!lboSoftwareToSelect.ListCount -
1
' If there is a match, mark it in the list box as selected


If (dyntblTempSoftware!SoftwareIdNo =
Me!lboSoftwareToSelect.ItemData(intCurrTable)) And
(dyntblTempSoftware!TempID =
[Forms]![frmTemp]![txtTempIDS]) Then
Me!lboSoftwareToSelect.Selected(intCurrTable) = True
Exit For
End If
Next intCurrTable
dyntblTempSoftware.MoveNext
Loop
End Sub
-----Original Message-----
I am trying to sort out a problem with a list box. The
following code works fine but will cause problems if I
don't sort it out. When items in the list box are selected
and saved it saves to a table 'software' as text,
'SoftwareIDNo' as the numeric ID and a form ID from the
main form which I also need to hold. The problem is that
this code, when I reopen the list box and the previous
items are shown as selected, does a check with the table
and highlights the items it should but does the check on
the 'software' and not on the 'softwareIDNo'. What I really
need it to do is look at the ID No and if there is a match
highlight the text. I don't now how to make it look at the
ID and highlight the text which is a different column
basically. I know there is quite a lot of code here but not
much of it is applicable to what needs to be changed.
Problems will occur if the 'software' name is changed in
the list for some reason, hence I need to use the 'IDNo'
instead and can then not saave the 'software' in the table
at all. Hope this makes sense, please post back if it
doesn't and I'll try to explain another way.

Any help greatly appreciated.

The rowsource for the list box to select form is
SELECT [tblSoftware].[Software],
[tblSoftware].[SoftwareIDS] FROM tblSoftware ORDER BY
[tblSoftware].[Software];


Private Sub Form_Load()
Dim dyntblTempSoftware As Recordset
Dim intCurrTable As Integer
' Open the table containing previously selected choices
Set dyntblTempSoftware =
CurrentDb.OpenRecordset("tblTempSoftware", dbOpenDynaset)
' Loop through the selected choices
Do Until dyntblTempSoftware.EOF
' For each of the items in the listbox, see if it matches
the
' current table name.
For intCurrTable = 0 To Me!lboSoftwareToSelect.ListCount -
1
' If there is a match, mark it in the list box as selected
If (dyntblTempSoftware!Software =
Me!lboSoftwareToSelect.ItemData(intCurrTable)) And
(dyntblTempSoftware!TempID =
[Forms]![frmTemp]![txtTempIDS]) Then
Me!lboSoftwareToSelect.Selected(intCurrTable) = True
Exit For
End If
Next intCurrTable
dyntblTempSoftware.MoveNext
Loop

'********show software selected*******************
Function ShowSoftwareSelected() As String
Dim varTable As Variant
Dim strTemp As String
' for each of the items in the ItemsSelected collection
For Each varTable In Me!lboSoftwareToSelect.ItemsSelected()
' If not the first item, put a carriage return line feed
' in front of the item
If Len(strTemp) <> 0 Then
strTemp = strTemp & vbCrLf
End If
' Grab the current item
strTemp = strTemp &
Me!lboSoftwareToSelect.ItemData(varTable)
Next
' Assign the final string pass it back
ShowSoftwareSelected = strTemp
End Function

**************Save software chosen ******************
Private Sub cmdSavesoftwareChosen_Click()
Dim dyntblTempSoftware As Recordset
Dim intCurrTable As Integer
Dim varTable As Variant
CurrentDb
' Delete the previous choices from the table
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * FROM tblTempSoftware WHERE
tblTempSoftware.TempID=[Forms]![frmTemp]![txtTempIDS];"
DoCmd.SetWarnings False
'Open the TablesChosen table
Set dyntblTempSoftware =
CurrentDb.OpenRecordset("tblTempSoftware", dbOpenDynaset)
'For each of the items in the ItemsSelected collection
'Add a new record in the TablesChosen table.
For Each varTable In Me!lboSoftwareToSelect.ItemsSelected()
dyntblTempSoftware.AddNew
dyntblTempSoftware!Software =
Me!lboSoftwareToSelect.ItemData(varTable)
dyntblTempSoftware!TempID = [Forms]![frmTemp]![txtTempIDS]
dyntblTempSoftware!SoftwareIDNo =
Me!lboSoftwareToSelect.Column(1, varTable)
dyntblTempSoftware.Update
dyntblTempSoftware.Requery
Next varTable


.
Click to expand...
 
Back
Top