VBA for entering record or passing field values between forms

[lefty2cox]

I'm about to try and sort out writing some code that will allow me to enter a record, ultimately into a table. Maybe with a button or some event. In my limited knowledge, I see two avenues and was wondering which would be better.



I want to take information from FormOrigin which is based on TableOrigin and create a record in TableTarget. I typically view TableTarget through SubFormTarget which is the child of FormTarget. I know I can either do this by sending the info to the SubFormTarget from FormOrigin, or I can send it directly to TableTarget using recordset code, which I would need to brush up on. Which is the preferred method, or does it matter, or would you recommend another method. I don't have a problem with having both forms open at the time, FormOrigin and FormTarget, which contains SubForm Target.

Thank you.

[CJ_London]

Preferred method depends on the requirement of the application. Sounds like you are not binding your form to a table which can make things more complicated but in principle the method would be to use the control after update or form before update event to assign the value. Code might be something like

me.subformnamr.form.controlname=me.mycontrolname

Note it is generally bad practice to store the same value in multiple locations

[lefty2cox]

I'm trying to create a situation where I can add parts to an order from inventory but I don't want to add every part to inventory. Most parts will be entered manually...special order if you will. Sometimes, I'd like to add one from inventory to the partsInOrder table occasionally. But I see what you mean. There will be duplicate data. I do enjoy this but it seems I spend most of my development time starting over. I think it's because there is so much downtime between my projects.

[lefty2cox]

On a side note. I'm subscribed to my posts but don't get notification via email. Nothing in my spam either. I'm wondering why I'm not getting notifications.

[CJ_London]

I'm subscribed to my posts but don't get notification via email

just check that in settings>my account, you have set Default Thread Subscription Mode to instantly via email

I'm trying to create a situation where I can add parts to an order from inventory but I don't want to add every part to inventory.

this would normally be done via a combo in your subform

I spend most of my development time starting over

Are you familiar with database normalisation? if not, google it to find out more. You should start your project by developing your tables - queries forms, reports come later

[lefty2cox]

I thought I was but I'll revisit it and report back. Thank you.

[lefty2cox]

I still struggle with this. I think I understand it but it's like an onion.

I have a friend. We buy equipment to refurbish and sell. I want to be able to track what it is, who bought it, the parts that went into it, who paid for the parts and who put labor into the equipment. I want to then sell it and calculate the profit each person should receive considering the cost of the equipment, the cost of parts, labor and the selling price. The reason I'm hung up on inventory is that we've started keeping an inventory of some parts. But most parts are I guess you would call "special order" and would not be inventoried. I'm trying to track the inventory parts as well, who paid for them, etc. I'd like to upload a file but I'm not even to that point yet. I'm kind of just putting stuff together and trying it out...trying to refresh my memory of all the stuff I used to know, plus trying to learn new stuff.

[CJ_London]

But most parts are I guess you would call "special order" and would not be inventoried.

the part becomes part of inventory as soon as it arrives.

Standard practice is to have a parts table, a transactions table and a transaction type table - something like this

tblParts
partPK
PartName

if you have a lot of parts that are 'one off' perhaps include a flag in the parts table so they can be excluded from standard inventory reporting/listing


tblTranTypes
tranTypePK
TrantypeName (e,g, Order, receipt, issued, reserved, stock adjustment, etc)
Multiplier (1 for stock added, -1 for stock issued)

tblTransactions
tranPK
PartFK
TranTypeFK
tranDate
Qty (always positive, depending on transaction type multiplying by the multiplier will convert to negative when required)

With regards price, this might be in your parts table or transactions table - usually the latter

You may also want a 'documents' table to store information such as order numbers, supplier, ordered by, issued to, etc, or you might include in your transactions table - depends on how detailed you need to be

[lefty2cox]

I think I get it. I'll throw something together with some data and watch it in action. That usually helps me wrap my brain around things. Thank you.

[lefty2cox]

Regarding the multiplier in tblTranTypes, would I just use a combobox with a value list and row source of "1,-1" ?

[CJ_London]

Regarding the multiplier in tblTranTypes, would I just use a combobox with a value list and row source of "1,-1" ?

you can do, also make it required. Or use an option group, there are plenty of ways.

[lefty2cox]

I'm having a hard time figuring out how to connect parts to my machines. Should I be doing through tbltransactions or directly to tblParts?

[lefty2cox]

This maybe? Or am I missing it?

[Micron]

Doesn't look right to me, assuming I understand the purpose. Try thinking of it this way when deciding whether or not field and joins are correct. For tblMachineParts, every record will have a PK id value and fk values for machine and transaction - and nothing else. So what purpose does it serve other than bridging between transactions and machine? If a machine is worked on, isn't the activity related to the machine, so transactions should have machine fk in it and forget the junction table? That table name suggests it's a marriage of machine and parts yet there's no relationship to parts table.

Does a buyer only buy machines or is the activity of purchasing parts also to be recorded? If so, I can't see how you can capture that somebody ordered something without recording it against the machine as a transaction. Maybe that's what you want, but it seems to me that means consumables or miscellaneous parts (e.g. electrical connectors) couldn't be recorded unless the whole package was charged to one machine.

When you think you've got it right, copy the db and try entering test data in the tables. It can be a bit difficult because you'll have to create a record in one and then remember the pk so that you can enter it as fk value elsewhere. Or you can use form wizard to generate forms to some extent but I wouldn't spend much time with the garbage designs you'll get. For me, they'd just be for testing data entry and I'd likely start over with the saved copy - assuming all went well with the test. Otherwise, you've discovered table design wasn't right, in which case you don't want to waste a whole lot of time designing forms and reports that won't be of any use later.

[lefty2cox]

I think I understand what you're saying. But let me add some details. Right now, and probably forever for the purposes of this database, there are only two buyers, my partner and myself. The reason i put us in a table was to avoid data redundancy. In other words, I'm trying to track who buys the parts and who buys the machines. The end user of the parts is always the machines. I guess you could look at the machines as the customer when it comes to parts. I'm trying to connect the parts to the machine though. This may already be taken care of in the suggestion from CJ but I'm having trouble seeing it. Everything hinges on the machine. I will not be selling parts to anyone. They will either live in inventory or be issued to a machine. I know this is a weird scenario and apologize if this complicates things.

I was initially going to add machineFK to transactions to track it but was concerned that only some of the transactions would have a machineFK. In other words, if I received parts the machineFK would be blank or 0. I wasn't sure if that was ok.