Prev: Tabbing creates new record, which I don't want
Next: Form Code to update the table
From: Amelia on 6 Nov 2009 12:37
Here is what I want to accomplish. I have an OrdTbl that holds the [PO#],
[OrderDate], and other fields not neccessary for what I want to do.

Okay, now in my inventory form I have a field [PO#] and [PurchaseDate]
field. I want it to automatically populate the purchase date once I enter the
PO#. I know I am missing some sort of link, but I don't know what it is. If I
do a query to pull the data and then create a form, I get a mismatch error in
the query.

Hopefully this makes some sort of sense to someone and they can help me!

(I am pulling the OrdTbl from our Purchase Order Database into our Inventory
Database if this makes a difference)
From: kismert on 6 Nov 2009 14:58
Sounds like what you really want to do is set the Default Value of the
Purchase Date control to Date(). That will set it to the current date for a
new record.

-Ken
From: kismert on 6 Nov 2009 15:10
Sorry, reading too fast.

Is PurchaseDate the same as OrderDate, or is PurchaseDate a field in the
OrdTbl for an inventory item?

If so, you can use a DLookup, like:
DLookup("[PurchaseDate]","OrdTbl","[PO#]='" & txtPONumber & "'")
where txtPONumber holds the PO on your inventory form.

A deeper question is: is this duplication really necessary? If the PO# is a
unique key to OrdTbl, you can reference all the information in OrdTbl by
joining it to the Inventory table in a query.

-Ken

From: KenSheridan via AccessMonster.com on 7 Nov 2009 15:38
First question is why two separate databases? Having both tables in one
database makes a lot more sense as you can then enforce referential integrity.
A single back end and different front ends for ordering and inventory
purposes would separate the two from the users' perspective if desired, but
allow you to protect the integrity of the data.

If the two tables were incorporated in a single back end and referential
integrity enforced, the other point to be considered is whether the purchase
date will always be the same as the order date for the item in question. If
so then having a purchase date column in the inventory table introduces
redundancy and can be (and moreover should be) deleted as the date is
available at any time by joining the tables in a query or looking it up in
code. If the purchase date can legitimately be independently edited so it
differs from the order date, i.e. the order date is only regarded as a
default for the purchase date, then there is no redundancy. In this case you
can assign the order date value to the purchase date control in your form in
the PO# controls AfterUpdate event procedure. One way to do this would be
use a combo box as the control for the PO#, setting it up as follows:

ControlSource: [PO#]

RowSource: SELECT [PO#], [OrderDate] FROM [OrdTbl] ORDER BY [PO#];

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

If your units of measurement are imperial rather than metric Access will
automatically convert them.

In its AfterUpdate procedure put:

Dim ctrl As Control

Set ctrl = Me.ActiveControl
Me.[PurchaseDate] = ctrl.Column(1)

The Column property is zero based, so Column(1) is the second column, the
hidden OrderDate.

Ken Sheridan
Stafford, England

Amelia wrote:
>Here is what I want to accomplish. I have an OrdTbl that holds the [PO#],
>[OrderDate], and other fields not neccessary for what I want to do.
>
>Okay, now in my inventory form I have a field [PO#] and [PurchaseDate]
>field. I want it to automatically populate the purchase date once I enter the
>PO#. I know I am missing some sort of link, but I don't know what it is. If I
>do a query to pull the data and then create a form, I get a mismatch error in
>the query.
>
>Hopefully this makes some sort of sense to someone and they can help me!
>
>(I am pulling the OrdTbl from our Purchase Order Database into our Inventory
>Database if this makes a difference)

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Tabbing creates new record, which I don't want
Next: Form Code to update the table