DLookup() problems Please Help!

[Kalvin Schroder]

I am fairly new to Access, and am trying to put together an invoice
form. The main form in called InvoiceDetailFm. Source is the table
InvoiceDetail and has invoice number, saleman, and CustID as some of
its fields. I have put together a combobox, named it CustID, and am
trying to use DLookup() to bring the complete Customer Name etc onto
the form using DLookup() function for the fields I need. The combobox
will bring up the CustID in the Customer table and DLookup brings up
the fields I want just fine. HOWEVER, if the user decides that the
CustID was the wrong one, a second try on the Combobox will produce
another CustID, but will NOT change any of the previous DLookup()
fields. So they way it is now, If the user made a mistake and put the
wrong CustID in the first time, and he changes it, the DLookup() will
not change.

What am I doing wrong, and how can I correct this?
/kal

[Chuck Van Den Corput]

On 5 Apr 2004 08:39:15 -0700, vi***********@hotmail.com (Kalvin
Schroder) wrote:

I am fairly new to Access, and am trying to put together an invoice
form. The main form in called InvoiceDetailFm. Source is the table
InvoiceDetail and has invoice number, saleman, and CustID as some of
its fields. I have put together a combobox, named it CustID, and am
trying to use DLookup() to bring the complete Customer Name etc onto
the form using DLookup() function for the fields I need. The combobox
will bring up the CustID in the Customer table and DLookup brings up
the fields I want just fine. HOWEVER, if the user decides that the
CustID was the wrong one, a second try on the Combobox will produce
another CustID, but will NOT change any of the previous DLookup()
fields. So they way it is now, If the user made a mistake and put the
wrong CustID in the first time, and he changes it, the DLookup() will
not change.

What am I doing wrong, and how can I correct this?
/kal

In the After Update event for CustID:

If isnull(me.CustID)=false then
'execute all the Dlookups to retrieve data related to CustID
end if

Chuck

[Chuck Van Den Corput]

On Mon, 05 Apr 2004 16:53:56 -0400, Chuck Van Den Corput
<NO*****************@sympatico.ca> wrote:

On 5 Apr 2004 08:39:15 -0700, vi***********@hotmail.com (Kalvin
Schroder) wrote:

I am fairly new to Access, and am trying to put together an invoice
form. The main form in called InvoiceDetailFm. Source is the table
InvoiceDetail and has invoice number, saleman, and CustID as some of
its fields. I have put together a combobox, named it CustID, and am
trying to use DLookup() to bring the complete Customer Name etc onto
the form using DLookup() function for the fields I need. The combobox
will bring up the CustID in the Customer table and DLookup brings up
the fields I want just fine. HOWEVER, if the user decides that the
CustID was the wrong one, a second try on the Combobox will produce
another CustID, but will NOT change any of the previous DLookup()
fields. So they way it is now, If the user made a mistake and put the
wrong CustID in the first time, and he changes it, the DLookup() will
not change.

What am I doing wrong, and how can I correct this?
/kal

In the After Update event for CustID:

If isnull(me.CustID)=false then
'execute all the Dlookups to retrieve data related to CustID
end if

Chuck

I should have mentioned that the Dlookups should go in code rather
than the Control Source for the customer-related fields (if, in fact,
that's what you are doing).

Chuck

[NeilAnderson]

Chuck

Do you need to use dlookup()? I find them really slow. You should have
a table tblCustomers with contactTitle, contactFirst, contactLast etc.
If you don't maybe you should consider it. If you do make a new combo
set the combo's rowsource to(something like):

SELECT DISTINCTROW customerID, CustomerFirst & " " & CustomerLast as
Customer, CustomerLast FROM tblCustomers ORDER BY CustomerLast

set the bound column to 2 & you'll display the customer's name just
nicely. It'll load much faster than a dlookup() & there's no code to
mess around with

Neil

Chuck Van Den Corput <NO*****************@sympatico.ca> wrote in message news:<n6********************************@4ax.com>. ..

On Mon, 05 Apr 2004 16:53:56 -0400, Chuck Van Den Corput
<NO*****************@sympatico.ca> wrote:

On 5 Apr 2004 08:39:15 -0700, vi***********@hotmail.com (Kalvin
Schroder) wrote:

I am fairly new to Access, and am trying to put together an invoice
form. The main form in called InvoiceDetailFm. Source is the table
InvoiceDetail and has invoice number, saleman, and CustID as some of
its fields. I have put together a combobox, named it CustID, and am
trying to use DLookup() to bring the complete Customer Name etc onto
the form using DLookup() function for the fields I need. The combobox
will bring up the CustID in the Customer table and DLookup brings up
the fields I want just fine. HOWEVER, if the user decides that the
CustID was the wrong one, a second try on the Combobox will produce
another CustID, but will NOT change any of the previous DLookup()
fields. So they way it is now, If the user made a mistake and put the
wrong CustID in the first time, and he changes it, the DLookup() will
not change.

What am I doing wrong, and how can I correct this?
/kal

In the After Update event for CustID:

If isnull(me.CustID)=false then
'execute all the Dlookups to retrieve data related to CustID
end if

Chuck

I should have mentioned that the Dlookups should go in code rather
than the Control Source for the customer-related fields (if, in fact,
that's what you are doing).

Chuck

[Chuck Van Den Corput]

On 6 Apr 2004 07:39:17 -0700, ne***********@boroughmuir.edin.sch.uk
(NeilAnderson) wrote:

Chuck

Do you need to use dlookup()? I find them really slow. You should have
a table tblCustomers with contactTitle, contactFirst, contactLast etc.
If you don't maybe you should consider it. If you do make a new combo
set the combo's rowsource to(something like):

SELECT DISTINCTROW customerID, CustomerFirst & " " & CustomerLast as
Customer, CustomerLast FROM tblCustomers ORDER BY CustomerLast

set the bound column to 2 & you'll display the customer's name just
nicely. It'll load much faster than a dlookup() & there's no code to
mess around with

Neil

Neil,

I agree that your solution is preferable ro mine and the one I would
go with myself. I just wasn't sure how to explain it to someone new to
Access. Maybe I'll give it a whirl here.

Let's say that Kal wants read-only controls on his form displaying the
address, city and phone number corresponding to the customer the user
has selected.

His CustID combo box control could be driven by a querydef with these
columns: CustID, CustName, CustAddress, CustCity, CustPhoneNumber,
sorted by CustName.

While the combo box could show all these pieces of information in the
dropdown list, let's say he only wanted to see CustName. Then he would
set the Column Count to 5 and the Column Widths to 0;2;0;0;0 so that
the only column the user would see would be the second one. His Bound
Column would be 1.

Then, in his read-only controls (Enabled=No, Locked=Yes) he could set
the Control Source as:
txtAddress: =CustID.column(2)
txtCity: =CustID.column(3)
txtPhone: =CustID.column(4)

Note that the column numbers are offsets, thus the first column is 0.

The controls txtAddress, txtCity and txtPhone would always self-adjust
based on the value of CustID.

Chuck

[NeilAnderson]

Chuck

Yes, that looks fine. I've never really needed to do this sort of
thing myself. I must admit that I'd only put the customer's name on an
invoice form, if I needed their details I'd use a sub form. But maybe
he really needs them?

Neil

Chuck Van Den Corput <NO*****************@sympatico.ca> wrote in message news:<bd********************************@4ax.com>. ..

On 6 Apr 2004 07:39:17 -0700, ne***********@boroughmuir.edin.sch.uk
(NeilAnderson) wrote:

Chuck

Do you need to use dlookup()? I find them really slow. You should have
a table tblCustomers with contactTitle, contactFirst, contactLast etc.
If you don't maybe you should consider it. If you do make a new combo
set the combo's rowsource to(something like):

SELECT DISTINCTROW customerID, CustomerFirst & " " & CustomerLast as
Customer, CustomerLast FROM tblCustomers ORDER BY CustomerLast

set the bound column to 2 & you'll display the customer's name just
nicely. It'll load much faster than a dlookup() & there's no code to
mess around with

Neil

Neil,

I agree that your solution is preferable ro mine and the one I would
go with myself. I just wasn't sure how to explain it to someone new to
Access. Maybe I'll give it a whirl here.

Let's say that Kal wants read-only controls on his form displaying the
address, city and phone number corresponding to the customer the user
has selected.

His CustID combo box control could be driven by a querydef with these
columns: CustID, CustName, CustAddress, CustCity, CustPhoneNumber,
sorted by CustName.

While the combo box could show all these pieces of information in the
dropdown list, let's say he only wanted to see CustName. Then he would
set the Column Count to 5 and the Column Widths to 0;2;0;0;0 so that
the only column the user would see would be the second one. His Bound
Column would be 1.

Then, in his read-only controls (Enabled=No, Locked=Yes) he could set
the Control Source as:
txtAddress: =CustID.column(2)
txtCity: =CustID.column(3)
txtPhone: =CustID.column(4)

Note that the column numbers are offsets, thus the first column is 0.

The controls txtAddress, txtCity and txtPhone would always self-adjust
based on the value of CustID.

Chuck