VBA for entering record or passing field values between forms

[CJ_London]

So for the purposes of this db, a machine can be treated as a part

So my suggestion still stands - from your schema in post #13, you certainly don't need a machineparts table and probably not the machines table - the machine relevant fields (sales/serial number etc) can be included in the parts table and left blank for ordinary parts. Your transactions table would have an extra FK field for part records pointing to the transactionPK of the machine 'purchase' transaction.



I don't have time to create what I'm describing - if you upload a copy of your db (compact then zip it), I'll give it a go

[Micron]

probably not the machines table - the machine relevant fields (sales/serial number etc) can be included in the parts table and left blank for ordinary parts.

You would have a lot of nulls rather than repeat the serial/model info for every part record?? It seems to me that the major purpose of the db is to track everything about the machine from the time of purchase to the time of sale. Certainly warrants a machine table in my mind.

[CJ_London]

it is a matter for the OP - just saying that is the way I would do it (and I did say probably). From my understanding machines/equipment are all one offs. Null takes up no space in the db and you have simpler relationships and fewer forms to design, albeit with a bit more work to disable/hide controls not required. Don't know what type of machines we are talking about - lawnmowers? domestic appliances? tooling machines? any and all? but there has been no mention of needing to keep the construction design/specification in the db for future reference which is why I said probably. Effectively a machine is just another part, only difference between it and a part is the fact it has a sale transaction. In fact the only machine field that needs to be added to the parts table is serial number. Labour can be treated as a part in this respect.

[lefty2cox]

Both are correct. The machines are one-offs but I'd like to keep some detail on them. They are snow blowers. Things like model, serial, cost, sale price, date purchased, date sold and how many hours it took to repair is the stuff I'm looking to track on the machines. This is information that may come in handy is a similar machine comes in again. The ultimate goal is the be able to pull up a machine and see all of the above plus what parts went into the machine, who paid for the parts. All for calculating profit and how much each partner is due at the end of the transaction. Inevitably, I'm going to have unused parts accumulating, which is why I also wanted to track the inventory of parts. My partner or I may buy extra of something that we know is a common part or buy a part that we ultimately don't use on a machine.

I'm going to try to upload the file. Feel free to do anything you'd like to it as this is the ground floor. There really isn't any data in the file. This is more of a layout I've been putting together to show you guys. Sorry if I've been unclear throughout this process. I know this database is catering to a specific need that isn't common. Thank you.

[CJ_London]

That covers a lot more that you originally said.

If you order a part then don't use it - you need to know which make/model the part relates to - so in that situation you would need a machine table and also a machineFK field in the part table, but gets more complicated if a part can also be used on other models - you need a many to many table. Appreciate things like bolts and washers are not specific so you could leave the machineFK field blank in those situations

[lefty2cox]

The good news is no. As far as inventory goes, I don't need to know which machine the item fits and if I do, I can just include that in the description of the part. I just need to know that I have 5 of them in stock, their part number, part name and price. I just need to know that say 1 of the them went on a particular machine for and tracking purposes. The remaining ones in stock will just be like typical stock. Later, I may add them to a machine.

[lefty2cox]

The main purpose of the database is this: We acquire and then sell equipment, mostly snow blowers right now. One comes in, parts are ordered, put on the machine and then the machine is sold. Because I have a partner, I want to track who bought it, what parts went on it, who paid for the machine and who paid for the parts. Once it's sold, I want to process that information and figure out who is owed what. The inventory module is a nice add on that will let me track and use parts that end up hanging around or parts that we decide to purchase in quantity to make the operation more efficient.

[lefty2cox]

One option I was thinking of was to include a machineFK in tblTransactions to link any parts to the machine but wasn't sure if having nulls in that table was ok.

[Micron]

I don't imagine it's any different than having them there as elsewhere. When you query for charges against a machine, the null fk records (e.g. for sundry items) won't show. Want to know what you buy that isn't charged to a particular machine? Query where fk field Is Null.

[lefty2cox]

I'm not against any of these ideas. But some I'm having an easier time wrapping my brain around. I think having a machine table is necessary because everything is hinging on that. I'm not going to sell parts. And I may buy a machine that doesn't need anything, parts or labor. I would just track the identifying numbers, sale price, purchase price and buyer.

