Set the value of a combo box

[Justin Roby]

I currently have a combo box who's Row Source is a SELECT statement which
successfully returns a single value. The problem is I would like for this
value to automatically be set for the combo box so I don't have to click the
drop-down arrow to select it. I have tried everything I know using Macros and
SetValue but I just can't seem to get the combo box's value to be
automatically set to the value returned by the query in the Row Source. Any
help would be much appreciated, thanks.

 

[Steve Schapel]

Justin,

Use the Default Value property of the combobox.

 

[Justin Roby]

I have tried that but I obviously didn't put the correct value in the default
value property, what would it be? Here is my Row Source value = SELECT
[Contacts].[Address] FROM Contacts WHERE [ANI
File].[Customer]=[Contacts].[Company];

 

[Steve Schapel]

Justin,

I'm a bit lost at this point, I'm afraid?

First of all, what is "[ANI file].[Customer]"? There does not appear to be
a [ANI File] table or query included in your query?

Does this always only return 1 row? And this value will always be entered
in the combobox? In that case, what is the purpose of the combobox?

Is the combobox bound?

Sorry, just can't quite grasp what you are trying to do.

 

[Justin Roby]

ANI File and Contacts are two different tables. I have a form that reads
information from the ANI File and displays it, I also have the Save & New
command on the form which goes to the new record row on the ANI File. There
is a field on the form "Customer" which reads from the Contacts.Company field
in the Contacts table. What I would like is for the Address field on the form
to be automatically populated with the address of the Company that is
selected in the "Customer" field. So, the way I was able to get the combobox
to display this address correctly is with the SELECT statement from my
previous post. The problem is, even though it displays the address in the
dropdown box, it is not selected by default. I did try a couple of things.
First, I tried where the combo box was bound to the Address field in the ANI
File while reading information from the Contacts.Address field based on the
customer selected in the Customer Field. It displayed the address correctly
but I had to manually click on the drop down arrow to actually select it.
Second, I tried making the combobox unbound and then making a separate Text
field and set the default value of the text field to
[ComboBoxName].ItemData(1). It would grab the information from the Combobox
but it would not refresh the information as the Combobox updated. I tried
creating a requery macro but I don't seem to have that working.

Justin,

I'm a bit lost at this point, I'm afraid?

First of all, what is "[ANI file].[Customer]"? There does not appear to be
a [ANI File] table or query included in your query?

Does this always only return 1 row? And this value will always be entered
in the combobox? In that case, what is the purpose of the combobox?

Is the combobox bound?

Sorry, just can't quite grasp what you are trying to do.

--
Steve Schapel, Microsoft Access MVP

I have tried that but I obviously didn't put the correct value in the
default
value property, what would it be? Here is my Row Source value = SELECT
[Contacts].[Address] FROM Contacts WHERE [ANI
File].[Customer]=[Contacts].[Company];

 

[Justin Roby]

Here is a better question. Let me set it up first, I have 2 tables, one is
ANI File and the other is Contacts. Each table has a field called Address.
Contacts has a field called Company with a Relationship to the ANI File's
field Customer. I have a form that I want to have the address field populated
by the information in my ANI File, ANI File being the Record Source, while I
am scrolling through the records in the ANI File with the form. That is no
problem. I just have the ANI File Address field on the form, works fine. But,
when I click on Save and New, I want to be able to select a Customer in the
customer field and have the address field's value populate with that of the
address field information from Contacts. That way I can reference the correct
contact address and save that address information to the ANI file address.

Justin,

I'm a bit lost at this point, I'm afraid?

First of all, what is "[ANI file].[Customer]"? There does not appear to be
a [ANI File] table or query included in your query?

Does this always only return 1 row? And this value will always be entered
in the combobox? In that case, what is the purpose of the combobox?

Is the combobox bound?

Sorry, just can't quite grasp what you are trying to do.

--
Steve Schapel, Microsoft Access MVP

I have tried that but I obviously didn't put the correct value in the
default
value property, what would it be? Here is my Row Source value = SELECT
[Contacts].[Address] FROM Contacts WHERE [ANI
File].[Customer]=[Contacts].[Company];