Data Entry to Form with repeat data.

[FL]

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and Inspector's
Initials that repeat information from record to record for many data entries.
However, periodically any one of these fields will change and the data
entered will not be the same. So, for ease of data entry how can I have
these fields repeat the data entered until such time the inspector has to
change it? The Product ID and Product Description fields are selected from
drop down list (combo box). The other two fields are manually typed in.
Using Access 2000 in Windows Prof. system.

Thanks,

 

[Dirk Goldgar]

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and Inspector's
Initials that repeat information from record to record for many data
entries.
However, periodically any one of these fields will change and the data
entered will not be the same. So, for ease of data entry how can I have
these fields repeat the data entered until such time the inspector has to
change it? The Product ID and Product Description fields are selected
from
drop down list (combo box). The other two fields are manually typed in.
Using Access 2000 in Windows Prof. system.

I would expect the the Product Description to be an attribute of the
product, and hence not to change unless the Product ID is changed. If that
is so -- and it is the usual case -- then the product Description shouldn't
be stored in this form's recordsource table at all. If you want to see it
in a text box on the form (separate from your combo box), you can base the
form on a query that does an "autolookup". We can discuss that in more
detail in a followup , if you need to.

As for the other information that you want to repeat, one approach is to use
the AfterUpdate event of the form to set default values for the controls
from the current values. For example:

'------ start of example code ------
Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'------ end of example code ------

Note: If those aren't the actual names of the controls on your form, you
must replace them with the correct names.

 

[FL]

Dirk,
Thank you for the suggestions. I will try the AfterUpdate suggestion. As
for the the Prod. Description yes it cooresponds directly with the unique
Product ID. I don't understand your "autolookup" suggestion yet. I do
store both Product Id and Product Description in the one table where form
writes to. These Product ID and Product Description come from another table
that is a list only of these two fields. The combo box pulls from this
list. It would be great if I can solve this because the users are always
asking why do they have to select ID then description each from two different
combo boxes. I don't know how both can be selected at same time.

Thanks again,


--
FL

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and Inspector's
Initials that repeat information from record to record for many data
entries.
However, periodically any one of these fields will change and the data
entered will not be the same. So, for ease of data entry how can I have
these fields repeat the data entered until such time the inspector has to
change it? The Product ID and Product Description fields are selected
from
drop down list (combo box). The other two fields are manually typed in.
Using Access 2000 in Windows Prof. system.

I would expect the the Product Description to be an attribute of the
product, and hence not to change unless the Product ID is changed. If that
is so -- and it is the usual case -- then the product Description shouldn't
be stored in this form's recordsource table at all. If you want to see it
in a text box on the form (separate from your combo box), you can base the
form on a query that does an "autolookup". We can discuss that in more
detail in a followup , if you need to.

As for the other information that you want to repeat, one approach is to use
the AfterUpdate event of the form to set default values for the controls
from the current values. For example:

'------ start of example code ------
Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'------ end of example code ------

Note: If those aren't the actual names of the controls on your form, you
must replace them with the correct names.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[FL]

Dirk,
I just re-read your second suggestion to set the AfterUpdate to set default
values for the controls from the current values. If I do this will the
default values always the same? Or, will the default values change each
time we change the value in the field to a different description?

For example if I set default value of Product line to be "1" will it always
default to line 1 up until the inspector changes a record to reflect he is
now sampling from Line 2? At this point and forward will the new default
value be "2"? If so, this solves my problem.

Thanks again,

--
FL

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and Inspector's
Initials that repeat information from record to record for many data
entries.
However, periodically any one of these fields will change and the data
entered will not be the same. So, for ease of data entry how can I have
these fields repeat the data entered until such time the inspector has to
change it? The Product ID and Product Description fields are selected
from
drop down list (combo box). The other two fields are manually typed in.
Using Access 2000 in Windows Prof. system.

I would expect the the Product Description to be an attribute of the
product, and hence not to change unless the Product ID is changed. If that
is so -- and it is the usual case -- then the product Description shouldn't
be stored in this form's recordsource table at all. If you want to see it
in a text box on the form (separate from your combo box), you can base the
form on a query that does an "autolookup". We can discuss that in more
detail in a followup , if you need to.

As for the other information that you want to repeat, one approach is to use
the AfterUpdate event of the form to set default values for the controls
from the current values. For example:

'------ start of example code ------
Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'------ end of example code ------

Note: If those aren't the actual names of the controls on your form, you
must replace them with the correct names.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[John W. Vinson]

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and Inspector's
Initials that repeat information from record to record for many data entries.
However, periodically any one of these fields will change and the data
entered will not be the same. So, for ease of data entry how can I have
these fields repeat the data entered until such time the inspector has to
change it? The Product ID and Product Description fields are selected from
drop down list (combo box). The other two fields are manually typed in.
Using Access 2000 in Windows Prof. system.

