Automatically populate a field based on 2 others

[Darrell Childress]

I have a form on which we log employees' time spent on a certain part.
In a separate table I have the following fields:
Number sono lineno

The field "Number" is an Autonumber field and the employee has no idea
what the value of that field is - in fact, it's a hidden field on the
form. I would like for the employee to be able to type in the sono and
the lineno (they know these values) and have the form automatically
bring in the Number. Is this possible? Thanks,
Darrell

 

[Klatuu]

If you are talking about locating an existing record, you can do that using
the After Update events of sono and lineno. You have to include both so you
know you have both values before you look for the record. First, you will
want to put a function something like this in the form's code module:

Function FindNumber() As Boolean

If IsNull(Me.txtSono) Or IsNull(Me.Lineno) Then
Exit Function
End If

With Me.RecordsetClone
.FindFirst "[sono] = " & Me.txtSono & _
" [lineno] = " & Me.txtLineno
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No Matching Record Found"
End If
End With
End Function

Then in the After Update event of both controls, enter:
=FindNumber() in the After Update event text box in the properties dialog.

 

[Darrell Childress]

I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that using
the After Update events of sono and lineno. You have to include both so you
know you have both values before you look for the record. First, you will
want to put a function something like this in the form's code module:

Function FindNumber() As Boolean

If IsNull(Me.txtSono) Or IsNull(Me.Lineno) Then
Exit Function
End If

With Me.RecordsetClone
.FindFirst "[sono] = " & Me.txtSono & _
" [lineno] = " & Me.txtLineno
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No Matching Record Found"
End If
End With
End Function

Then in the After Update event of both controls, enter:
=FindNumber() in the After Update event text box in the properties dialog.

I have a form on which we log employees' time spent on a certain part. In a
separate table I have the following fields:
Number sono lineno

The field "Number" is an Autonumber field and the employee has no idea
what the value of that field is - in fact, it's a hidden field on the
form. I would like for the employee to be able to type in the sono and the
lineno (they know these values) and have the form automatically bring in
the Number. Is this possible? Thanks,
Darrell

 

[BruceM via AccessMonster.com]

The code module is the code associated with the form. If you open the form
in design view and click View >> Code you the VBA editor will open. On the
right is the code, starting (probably) with Option Compare Database at the
top. Press Ctrl + R to be sure the Project Explorer is open on the left. It
should show the form name highlighted. All of what you see in the right-hand
pane is the form's code module. You can think of it as the code library for
the form.

If you understand the After Update event you may already have seen the form's
code module. The code Klatuu provided goes into the code module. Use your
actual control names in place of txtSono and txtLineNo as needed. The code
(a user-defined function) could go anywhere in the module other than inside
another procedure. To run the code, open the form's property sheet. Click
the event tab, and type =FindNumber() directly on the property sheet at the
After Update event.

I would use Klatuu's method with the RecordsetClone. I would have a
difficult time explaining just why, but if you are interested you could post
a new thread asking about the difference. There are people who could explain
it easily. Regarding your DLookup, there is an extra quote:
And "[lineno]=
should be
And [lineno]=

The DLookup assumes Lineno and Sono are text fields.

I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that using
the After Update events of sono and lineno. You have to include both so you

[quoted text clipped - 31 lines]

 

[Darrell Childress]

Okay, all of that makes sense, I apologize for my ignorance regarding
the code. I should have known what he was referring to.

There's still a major piece of the puzzle missing, however. I am
entering NEW records into a table, tblProgressReport (via a form). The
information that the user has is the sono and the lineno. There is a
SEPARATE master table (tblSO_Items) which contains all of the items in
our system. That table contains the following fields: Number, sono,
lineno. When the user enters the sono and lineno, I would like the form
to look in the tblSO_Items table and bring in the contents of the Number
field. This is why I was trying to use the DLookup feature.

I entered the code supplied by Klatuu, but it doesn't seem to work. I
always get "No Matching Record Found". I assume that's because it's
looking for a matching record in the table in which I'm entering the new
data, but I need it to pull info from a separate table.

