Autopopulate second form

[Nancy]

I posted this under Forms, but got no responses. Maybe I can get some help
under Macros. I am pretty sure I can do this with the where clause, but I am
a confused.

I have the following:
T_Orders
T_OrderDetails
Q_Orders
Q_OrdersDetailsExtended
F_PurchaseOrder
Subform for F_PurchaseOrder is F_OrderDetails

F_CoreInformation

I now have the F_PurchaseOrder that lists my main purchase order info: i.e.
PO#, Shipping, Order Date, Vendor, etc.

My subform lists all the detail info of my purchase order: i.e.
nomenclature, part number, qty, serial number (if applicable), Unit cost,
Core Return required, and warranty info.

My Core Return is now a check box (I had a yes/no, but after rereading
Allenbrowne.com again, chose to go with just a check box) If the check box is
activated, a core return is required. When I click to activate this
checkbox, I have an event procedure, using a macro, to open
F_CoreInformation. This is now working fine. Everything is being saved in
the tables as it should.

On my F_CoreInformation form, it lists the following fields:

OrderID
Core ID
Nomenclature
PartNumber
Serial number
Vendor
Return date
Shipping
etc.

Most of this information required on the F_CoreInformation is listed in the
F_PurchaseOrder & F_OrderDetails. When I activate the core return check box
and the F_CoreInformation opens up, I would like the basic info to auto
populate the F_CoreInformation . Then, I can manually enter the remaining
information that applies as it becomes available. For instance, the core may
not be returned for 2-3 weeks. I need to be able to come back and complete
the rest of the form at a later date. Does this make sense and can it be
done. If so how do I do it?

Thank you

 

[David Benyo via AccessMonster.com]

Nancy,

Yes, pre-population can be done. However, it sounds like your table structure
might need a bit of attention/normalization. If I read this correctly, you're
storing the same data in two or more tables.

However, perhaps you have a valid reason for this setup that I'm not getting.
So in the design of F_CoreInformation, select a text box like Nomenclature.
Open its Properties and find "Default Value" under the Data tab. In the
Default Value area, reference the form's field you're trying to get to
autopopulate...i.e. [Forms]![F_PurchaseOrder].[F_OrderDetails].[Form]!
[Nomenclature]

This will only prepopulate New Records. Once the record is saved, you can
change this data to whatever you want.

Good luck.

 

[Nancy]

David,

Thanks for the response. I went back and took another look at my tables and
saw what you mentioned about table structure. I made a few changes to that.
I followed your advice and I now have the second form autopopulating. Next
question.

All records on the purchase order form are autopopulating to the
F_CoreInformation form. I need to limit which records go onto the form. On
my F_OrdersDetail(subform of F_PurchaseOrder) I have a check box. When the
check box is clicked it activates the F_CoreInformation form to open. But
all the records show in the F_CoreInformation. Does not matter if the box is
checked or not. I need to limit which records populate the F_CoreInformation
by the check box. When the check box is activated I want the form to open
and autopopulate just that record of information. We may use 50 purchase
orders and out of that number we may only have 5 items that require a core
return. I need to be able to track these core returns on a seperate form.
Once the basic info is populated to this form and stored it may be a week or
so before the core is returned to the vendor. At that time I need to open
this F_CoreInformation and complete the form with the return date, serial
number, shipping methods, etx. I think I can limit it with a where clause in
the Open form macro, but have not been able to figure out the correct clause
statement.

Thanks for your help and patience.

Nancy,

Yes, pre-population can be done. However, it sounds like your table structure
might need a bit of attention/normalization. If I read this correctly, you're
storing the same data in two or more tables.

However, perhaps you have a valid reason for this setup that I'm not getting.
So in the design of F_CoreInformation, select a text box like Nomenclature.
Open its Properties and find "Default Value" under the Data tab. In the
Default Value area, reference the form's field you're trying to get to
autopopulate...i.e. [Forms]![F_PurchaseOrder].[F_OrderDetails].[Form]!
[Nomenclature]

This will only prepopulate New Records. Once the record is saved, you can
change this data to whatever you want.

Good luck.

I posted this under Forms, but got no responses. Maybe I can get some help
under Macros. I am pretty sure I can do this with the where clause, but I am
a confused.