Thanks,

The Product Description field should ideally be stored once and once only, in
the Product table - you shouldn't need to enter it repeatedly!

For the Line and Initials fields, you can set the textbox's DefaultValue
property in the textbox's AfterUpdate event: e.g.

Private Sub txtProductionLine_AfterUpdate()
Me!txtProductionLine.DefaultValue = """" & Me!txtProductionLine & """"
End Sub

That's four consecutive " marks, needed to insert a single " in the string.

 

[Dirk Goldgar]

Dirk,
I just re-read your second suggestion to set the AfterUpdate to set
default
values for the controls from the current values. If I do this will the
default values always the same? Or, will the default values change each
time we change the value in the field to a different description?

The latter.

For example if I set default value of Product line to be "1" will it
always
default to line 1 up until the inspector changes a record to reflect he is
now sampling from Line 2? At this point and forward will the new
default
value be "2"? If so, this solves my problem.

When the form opens, there will be no default value for the field. Then,
the first time the user enters a value for the field (and saves the record),
that value will become the default value for the field until the next time
the the user enters a different value. Then the new value will be the
default.

 

[Dirk Goldgar]

Dirk,
Thank you for the suggestions. I will try the AfterUpdate suggestion.
As
for the the Prod. Description yes it cooresponds directly with the unique
Product ID. I don't understand your "autolookup" suggestion yet. I do
store both Product Id and Product Description in the one table where form
writes to. These Product ID and Product Description come from another
table
that is a list only of these two fields. The combo box pulls from this
list. It would be great if I can solve this because the users are always
asking why do they have to select ID then description each from two
different
combo boxes. I don't know how both can be selected at same time.

