Save contents of Listbox to Field (1 Viewer)

[deagio]

Hi,

I've made a form with conditional combo boxes, based on the selection in the second combo box an address appears in 4 list boxes, but the problem is, they arnt saved to the record type on exiting the form.

Any ideas how to make the contents of the list box save to a field on exit?

Here is the code on the form if this helps! Its done in SQL..

Option Compare Database
Dim Nme1
Private Sub Combo0_AfterUpdate()
Dim Nme
Nme = Combo0.Text
Nme1 = Nme
Combo6.Value = ""

Dim Ctl1 As Control
Set Ctl1 = Me.Combo6
'Ctl1.Clear

Ctl1.RowSourceType = "Table/View/StoredProc"
Ctl1.RowSource = "Select Contract_Address1 FROM Contracts WHERE Customer_Name = '" & Nme & "'"

Dim Ctl2 As Control
Set Ctl2 = Me.List13
Ctl2.RowSourceType = "Table/View/StoredProc"
Ctl2.RowSource = "Select Address1, Address2, Town, County, Postcode FROM Customers WHERE Customer_Name = '" & Nme & "'"

Dim Ctl3 As Control
Set Ctl3 = Me.List15
Ctl3.RowSourceType = "Table/View/StoredProc"
Ctl3.RowSource = "Select Address2, Town, County, Postcode FROM Customers WHERE Customer_Name = '" & Nme & "'"

Dim Ctl4 As Control
Set Ctl4 = Me.List17
Ctl4.RowSourceType = "Table/View/StoredProc"
Ctl4.RowSource = "Select Town, County, Postcode FROM Customers WHERE Customer_Name = '" & Nme & "'"

Dim ctl5 As Control
Set ctl5 = Me.List19
ctl5.RowSourceType = "Table/View/StoredProc"
ctl5.RowSource = "Select County, Postcode FROM Customers WHERE Customer_Name = '" & Nme & "'"

Dim ctl6 As Control
Set ctl6 = Me.List21
ctl6.RowSourceType = "Table/View/StoredProc"
ctl6.RowSource = "Select Postcode FROM Customers WHERE Customer_Name = '" & Nme & "'"

End Sub


Private Sub Combo6_AfterUpdate()
Dim Nme
Nme = Combo6.Text

Dim Ctl1 As Control
Set Ctl1 = Me.List23
Ctl1.RowSourceType = "Table/View/StoredProc"
Ctl1.RowSource = "Select Contract_Address2 FROM Contracts WHERE Contract_Address1 = '" & Nme & "' AND Customer_Name = '" & Nme1 & "'"

Dim Ctl2 As Control
Set Ctl2 = Me.List25
Ctl2.RowSourceType = "Table/View/StoredProc"
Ctl2.RowSource = "Select Town FROM Contracts WHERE Contract_Address1 = '" & Nme & "' AND Customer_Name = '" & Nme1 & "'"

Dim Ctl3 As Control
Set Ctl3 = Me.List26
Ctl3.RowSourceType = "Table/View/StoredProc"
Ctl3.RowSource = "Select County FROM Contracts WHERE Contract_Address1 = '" & Nme & "' AND Customer_Name = '" & Nme1 & "'"

Dim Ctl4 As Control
Set Ctl4 = Me.List27
Ctl4.RowSourceType = "Table/View/StoredProc"
Ctl4.RowSource = "Select Postcode FROM Contracts WHERE Contract_Address1 = '" & Nme & "' AND Customer_Name = '" & Nme1 & "'"



End Sub


Private Sub Command39_Click()
On Error GoTo Err_Command39_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command39_Click:
Exit Sub

Err_Command39_Click:
MsgBox Err.Description
Resume Exit_Command39_Click

End Sub
Private Sub Command40_Click()
On Error GoTo Err_Command40_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click

End Sub
Private Sub Combo43_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[Contract_Town] = '" & Me![Combo43] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Thanks,



James

 

[llkhoutx]

I think the better method is to selectively create the rowsource on as as need basis.