Form DLookup

[Sue Cardwell via AccessMonster.com]

I'm rather new at VBA and so this question may have a simple answer. I'm
creating a form where I would like to place a DLookup in a control so that
once a choice is made in another control, the program will lookup its correct
counterpart. I have used the DLookup before, and it works. However, this
time, it seems to be in a continuous loop and freezes up the computer each
time I try to run it. Any suggestions on what might be happening to cause
this problem? If I can't get this to work, does VBA provide for a way to
create a list box for a control based on the information in a previous
control?

 

[Rob Oldfield]

Could you post the code that you're currently trying please.

 

[Sue Cardwell via AccessMonster.com]

Certainly. I have a table called tblBlockCaptain which contains two fields
(WardID and BlockCaptain). My code is as follows: =DLookup("BlockCaptain" ,
"tblBlockCaptain" , "WardID =" Forms!frmMemberIntake!WardID)

Could you post the code that you're currently trying please.

I'm rather new at VBA and so this question may have a simple answer. I'm
creating a form where I would like to place a DLookup in a control so that

[quoted text clipped - 5 lines]

create a list box for a control based on the information in a previous
control?

 

[Rob Oldfield]

Try...

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID ="
&Forms!frmMemberIntake!WardID)

You basically need to build the criteria string by concatenation.

Certainly. I have a table called tblBlockCaptain which contains two fields
(WardID and BlockCaptain). My code is as follows: =DLookup("BlockCaptain" ,
"tblBlockCaptain" , "WardID =" Forms!frmMemberIntake!WardID)

Could you post the code that you're currently trying please.

I'm rather new at VBA and so this question may have a simple answer. I'm
creating a form where I would like to place a DLookup in a control so

that
[quoted text clipped - 5 lines]

create a list box for a control based on the information in a previous
control?

 

[Allan Murphy]

Sue

You will need to add an & and [ ] .

=DLookup("[BlockCaptain]", "tblBlockCaptain" , "WardID =" &
Forms!frmMemberIntake!WardID)


--
Allan Murphy
Email: (e-mail address removed)

Certainly. I have a table called tblBlockCaptain which contains two fields
(WardID and BlockCaptain). My code is as follows: =DLookup("BlockCaptain" ,
"tblBlockCaptain" , "WardID =" Forms!frmMemberIntake!WardID)

Could you post the code that you're currently trying please.

I'm rather new at VBA and so this question may have a simple answer. I'm
creating a form where I would like to place a DLookup in a control so

that
[quoted text clipped - 5 lines]

create a list box for a control based on the information in a previous
control?

 

[Sue Cardwell via AccessMonster.com]

I tried adding both the "[ ]" and the "&" and I'm still getting an #Error
message. Any other suggestions?

Sue

You will need to add an & and [ ] .

=DLookup("[BlockCaptain]", "tblBlockCaptain" , "WardID =" &
Forms!frmMemberIntake!WardID)

Certainly. I have a table called tblBlockCaptain which contains two fields
(WardID and BlockCaptain). My code is as follows: =DLookup("BlockCaptain" ,

[quoted text clipped - 7 lines]

 

[Allan Murphy]

Sue
In your original request you said "once a choice is made in another
control", could you please tell me how are making this choice.? Do you enter
a value for Ward ID or Select the Ward ID from a Drop Down list using a
query as the data source?

 

[Rob Oldfield]

Or is it a list box? Is it showing an error both before and after you
select/enter a value in the source control? If it's a list or combo does it
contain multiple columns? What is the data source? And WardID in
tblBlockCaptain is a numeric field?

 

[Sue Cardwell via AccessMonster.com]

A choice is made by entering data. There is no list box and I do get an
#error even before I enter any data. Both the Ward ID and the Block Captin
fields are text data types and have the same field sizes.

 

[Sue Cardwell via AccessMonster.com]

I'm sorry I was out of the office for a couple of days, but I'm still looking
for help on this question. Thanks for your prompt responses. A choice is
made by entering data. The form is based on a table and there is no query
involved. The table itself only has two columns, the Ward ID and the
BlockCaptains and both fields are text fields since the WardID is something
like FR-1 and the block captain is a person's name. The control does show an
error both before and after I enter a value in the source control.

 

[Rob Oldfield]

If it's a text field then the criteria will need to end up looking something
like:

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID ='FR-1'")

and your problem is that you currently don't have the ' characters in there.
Try:

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID
='"&Forms!frmMemberIntake!WardID&"'")

The [] characters, by the way, are only strictly necessary if the field
you're looking up - BlockCaptain in your case - contains spaces. The same
thing applies to WardID.

 

[Sue Cardwell via AccessMonster.com]

Okay, I've tried using the ', but it's still not working. I still get the
#Error message. I quess I'll try something else. I'm just not sure why it
won't work. I have used this same formula in other databases and it works
just fine. I'm sure I'm doing something wrong, but I can't for the life of
me figure out what it is. Thank you so much for all your help. You guys are
great!

If it's a text field then the criteria will need to end up looking something
like:

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID ='FR-1'")

and your problem is that you currently don't have the ' characters in there.
Try:

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID
='"&Forms!frmMemberIntake!WardID&"'")

The [] characters, by the way, are only strictly necessary if the field
you're looking up - BlockCaptain in your case - contains spaces. The same
thing applies to WardID.

I'm sorry I was out of the office for a couple of days, but I'm still looking
for help on this question. Thanks for your prompt responses. A choice is

[quoted text clipped - 14 lines]

 

[Rob Oldfield]

One thing to try: does

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID ='FR-1'")

work? If it doesn't then I'd guess that there's something wrong with the
field names that you're using.

Okay, I've tried using the ', but it's still not working. I still get the
#Error message. I quess I'll try something else. I'm just not sure why it
won't work. I have used this same formula in other databases and it works
just fine. I'm sure I'm doing something wrong, but I can't for the life of
me figure out what it is. Thank you so much for all your help. You guys are
great!

If it's a text field then the criteria will need to end up looking something
like:

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID ='FR-1'")

and your problem is that you currently don't have the ' characters in there.
Try:

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID
='"&Forms!frmMemberIntake!WardID&"'")

The [] characters, by the way, are only strictly necessary if the field
you're looking up - BlockCaptain in your case - contains spaces. The same
thing applies to WardID.

I'm sorry I was out of the office for a couple of days, but I'm still looking
for help on this question. Thanks for your prompt responses. A choice

is
[quoted text clipped - 14 lines]

a value for Ward ID or Select the Ward ID from a Drop Down list using a
query as the data source?

 

[Sue Cardwell via AccessMonster.com]

You were right! I finally got it to work. Thanks so much for your help.

One thing to try: does

=DLookup("BlockCaptain" ,"tblBlockCaptain" , "WardID ='FR-1'")

work? If it doesn't then I'd guess that there's something wrong with the
field names that you're using.

Okay, I've tried using the ', but it's still not working. I still get the
#Error message. I quess I'll try something else. I'm just not sure why it

[quoted text clipped - 23 lines]