The code module is the code associated with the form. If you open the form
in design view and click View >> Code you the VBA editor will open. On the
right is the code, starting (probably) with Option Compare Database at the
top. Press Ctrl + R to be sure the Project Explorer is open on the left. It
should show the form name highlighted. All of what you see in the right-hand
pane is the form's code module. You can think of it as the code library for
the form.

If you understand the After Update event you may already have seen the form's
code module. The code Klatuu provided goes into the code module. Use your
actual control names in place of txtSono and txtLineNo as needed. The code
(a user-defined function) could go anywhere in the module other than inside
another procedure. To run the code, open the form's property sheet. Click
the event tab, and type =FindNumber() directly on the property sheet at the
After Update event.

I would use Klatuu's method with the RecordsetClone. I would have a
difficult time explaining just why, but if you are interested you could post
a new thread asking about the difference. There are people who could explain
it easily. Regarding your DLookup, there is an extra quote:
And "[lineno]=
should be
And [lineno]=

The DLookup assumes Lineno and Sono are text fields.

I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that using
the After Update events of sono and lineno. You have to include both so you

[quoted text clipped - 31 lines]

the Number. Is this possible? Thanks,
Darrell

 

[Klatuu]

Now you have me confused. I thought you wanted to find an existing record.
If you are entering a new record, there is no number to find. Where does
the number you want to add to the record exist?

Okay, all of that makes sense, I apologize for my ignorance regarding the
code. I should have known what he was referring to.

There's still a major piece of the puzzle missing, however. I am entering
NEW records into a table, tblProgressReport (via a form). The information
that the user has is the sono and the lineno. There is a SEPARATE master
table (tblSO_Items) which contains all of the items in our system. That
table contains the following fields: Number, sono, lineno. When the user
enters the sono and lineno, I would like the form to look in the
tblSO_Items table and bring in the contents of the Number field. This is
why I was trying to use the DLookup feature.

I entered the code supplied by Klatuu, but it doesn't seem to work. I
always get "No Matching Record Found". I assume that's because it's
looking for a matching record in the table in which I'm entering the new
data, but I need it to pull info from a separate table.

The code module is the code associated with the form. If you open the
form
in design view and click View >> Code you the VBA editor will open. On
the
right is the code, starting (probably) with Option Compare Database at
the
top. Press Ctrl + R to be sure the Project Explorer is open on the left.
It
should show the form name highlighted. All of what you see in the
right-hand
pane is the form's code module. You can think of it as the code library
for
the form.

If you understand the After Update event you may already have seen the
form's
code module. The code Klatuu provided goes into the code module. Use
your
actual control names in place of txtSono and txtLineNo as needed. The
code
(a user-defined function) could go anywhere in the module other than
inside
another procedure. To run the code, open the form's property sheet.
Click
the event tab, and type =FindNumber() directly on the property sheet at
the
After Update event.

I would use Klatuu's method with the RecordsetClone. I would have a
difficult time explaining just why, but if you are interested you could
post
a new thread asking about the difference. There are people who could
explain
it easily. Regarding your DLookup, there is an extra quote:
And "[lineno]=
should be
And [lineno]=

The DLookup assumes Lineno and Sono are text fields.

I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that
using the After Update events of sono and lineno. You have to include
both so you
[quoted text clipped - 31 lines]
the Number. Is this possible? Thanks,
Darrell

 

[Darrell Childress]

The existing record is in a master table called tblSO_Items. This is a
table which contains all line items from all Sales Orders. I am NOT
adding anything to this table. The relevant fields in this table are
Number, sono, and lineno.

I am adding a new record to a table called tblProgressReport, where we
track the manufacturing time spent on each part. I am entering the sono
and the lineno and then adding other information (time spent,
department, and employee who worked on this item). When I enter the sono
and lineno, I would like the form to go look in tblSO_Items and
automatically pull in the contents of the corresponding Number field
(this is the primary key of tblSO_Items, and is an AutoNumber field)

