Building a Form/subform that will autofill fields from some records from 2 independant databases

[CountryLane]

I'm fairly new to access and I'm trying to figure out how to streamline it for our companies specific needs. We are a manufacturer primarily, but we also do a good deal of field work which often entails installing the products we manufacture. We primarily sell our products to our network of 200+ dealers, but we also have an in-house local retail sales department.

The first database is for our dealers which has basic info like name, address, phone, email, etc.

Our second DB is a list of the products we make and who is buying them along with specific product details like size, color, material. The key in this DB is the PO# which is an autonumber.

The third DB is for our home installation work which includes customer contact info, site address, and product info/job details.



Most of the time we will be installing products from our second DB - so I would like to have a form for entering data into the third DB that would autofill fields such as Dealer and Product size, color, etc. IF I use a PO# from the second DB. Basically I want it to autofill all the duplicate info when there is duplicate info. If its a stand alone install that doesn't involve one of our products then I'll just input everything manually without a PO#.

[rpeare]

Where will the data entry take place? Will all the databases be able to 'see' one another?

If, for instance, all the data entry occurs on the server at your company you could link in the PO table from your second database and provide the list of purchase order numbers in a combo box, if that field is populated then you can perform a lookup of the basic PO information or simply rely on a link to pull the information at the time you generate reports/invoices since your data will be readily identifiable by either the lack or presence of the PO number.

[CountryLane]

All DB are on the same server. One thing that I should have mentioned is that we want to use the third DB to actively schedule our installations. Here's an image of what I'd like to do using a master form for production and a subform for the install. When I select the PO# in the drop down box in Installation Entry I'd like those couple of fields to copy over. How do I perform a lookup so that each field will populate automatically? And, is there a way to make the subform appear only when the CLW Install checkbox is checked. Hopefully the image is clear enough for you to make out what's going on. I really appreciate your input.

[rpeare]

I started writing code and realized something....

Why are you copying all the information from the PO to the installation record in a form/subform arrangement?

Do you have multiple installation records for each Shop Order (main record)?

What is your link between the main form/subform if it's the CLW PO # you don't need to copy the information at all you can just pull it at the time you need to run the report or, in this case, build the subform on a query so it appears as though the data is entered but really it's just accessing the data from the existing PO.

[CountryLane]

The subform is not linked, it's just there. I don't know what the best way to link it would be. We have installation jobs without the product or shop order so I do need a separate DB for installs - so far we have no install records in access. Trying to figure out how to couple these things together.

I have to enter each order we receive into access and it would be convenient for me to be able to enter all the job info on one screen or form. Maybe one out of every five orders that I enter will be an install. But there will be other installs that don't involve the products in which case they will be entered into the install DB by someone else. That person will also need to see the installs I'm entering so he can schedule and group jobs together in close proximity to each other based on a zip code lookup that I'm working on. We've been using the P&P DB for a year and have been putting all our install work in a separate excel sheet. Prior to 2014 we did everything in excel. So it's very possible that I'm going about this the wrong way, but if that's the case I may be too close to it to get a clear picture of what I need to do. Thanks again.

[CountryLane]

Do you have multiple installation records for each Shop Order (main record)? - Sometimes we do have to go back to correct a mistake in which we'll want a separate entry for that in the install db

[rpeare]

Ok, so if I understand this correctly in your original screen shot you just want part of the information that appears on the 'main' form to be copied to your 'subform'

My suggestion is this. Do not use the form/subform for this application.

I'm enclosing a sample database using a principle called 'auto lookup'. This principle works ONLY if you are connecting to the 'lookup' table via the primary key.

When you select a PO number the fields from that table appear to be populated, but in fact they are NOT being stored, they are simply looking up the values from your PO table.


CountryLane.zip