DLookup, unbound controls questions

J

JIM

I have a work order form to add work orders to tlbWorkOrders. On the form is
a combo box, when clicked it updates a listbox then when listbox is clicked
it updates several unbound fields and a foreign key is put in a bound
invisible textbox. The unbound fields are for display only and as user
advances thru file I want the related info update on each record to show-the
unbound fields, that is. It works well except for a few things. If a new
record is added then if user uses record selector to view previous records,
then the DLookup unbound fields do not change and snstead show info from last
listbox click in unbound fields. How do I get them to update? Also if the
listbox is clicked on any old work order to change info I get run time error
-2147352567.... You can't assign a value to this object.
Here's my code:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String 'Fill listbox with Buildings
that apply to Customer when Customer is entered or updated
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.Reg,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2,
tblClientBuildings.JobAddress3, tblClientBuildings.JobAddress4,
tblClientBuildings.BuildingNo, tblClientBuildings.ContactName1,
tblClientBuildings.Phone1, tblClientBuildings.Ext1, tblClientBuildings.Cell1,
tblClientBuildings.Email1, tblClientBuildings.ContactName2,
tblClientBuildings.Phone2, tblClientBuildings.Ext2, tblClientBuildings.Cell2,
tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll FROM tblCustomer INNER JOIN tblClientBuildings ON
tblCustomer.CustomerName = tblClientBuildings.CustomerName WHERE
tblClientBuildings.CustomerName = Forms!frmWorkOrders!cboCustomerName ORDER
BY tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub


