Form pulling data from table1 to autofill fields in table2

[Charlie Shaffer]

Hi all, here is what I'm having trouble with today.
I have two separate tables in a db.

Table 1 - UserAcctsTBL
Table 2 - JobTicketsTBL

I have a form that I use to fill in some information for JobTicketsTBL.
JobTicketsTBL is the main table for Network Job Trouble Tickets. I've got it
set up to generate a ticket number and what not. I have a drop-down that I
use to get the reporting user's name from UserAcctsTBL. UserAcctsTBL has the
users office extension, office symbol (identifies where they work) and some
other information. I have a subtable for UserAcctsTBLE that I use for
tracking account activity (password reset, updated computer security training
etc).

What I want to do is select a name using the drop-down (or a combo box if
necessary) on JobTicketAddFRM so that when the name is selected the office
extension and symbol fields will fill in with the correct information. When
the ticket is saved, I want the office ext and symbol information to be saved
to JobTicketsTBL. How do I accomplish this? I'm going to use the
information in a report that will print out looking like a "Job Ticket" based
on the ticket number.

I've seen some similar questions but the table reference names are generic
and have got my head spinning. I figured I asked the question using my own
table names so I'll understand how the answer can be applied both now and in
the future on similar taskings.

 

[Charlie Shaffer]

Okay, I understand what you are saying. However, if the user moves to
another section and I need to pull up the ticket after I've changed their
extension and symbol, I need the information to remain unchanged on the
ticket, even if I pull it up a year later.

Example:

While Mr Smith was at extension x-4210 and Office ABC, he requested help
that generated Job Ticket #2009-02-11. The job was completed and Ticket
#2009-02-11 was closed.
Two months after Mr Smith moved to extension x-4225 and Office XYZ I pull up
the ticket whatever reason. I need the extension and symbol for Mr Smith to
still show as X-4210 and ABC even though these may have changed since the
ticket was originally generated.

On the other hand, can you also show me what you discribed in the event I
need it for another project in the future?

Thanks muchly.

 

[Beetle]

OK, so this is one of the exceptions to the rule. It is acceptable to store
this information in another table if you need it for historical purposes.

You mentioned you were using a combo box to select the name. You can use
that same combo box to provide the additional information you need to store
in the JobTicketsTBL. For example, your current combo box might have a Row
Source like the following;

Select ClientID, FirstName & " " & LastName AS ClientName From UserAcctsTBL

The above Row Source query example would retrieve the ClientID and a
concatenated first and last name (aliased as ClientName) from a table
called UserAcctsTBL. If we take that same query and modify it as follows;

Select ClientID, FirstName & " " & LastName As ClientName, OfficeExt,
ClientSymbol From UserAcctsTBL

Now we have a combo box that retrieves ClientID, ClientName, OfficeExt
and ClientSymbol. Each of these values constitutes a column in the Row Source
query of the combo box. These columns are identified by a numeric index. The
index is zero based, so the first column (ClientID) is Column(0), the second
column (ClientName) is Column(1), etc.

So now, on your form for job tickets let's suppose you have two text boxes
that
are bound to the OfficeExt and ClientSymbol fields in the JobTicketsTBL (we'll
call these text boxes txtOfficeExt and txtClientSymbol respectively). You can
refer to the columns in your combo box to get the values you want to store
in those fields. This would typically be done in the After Update event of the
combo box. The code might look like the following;

Private Sub cboSelectClient_AfterUpdate ()

Me.txtOfficeExt = Me.cboSelectClient.Column(2)
Me.txtClientSymbol = Me.cboSelectClient.Column(3)

End Sub

When you select a Client from the combo box, the appropriate values for that
clients office extension and symbol will be retrieved from the combo box and
stored in the JobTicketsTBL.

You also asked about how to just display information, without necessarily
storing it in another table. You could use the same method as described
above, except that the text boxes on your form would be unbound (not bound
to a field in any table), and they could simply have a Control Source like;

=cboSelectClient.Column(2)

which would just display the value from that column of the combo box in
the unbound text box. However, you need to be aware that this method
(usind unbound text boxes) will not work correctly on a continuous form.

