Whati s wrong with this Line of code? 1

[sanan]

Hi there
I have a form in a application with Access as FE and SQl as BE.
The following line of codes (Different version of the same Line) Does not work any suggestion of what is wrong with it?
I had these line in a Data Source of a TxtBox control,
The Field Data Type of the comboBox Contol is called CustomerId and is nchar.
Table name to look into is called Customers.
ComboCustomer is the name of the comboBox.

=DLookUp("[address]","[customers]","[customerID]=form![comboCustomer].value")
=DLookUp("[address]","[customers]","[customerID]=form![Sales]![comboCustomer].column(0)")
=DLookUp("address","TableSales","CustomerID=" & Val([Me]![comboCustomer]))
=DLookUp("[address]","[TableSales]","[CustomerID]=" & Val([Me]![comboCustomer]))
=DLookUp("[address]","[TableSales]","[CustomerID]='" & Val([Me]![comboCustomer]) & "'")



Best regards
Sanan

 

[PHV]

And what about this ?
=DLookUp("address","customers","CustomerID='" & Val(Me!comboCustomer) & "'")

ie, in SQL, SELECT address FROM customers WHERE CustomerID='NumericValue'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanan]

Hi PHV
Thanks so much for your reply,
I tried the following codes , but no success;
=DLookUp("address","customers","CustomerID='" & Val(Me!comboCustomer) & "'")

Also I tried the following from my previous Comment, apparently I made a mistake on the table name;
=DLookUp("address","Customers","CustomerID=" & Val([Me]![comboCustomer]))
=DLookUp("[address]","[Customers]","[CustomerID]=" &Val([Me]![comboCustomer]))
=DLookUp("[address]","[Customers]","[CustomerID]='" & Val([Me]![comboCustomer]) & "'")

But no Success.
A note just a Reminder that in my comboCustomer, Following is true
in SQL, SELECT address FROM customers WHERE CustomerID='nchar'

Otherwise the value of my ComboCustomer or the Field of my Customers Table, “CustomerId” are some thing like This; HXYZ, IBMI, KLMO, and so on, and it is not a Number.

Best Regards
Sanan

 

[PHV]

it is not a Number
=DLookUp("address","customers","CustomerID='" & Me!comboCustomer & "'")
Note: the bound column of the combo must obviously be a customerID ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sanan]

Hi PHV
This is also a reply to your reply to my other Thread "Problem of closing a form"
I tried the following, but still no Success;

Private Sub ComboCustomer_AfterUpdate()
Me!ShipName = Me![ComboCustomer].Column(1)
Set rs = CurrentProject.Connection.Execute("select * from Customers " & _
"WHERE CustomerID='" & Form!ComboCustomer.Column(0) & "'", , adCmdText)
Me!City.Value = rs.Fields("city").Value
End Sub

The following line was tried for different versions of it, but still no success;
"WHERE CustomerID='" & Form!ComboCustomer.Column(0) & "'", , adCmdText)
I tried these;
"WHERE CustomerID='" & me!ComboCustomer.Column(0) & "'", , adCmdText)
"WHERE CustomerID='" & me!ComboCustomer & "'", , adCmdText)

Also I tried the followings in the ControlSource of txtBox called city;
Set rs = CurrentProject.Connection.Execute("select * from Customers " & _
"WHERE CustomerID='" & me!ComboCustomer.Column(0) & "'", , adCmdText)
Me!City.Value = rs.Fields("city").Value
But No success, Could you verify for me that in What control, and what Event I should try the above codes, I am a little confused about it.

I should mention, that the following finally worked, which is s sort of different version of PHV’s latest reply;
This is off course a simple “DLookup” Fuction;
=DLookUp("address","customers","CustomerID='" & [Form]![comboCustomer].[column](0) & "'")


Best Regards
Sanan