I am very sorry for the confusion.

Now you have me confused. I thought you wanted to find an existing record.
If you are entering a new record, there is no number to find. Where does
the number you want to add to the record exist?

Okay, all of that makes sense, I apologize for my ignorance regarding the
code. I should have known what he was referring to.

There's still a major piece of the puzzle missing, however. I am entering
NEW records into a table, tblProgressReport (via a form). The information
that the user has is the sono and the lineno. There is a SEPARATE master
table (tblSO_Items) which contains all of the items in our system. That
table contains the following fields: Number, sono, lineno. When the user
enters the sono and lineno, I would like the form to look in the
tblSO_Items table and bring in the contents of the Number field. This is
why I was trying to use the DLookup feature.

I entered the code supplied by Klatuu, but it doesn't seem to work. I
always get "No Matching Record Found". I assume that's because it's
looking for a matching record in the table in which I'm entering the new
data, but I need it to pull info from a separate table.

The code module is the code associated with the form. If you open the
form
in design view and click View >> Code you the VBA editor will open. On
the
right is the code, starting (probably) with Option Compare Database at
the
top. Press Ctrl + R to be sure the Project Explorer is open on the left.
It
should show the form name highlighted. All of what you see in the
right-hand
pane is the form's code module. You can think of it as the code library
for
the form.

If you understand the After Update event you may already have seen the
form's
code module. The code Klatuu provided goes into the code module. Use
your
actual control names in place of txtSono and txtLineNo as needed. The
code
(a user-defined function) could go anywhere in the module other than
inside
another procedure. To run the code, open the form's property sheet.
Click
the event tab, and type =FindNumber() directly on the property sheet at
the
After Update event.

I would use Klatuu's method with the RecordsetClone. I would have a
difficult time explaining just why, but if you are interested you could
post
a new thread asking about the difference. There are people who could
explain
it easily. Regarding your DLookup, there is an extra quote:
And "[lineno]=
should be
And [lineno]=

The DLookup assumes Lineno and Sono are text fields.

Darrell Childress wrote:
I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that
using the After Update events of sono and lineno. You have to include
both so you
[quoted text clipped - 31 lines]
the Number. Is this possible? Thanks,
Darrell

 

[BruceM via AccessMonster.com]

You say you want to bring in the contents of *the* number field. That
suggests there is only one for a given combination of sono and lineno. It
would really help to know something of the database's purpose and structure,
including relationships. You say there is a master table, and you mention
three fields in the table. If there are no other fields it is hard to see
the purpose of the master table. If there are other fields it would help to
understand something of the real-world situation. It would help in any case.

Klatuu's code did not work because he assumed you were looking within the
current recordset. I did not understand that you were using DLookup for a
table (recordset) other than the current one.

It sounds as if you should be storing the Number field only, and displaying
the data contained in the record that has that Number value. As a general
example of this, probably unrelated to your current situation, if you are
adding an employee name to a record you would look up the employee by way of
a combo box or list box that gets its Row Source from the employee table, but
store only the employee number. You can then display FirstName, LastName etc.
that are related to that employee number. The details depend on your
specific situation.

Okay, all of that makes sense, I apologize for my ignorance regarding
the code. I should have known what he was referring to.

There's still a major piece of the puzzle missing, however. I am
entering NEW records into a table, tblProgressReport (via a form). The
information that the user has is the sono and the lineno. There is a
SEPARATE master table (tblSO_Items) which contains all of the items in
our system. That table contains the following fields: Number, sono,
lineno. When the user enters the sono and lineno, I would like the form
to look in the tblSO_Items table and bring in the contents of the Number
field. This is why I was trying to use the DLookup feature.

I entered the code supplied by Klatuu, but it doesn't seem to work. I
always get "No Matching Record Found". I assume that's because it's
looking for a matching record in the table in which I'm entering the new
data, but I need it to pull info from a separate table.

