Date on subform to equal that of the Parent

[laser02910]

I have a date on my subform (TransactionDate) that I don't want to have shown
but I want it to be filled in by the date (OrderDate) on the main form. I've
tried the following-

Private Sub TransactionDate_AfterUpdate()

Me!TransactionDate = Me.Parent!OrderDate
Me.Requery

End Sub

among other attempts. Could someone please point me in the right direction??

 

[laser02910]

Also, how do I hide the field so it does not have to be tab'ed over or
"sized" out of view??

 

[BruceM]

I'm not quite clear on what you need to accomplish, but for one thing if the
date is in the main form's record source there is no need to store it in the
subform's record source. From a different angle, the After Update event will
run only after something in that control has changed (been updated). If you
run the same code in the subform's Got Focus event or the After Update event
of another control on the subform or the subform's Before Update event or
something like that it should work. Another point is that if the control is
hidden in this case it really doesn't need to be there. You can upadate the
field directly.
But back to my first point: There is probably no need to copy a value from
the main form to the subform.

 

[BruceM]

To hide a control, set its visible property to No (on the control's property
sheet, which you get to by clicking the control in design view, then click
View > Properties). To eleiminate it from the tab order, set Tab Stop to No
(also on the property sheet). But if it will never be seen and is not part
of a calculation or otherwise used by other controls or calcualtions, you
shouldn't need the control at all.

 

[laser02910]

Thank you for replying so quickly Bruce. You may be correct that I don't need
the date field to be copied, but I can't find a way around this problem. For
inventory purposes I am keeping all transactions having to do with products
in one table and all other details having to do with the customers orders in
another. Thus I have a TransactionDate which denotes all in-coming as well as
out-going transactions for the product and an OrderDate which just indicates
when a product is sold. Since the OrdersForm references Orderstbl and
Companytbl in the Ordersqry and OrderDetails (subform) references Productstbl
and InventoryTransactionstbl for the OrdersDetailsqry I'm not sure how to
have the dates work as one or be included in one of the Queries. Ultimately I
want the OrderDate to be used in my Invoice and TransactionDate to be the
same and only shown when I view my product details. I hope this clears up
what I'm trying to accomplish and/or if I am going about it the wrong way.

 

[BruceM]

I don't quite follow what you need to do, but I suspect your design could use
some tightening. When you say "the OrdersForm references Orderstbl and
Companytbl in the Ordersqry" are you saying that the record source for
OrdersForm is Ordersqry, which is based on Orderstbl and Companytbl? If so,
that may not be the correct approach. I would imagine that each order is
associated with a single company, and that each company can have many orders.
In that case there would be a one-to-many relationship between Orderstbl and
Companytbl, but they would not be combined in a query. If the idea is
something like that you select a company from a list, and the address and so
forth are filled in automatically, you could use a subform for the company
information. Another subform could be for the order details. The primary
key from Orderstbl would be the foreign key for both tables.
A question here is what needs to happen if a company gets a new name or
address. When you go back and look at the order do you need to see the
current company address, or do you need to see the address at the time the
order was prepared? If the former you definitely should relate the tables to
each other and procced along lines like I have described. If the latter you
will need to store the values from Companytbl in Orderstbl. To do that I
would be inclined to use the After Update event of the Company combo box
(assuming you are selecting the company from a combo box on OrdersForm) to
populate fields with hidden columns from the combo box row source, but I have
to acknowledge I am not sure that is the best approach.
Your subform record source may have some of the same issues as the main form
with combining tables into a record source query.
It would help if you posted a brief description of relevant table fields,
and the relationships of fields to each other. Then, describe just what
needs to appear on the Orders form. You can certainly keep track of
inventory by counting the quantity of various products, but you would
probably do that separately from the Orders form.
After reading your reply I may need to suggest that you start a new thread,
because some of this may be beyond my skill level (specifically, the
inventory tracking element of your project), but I expect I can help steer
you in the right direction.

 

[laser02910]

You are correct that the OrdersForm is based on the Ordersqry based on the
Companytbl and Orderstbl. This is the same as in Northwind. I like how the
whole system works and the Orders table can have many different Ship To
addresses including- the current, former, or address of a lease company that
did the actual purchase, with the one Company associated to it the Order. If
the company address changes the CompanyID does not and all previous Orders
still have the addresses for future reference.
Maybe it would help to see the table fields that are involved.

Company Table
CompanyID
CoAddress
CoCity
CoState
CoZip
CoCountry
CoPhone

Orders Table
OrderID
CompanyID
EmployeeID
OrderDate
RequiredDate
ShipDate
ShipMethodID
ShipCost
ShipName
ShipAttn
ShipAddress
ShipCity
ShipState
ShipZip
ShipCountry

Inventory Transactions Table
TransactionID
ProductID
OrderID
PurchaseOrderID
ServiceID
TransactionDate
Warranty
TransactionDescription
UnitPrice
Discount
UnitsOrdered
UnitsRecieved
UnitsSold
UnitsServicedOut
UnitsServicedIn
UnitsWarrantyOut
UnitsWarrantyIn
UnitsShrinkage

