dlookup error

[enrico via AccessMonster.com]

i created a form(frmInterviewer) with:

1 combobox - row source(SELECT Lastname, InterviewerID FROM tblInterviewer)
1 textbox(txtID) - bound column(1) = returns the InterviewerID of the name
selected on the combobox
3 textboxes, namely:
txtLastname = control source (=DLookup("Query1","Lastname")
txtFirstname = control source (=DLookup("Query1","Firstname")
txtMI= control source (=DLookup("Query1","MI")
1 button - me.refresh


i created a query(Query1):

SELECT Lastname, Firstname, MI
FROM tblInterviewer
WHERE (((tblInterviewer.Lastname)=[Forms].[frmInterviewer].[txtID]));

the problem is every time i open my form the three textboxes already has an
"error" word and it doesn't return anything. doesn't access recognize dlookup
if the source is from a bound textbox? what's the problem to my form?

 

[Allen Browne]

This could be a timing problems (dependencies.)

Perhaps you could solve the problem by setting these properties for your
combo:
Bound Column 1
Column Count 2
Column Widths 0

Then set its RowSource like this:
SELECT InterviewerID, Lastname & ", " + FirstName AS FullName
FROM tblInterviewer ORDER BY LastName, FirstName;

This gives you 2 columns in the combo, but the first one is hidden
(zero-width.) Therefore the combo displays the second column. This column is
made up of the surname and first name together, so you don't need any other
text boxes to see the person's full name. If you need to bind the
InterviewID value to a field, that's the value of the combo.

 

[enrico via AccessMonster.com]

i tried it but i need to edit the fields. how is it possible?

 

[Allen Browne]

i tried it but i need to edit the fields. how is it possible?

I don't understand what you are trying to do.

If you set the Control Source of your text box to a DLookup() expression (as
in your original post), they will not be editable.

Perhaps you need a button that opens another form bound to the
tblInterviewer table, so you can edit the values there?

 

[enrico via AccessMonster.com]

yeah! that's what i'm trying to do, edit a field on a table. how will i do
that? how do i choose a specific name from my client and edit one or two of
its fields?

 

[Allen Browne]

See:
Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

Option number 2 sounds like what you need to do.

 

[enrico via AccessMonster.com]

it can only be applied in 2007 versions. are there any other way? all i want
is choose an entry and edit, that's all. i know how to retrieve but its not
editable.

 

[Allen Browne]

it can only be applied in 2007 versions. are there any other way?
all i want is choose an entry and edit, that's all. i know how to
retrieve but its not editable.

I'm not sure you read the article.

Option 2 shows:
a) how to use Edit List Items in A2007, and
b) the code to open a form.
Clearly you need to use the 2nd one there if you don't have A2007.

Not to mention that the article gave you 4 choices anyway.