DLookUp

[Guest]

I have several text boxes on a sub form that are populated using DLookUp
statements in the control source. The following statement is an example of
one:

=DLookUp("[strName]","tblTrainerInfo","[tblTrainerInfo].[strEmplId]=" &
Nz("[forms]![frmViewSchedule]![frmViewScheduleSubForm].[Form]![strTrainer]",0))

When I split my db into fe and be, the DLookUps became slow. Would it be
faster to set the control source using code? If so, how do I do this?

TIA,

Chris

 

[Allen Browne]

Any chance of setting the RecordSource of the form to tblTrainerInfo?
You could then just set the ControlSource of each text box to the relevant
field.

If that is not possible, you could leave the controls unbound (nothing in
their ControlSource), and in the Current event of the form to OpenRecordset
on a SQL statement that gets all the values you need and assign them to the
unbound controls. This would be much faster than the DLookup()s as it is a
single operation instead of one for each control.

 

[Guest]

Allen,

I tried the following code in the OnCurrent Event of the sub form and in the
OnCurrent event of the main form:

Private Sub Form_Current()
Dim strTrainer As DAO.Recordset

Set strTrainer = CurrentDb.OpenRecordset("SELECT strName FROM
tblTrainerInfo " & _
"WHERE strEmplID = " &
"Forms!frmViewSchedule!frmViewScheduleSubForm.Form!strTrainer")
Me.txtTrainer = strTrainer
End Sub

both ways I get the same error: Run time error '3061': Too Few parameters.
Expected 1.

Any thoughts?

Thanks for your help.

Chris

 

[Duane Hookom]

Remove the quotes from around your form/control reference. Your specific
syntax would depend on the data type of strEmplID.

 

[Guest]

strEmplID is a text field and the data is a combination of letters and
numbers. For my own understanding, I thought that a string had to be in
quotes. Should I use single quotes as below?

Set strTrainer = CurrentDb.OpenRecordset("SELECT strName FROM
tblTrainerInfo " & _
"WHERE strEmplID = ' " &
Forms!frmViewSchedule!frmViewScheduleSubForm.Form!strTrainer & "'")
Me.txtTrainer = strTrainer

Thanks,

Chris

 

[Allen Browne]

Okay, use a separate string for the SQL statement.
Add a Debug.Print line so that when it fails, you can open the Immeidate
Window (Ctrl+G) and see what it generated. You can even paste this into SQL
view in a new query to see if Access can understand it.

You also want to test whether the recordset returned any records. In the
code you posted, it probably didn't as there is a space after your single
quote and before your double quote.

Dim rsTrainer As DAO.Recordset
Dim strSql As String

strSql = "SELECT strName FROM tblTrainerInfo WHERE strEmplID = """ &
Forms!frmViewSchedule!frmViewScheduleSubForm.Form!strTrainer & """;"

Debug.Print strSql
Set rsTrainer = CurrentDb.OpenRecordset(strSql)
If rsTraniner.RecordCount = 0 Then
Else
Me.txtTrainer = rsTrainer!strName
End If
rsTrainer.Close

As an alternative, if you already have the value of strEmpID on the form,
could you just use a combo to get the name?

 

[Guest]

That did it! Thanks for taking the time to help.

Chris