Products Table
ProductName
ProductDescription
SupplierID
UnitPrice
UnitsInStock
UnitsOnOrder
ReorderLevel
Discontinued

Product Details Table
ProductDetailsID
ProductID
DateIn
SerialNumber
ProductDetailsNotes

As you can see the Inventory Transactions can be used in several different
forms. Orders (Products out), Purchace Orders (Products Ordered), Warranty
Issues (Products in and out), and Service (Products in and out) This gives me
a running total at all times of my products. That is why the TransactionDate
is necessary. The OrderDate is also necessary as I see it for records. Does
this help? I thank you for your continued help. At the very least it helps me
to know if I'm way out in left field or not.

 

[BruceM]

Had I known where your original inquiry was going I would not have attempted
to answer. I do see why you combined the tables as you did in the query. I
don't know if my earlier suggestions had any relevance at all, or if you even
tried them. If transaction date is necessary, why not just enter it? Would
a default date (such as today's date) work? If transaction date is the same
as order date and you need to copy it you can do so by attaching the code you
originally posted to an event that actually runs. The After Update event of
a text box is not the way to put data into that text box. Until the text box
has data or the data changes there is nothing to update, and the event will
not run.
For calculating totals in a query, have you looked at Help on that topic?
"Perform Calculations in a Query" is the Answer Wizard text that will get you
there.
In Northwinds the Ship To address is just copied from the Customer Table to
the Orders table, but it can be changed on the form. By putting it onto the
Orders form (and into the Orders table) then it is a part of that record, no
matter if the company name or address changes. I do not find the Northwinds
database to be especially helpful. In the case of keeping track of product I
cannot figure out how that happens. If I had a half day or so to wade
through the bloated monstrosity (did I mention I don't like Northwinds?)
maybe I could find it, but there is way too much going on in there to figure
it out without a roadmap. It's almost as bad as trying to learn Access by
reading the Help files.
That's all I can offer. I urge you to start a new thread. Make it clear
that you are asking about inventory tracking. When posting the table
structure it is enough to list your key fields and then a brief description
of the rest of the fields (e.g. Company Details, rather than address, city,
etc.). Also, when responding, keep the thread intact. It is usually
simplest to respond when the whole thread is visible in one window.

 

[laser02910]

Thank you for trying Bruce! I have opted to hide the field and it’s updated
to the Order date. Sometimes they enter the orders in, days or even weeks,
after the order has been placed. This is a habit that I can’t break so I
leave it to the user to fill in the Order date and keep their records
"clean". I’m still relatively new to Access and I don’t get enough practice
to be as knowledgeable as I need to be. Thanks again!

 

[Jody :o)]

I know this has been a long time ago, but I'm facing this same problem in my
database I've created to keep track of my daughter's homeschool grades. Did
you ever get this figured out?

Thanks,
Jody )

 

[Jody :o)]

Hello,

I think this might be a fairly easy thing to do, although I am unable to
figure this out. I keep track of my daughter's homeschool grades on a
database I created. I have a mainform where I enter the date of her
attendance and in it I also keep track of her subjects/grades in a subform.
I don't want to have to keep typing in the date for every grade I put in for
the day, nor do I always enter the grade for each day.

Is there a way that I can just enter in the date in my mainform once and it
automatically enter the date into my subform?

Thanks for your time. I don't know what I would do if I didn't have this
group to turn to.

FYI, I use AT_ID as the linked child and parent fields.

Jody )

 

[Rick Brandt]

Hello,

I think this might be a fairly easy thing to do, although I am unable
to figure this out. I keep track of my daughter's homeschool grades
on a database I created. I have a mainform where I enter the date of
her attendance and in it I also keep track of her subjects/grades in
a subform. I don't want to have to keep typing in the date for every
grade I put in for the day, nor do I always enter the grade for each
day.

Is there a way that I can just enter in the date in my mainform once
and it automatically enter the date into my subform?

Thanks for your time. I don't know what I would do if I didn't have
this group to turn to.

FYI, I use AT_ID as the linked child and parent fields.

As has already been stated you put one date in the main form and THAT BECOMES
the date for all the subform records (without having to copy it).

Take the sales order example. Order header in the main form and items ordered
in the subform. They are linked by order number. There is no reason to copy
the CustomerID into the ItemsOrdered table because it can be retrieved from the
Orders table via the linked field.

The same is true in your case. When you create a query or report where you need
to see the date associated with each grade you can pull that from your main
table. There is absolutely no reason to have that data redundantly stored in
the Grades table.

 

[Jody :o)]

I finally today GET IT and I'm very excited and grateful for everyone's
help!!!!

I'm sorry, it must be frustrating for you experts to try to make us novices
get it. I do realize that and very much appreciate your patients!!

YIPPEEEEEEEEE It works!!

Jody ) ) ) )