run-time error '2001' 1

[MICKI0220]

I keep getting the error "run-time error '2001'", saying I cancelled the previous operation....I am using dlookup instead of trying to create more subforms in my form....I have a query that utilizes the 'job_Id' in my open form. This query houses the fields I want to uses in these 25 unbound text boxes....here is the attached code...it gives me the error message when it attempts the second dlookup operation.
Private Sub Combo44_Change()

vara = DLookup("[lastofbt_10]", "qrylast")
varb = DLookup("[lastofbt_40]", "qrylast")
varc = DLookup("[lastofCEC]", "qrylast")
vard = DLookup("[lastofCF]", "qrylast")
vare = DLookup("[lastofCP]", "qrylast")
varf = DLookup("[lastofFC_60]", "qrylast")
varg = DLookup("[lastofFP]", "qrylast")
varh = DLookup("[lastofSD_05]", "qrylast")
vari = DLookup("[lastofSD_10]", "qrylast")
varj = DLookup("[lastofSD_15]", "qrylast")
vark = DLookup("[lastofSD_18]", "qrylast")
varl = DLookup("[lastofSD_20]", "qrylast")
varm = DLookup("[lastofSD_23]", "qrylast")
varn = DLookup("[lastofSD_25]", "qrylast")
varo = DLookup("[lastofSC_26]", "qrylast")
varp = DLookup("[lastofSD_30]", "qrylast")
varq = DLookup("[lastofSD_35]", "qrylast")
varr = DLookup("[lastofSD_40]", "qrylast")
vars = DLookup("[lastofSD_45]", "qrylast")
vart = DLookup("[lastofSD_50]", "qrylast")
varu = DLookup("[lastofSD_55]", "qrylast")
varv = DLookup("[lastofSD_58]", "qrylast")
varw = DLookup("[lastofSD_60]", "qrylast")
varx = DLookup("[lastofSD_85]", "qrylast")
vary = DLookup("[lastofSD_90]", "qrylast")
Text58.SetFocus
Text58.Text = vara
Text60.SetFocus
Text60.Text = varb
Text60.SetFocus
Text61.Text = varc
Text62.SetFocus
Text62.Text = vard
Text63.SetFocus
Text63.Text = vare
Text64.SetFocus
Text64.Text = varf
Text65.SetFocus
Text65.Text = varg
Text66.SetFocus
Text66.Text = varh
Text67.SetFocus
Text67.Text = vari
Text68.SetFocus
Text68.Text = varj
Text69.SetFocus
Text69.Text = vark
Text70.SetFocus
Text70.Text = varl
Text71.SetFocus
Text71.Text = varm
Text72.SetFocus
Text72.Text = varn
Text73.SetFocus
Text73.Text = varo
Text74.SetFocus
Text74.Text = varp
Text75.SetFocus
Text75.Text = varq
Text76.SetFocus
Text76.Text = varr
Text77.SetFocus
Text77.Text = vars
Text78.SetFocus
Text78.Text = vart
Text79.SetFocus
Text79.Text = varu
Text80.SetFocus
Text80.Text = varv
Text81.SetFocus
Text81.Text = varw
Text82.SetFocus
Text82.Text = varx
Text83.SetFocus
Text83.Text = vary
End Sub

Any ideas will be greatly appreciated.

Micki

 

[genomon]

Got a hit for you on Google that will help:

http://p2p.wrox.com/topic.asp?TOPIC_ID=15620

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[Remou]

I think it would be better and quicker to use a recordset.

 

[MICKI0220]

Remou.

I have been trying that with no success also...here is my code for that.


Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset

myrecordset.ActiveConnection = cnn1
'store the sql statement in a variable
Dim mysql As String
mysql = "SELECT History.Job_ID, Last(History.Date) AS LastOfDate, Last(History.BT_10)AS LastOfBT_10 FROM History Where (((History.Job_ID)=Forms!Constant!Combo44)) GROUP BY History.Job_ID"

myrecordset.Open mysql
'myrecordset.Fields(1) = BT_10.Text


It isn't populated with all the fields because I got an error so I was trying to take it slow and see where the problem is....the main error I keep getting is..."Run-time error *****...No value given for one or more required parameters".

 

[Remou]

You need to put the form bit ourside the quotes;

[tt]Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset

myrecordset.ActiveConnection = cnn1
'store the sql statement in a variable
Dim mysql As String
mysql = "SELECT History.Job_ID, Last(History.Date) AS LastOfDate, Last(History.BT_10)AS LastOfBT_10 FROM History Where (((History.Job_ID)=" _
& Forms!Constant!Combo44)) _
& " GROUP BY History.Job_ID"

myrecordset.Open mysql
'myrecordset.Fields(1) = BT_10.Text[/tt]

Assuming that JobID is numeric.

 

[Babyshamu]

Just for documentation reasons the error also comes up if you have a Field/control mistyped.

 

[KornGeek]

Remou is definitely on the right track about using a recordset. If your table/query has any significant number of records, DLookup becomes much slower than using a recordset. I strongly recommend against using that man DLookup commands in a row.