Automatic Data Entry

[Guest]

I am trying to update a field in a form automatically based on the field
prior to it. For example When I enter a tent number such as 2501 I want the
location of that then number Verot School Road to automatically update in the
Location Field of the form. Both fields are from one table that contain only
tent numbers and their location name.

I have tried DLookup, but I get no data in my location field. Any help
would be greatly appreicated.

 

[Joseph Meehan]

I am trying to update a field in a form automatically based on the
field prior to it. For example When I enter a tent number such as
2501 I want the location of that then number Verot School Road to
automatically update in the Location Field of the form. Both fields
are from one table that contain only tent numbers and their location
name.

I have tried DLookup, but I get no data in my location field. Any
help would be greatly appreicated.

I don't think you want to do that. If I understand what you want, that
would mean you are storing redundant data. If every 2501 relates to the
same location, then you want a table with the numbers and locations. So
when you use 2501 in whatever place you may use it, that form, query report
etc. can just reference that look up table and add in the address as needed.

 

[Pat Hartman \(MVP\)]

The answer is different depending on whether you need to actually store the
location because you need its value to be historically static or whether you
can look up the location and always view the current location regardless of
whether you are looking at an old record or a new one.

1. Storing the value:
Use a combo that includes the location field. it is not necessary for the
field to be visible so you can make its length 0 to hid it in the
ColumnWidths property. In the AfterUpdate event of the combo, add a single
line of code:
Me.Location = Me.YourComboName.Column(2)
The number in .Column(x) represents the position of the location field in a
zero-based array. Therefore, position 1 = .Column(0), position 2 =
..Column(1), etc.
Unless you need to allow manual overrides of the location field, you should
set its locked property to yes and its tabStop property to No to prevent the
value from being changed and to prevent the cursor from tabbing into the
field at all.

2. Referencing the current value:
Create a query that joins the main table with the lookup table and select
all the fields you are currently using from the main table plus the location
value from the lookup table. Use this query as the RecordSource for your
form. As long as you didn't change any column names, you can just replace
the existing value with the name of the new query. Then to avoid
accidentally updating the location from this form, set the locked property
to yes and the tabStop property to No.

Keep in mind that in most situations, option #2 is the technically correct
solution. Do not store duplicate data unnecessarily. You can easily obtain
lookup data by using a query that joins to the lookup table as the
RecordSource for your forms and reports.

 

[Guest]

The table base for the form is called Assigned Repack Numbers. When our tent
operators return fireworks they put a lable with a bar code on the box. We
use the bar code number for traking. I need to assign each bar code to a
specific tent number and inidcate the price level. In my form I have used a
combo box for the data entry person to select the correct tent number - that
much be attached to the box number. I am wanting the Location name to pop up
as a double check that they have selected the correct tent number. If base
this off a query, will my tent number although it is from the look up table
still be assigned to each box number?

 

[Guest]

It works very well, thank you
--
Lynette

The answer is different depending on whether you need to actually store the
location because you need its value to be historically static or whether you
can look up the location and always view the current location regardless of
whether you are looking at an old record or a new one.

1. Storing the value:
Use a combo that includes the location field. it is not necessary for the
field to be visible so you can make its length 0 to hid it in the
ColumnWidths property. In the AfterUpdate event of the combo, add a single
line of code:
Me.Location = Me.YourComboName.Column(2)
The number in .Column(x) represents the position of the location field in a
zero-based array. Therefore, position 1 = .Column(0), position 2 =
..Column(1), etc.
Unless you need to allow manual overrides of the location field, you should
set its locked property to yes and its tabStop property to No to prevent the
value from being changed and to prevent the cursor from tabbing into the
field at all.

2. Referencing the current value:
Create a query that joins the main table with the lookup table and select
all the fields you are currently using from the main table plus the location
value from the lookup table. Use this query as the RecordSource for your
form. As long as you didn't change any column names, you can just replace
the existing value with the name of the new query. Then to avoid
accidentally updating the location from this form, set the locked property
to yes and the tabStop property to No.

Keep in mind that in most situations, option #2 is the technically correct
solution. Do not store duplicate data unnecessarily. You can easily obtain
lookup data by using a query that joins to the lookup table as the
RecordSource for your forms and reports.

 

[Guest]

I am trying the store value option for another form. I want to type in an
product description and have the item # pop up in another field, Yes I do
need to store it as it is connected to a box number (order #). But when I do
the
Me.InternalItemNo = Me.ItemDescription.Column(2) - nothing happens.

Any suggestions?