My initial database had a machine table and other supporting tables to normalize. Parts that went into a machine where entered into tblParts and linked with a foreign key for MachineID. The inventory portion for parts was kind of an add on to handle the parts that were unused or the extra ones purchased for future use. Then I thought it would be nice to automate the process of entering them into the parts table without having to do it manually.

[lefty2cox]

But I am intrigued by the idea of treating a machine as a part. I guess I could have a relationship between the parts table and another table that handles details about a machine. tblMachineDetails maybe? This way, not all parts will have a record in tblMachineDetails, only the machines. Am I understanding correctly or am I just getting more confused?

[orange]

You/we/readers really need to understand the business processes and flow to get the tables and relationships designed and tested. It seems you may be a little bit too early to be involved with forms.

Can you flesh out your general business description with some details?
We acquire and then sell equipment, mostly snow blowers right now. One comes in, parts are ordered, put on the machine and then the machine is sold. Because I have a partner, I want to track who bought it, what parts went on it, who paid for the machine and who paid for the parts. Once it's sold, I want to process that information and figure out who is owed what. The inventory module is a nice add on that will let me track and use parts that end up hanging around or parts that we decide to purchase in quantity to make the operation more efficient.

I have this preliminary list gleaned from the thread dialog, but you could add the details that only you and your partner know.

Our company buys,refurbishes and sells Machines.(in some cases no refurbish is needed)
Machines can be of different types, but are mainly snowBlowers (at the moment)
Our Company has 2 Partners
A Partner buys one or more Machines (from a Seller?)
The Purchase Price/Cost and DatePurchased are recorded
A Partner identifies/estimates the labor and Parts required to refurbish a Machine
A Partner orders the Part(s) to refurbish a Machine
A Partner may use a Part from Inventory in a refurbishment
A Partner pays the Part(s) to refurbish a Machine
Each Machine is associated with a manufacturer/brand and model
Each Machine has a serial
A Partner orders consummables(nuts/bolts/washers..) for general usage
A Partner may order common Part(s) for inventory (not specific to a refurbishment)
A Partner records the Labor used to complete a refurbishment
A Partner sells a Machine to a Buyer
The Sale Price and Date of Sale and Buyer Details are recorded


Calculations are required to determine:
what parts and labor went into the machine (refurbishment)
which Partner performed the labor
which Partner paid for the parts
how much each partner is due at the end of the refurbishment
what parts are in inventory (available for future refurbishments)
what models are the parts in inventory associated with

Hope this helps with the business rules to design and vet your tables and relationships.

[lefty2cox]

You're 100% correct. I'll add a couple of points in the event they simplify things.

-I'm the only one who will be using the database and will perform all the data entry.
-I will also perform all estimating and labor. My partner will do all of the buying and selling of machines. We will both purchase parts from suppliers.
-Our compensation is taken from the profit after cost plus parts. After sale price minus purchase price minus parts, and after each are compensated for each of those, the remaining will be considered profit and divided 50/50. My labor hours are solely for calculating what my share translated into as an hourly rate.

I know that this may be a bit short-sighted as many may think that planning for otherwise would be wise. If having the ability to work outside of what I just described doesn't complicate the development too badly, I'm not against expanding the capability. But we (partners) each have our areas of expertise and I don't see that changing in the future.

I hope this simplifies things rather than complicating. But what you (orange) described is exactly what I'm trying to do.

Thank you for articulating it better than I could.

[lefty2cox]

I just had a thought that may be inherent in your design but I didn't mention. I would like to have a notes table connected by relationship to the machine table, if there ends up being a machine table. I had one in my original design. It had only 2 fields, date and note. I would use this as I progressed through repairs. Doesn't have to be done that specific way, it's just the way I had done it.

The way I was imagining the database before we all started speaking was kind of like a computer or any other repair shop. Where you would have "tickets". Except because these are one offs, the Machine would act as customer, machine and ticket all at once. Not sure if that's the right way of thinking about it.

Thanks.

[orange]

Yes, conceptually. Quite common to have a Notes/ Comments table to record "things to remember"/"something I learned"/"key point for reference"/...etc...(any aspect, topic, comment you feel is worth recording...)
DateOfNote,Subject/Details, Author.
Can be pretty loose when only 2 people involved. When more people involved, or different/broader subject matter, you could have a Category to group Comments, but not likely needed in a 2 person operation.