Private Sub Form_Current()
If NewRecord Then 'Blank out all
DLookup Control Sources so that list box can assign values
Me.txtRegion.ControlSource = ""
Me.txtJobAddress1.ControlSource = ""
Me.txtJobAddress2.ControlSource = ""
Me.txtJobAddress3.ControlSource = ""
Me.txtJobAddress4.ControlSource = ""
Me.txtContactName1.ControlSource = ""
Me.txtContactName2.ControlSource = ""
Me.txtContactName3.ControlSource = ""
Me.txtPhone1.ControlSource = ""
Me.txtPhone2.ControlSource = ""
Me.txtPhone3.ControlSource = ""
Me.txtExt1.ControlSource = ""
Me.txtExt2.ControlSource = ""
Me.txtExt3.ControlSource = ""
Me.txtCell1.ControlSource = ""
Me.txtCell2.ControlSource = ""
Me.txtCell3.ControlSource = ""
Me.txtEmail1.ControlSource = ""
Me.txtEmail2.ControlSource = ""
Me.txtEmail3.ControlSource = ""
Me.txtSpecInstNotes.ControlSource = ""
Me.txtRoofPlanLoc.ControlSource = ""
Me.txtPrintAll.ControlSource = ""
Else
' Set Rs = db.OpenRecordset("SELECT tblClientBuildings.Reg,
tblClientBuildings.JobAddress1, tblClientBuildings.JobAddress2,
tblClientBuildings.JobAddress3, tblClientBuildings.JobAddress4,
tblClientBuildings.BuildingNo, tblClientBuildings.ContactName1,
tblClientBuildings.Phone1, tblClientBuildings.Ext1, tblClientBuildings.Cell1,
tblClientBuildings.Email1, tblClientBuildings.ContactName2,
tblClientBuildings.Phone2, tblClientBuildings.Ext2, tblClientBuildings.Cell2,
tblClientBuildings.Email2,
tblClientBuildings.ContactName3,tblClientBuildings.Phone3,
tblClientBuildings.Ext3, tblClientBuildings.Cell3, tblClientBuildings.Email3,
tblClientBuildings.SpecInstNotes, tblClientBuildings.RoofPlanLoc,
tblClientBuildings.PrintAll FROM tblCustomer INNER JOIN tblClientBuildings ON
tblCustomer.CustomerName = tblClientBuildings.CustomerName WHERE
tblClientBuildings.CustomerName = Forms!frmWorkOrders!cboCustomerName and
tblClientBuildings.BuildingNo = Forms!frmWorkOrders!txtBuildNo")
'Me.txtRegion.ControlSource = DLookup("Reg", "tblClientBuildings",
"[CustomerName]=""" & [CustomerName] & """ And [BuildingNo]= " & [BuildNo] &
"")
'Me.txtJobAddress1.ControlSource = DLookup("JobAddress1",
"tblClientBuildings", "[CustomerName]=""" & [CustomerName] & """ And
[BuildingNo]= " & [BuildNo] & "")
'Me.txtJobAddress2.ControlSource = DLookup("JobAddress2",
"tblClientBuildings", "[CustomerName]=""" & [CustomerName] & """ And
[BuildingNo]= " & [BuildNo] & "")
'Me.txtJobAddress3.ControlSource = DLookup("JobAddress3",
"tblClientBuildings", "[CustomerName]=""" & [CustomerName] & """ And
[BuildingNo]= " & [BuildNo] & "")
'Me.txtJobAddress4.ControlSource = DLookup("JobAddress4",
"tblClientBuildings", "[CustomerName]=""" & [CustomerName] & """ And
[BuildingNo]= " & [BuildNo] & "")
'Me.txtContactName1.ControlSource = ""
'Me.txtContactName2.ControlSource = ""
'Me.txtContactName3.ControlSource = ""
'Me.txtPhone1.ControlSource = ""
'Me.txtPhone2.ControlSource = ""
'Me.txtPhone3.ControlSource = ""
'Me.txtExt1.ControlSource = ""
'Me.txtExt2.ControlSource = ""
'Me.txtExt3.ControlSource = ""
'Me.txtCell1.ControlSource = ""
'Me.txtCell2.ControlSource = ""
'Me.txtCell3.ControlSource = ""
'Me.txtEmail1.ControlSource = ""
'Me.txtEmail2.ControlSource = ""
'Me.txtEmail3.ControlSource = ""
'Me.txtSpecInstNotes.ControlSource = ""
'Me.txtRoofPlanLoc.ControlSource = ""
'Me.txtPrintAll.ControlSource = ""

End If
Me!lstBuildings.RowSource = "" 'When move
to new record listbox will be cleared

End Sub


Private Sub lstBuildings_Click() 'if listbox is
clicked then move all data to subform
If Not IsNull(Me.lstBuildings) Then
Me.txtBuildNo = Me.lstBuildings.Column(6) 'Put
Building No. in main form
Me.txtRegion = Me.lstBuildings.Column(1) 'Put Region
in subform
Me.txtJobAddress1 = Me.lstBuildings.Column(2) 'Put Job Address1
in subform
If Not IsNull(Me.lstBuildings.Column(3)) Then
'Put rest of data in subform, check for null field first
Me.txtJobAddress2 = Me.lstBuildings.Column(3)
Else
Me.txtJobAddress2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(4)) Then
Me.txtJobAddress3 = Me.lstBuildings.Column(4)
Else
Me.txtJobAddress3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(5)) Then
Me.txtJobAddress4 = Me.lstBuildings.Column(5)
Else
Me.txtJobAddress4 = ""
End If
If Not IsNull(Me.lstBuildings.Column(7)) Then
Me.txtContactName1 = Me.lstBuildings.Column(7)
Else
Me.txtContactName1 = ""
End If
If Not IsNull(Me.lstBuildings.Column(8)) Then
Me.txtPhone1 = Me.lstBuildings.Column(8)
Else
Me.txtPhone1 = ""
End If
If Not IsNull(Me.lstBuildings.Column(9)) Then
Me.txtExt1 = Me.lstBuildings.Column(9)
Else
Me.txtExt1 = ""
End If
If Not IsNull(Me.lstBuildings.Column(10)) Then
Me.txtCell1 = Me.lstBuildings.Column(10)
Else
Me.txtCell1 = ""
End If
If Not IsNull(Me.lstBuildings.Column(11)) Then
Me.txtEmail1 = Me.lstBuildings.Column(11)
Else
Me.txtEmail1 = ""
End If
If Not IsNull(Me.lstBuildings.Column(12)) Then
Me.txtContactName2 = Me.lstBuildings.Column(12)
Else
Me.txtContactName2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(13)) Then
Me.txtPhone2 = Me.lstBuildings.Column(13)
Else
Me.txtPhone2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(14)) Then
Me.txtExt2 = Me.lstBuildings.Column(14)
Else
Me.txtExt2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(15)) Then
Me.txtCell2 = Me.lstBuildings.Column(15)
Else
Me.txtCell2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(16)) Then
Me.txtEmail2 = Me.lstBuildings.Column(16)
Else
Me.txtEmail2 = ""
End If
If Not IsNull(Me.lstBuildings.Column(17)) Then
Me.txtContactName3 = Me.lstBuildings.Column(17)
Else
Me.txtContactName3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(18)) Then
Me.txtPhone3 = Me.lstBuildings.Column(18)
Else
Me.txtPhone3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(19)) Then
Me.txtExt3 = Me.lstBuildings.Column(19)
Else
Me.txtExt3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(20)) Then
Me.txtCell3 = Me.lstBuildings.Column(20)
Else
Me.txtCell3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(21)) Then
Me.txtEmail3 = Me.lstBuildings.Column(21)
Else
Me.txtEmail3 = ""
End If
If Not IsNull(Me.lstBuildings.Column(22)) Then
Me.txtSpecInstNotes = Me.lstBuildings.Column(22)
Else
Me.txtSpecInstNotes = ""
End If
If Not IsNull(Me.lstBuildings.Column(23)) Then
Me.txtRoofPlanLoc = Me.lstBuildings.Column(23)
Else
Me.txtRoofPlanLoc = ""
End If
If Not IsNull(Me.lstBuildings.Column(24)) Then
Me.txtPrintAll = Me.lstBuildings.Column(24)
Else
Me.txtPrintAll = ""
End If

End If
End Sub
I've tried doing this with a subform but have had most success with unbound
controls. Any help appreciated.
TIA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Losing value of variable 2
error message-What to do? 1
Unbound Combo Boxes go Null for no reason 0
DLookup and Duplicate Values 4
Duplicates phone numbers and extensions 6
Help with VBA with an unbound form 4
DLookup causes MSACCESS.EXE process to not close 2
DLookup Help 4

Top