using DLookUp with a particular column in a combo box (I'm baffled

[Guest]

I have a form with an address combo box, when I select an address, I want
another text box ‘From_PostCode’ to update the corresponding postcode from
one of the columns on the combo box, but when I use the combo, I just get,
#name? appear in the ‘From_PostCode’ textbox

This is my code on the [From_PostCode] field set on the ControlSource
property
=DLookUp(" [PostCode]
","tbl_Street_Names","[StreetNameID]"=Forms!frm_Get_Rounds!FromGetRound.Column(3))

What am I doing wrong?

 

[Allen Browne]

Suggestions:

The equal sign needs to be inside the quotes in the 3rd argument, and you
then need to concatenate the value:
"[StreetNameID] = " & Forms!frm_Get_Rounds!FromGetRound.Column(3)

If StreetNameID is a Text field (not a Number field), you need extra quotes,
as described here:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

The Column() property is zero-based (i.e. the first column is zero, the 2nd
is 1, etc), so verify that the postcode is actually in the 4th column of the
combo.

It will error if the 4th column does not contain a number.

Omit the spaces and square brackets from the first argument.

 

[Walkabout via AccessMonster.com]

Efandango,

The best solution here is to not even use the Dlookup. In your text box
(From_PostCode), all you have to do is set up the default to carry the value
of the combo box column you want:

=[AddressCombo].[column](3)

and then in the onchange of the combo box (AddressCombo) put:

Me.From_PostCode = [AddressCombo].[Column](3)

This is more flexible, faster and easier than using the lookup function to
populate you text box. The Lookup would be more appropriate if you were
looking for a value from another table to poplulate the text box with only
the criteria coming from the Combo Box.

Walkabout

I have a form with an address combo box, when I select an address, I want
another text box ‘From_PostCode’ to update the corresponding postcode from
one of the columns on the combo box, but when I use the combo, I just get,
#name? appear in the ‘From_PostCode’ textbox

This is my code on the [From_PostCode] field set on the ControlSource
property
=DLookUp(" [PostCode]
","tbl_Street_Names","[StreetNameID]"=Forms!frm_Get_Rounds!FromGetRound.Column(3))

What am I doing wrong?

 

[Guest]

Thanks for your advice and direction Allen. it helped immensely. I didn't
realise that the .Column# feature beings at 0!...

This is the line that works:

Me.From_PostCode = DLookup("[PostCode]", "tbl_Street_Names", "[StreetNameID]
=" & FromGetRound.Column(2))

regards

Eric

Suggestions:

The equal sign needs to be inside the quotes in the 3rd argument, and you
then need to concatenate the value:
"[StreetNameID] = " & Forms!frm_Get_Rounds!FromGetRound.Column(3)

If StreetNameID is a Text field (not a Number field), you need extra quotes,
as described here:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

The Column() property is zero-based (i.e. the first column is zero, the 2nd
is 1, etc), so verify that the postcode is actually in the 4th column of the
combo.

It will error if the 4th column does not contain a number.

Omit the spaces and square brackets from the first argument.

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

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

I have a form with an address combo box, when I select an address, I want
another text box ‘From_PostCode’ to update the corresponding postcode from
one of the columns on the combo box, but when I use the combo, I just get,
#name? appear in the ‘From_PostCode’ textbox

This is my code on the [From_PostCode] field set on the ControlSource
property
=DLookUp(" [PostCode]
","tbl_Street_Names","[StreetNameID]"=Forms!frm_Get_Rounds!FromGetRound.Column(3))

What am I doing wrong?

 

[Guest]

Walkabout; that second section:

"and then in the onchange of the combo box (AddressCombo) put:

Me.From_PostCode = [AddressCombo].[Column](3)"

Will that act as the update facility for when I change the address, but
using the same 'street name', but with a different postcode?

That was the problem that led me to the DLookUp solution in the first place,
the problem was that the Postcode box would only update if I selected a
different street name, If I used:

North Street EC1
North Street NW6

then the postcode would remain unchanged.

But if I went to:

North St EC1
Anystreet NW6

it would work.

Efandango,

The best solution here is to not even use the Dlookup. In your text box
(From_PostCode), all you have to do is set up the default to carry the value
of the combo box column you want:

=[AddressCombo].[column](3)

and then in the onchange of the combo box (AddressCombo) put:

Me.From_PostCode = [AddressCombo].[Column](3)

This is more flexible, faster and easier than using the lookup function to
populate you text box. The Lookup would be more appropriate if you were
looking for a value from another table to poplulate the text box with only
the criteria coming from the Combo Box.

Walkabout

I have a form with an address combo box, when I select an address, I want
another text box ‘From_PostCode’ to update the corresponding postcode from
one of the columns on the combo box, but when I use the combo, I just get,
#name? appear in the ‘From_PostCode’ textbox

This is my code on the [From_PostCode] field set on the ControlSource
property
=DLookUp(" [PostCode]
","tbl_Street_Names","[StreetNameID]"=Forms!frm_Get_Rounds!FromGetRound.Column(3))

What am I doing wrong?

 

[Walkabout via AccessMonster.com]

Efandnago,

If there are seperate records inside the same table used as the source for
the Combo Box, the OnChange will update the Me.From_PostCode with the value
from the record selected by the combo box. So if you have two records:

North Street EC1
North Street NW6

In whatever control you use the field that contains North Street, it remains
North Street. While if you have your Combo Box changing to a different
record - the text box will change because the OnChange code has changed it by
using the value in that field (column) for that record from the selected
record in the Combo Box control.

Walkabout


Walkabout

Walkabout; that second section:

"and then in the onchange of the combo box (AddressCombo) put:

Me.From_PostCode = [AddressCombo].[Column](3)"

Will that act as the update facility for when I change the address, but
using the same 'street name', but with a different postcode?

That was the problem that led me to the DLookUp solution in the first place,
the problem was that the Postcode box would only update if I selected a
different street name, If I used:

North Street EC1
North Street NW6

then the postcode would remain unchanged.

But if I went to:

North St EC1
Anystreet NW6

it would work.

Efandango,

[quoted text clipped - 26 lines]