D-look did work before, but it is now "RUN TIME ERROR 2001" suddendly!

[1308057]

I have a Form "lv3_Norminate", there is combobox "txtEventName" which link to a query with a list of event details,
when i change the value of "txtEventName", I want other information of the event, i.e. Date, venue, to be displayed on the form as well.

Previously it worked perfectly. I dont know why, with keeping on establishing the database, it suddendly failed.

the followings are the code of the combobox [txtEventName],
***********************************************
Private Sub txtEventName_AfterUpdate()

Me.txtEventDate = DLookup("[Event Date]", "tbl_event database", "[Event Name]='" & Forms![lv3_Norminate]![txtEventName] & "'")
Me.txtVenue = DLookup("[Venue]", "tbl_event database", "[Event Name]='" & Forms![lv3_Norminate]![txtEventName] & "'")
Me.txtStarting = DLookup("[Starting Time]", "tbl_event database", "[Event Name]='" & Forms![lv3_Norminate]![txtEventName] & "'")
Me.txtEnding = DLookup("E[nding Time]", "tbl_event database", "[Event Name]='" & Forms![lv3_Norminate]![txtEventName] & "'")
Me.txtDescription = DLookup("[Description]", "tbl_event database", "[Event Name]='" & Forms![lv3_Norminate]![txtEventName] & "'")
Me.txtDeadline = DLookup("[Deadline of Normination]", "tbl_event database", "[Event Name]='" & Forms![lv3_Norminate]![txtEventName] & "'")

End Sub
*************************************************

couldnt any one suggest some way to address this problem?? I appreciate so much.

 

[khawar]

try using [tbl_event database] instead of tbl_event database

 

[pbaldy]

And it would be more efficient to include all the fields in the row source of the combo and get the values with the Column property:

txtEventName.Column(x)

where x is the desired column.

 

[DCrake]

Simple Software Solutions

Do you like relay racing? becuse that's what you are doing at the moment. A simpler and far more efficent way of doing what yu want is as follows:

Sub Combo AfterUpdate()


Dim Rs As DAO.RecordSet

Set Rs = CurrentDb.OpenRecordset("Select * From YourTable Where FldId=" & Me.Combo)

If Not Rs.EOF Then
    Me.Txt1 = Rs("fld1")
    Me.Txt2 = Rs("fld2")
    etc
End If

Rs.Close
Set Rs = Nothing

End Sub

Code simplified for brevity.

Summary
What you are doing now is bringing back all the details for the specifed record in one instance and populating all the form controls together. Instead of going to the table everytime you want a different value.


Another solution would be to base the rowsource for your combo box on the table in question and add each of the desired fields in the columns of the combo but set their colwidths to 0

Then on the afterupdate of the combo set the additional fields thus:

Me.Txt1 = Me.Combo.Columns(1)
Me.Txt2 = Me.Combo.Columns(2)


Which ever way you find more comfortable with.

CodeMaster: