Copy one field from one table to another

  • Thread starter Thread starter bhp
  • Start date Start date
B

bhp

I've been using Access for a while, but I've never learned VBA. Now I'm
trying to create a new database that requires VBA for some of the forms. Hope
someone out there can help.

I have a table that is used to track requests. The requests are connected to
items in another table, which are tracked by serial number. On the form for
the item requests, I want to be able to click a button, access a form with
the item listed ( based on its serial number), and copy the date shipped
field from the request to the date shipped field in the item's record.

I can't connect the two tables for multiple reasons. One, the requests come
in but the item isn't linked to it until later, so I can't add the request if
it is part of the item's record. Two, the requests also include what item
they shipped back, which is from the same table.

For the button so far, I used the button creation wizard to access the items'
records. Here is the code it created:

Private Sub UpdateSent_IEPU_Click()
On Error GoTo Err_UpdateSent_IEPU_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "IEPU history"

stLinkCriteria = "[IEPU_SN]=" & "'" & Me![IEPU Sent] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_UpdateSent_IEPU_Click:
Exit Sub

Err_UpdateSent_IEPU_Click:
MsgBox Err.Description
Resume Exit_UpdateSent_IEPU_Click

End Sub

If anyone does have any idea for making the button work, or for simplifing
the tables, I'd really appreciate it.
 
I've been using Access for a while, but I've never learned VBA. Now I'm
trying to create a new database that requires VBA for some of the forms. Hope
someone out there can help.

I have a table that is used to track requests. The requests are connected to
items in another table, which are tracked by serial number. On the form for
the item requests, I want to be able to click a button, access a form with
the item listed ( based on its serial number), and copy the date shipped
field from the request to the date shipped field in the item's record.

I can't connect the two tables for multiple reasons. One, the requests come
in but the item isn't linked to it until later, so I can't add the request if
it is part of the item's record. Two, the requests also include what item
they shipped back, which is from the same table.
Click to expand...

I don't see any reason why you can't connect the tables. How is the
REquest table structured? Do you have multiple fields for the serial
number in the request table?