I have the following:
T_Orders
T_OrderDetails
Q_Orders
Q_OrdersDetailsExtended
F_PurchaseOrder
Subform for F_PurchaseOrder is F_OrderDetails

F_CoreInformation

I now have the F_PurchaseOrder that lists my main purchase order info: i.e.
PO#, Shipping, Order Date, Vendor, etc.

My subform lists all the detail info of my purchase order: i.e.
nomenclature, part number, qty, serial number (if applicable), Unit cost,
Core Return required, and warranty info.

My Core Return is now a check box (I had a yes/no, but after rereading
Allenbrowne.com again, chose to go with just a check box) If the check box is
activated, a core return is required. When I click to activate this
checkbox, I have an event procedure, using a macro, to open
F_CoreInformation. This is now working fine. Everything is being saved in
the tables as it should.

On my F_CoreInformation form, it lists the following fields:

OrderID
Core ID
Nomenclature
PartNumber
Serial number
Vendor
Return date
Shipping
etc.

Most of this information required on the F_CoreInformation is listed in the
F_PurchaseOrder & F_OrderDetails. When I activate the core return check box
and the F_CoreInformation opens up, I would like the basic info to auto
populate the F_CoreInformation . Then, I can manually enter the remaining
information that applies as it becomes available. For instance, the core may
not be returned for 2-3 weeks. I need to be able to come back and complete
the rest of the form at a later date. Does this make sense and can it be
done. If so how do I do it?

Thank you

 

[David Benyo via AccessMonster.com]

Nancy,

Glad the first part is working. Now onto the second part...there are many
options to solve this. I'll give you two:

1) Assuming F_CoreInformation ONLY opens when the checkbox is clicked, you
can hard-code the recordsource using a where clause in the recordsource. I'm
not sure how your recordsource reads so I can't tell you exactly what it
should say...maybe something like

SELECT T_Purchases.PurchaseID, T_Purchases.Field1, T_Purchases.Field2,
T_Purchases.Field3, T_Orders.OrderID, T_Orders.Field4, T_Orders.Field5 FROM
T_Purchases, T_Orders WHERE (([T_Purchases].[PurchaseID] = [Forms]!
[F_PurchaseOrder].[PurchaseID]) AND ([T_Orders].[OrderID] = [Forms]![F-
PurchaseOrder].[F_OrderDetails].[Form].[OrderID]));



2) When the checkbox is checked, you have code that opens F_CoreInformation.
There's a filter option already built within that code. When it's blank, all
records are returned. Create the filter (similar to above) and when the form
opens, the records will be filtered to your criteria. You'd also need to make
sure the FilterOnLoad of the form is set to Yes. You're filter might look
something like:

"(([Field1] = " & Forms!F_PurchaseOrder.PurchaseID & ") AND ([Field2] = " &
Forms!F_PurchaseOrder.F_OrderDetails.Form.OrderID & "))"

This is all off the top of my head and not tested. It should give you a
starting point to go from. Let me know how this works for you. I would tend
to use option 1, but that's just my preference.

Dave

David,

Thanks for the response. I went back and took another look at my tables and
saw what you mentioned about table structure. I made a few changes to that.
I followed your advice and I now have the second form autopopulating. Next
question.

All records on the purchase order form are autopopulating to the
F_CoreInformation form. I need to limit which records go onto the form. On
my F_OrdersDetail(subform of F_PurchaseOrder) I have a check box. When the
check box is clicked it activates the F_CoreInformation form to open. But
all the records show in the F_CoreInformation. Does not matter if the box is
checked or not. I need to limit which records populate the F_CoreInformation
by the check box. When the check box is activated I want the form to open
and autopopulate just that record of information. We may use 50 purchase
orders and out of that number we may only have 5 items that require a core
return. I need to be able to track these core returns on a seperate form.
Once the basic info is populated to this form and stored it may be a week or
so before the core is returned to the vendor. At that time I need to open
this F_CoreInformation and complete the form with the return date, serial
number, shipping methods, etx. I think I can limit it with a where clause in
the Open form macro, but have not been able to figure out the correct clause
statement.

Thanks for your help and patience.

Nancy,

[quoted text clipped - 64 lines]