My DLookup FN works but gives #error on new recoed

[Guest]

I have a dlookup function to automatically display the address when a company
name is selected and this works.
=DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]!CustomerID)

But when i go to a new record the fields show #error. As i fill in the
fields of this new record and come to filling in the company name the #error
in the address fields are replaced by the address. Does anyone know how to
get rid of the #error

 

[Al Campagna]

Natasha,
Try...
=DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] = Forms![Installation
form]![CustomerID]")

 

[Al Campagna]

Natasha,
Sorry, I missed the part about New record ERROR.
Try...
=IIF(IsNull( Forms![Installation form]![CustomerID]), "",
DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]![CustomerID])


But... there is a better solution... that avoids the DLookup.
If you had a combobox (ex. cboCustID) bound to the key field in your table (ex.
CustID), with columns of...
CustID | CustName | CustShipAddr

and the ColumnCount was set to 3, and Column Widths set to 0" ; 1.5"; 2.0",
then the combo would do the following...
It would allow the user to select Customer by CustName, "display" that CustName in the
combo, but... really store the CustID in the bound CustID field.
OK, now an unbound text control on the form with a ControlSource of...
= cboCustID.Column(2)
would always display the associated ShipAddress from the 3rd column. (Combo cols are
numbered 0,1,2, etc)

 

[Guest]

=Nz(DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]!CustomerID),"")

 

[Guest]

Hi Al

Thanks for you help. I tried inputting the first solution but it says that
there is a closes bracket missind [ or a virtical line. I have copied yours
in so it not a type error. Havn't tried the second one yet will later.

Natasha

Natasha,
Sorry, I missed the part about New record ERROR.
Try...
=IIF(IsNull( Forms![Installation form]![CustomerID]), "",
DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]![CustomerID])


But... there is a better solution... that avoids the DLookup.
If you had a combobox (ex. cboCustID) bound to the key field in your table (ex.
CustID), with columns of...
CustID | CustName | CustShipAddr

and the ColumnCount was set to 3, and Column Widths set to 0" ; 1.5"; 2.0",
then the combo would do the following...
It would allow the user to select Customer by CustName, "display" that CustName in the
combo, but... really store the CustID in the bound CustID field.
OK, now an unbound text control on the form with a ControlSource of...
= cboCustID.Column(2)
would always display the associated ShipAddress from the 3rd column. (Combo cols are
numbered 0,1,2, etc)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a dlookup function to automatically display the address when a company
name is selected and this works.
=DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]!CustomerID)

But when i go to a new record the fields show #error. As i fill in the
fields of this new record and come to filling in the company name the #error
in the address fields are replaced by the address. Does anyone know how to
get rid of the #error

 

[Al Campagna]

Natasha,
Try cutting and pasting this...
= IIF(IsNull(Forms![Installation form]![CustomerID]), "",
DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] = Forms![Installation
form]![CustomerID]"))

In an email, we have no way to check a syntax except visually. There should be 2 Close
Parens at the end of the IFF.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi Al

Thanks for you help. I tried inputting the first solution but it says that
there is a closes bracket missind [ or a virtical line. I have copied yours
in so it not a type error. Havn't tried the second one yet will later.

Natasha

Natasha,
Sorry, I missed the part about New record ERROR.
Try...
=IIF(IsNull( Forms![Installation form]![CustomerID]), "",
DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]![CustomerID])


But... there is a better solution... that avoids the DLookup.
If you had a combobox (ex. cboCustID) bound to the key field in your table (ex.
CustID), with columns of...
CustID | CustName | CustShipAddr

and the ColumnCount was set to 3, and Column Widths set to 0" ; 1.5"; 2.0",
then the combo would do the following...
It would allow the user to select Customer by CustName, "display" that CustName in
the
combo, but... really store the CustID in the bound CustID field.
OK, now an unbound text control on the form with a ControlSource of...
= cboCustID.Column(2)
would always display the associated ShipAddress from the 3rd column. (Combo cols are
numbered 0,1,2, etc)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a dlookup function to automatically display the address when a company
name is selected and this works.
=DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]!CustomerID)

But when i go to a new record the fields show #error. As i fill in the
fields of this new record and come to filling in the company name the #error
in the address fields are replaced by the address. Does anyone know how to
get rid of the #error

 

[Guest]

Thank you for your help i have the combo option in place and is working well.

Great Thanks
Natasha

Natasha,
Sorry, I missed the part about New record ERROR.
Try...
=IIF(IsNull( Forms![Installation form]![CustomerID]), "",
DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]![CustomerID])


But... there is a better solution... that avoids the DLookup.
If you had a combobox (ex. cboCustID) bound to the key field in your table (ex.
CustID), with columns of...
CustID | CustName | CustShipAddr

and the ColumnCount was set to 3, and Column Widths set to 0" ; 1.5"; 2.0",
then the combo would do the following...
It would allow the user to select Customer by CustName, "display" that CustName in the
combo, but... really store the CustID in the bound CustID field.
OK, now an unbound text control on the form with a ControlSource of...
= cboCustID.Column(2)
would always display the associated ShipAddress from the 3rd column. (Combo cols are
numbered 0,1,2, etc)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a dlookup function to automatically display the address when a company
name is selected and this works.
=DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]!CustomerID)

But when i go to a new record the fields show #error. As i fill in the
fields of this new record and come to filling in the company name the #error
in the address fields are replaced by the address. Does anyone know how to
get rid of the #error

 

[Guest]

Hi

Ihave another problem with this now. The address in entered now
automatically but the information does not go to the table. All the other
information does but not the fields that are automated. Can you help.

Natasha

Thank you for your help i have the combo option in place and is working well.

Great Thanks
Natasha

Natasha,
Sorry, I missed the part about New record ERROR.
Try...
=IIF(IsNull( Forms![Installation form]![CustomerID]), "",
DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]![CustomerID])


But... there is a better solution... that avoids the DLookup.
If you had a combobox (ex. cboCustID) bound to the key field in your table (ex.
CustID), with columns of...
CustID | CustName | CustShipAddr

and the ColumnCount was set to 3, and Column Widths set to 0" ; 1.5"; 2.0",
then the combo would do the following...
It would allow the user to select Customer by CustName, "display" that CustName in the
combo, but... really store the CustID in the bound CustID field.
OK, now an unbound text control on the form with a ControlSource of...
= cboCustID.Column(2)
would always display the associated ShipAddress from the 3rd column. (Combo cols are
numbered 0,1,2, etc)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a dlookup function to automatically display the address when a company
name is selected and this works.
=DLookUp("[DeliveryAddress1]","tblcustomer","[CustomerID] =" &
Forms![Installation form]!CustomerID)

But when i go to a new record the fields show #error. As i fill in the
fields of this new record and come to filling in the company name the #error
in the address fields are replaced by the address. Does anyone know how to
get rid of the #error