Since you have a Products table that stores the [Product Description] for
each product, you shouldn't be storing that description in your form's
recordsource table at all. There shouldn't even be a field for it in that
table. Instead, the value should always be looked up from the Products
table. You can get the form to do this automatically -- what we call an
"autolookup" -- by setting the form's RecordSource property to a query that
joins the two tables. The SQL view of such a query would be something like
this (though I don't know your table and field names):

SELECT
InspectionInfo.*,
Products.[Product Description]
FROM
InspectionInfo INNER JOIN Products
On InspectionInfo.[Product ID] = Products.[Product ID];

On your form, you would simply bind a text box to the [Product Description]
field, and it would automatically display the value for any [Product ID] you
enter on the form. You'd probably want to lock that text box so that the
user can't inadvertently change the description for the product.

 

[FL]

Dirk,
Perfect! This will solve my problem. I will try it out. Thanks so much.

 

[Dirk Goldgar]

Firstly the table underlying the form should have a Product ID column
(field)
but not a Product Description column. The former is a foreign key which
maps
to the relevant row in the Products table, so the Product Description is
known from that. In your form you just need a combo box bound to the
ProductID column, and set up as follows:

ControlSource: [Product ID]

RowSource: SELECT [Product ID], [Product Description] FROM [Products]
ORDER BY [Product Description];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least
as
wide as the combo box.

The user cab then select for the list of product descriptions and the
selected description will show in the control, but its underlying value
will
be the hidden Product ID.

I agree that this is an alternative to the autolookup query I suggested. If
the users are more familiar with product descriptions the product IDs -- and
the descriptions are unique -- then this is a better way. I didn't suggest
it because I didn't want to make those assumptions.

 

[FL]

The Product ID field is not an AutoNumber. It similar to a unique Part
Number. But, for the purpose of the form records collected and our
production line application this Product ID is made repeatedly on any given
day. Likewise, we make other unique products that also can be made any day
based on production needs.

Example: today we made Product ID 1001 and 1007. Tomorrow we will make
Product ID 1001 again and 1003. The following day we may be scheduled to
make only part ID 1007. So, these Product ID's and there unique
descriptions are made repeatedly. But, we can stay on one or two products
for many days too. We switch products any time during the work shift.

Thanks


--
FL

Firstly the table underlying the form should have a Product ID column
(field)
but not a Product Description column. The former is a foreign key which
maps
to the relevant row in the Products table, so the Product Description is
known from that. In your form you just need a combo box bound to the
ProductID column, and set up as follows:

ControlSource: [Product ID]

RowSource: SELECT [Product ID], [Product Description] FROM [Products]
ORDER BY [Product Description];

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least
as
wide as the combo box.

The user cab then select for the list of product descriptions and the
selected description will show in the control, but its underlying value
will
be the hidden Product ID.

I agree that this is an alternative to the autolookup query I suggested. If
the users are more familiar with product descriptions the product IDs -- and
the descriptions are unique -- then this is a better way. I didn't suggest
it because I didn't want to make those assumptions.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

.

 

[FL]

Dirk,

I tried your suggestion for default updating my Production Line and
Inspector Initials fields. But, I get a compile error (Invaid or
unqualified reference).

Here's my code...It highlights the area of .Value in first line.

Private Sub Form_AfterUpdate()
With Me![Production Line Number].DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![QC Inspector Initials].DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub

--
FL

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and Inspector's
Initials that repeat information from record to record for many data
entries.
However, periodically any one of these fields will change and the data
entered will not be the same. So, for ease of data entry how can I have
these fields repeat the data entered until such time the inspector has to
change it? The Product ID and Product Description fields are selected
from
drop down list (combo box). The other two fields are manually typed in.
Using Access 2000 in Windows Prof. system.

I would expect the the Product Description to be an attribute of the
product, and hence not to change unless the Product ID is changed. If that
is so -- and it is the usual case -- then the product Description shouldn't
be stored in this form's recordsource table at all. If you want to see it
in a text box on the form (separate from your combo box), you can base the
form on a query that does an "autolookup". We can discuss that in more
detail in a followup , if you need to.

As for the other information that you want to repeat, one approach is to use
the AfterUpdate event of the form to set default values for the controls
from the current values. For example:

'------ start of example code ------
Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'------ end of example code ------

Note: If those aren't the actual names of the controls on your form, you
must replace them with the correct names.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,

I tried your suggestion for default updating my Production Line and
Inspector Initials fields. But, I get a compile error (Invaid or
unqualified reference).

Here's my code...It highlights the area of .Value in first line.

Private Sub Form_AfterUpdate()
With Me![Production Line Number].DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![QC Inspector Initials].DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub

That's not what I posted. This is what I posted:

Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub

That's a total of six distinct statements, aside from the Sub and End Sub
statements, not counting the blank lines.

Adjusting that for your control names, try this:

'----- start of code -----
Private Sub Form_AfterUpdate()

With Me![Production Line Number]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![QC Inspector Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'----- end of code -----

 

[Clif McIrvin]

Looks like newsreader word wrap got you.

Try:

With Me![Production Line Number]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![QC Inspector Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

Each With ... End With is three lines ....

--
Clif

Dirk,

I tried your suggestion for default updating my Production Line and
Inspector Initials fields. But, I get a compile error (Invaid or
unqualified reference).

Here's my code...It highlights the area of .Value in first line.

Private Sub Form_AfterUpdate()
With Me![Production Line Number].DefaultValue = Chr(34) & .Value &
Chr(34)
End With

With Me![QC Inspector Initials].DefaultValue = Chr(34) & .Value &
Chr(34)
End With

End Sub

--
FL

How can I configure two or more fields on an Access form to update
automatically?

I have Product ID, Product Description, Production Line and
Inspector's
Initials that repeat information from record to record for many
data
entries.
However, periodically any one of these fields will change and the
data
entered will not be the same. So, for ease of data entry how can
I have
these fields repeat the data entered until such time the inspector
has to
change it? The Product ID and Product Description fields are
selected
from
drop down list (combo box). The other two fields are manually
typed in.
Using Access 2000 in Windows Prof. system.

I would expect the the Product Description to be an attribute of the
product, and hence not to change unless the Product ID is changed.
If that
is so -- and it is the usual case -- then the product Description
shouldn't
be stored in this form's recordsource table at all. If you want to
see it
in a text box on the form (separate from your combo box), you can
base the
form on a query that does an "autolookup". We can discuss that in
more
detail in a followup , if you need to.

As for the other information that you want to repeat, one approach is
to use
the AfterUpdate event of the form to set default values for the
controls
from the current values. For example:

'------ start of example code ------
Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'------ end of example code ------

Note: If those aren't the actual names of the controls on your form,
you
must replace them with the correct names.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[FL]

Dirk,

It works great! Thanks again for all your help.


--
FL

Dirk,

I tried your suggestion for default updating my Production Line and
Inspector Initials fields. But, I get a compile error (Invaid or
unqualified reference).

Here's my code...It highlights the area of .Value in first line.

Private Sub Form_AfterUpdate()
With Me![Production Line Number].DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![QC Inspector Initials].DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub

That's not what I posted. This is what I posted:

Private Sub Form_AfterUpdate()

With Me![Production Line]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![Inspector's Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub

That's a total of six distinct statements, aside from the Sub and End Sub
statements, not counting the blank lines.

Adjusting that for your control names, try this:

'----- start of code -----
Private Sub Form_AfterUpdate()

With Me![Production Line Number]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

With Me![QC Inspector Initials]
.DefaultValue = Chr(34) & .Value & Chr(34)
End With

End Sub
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)