autofill textbox with combobox values & add new values

[Guest]

Hi,

I just wanted an advice from somebody:

I've got 2 fields on my form "Orders" - a combobox with Names, which I
select from table T_Names and a textbox for Addresses, which I want to be
filled up automatically from the second column of the same T_Names table when
the "Name" matches. The values that I enter are stored in the T_Orders table.

The point is the Names field is not limited to list and the Addresses field
is not limited to list. So I want to be able to enter any Name and any
Address. But when the name is from the list - I want the address to be filled
into the textbox automatically.

So I used the following update querry for the "on exit" event for "Names"
combobox:

UPDATE T_Orders, T_Names SET T_Orders.Address = T_Names.Address
WHERE (((T_Orders.ID)=Forms!Orders.ID) And
(Forms!Orders![Name]=T_Names.Name));

This seems work all right, but now I want the new names which are not on
list to automatically be added to my T_Names table, so that next time they
also appear on the list.

Is it going to be another update query which compares "Names" values from 2
tables and adds new values to my T_Names table?
I am also not sure that I know exactly the way how to do it.

The other way I was thinking about is instead of using T_Names table for my
combobox, to make some query, which would lookup values in my T_Orders table
in the Names field and select only the unique names, not including
duplicates, so the list for the combobox would be some 20 Names instead of
200.
This "duplicate" thing is also seem to me so complicated.

Can somebody advise me on a better way to organize this?
I would appreciate any help.

Thank you.
Lana

 

[Guest]

Lana,
You have a lot of things going on here. I will try to address them
logically. First, an easy way to populate a text box off a combo box is to
put the following on the After Update event of your Name field (you need to
the combobox set up so that the address is part of the fields listed,
although you can have the width set to zero so that it does not show).

Me.Address = Me.NameField.Column(1)
Me.City = Me.NameField.Column(2)
Me.State = Me.NameField.Column(3)
etc.

(where column 1 represents the second column, counting starts at zero)

Now, in order to populate a name field in the lookup table with a value "on
the fly" you can do it a couple of ways. Doing this based off your orders
table does work if you base the combo box off a Totals query that is grouped
by the Name field. On the after update event of the combo box you need to
put
Me.Requery to repopluate the list.
The problem with this approach is that your drop down will only list
customers who have placed an order at some point and it will not populate the
address field at the same time. An approach I use more often is to have a
button to add a new name, a pop up box will come up where the Name and
Address can be entered. On the On Close event of the pop up form I populate
the fields on the original form and then also requery the combo box. If data
entry needs to be streamlined, you can have the new form open off key strokes
in addition to the mouse click.

Hope this makes sense. Let me know if you need some clarification.

Hi,

I just wanted an advice from somebody:

I've got 2 fields on my form "Orders" - a combobox with Names, which I
select from table T_Names and a textbox for Addresses, which I want to be
filled up automatically from the second column of the same T_Names table when
the "Name" matches. The values that I enter are stored in the T_Orders table.

The point is the Names field is not limited to list and the Addresses field
is not limited to list. So I want to be able to enter any Name and any
Address. But when the name is from the list - I want the address to be filled
into the textbox automatically.

So I used the following update querry for the "on exit" event for "Names"
combobox:

UPDATE T_Orders, T_Names SET T_Orders.Address = T_Names.Address
WHERE (((T_Orders.ID)=Forms!Orders.ID) And
(Forms!Orders![Name]=T_Names.Name));

This seems work all right, but now I want the new names which are not on
list to automatically be added to my T_Names table, so that next time they
also appear on the list.

Is it going to be another update query which compares "Names" values from 2
tables and adds new values to my T_Names table?
I am also not sure that I know exactly the way how to do it.

The other way I was thinking about is instead of using T_Names table for my
combobox, to make some query, which would lookup values in my T_Orders table
in the Names field and select only the unique names, not including
duplicates, so the list for the combobox would be some 20 Names instead of
200.
This "duplicate" thing is also seem to me so complicated.

Can somebody advise me on a better way to organize this?
I would appreciate any help.

Thank you.
Lana

 

[Guest]

Sorry, Jackie L,

I am very stupid and I didn't understand anything of what you've suggested.

Lana,
You have a lot of things going on here. I will try to address them
logically. First, an easy way to populate a text box off a combo box is to
put the following on the After Update event of your Name field (you need to
the combobox set up so that the address is part of the fields listed,
although you can have the width set to zero so that it does not show).

Me.Address = Me.NameField.Column(1)
Me.City = Me.NameField.Column(2)
Me.State = Me.NameField.Column(3)
etc.

(where column 1 represents the second column, counting starts at zero)

Now, in order to populate a name field in the lookup table with a value "on
the fly" you can do it a couple of ways. Doing this based off your orders
table does work if you base the combo box off a Totals query that is grouped
by the Name field. On the after update event of the combo box you need to
put
Me.Requery to repopluate the list.
The problem with this approach is that your drop down will only list
customers who have placed an order at some point and it will not populate the
address field at the same time. An approach I use more often is to have a
button to add a new name, a pop up box will come up where the Name and
Address can be entered. On the On Close event of the pop up form I populate
the fields on the original form and then also requery the combo box. If data
entry needs to be streamlined, you can have the new form open off key strokes
in addition to the mouse click.