Another alternative (if you don't have a combo box to get values from)
would be to use the DLookup function in the Control Source of an
unbound text box. It might look like;

=DLookup("ClientSymbol", "UserAcctsTBL", "ClientID=" & [ClientID])

Again, this method will have problems if it is a continuous form.

Lastly, you can create a query that retrieves all of the values you need, and
use the query (instead of a table) as the Record Source of your form / report.
The potential drawback to this method is that, depending on how many tables
are involved in the query, it may become read only (not updateable). This
really
wouldn't matter in the case of a report, but for a form it would be a problem.

Keep in mind, there are work arounds for all of the potential drawbacks I
mentioned above, it just requires some extra steps.

 

[Charlie Shaffer]

Beetle,

Just a quick response. I did the things you described tinkering with the
field names and whatnot so they were created using the nameing conventions I
learned when someone taught me about VB. What you showed me worked
perfectly! Thanks for your help.

Charlie
--
"My hair isn''t turning grey, it''s getting singed because my personal CPU is
over-heating."

OK, so this is one of the exceptions to the rule. It is acceptable to store
this information in another table if you need it for historical purposes.

You mentioned you were using a combo box to select the name. You can use
that same combo box to provide the additional information you need to store
in the JobTicketsTBL. For example, your current combo box might have a Row
Source like the following;

Select ClientID, FirstName & " " & LastName AS ClientName From UserAcctsTBL

The above Row Source query example would retrieve the ClientID and a
concatenated first and last name (aliased as ClientName) from a table
called UserAcctsTBL. If we take that same query and modify it as follows;

Select ClientID, FirstName & " " & LastName As ClientName, OfficeExt,
ClientSymbol From UserAcctsTBL

Now we have a combo box that retrieves ClientID, ClientName, OfficeExt
and ClientSymbol. Each of these values constitutes a column in the Row Source
query of the combo box. These columns are identified by a numeric index. The
index is zero based, so the first column (ClientID) is Column(0), the second
column (ClientName) is Column(1), etc.

So now, on your form for job tickets let's suppose you have two text boxes
that
are bound to the OfficeExt and ClientSymbol fields in the JobTicketsTBL (we'll
call these text boxes txtOfficeExt and txtClientSymbol respectively). You can
refer to the columns in your combo box to get the values you want to store
in those fields. This would typically be done in the After Update event of the
combo box. The code might look like the following;

Private Sub cboSelectClient_AfterUpdate ()

Me.txtOfficeExt = Me.cboSelectClient.Column(2)
Me.txtClientSymbol = Me.cboSelectClient.Column(3)

End Sub

When you select a Client from the combo box, the appropriate values for that
clients office extension and symbol will be retrieved from the combo box and
stored in the JobTicketsTBL.

You also asked about how to just display information, without necessarily
storing it in another table. You could use the same method as described
above, except that the text boxes on your form would be unbound (not bound
to a field in any table), and they could simply have a Control Source like;

=cboSelectClient.Column(2)

which would just display the value from that column of the combo box in
the unbound text box. However, you need to be aware that this method
(usind unbound text boxes) will not work correctly on a continuous form.

Another alternative (if you don't have a combo box to get values from)
would be to use the DLookup function in the Control Source of an
unbound text box. It might look like;

=DLookup("ClientSymbol", "UserAcctsTBL", "ClientID=" & [ClientID])

Again, this method will have problems if it is a continuous form.

Lastly, you can create a query that retrieves all of the values you need, and
use the query (instead of a table) as the Record Source of your form / report.
The potential drawback to this method is that, depending on how many tables
are involved in the query, it may become read only (not updateable). This
really
wouldn't matter in the case of a report, but for a form it would be a problem.

Keep in mind, there are work arounds for all of the potential drawbacks I
mentioned above, it just requires some extra steps.
--
_________

Sean Bailey

Okay, I understand what you are saying. However, if the user moves to
another section and I need to pull up the ticket after I've changed their
extension and symbol, I need the information to remain unchanged on the
ticket, even if I pull it up a year later.

Example:

While Mr Smith was at extension x-4210 and Office ABC, he requested help
that generated Job Ticket #2009-02-11. The job was completed and Ticket
#2009-02-11 was closed.
Two months after Mr Smith moved to extension x-4225 and Office XYZ I pull up
the ticket whatever reason. I need the extension and symbol for Mr Smith to
still show as X-4210 and ABC even though these may have changed since the
ticket was originally generated.

On the other hand, can you also show me what you discribed in the event I
need it for another project in the future?

Thanks muchly.