The code module is the code associated with the form. If you open the form
in design view and click View >> Code you the VBA editor will open. On the

[quoted text clipped - 52 lines]

 

[Klatuu]

How is your form set up?
Is it a form/subform?
Does tblSO_Items have the lineno and sono as fields?

The existing record is in a master table called tblSO_Items. This is a
table which contains all line items from all Sales Orders. I am NOT adding
anything to this table. The relevant fields in this table are Number,
sono, and lineno.

I am adding a new record to a table called tblProgressReport, where we
track the manufacturing time spent on each part. I am entering the sono
and the lineno and then adding other information (time spent, department,
and employee who worked on this item). When I enter the sono and lineno, I
would like the form to go look in tblSO_Items and automatically pull in
the contents of the corresponding Number field (this is the primary key of
tblSO_Items, and is an AutoNumber field)

I am very sorry for the confusion.

Now you have me confused. I thought you wanted to find an existing
record. If you are entering a new record, there is no number to find.
Where does the number you want to add to the record exist?

Okay, all of that makes sense, I apologize for my ignorance regarding
the code. I should have known what he was referring to.

There's still a major piece of the puzzle missing, however. I am
entering NEW records into a table, tblProgressReport (via a form). The
information that the user has is the sono and the lineno. There is a
SEPARATE master table (tblSO_Items) which contains all of the items in
our system. That table contains the following fields: Number, sono,
lineno. When the user enters the sono and lineno, I would like the form
to look in the tblSO_Items table and bring in the contents of the Number
field. This is why I was trying to use the DLookup feature.

I entered the code supplied by Klatuu, but it doesn't seem to work. I
always get "No Matching Record Found". I assume that's because it's
looking for a matching record in the table in which I'm entering the new
data, but I need it to pull info from a separate table.


BruceM via AccessMonster.com wrote:
The code module is the code associated with the form. If you open the
form
in design view and click View >> Code you the VBA editor will open. On
the
right is the code, starting (probably) with Option Compare Database at
the
top. Press Ctrl + R to be sure the Project Explorer is open on the
left. It
should show the form name highlighted. All of what you see in the
right-hand
pane is the form's code module. You can think of it as the code
library for
the form.

If you understand the After Update event you may already have seen the
form's
code module. The code Klatuu provided goes into the code module. Use
your
actual control names in place of txtSono and txtLineNo as needed. The
code
(a user-defined function) could go anywhere in the module other than
inside
another procedure. To run the code, open the form's property sheet.
Click
the event tab, and type =FindNumber() directly on the property sheet at
the
After Update event.

I would use Klatuu's method with the RecordsetClone. I would have a
difficult time explaining just why, but if you are interested you could
post
a new thread asking about the difference. There are people who could
explain
it easily. Regarding your DLookup, there is an extra quote:
And "[lineno]=
should be
And [lineno]=

The DLookup assumes Lineno and Sono are text fields.

Darrell Childress wrote:
I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate
event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that
using the After Update events of sono and lineno. You have to
include both so you
[quoted text clipped - 31 lines]
the Number. Is this possible? Thanks,
Darrell

 

[Darrell Childress]

Yes, tblSO_Items has sono and lineno as fields. Currently, I have it
setup as a form/subform, but here is what happens. The user will type in
the sono into a combobox and the subform will then display all of the
line items for that order. I have a button beside each line item, and
when the user clicks on the button, it will then populate the Number
field on the form with the contents of the Number field on the subform

I have put a screenshot here:
http://www.sterlingblower.com/sterling/pr_data.jpg

In the screenshot, I have entered SO 81517, and it brought in the line
items below on the subform (Record Source = tblSO_Items). Then, the user
would click on the button with the arrow (on the left side) for the
corresponding line item (lineno) and I have written code for OnClick
which simply sets the value of Number and lineno fields on the main form
with the values from the subform. This is working fine, but when you
have to enter 300 of these or more a day, it can become cumbersome and
would go a lot quicker to enter the sono and lineno field and have it
automatically bring in the Number field instead of having to stop, grab
the mouse, sometimes scroll down if there are more than 20 items or so

