Default value - Text box

  • Thread starter Thread starter Stephanie Reingold
  • Start date Start date
S

Stephanie Reingold

Hi there,

I've got a form with a subform that I want to set a text box to lookup an
employees pay. I'm trying to use a dlookup.
The text box isn't pulling the data at all. What might be wrong here or is
there an easier way.

DLookUp("[Pay]","[Employees]","[TutorID]='" & [Employees]![TutorID] & "'")

TIA.
Stephanie
 
Suggestions:

1. You need an equal sign as the first character in the Control Source of
your text box.

2. Unless the form is based on a query that has 2 fields named TutorID (from
2 different tables), drop the Employees! from the 3rd argument.

3. If TutorID is a Number type field (not a Text type field), drop the extra
quotes.

4. If you are at a new record where TutorID is blank, the 3rd argument will
be incomplete and so show #Error. Use Nz() to avoid that.

Try:
=DLookUp("[Pay]","[Employees]","[TutorID] = " & Nz([TutorID], 0))

For more info, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
 
The way this statement is constructed, it looks like the [Emplyoyees] may be
a form rather than a field in a query. Because the OP is not using any
naming conventions, it is hard to tell. If it is not a form, then the
statement is trying to compare a field to itself. In addition to the other
problems you point out, I think there may be an ambiguity problem; If not for
Access, then at least for us humans.

Allen Browne said:
Suggestions:

1. You need an equal sign as the first character in the Control Source of
your text box.

2. Unless the form is based on a query that has 2 fields named TutorID (from
2 different tables), drop the Employees! from the 3rd argument.

3. If TutorID is a Number type field (not a Text type field), drop the extra
quotes.

4. If you are at a new record where TutorID is blank, the 3rd argument will
be incomplete and so show #Error. Use Nz() to avoid that.

Try:
=DLookUp("[Pay]","[Employees]","[TutorID] = " & Nz([TutorID], 0))

For more info, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Stephanie Reingold said:
Hi there,

I've got a form with a subform that I want to set a text box to lookup an
employees pay. I'm trying to use a dlookup.
The text box isn't pulling the data at all. What might be wrong here or
is there an easier way.

DLookUp("[Pay]","[Employees]","[TutorID]='" & [Employees]![TutorID] & "'")

TIA.
Stephanie
Click to expand...
Click to expand...
 
Stephanie, I think I would be tempted to use the BeforeInsert event of the
subform to check that there is a record in the main form, and drop in the
tutor and their current default price into the subform (assuming that the
Tutor_Price in the subform is a stored field).

This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel =True
MsgBox "Fill in the main form first."
Else
If Not IsNull(!TutorID) Then
Me.TutorID = !TutorID
strWhere = "TutorID = " & !TutorID
Me.Tutor_Price = DLookup("Pay","Employees", strWhere)
End If
End If
End Sub
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Stephanie Reingold said:
The value now is $0.00.

As you can see in the attachment, the main form has a combo box to select
the main tutor. I would like to set the Tutor_Price text field to the
employees pay.

I'm really rusty at this stuff.
Thanks,
Stephanie

Allen Browne said:
Suggestions:

1. You need an equal sign as the first character in the Control Source of
your text box.

2. Unless the form is based on a query that has 2 fields named TutorID
(from
2 different tables), drop the Employees! from the 3rd argument.

3. If TutorID is a Number type field (not a Text type field), drop the
extra
quotes.

4. If you are at a new record where TutorID is blank, the 3rd argument
will
be incomplete and so show #Error. Use Nz() to avoid that.

Try:
=DLookUp("[Pay]","[Employees]","[TutorID] = " & Nz([TutorID], 0))

For more info, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html


Stephanie Reingold said:
Hi there,

I've got a form with a subform that I want to set a text box to lookup
an
employees pay. I'm trying to use a dlookup.
The text box isn't pulling the data at all. What might be wrong here or
is there an easier way.

DLookUp("[Pay]","[Employees]","[TutorID]='" & [Employees]![TutorID] &
"'")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top