Hope this makes sense. Let me know if you need some clarification.

Hi,

I just wanted an advice from somebody:

I've got 2 fields on my form "Orders" - a combobox with Names, which I
select from table T_Names and a textbox for Addresses, which I want to be
filled up automatically from the second column of the same T_Names table when
the "Name" matches. The values that I enter are stored in the T_Orders table.

The point is the Names field is not limited to list and the Addresses field
is not limited to list. So I want to be able to enter any Name and any
Address. But when the name is from the list - I want the address to be filled
into the textbox automatically.

So I used the following update querry for the "on exit" event for "Names"
combobox:

UPDATE T_Orders, T_Names SET T_Orders.Address = T_Names.Address
WHERE (((T_Orders.ID)=Forms!Orders.ID) And
(Forms!Orders![Name]=T_Names.Name));

This seems work all right, but now I want the new names which are not on
list to automatically be added to my T_Names table, so that next time they
also appear on the list.

Is it going to be another update query which compares "Names" values from 2
tables and adds new values to my T_Names table?
I am also not sure that I know exactly the way how to do it.

The other way I was thinking about is instead of using T_Names table for my
combobox, to make some query, which would lookup values in my T_Orders table
in the Names field and select only the unique names, not including
duplicates, so the list for the combobox would be some 20 Names instead of
200.
This "duplicate" thing is also seem to me so complicated.

Can somebody advise me on a better way to organize this?
I would appreciate any help.

Thank you.
Lana

 

[Guest]

I would be happy to send an example if you would like to give me your email
address

Sorry, Jackie L,

I am very stupid and I didn't understand anything of what you've suggested.

Lana,
You have a lot of things going on here. I will try to address them
logically. First, an easy way to populate a text box off a combo box is to
put the following on the After Update event of your Name field (you need to
the combobox set up so that the address is part of the fields listed,
although you can have the width set to zero so that it does not show).

Me.Address = Me.NameField.Column(1)
Me.City = Me.NameField.Column(2)
Me.State = Me.NameField.Column(3)
etc.

(where column 1 represents the second column, counting starts at zero)

Now, in order to populate a name field in the lookup table with a value "on
the fly" you can do it a couple of ways. Doing this based off your orders
table does work if you base the combo box off a Totals query that is grouped
by the Name field. On the after update event of the combo box you need to
put
Me.Requery to repopluate the list.
The problem with this approach is that your drop down will only list
customers who have placed an order at some point and it will not populate the
address field at the same time. An approach I use more often is to have a
button to add a new name, a pop up box will come up where the Name and
Address can be entered. On the On Close event of the pop up form I populate
the fields on the original form and then also requery the combo box. If data
entry needs to be streamlined, you can have the new form open off key strokes
in addition to the mouse click.

Hope this makes sense. Let me know if you need some clarification.

Hi,

I just wanted an advice from somebody:

I've got 2 fields on my form "Orders" - a combobox with Names, which I
select from table T_Names and a textbox for Addresses, which I want to be
filled up automatically from the second column of the same T_Names table when
the "Name" matches. The values that I enter are stored in the T_Orders table.

The point is the Names field is not limited to list and the Addresses field
is not limited to list. So I want to be able to enter any Name and any
Address. But when the name is from the list - I want the address to be filled
into the textbox automatically.

So I used the following update querry for the "on exit" event for "Names"
combobox:

UPDATE T_Orders, T_Names SET T_Orders.Address = T_Names.Address
WHERE (((T_Orders.ID)=Forms!Orders.ID) And
(Forms!Orders![Name]=T_Names.Name));

This seems work all right, but now I want the new names which are not on
list to automatically be added to my T_Names table, so that next time they
also appear on the list.

Is it going to be another update query which compares "Names" values from 2
tables and adds new values to my T_Names table?
I am also not sure that I know exactly the way how to do it.

The other way I was thinking about is instead of using T_Names table for my
combobox, to make some query, which would lookup values in my T_Orders table
in the Names field and select only the unique names, not including
duplicates, so the list for the combobox would be some 20 Names instead of
200.
This "duplicate" thing is also seem to me so complicated.

Can somebody advise me on a better way to organize this?
I would appreciate any help.

Thank you.
Lana

 

[Guest]

Yea, please, do: (e-mail address removed)

I would be happy to send an example if you would like to give me your email
address

Sorry, Jackie L,

I am very stupid and I didn't understand anything of what you've suggested.

Lana,
You have a lot of things going on here. I will try to address them
logically. First, an easy way to populate a text box off a combo box is to
put the following on the After Update event of your Name field (you need to
the combobox set up so that the address is part of the fields listed,
although you can have the width set to zero so that it does not show).

Me.Address = Me.NameField.Column(1)
Me.City = Me.NameField.Column(2)
Me.State = Me.NameField.Column(3)
etc.