How is your form set up?
Is it a form/subform?
Does tblSO_Items have the lineno and sono as fields?

The existing record is in a master table called tblSO_Items. This is a
table which contains all line items from all Sales Orders. I am NOT adding
anything to this table. The relevant fields in this table are Number,
sono, and lineno.

I am adding a new record to a table called tblProgressReport, where we
track the manufacturing time spent on each part. I am entering the sono
and the lineno and then adding other information (time spent, department,
and employee who worked on this item). When I enter the sono and lineno, I
would like the form to go look in tblSO_Items and automatically pull in
the contents of the corresponding Number field (this is the primary key of
tblSO_Items, and is an AutoNumber field)

I am very sorry for the confusion.

Now you have me confused. I thought you wanted to find an existing
record. If you are entering a new record, there is no number to find.
Where does the number you want to add to the record exist?

Okay, all of that makes sense, I apologize for my ignorance regarding
the code. I should have known what he was referring to.

There's still a major piece of the puzzle missing, however. I am
entering NEW records into a table, tblProgressReport (via a form). The
information that the user has is the sono and the lineno. There is a
SEPARATE master table (tblSO_Items) which contains all of the items in
our system. That table contains the following fields: Number, sono,
lineno. When the user enters the sono and lineno, I would like the form
to look in the tblSO_Items table and bring in the contents of the Number
field. This is why I was trying to use the DLookup feature.

I entered the code supplied by Klatuu, but it doesn't seem to work. I
always get "No Matching Record Found". I assume that's because it's
looking for a matching record in the table in which I'm entering the new
data, but I need it to pull info from a separate table.


BruceM via AccessMonster.com wrote:
The code module is the code associated with the form. If you open the
form
in design view and click View >> Code you the VBA editor will open. On
the
right is the code, starting (probably) with Option Compare Database at
the
top. Press Ctrl + R to be sure the Project Explorer is open on the
left. It
should show the form name highlighted. All of what you see in the
right-hand
pane is the form's code module. You can think of it as the code
library for
the form.

If you understand the After Update event you may already have seen the
form's
code module. The code Klatuu provided goes into the code module. Use
your
actual control names in place of txtSono and txtLineNo as needed. The
code
(a user-defined function) could go anywhere in the module other than
inside
another procedure. To run the code, open the form's property sheet.
Click
the event tab, and type =FindNumber() directly on the property sheet at
the
After Update event.

I would use Klatuu's method with the RecordsetClone. I would have a
difficult time explaining just why, but if you are interested you could
post
a new thread asking about the difference. There are people who could
explain
it easily. Regarding your DLookup, there is an extra quote:
And "[lineno]=
should be
And [lineno]=

The DLookup assumes Lineno and Sono are text fields.

Darrell Childress wrote:
I AM talking about locating an existing record and adding the value of
the "Number" field to the form (table, actually).

I don't know where to put the code you gave me, can you please explain
what you mean by the "form's code module"? I do understand the
AfterUpdate event.

In the meantime, I have tried experimenting with the DLookup function,
which almost works. Here's what I have entered in the AfterUpdate
event

Private Sub lineno_AfterUpdate()
Number = DLookup("[Number]", "tblSO_Items", "[sono]='" &
Forms![sbfProgressReport]![sono] & "'" And "[lineno]='" &
Forms![sbfProgressReport]![lineno] & "'")
End Sub

It gives me a Run-time error '13':
Type mismatch

If I enter criteria manually in the AfterUpdate, such as
Number = DLookup("[Number]", "tblSO_Items", "[sono]='81517' And
[lineno]='3'")

then it works great.

If you are talking about locating an existing record, you can do that
using the After Update events of sono and lineno. You have to
include both so you
[quoted text clipped - 31 lines]
the Number. Is this possible? Thanks,
Darrell