Note that the item is NOT stored in the form (and you can't even
assume that the form is open; and opening the form won't give you any
better access to the item or the date than you already have to the
table. After all, data's stored in TABLES, not in forms!

I suspect that a DLookUp() in the table, looking up the date given the
IEPU_SN value, will be your solution - but since I don't know the
structure of the table I don't know how this DLookUp would be
constructed.

Which table is the date that you want stored in? Which field within
that table? How does the IEPU_SN value identify which record in that
table contains the date?

John W. Vinson[MVP]
 
I don't see any reason why you can't connect the tables. How is the
REquest table structured? Do you have multiple fields for the serial
number in the request table?
Click to expand...

The Request table has two IEPU_SN values; the one sent out and the one
returned (always two different ones). Also, like I said, the request comes
in and must be tracked BEFORE there is a part set to be sent. I can't update
the part record before I know the part record needs to be updated.
Note that the item is NOT stored in the form (and you can't even
assume that the form is open; and opening the form won't give you any
better access to the item or the date than you already have to the
table. After all, data's stored in TABLES, not in forms!
Click to expand...

I know data is stored in tables, not forms. I never said it was. All users
will only be accessing the forms, not the tables. What I'm worried about is
simplifying the way data is entered through the form.
I suspect that a DLookUp() in the table, looking up the date given the
IEPU_SN value, will be your solution - but since I don't know the
structure of the table I don't know how this DLookUp would be
constructed.

Which table is the date that you want stored in? Which field within
that table? How does the IEPU_SN value identify which record in that
table contains the date?
Click to expand...

IEPU_SN is not the date value; it's the part serial number value. That is
what is used to lookup the part record in that table (IEPU History). Looking
up the part record from the Request form already works using the code shown
before (this code was built using the command button wizard). What I need is
a way to also transfer the date information is in the Date_Sent field in the
request table to the ShippedFromLocation field in the IEPU History table.
 
What I need is
a way to also transfer the date information is in the Date_Sent field in the
request table to the ShippedFromLocation field in the IEPU History table.
Click to expand...

If the Form can pull it out of the table to display it, then DLookUp
can pull it out of the table.

What I'm saying is that opening a separate form to get a date value is
unnecessary and extremely complex.

I still do not understand the structure of your Request table, but if
the date which you wish to transfer is in that table, and if you can
identify (from the current form) which record in the request table
contains that date, you can use DLookUp to return that date, without
opening ANY new form.

You can call DLookUp from some suitable Form event (again, I don't
know what should trigger this - maybe the AfterUpdate event of the
item number field?) and "push" the looked-up value into the
ShippedFromLocation (hmmm... storing a Date/Time value in a Location
field? odd but it's your database...) field.

John W. Vinson[MVP]
 
DLookup is to view the value, correct? I need to COPY the value. The two
tables are sets of semi-related historical data, which I cannot combine into
one table, as I have said before.

What I need is to copy a date from one table into another. I am trying to to
this from two forms. From the request, I need a button that, when pressed,
will copy a displayed date, open another form, and paste the copied date. I
need the second form to open for further editing. I have to copy the date
because there is not a clear way to relate the records in the two tables. If
this sounds odd, I'm just trying to explain this briefly. I can tell you that
this is how I need it, and that if I tried doing it a different way, it would
screw up other items I have to track.

FYI: The ShippedFromLocation is as in "Shipped from the location on this
date".

If you don't understand what I'm saying, then just say what I'm not
explaining wel, and I can try to give you what you need. There's no need to
insult me and how I am doing this. I have to do it this way based on the
requirements and the information I was given to track. Unfortunately, that
limits my ability to make this into a simple, single table. Hence the need
for two tables with information that crosses over.

If you can help, I'd appreciate it. If you can't, fine. But I have a deadline
to meet, and I'm not in the mood for "sage" advice.
 
DLookup is to view the value, correct? I need to COPY the value.
Click to expand...

No, that is not correct.

DLookUp - used in VBA code - will RETRIEVE the value. You can then do
whatever you want with it, including copying it into a Form, updating
a Table, displaying it, whatever you need to do.
The two
tables are sets of semi-related historical data, which I cannot combine into
one table, as I have said before.
Click to expand...

And I'm not suggesting that you do so.
What I need is to copy a date from one table into another. I am trying to to
this from two forms.
Click to expand...

I understand that is what you are trying to do. My assertion is that
this is an INCORRECT way to do so.
From the request, I need a button that, when pressed,
will copy a displayed date, open another form, and paste the copied date. I
need the second form to open for further editing. I have to copy the date
because there is not a clear way to relate the records in the two tables. If
this sounds odd, I'm just trying to explain this briefly. I can tell you that
this is how I need it, and that if I tried doing it a different way, it would
screw up other items I have to track.

FYI: The ShippedFromLocation is as in "Shipped from the location on this
date".

If you don't understand what I'm saying, then just say what I'm not
explaining wel, and I can try to give you what you need.
Click to expand...

Well... ok... I don't understand what you're saying. It sounds like
you are opening a Form with the sole purpose of retrieving a single
date value from a Table. I am trying to explain that there are *other
and better ways* to retrieve a single date value from a table, ways
which will work (as your form approach apparently is not doing).
There's no need to
insult me and how I am doing this.
Click to expand...

I apologise if my manner came (or is coming) across as insulting.
That's not my intent. I'm simply trying to say "Oh, I'm sorry, you're
heading down the wrong street if you are trying to get to Maxim's,
it's down this direction".
I have to do it this way based on the
requirements and the information I was given to track. Unfortunately, that
limits my ability to make this into a simple, single table. Hence the need
for two tables with information that crosses over.
Click to expand...

That I understand. That is what DLookUp is good for. It can retrieve
information from one table and store it in a different table. That's
what it's FOR.
If you can help, I'd appreciate it. If you can't, fine. But I have a deadline
to meet, and I'm not in the mood for "sage" advice.
Click to expand...

Ok, let's try this:

Private Sub cmdGetDate_Click()
Dim dtTheDate As Date
dtTheDate = DLookUp("[DateSent]", "[IEPU History]", "[IEPU_SN] = " & _
Me![IEPU_SN])
Me!txtDatefield = dtTheDate
End Sub

This will look up the value of DateSent from the table IEPU History,
using the value of the control named IEPU_SN as a criterion on the
same named field in the IEPU History table; that result will then be
stored into the textbox named txtDatefield.

Can this be adapted to do what you are trying to accomplish?

John W. Vinson[MVP]
 
Thanks for the help. Sorry if I was a bit snippy.

Unfortunately what you suggested is not working. I guess it's because I'm not
explaining this right.
Well... ok... I don't understand what you're saying. It sounds like
you are opening a Form with the sole purpose of retrieving a single
date value from a Table. I am trying to explain that there are *other
and better ways* to retrieve a single date value from a table, ways
which will work (as your form approach apparently is not doing).
Click to expand...

What is happening is a form is open, and through this form a part request has
already been entered. The part request is now being updated with the part SN
sent and the date it was sent. The form will also be used again to enter the
SN of the part returned and the date it was returned. Before this point, the
date and SN information is not part of the request record.

So, I now have a date in one table (IEPU Requests) that needs to be entered
into another table (IEPU Information). I can't simply share the date field,
which it sounded like you were suggesting. This is also why I don't think the
DLookup will work, at least that's how it sounds if I am reading the VBA help
file correctly (although I might be reading it correctly, but they didn't
write it correctly, which has happened to me before). There can be multiple
records per part, based on the amount of movement. One part, for example, has
been transferred seven times, but there is not a part request for any of the
moves. If DLookup is used to view data in the IEPU Information table based on
the IEPU_SN fields in the IEPU Information and IEPU Requests tables, I still
don't see how I can use it to copy the date from the Date_Sent field in the
IEPU Requests table to the ShippedtoDate field in the IEPU Information table.
Does that make sense?

So currently in the work flow, the date and part SN have just been entered
through the IEPU Requests form, and the ShippedtoDate field in the IEPU
Information table is still blank for the most recent record of that
particular part SN. While just having the date be copied over once entered,
either automatically or after pressing a button, would be okay, I want to
have the form for the part information open because there are changes to make
to that record. For example, when a part is returned, we need to enter why
the part was returned (usually there is some problem with it) or we may need
to update the serial numbers of sub-parts connected ot the main part. With a
part that was sent out or returned, we need to update the parts current
location, as well as any other changes to the record. This requires the
creation of a new record in the IEPU Information table. This table is based
on the location of a part (i.e., each record for a particular part SN because
of the time and location fields; other information may or may not be the same)
.. I only want the information being performed through a form for end-user
simplicity. The information is currently in Excel, and clean data was quickly
corroded because people did not use each field properly.
Ok, let's try this:

Private Sub cmdGetDate_Click()
Dim dtTheDate As Date
dtTheDate = DLookUp("[DateSent]", "[IEPU History]", "[IEPU_SN] = " & _
Me![IEPU_SN])
Me!txtDatefield = dtTheDate
End Sub

This will look up the value of DateSent from the table IEPU History,
using the value of the control named IEPU_SN as a criterion on the
same named field in the IEPU History table; that result will then be
stored into the textbox named txtDatefield.
Click to expand...

I tried adapting the code you gave me unsuccessfully. As I said before, I
don't know VBA. I've learned a little, but not enough to fully know how I can
adapt what you gave me, if it can be adapted. My first post details the
filtering process to go from the request form to the information form (again,
it was built by the button command wizard in the design view of the form). I
know you can use the "Me!" (method? parameter? not sure of what it's called.
Can't find a name for it anywhere) as a way to copy data from one field to
another, and I have one code which copies information from the currently
viewed IEPU record into a new record in the IEPU Information form. However, I
don't know how to use it when going from one table to another, and looking at
your code I can't figure it out either.

I know this is not the best way of building a database, but it is the best
way based on what I have to record. I tried making basic information a single
record, with location changes a separate table, but that removes the ability
to track previous repair information. I tried making that separate, but the
only static data is the part SN. I then tried combining the part SN and the
request, but like I said, the request information is too different from the
part SN information, and the workflow precludes entering in request
information when the SN is unknown.

Again, thanks for the help. Sorry if I was rude, but this has been driving me
nuts, and I haven't had the best of luck with getting questions answered on
some other Access forums.
 
Back
Top