(where column 1 represents the second column, counting starts at zero)

Now, in order to populate a name field in the lookup table with a value "on
the fly" you can do it a couple of ways. Doing this based off your orders
table does work if you base the combo box off a Totals query that is grouped
by the Name field. On the after update event of the combo box you need to
put
Me.Requery to repopluate the list.
The problem with this approach is that your drop down will only list
customers who have placed an order at some point and it will not populate the
address field at the same time. An approach I use more often is to have a
button to add a new name, a pop up box will come up where the Name and
Address can be entered. On the On Close event of the pop up form I populate
the fields on the original form and then also requery the combo box. If data
entry needs to be streamlined, you can have the new form open off key strokes
in addition to the mouse click.

Hope this makes sense. Let me know if you need some clarification.

:

Hi,

I just wanted an advice from somebody:

I've got 2 fields on my form "Orders" - a combobox with Names, which I
select from table T_Names and a textbox for Addresses, which I want to be
filled up automatically from the second column of the same T_Names table when
the "Name" matches. The values that I enter are stored in the T_Orders table.

The point is the Names field is not limited to list and the Addresses field
is not limited to list. So I want to be able to enter any Name and any
Address. But when the name is from the list - I want the address to be filled
into the textbox automatically.

So I used the following update querry for the "on exit" event for "Names"
combobox:

UPDATE T_Orders, T_Names SET T_Orders.Address = T_Names.Address
WHERE (((T_Orders.ID)=Forms!Orders.ID) And
(Forms!Orders![Name]=T_Names.Name));

This seems work all right, but now I want the new names which are not on
list to automatically be added to my T_Names table, so that next time they
also appear on the list.

Is it going to be another update query which compares "Names" values from 2
tables and adds new values to my T_Names table?
I am also not sure that I know exactly the way how to do it.

The other way I was thinking about is instead of using T_Names table for my
combobox, to make some query, which would lookup values in my T_Orders table
in the Names field and select only the unique names, not including
duplicates, so the list for the combobox would be some 20 Names instead of
200.
This "duplicate" thing is also seem to me so complicated.

Can somebody advise me on a better way to organize this?
I would appreciate any help.

Thank you.
Lana

 

[Guest]

I'm trying to do something similar and, I think, simpler. I'd like to
prefill the text box "XType" based on what is selected in a combo box called
"LCode". I have a table called "LCodes" that has the following
columns/fields:

LCode
Blah
XType

I have a combo box for the LCode and when I select something from the list I
want XType prefilled. Can you help me?

Lana,
You have a lot of things going on here. I will try to address them
logically. First, an easy way to populate a text box off a combo box is to
put the following on the After Update event of your Name field (you need to
the combobox set up so that the address is part of the fields listed,
although you can have the width set to zero so that it does not show).

Me.Address = Me.NameField.Column(1)
Me.City = Me.NameField.Column(2)
Me.State = Me.NameField.Column(3)
etc.

(where column 1 represents the second column, counting starts at zero)

Now, in order to populate a name field in the lookup table with a value "on
the fly" you can do it a couple of ways. Doing this based off your orders
table does work if you base the combo box off a Totals query that is grouped
by the Name field. On the after update event of the combo box you need to
put
Me.Requery to repopluate the list.
The problem with this approach is that your drop down will only list
customers who have placed an order at some point and it will not populate the
address field at the same time. An approach I use more often is to have a
button to add a new name, a pop up box will come up where the Name and
Address can be entered. On the On Close event of the pop up form I populate
the fields on the original form and then also requery the combo box. If data
entry needs to be streamlined, you can have the new form open off key strokes
in addition to the mouse click.

Hope this makes sense. Let me know if you need some clarification.

Hi,

I just wanted an advice from somebody:

I've got 2 fields on my form "Orders" - a combobox with Names, which I
select from table T_Names and a textbox for Addresses, which I want to be
filled up automatically from the second column of the same T_Names table when
the "Name" matches. The values that I enter are stored in the T_Orders table.

The point is the Names field is not limited to list and the Addresses field
is not limited to list. So I want to be able to enter any Name and any
Address. But when the name is from the list - I want the address to be filled
into the textbox automatically.

So I used the following update querry for the "on exit" event for "Names"
combobox:

UPDATE T_Orders, T_Names SET T_Orders.Address = T_Names.Address
WHERE (((T_Orders.ID)=Forms!Orders.ID) And
(Forms!Orders![Name]=T_Names.Name));

This seems work all right, but now I want the new names which are not on
list to automatically be added to my T_Names table, so that next time they
also appear on the list.

Is it going to be another update query which compares "Names" values from 2
tables and adds new values to my T_Names table?
I am also not sure that I know exactly the way how to do it.

The other way I was thinking about is instead of using T_Names table for my
combobox, to make some query, which would lookup values in my T_Orders table
in the Names field and select only the unique names, not including
duplicates, so the list for the combobox would be some 20 Names instead of
200.
This "duplicate" thing is also seem to me so complicated.

Can somebody advise me on a better way to organize this?
I would appreciate any help.

Thank you.
Lana