Copy value of primary key on current record

[PPCEngineering]

Hello everyone,



First of all, thanks ahead of time to everyone on this board who takes the time to respond to questions from people like me! I couldn't function without you!

I have a fairly run-of-the-mill database which keeps track of my manufacturing work orders, packing slips and routers (work instructions for shop floor workers). Due to my busy schedule, I can only update and modify the database to work better during my free time, when I actually feel like working on it. As such, I am essentially a complete beginner when it comes to Microsoft Access (2010 x64).

What I am trying to accomplish seems so incredibly simple, but I swear I am going to tear all of my hair out if I don't figure it out soon. Right now I manually enter all of the data into a work order, either from a customer purchase order or a verbal order over the phone or by email. The work order is a main form containing customer information with a subform containing one record for each product on the purchase order. These records contain the part number, description, quantity, price, etc. Once I have the work order created, I need to create a router for each product on the work order. This will contain both the customer data AND the product data, and is used as a set of instructions for the workers on the shop floor to create the product. On to the first problem...

I want to simply copy all of the customer data and product data from the form and subform, and create a router for each product containing the appropriate information from each record. I have been successful in copying all of the customer data and transferring it to a new record on the routers form, but the subform data is proving difficult. Each work order has a "Work Order Number" primary key which is bound to an autonumber primary key on the subform. So, each record (many) in the subform is bound to a single work order (one). I have been trying to copy the value of the "Work Order Number" to be able to search the subform table and copy the appropriate data using a loop, but I seriously cannot get the VBA code to copy the value of the current records "Work Order Number" field. I even created a button with a few of the most basic lines of code to try to do this and cannot get it to work.

Private Sub Command199_Click()

dim msg as integer
msg = workordernum
MsgBox msg

End Sub

This always returns "0" no matter what record I'm on. I have tried everything I can think of to get it to read this value and 99% of my attempts have resulted in errors so far. I have tried using the currentdb, currentrecord, me., and various other methods, but nothing seems to work. Oddly enough, I can do the following without any problem,

Private Sub Command199_Click()

dim msg as string
msg = customer
MsgBox msg

End Sub

This displays the customer name from the current record with no problem at all. Does it have something to do with the fact that the "Work Order Number" is the primary key? Any help here would be appreciated. I'm feeling completely defeated right now, trying to do something so easy.

Thanks![/novel]

[ItsMe]

...I want to simply copy all of the customer data and product data from the form and subform...

This sounds like duplication of data and would then not be a Normalized data structure.

Have you considered creating records in tables to manage your data? Although the data may appear to be relative to the form you are viewing, the data is really in the table and it is only the tables that matter when managing data.

Forms are a GUI for the user. Tables are for data management. When you append data to your DB, think of it as appending data to your tables not appending data to a form.

[PPCEngineering]

I'm not sure I understand what you're asking. My data is, of course, stored in tables relative to each form. I am not trying to create duplicate records in the same form with duplicate primary key values. I am trying to copy the field values from form1 (work orders) to form2 (routers). Are you saying that task is more easily accomplished by accessing the data in the tables? I wouldn't even know how to approach that strategy. It also doesn't answer the question as to why I can copy the customer name but not the work order number, even though they are both on the same form.

[ItsMe]

...I am not trying to create duplicate records in the same form with duplicate primary key values. I am trying to copy the field values from form1 (work orders) to form2 (routers)...

And this is the source of your angst.

The duplication of data is the act of copying literal text from a field like CustomerName and placing that same literal text in another table with a field named something like CustNameWO. Instead, you would only "Copy" the primary key value from the table that describes customers and place it in the Foreign Key field of your Work Orders table.

Then, as you start to process multiple "Items" that are associated with the WO, you would only use the Primary Key value from the Work Orders table and use it in the Foreign Key field of your Routers table. You could create multiple records in your Routers table that would all relate to a single WO.

You would use a query to retrieve the Customer name when needed. You could query your Routers table and JOIN other tables to "See" the literal text from fields other than Key fields.

[PPCEngineering]

I think you're trying to do something more complicated than what I'm asking. While I'm sure it would work the way you describe it, I don't feel like what I'm doing needs to be that complicated. Lets go back to the simple question I posted first.

Why does this work,

Private Sub Command199_Click()

dim msg as string
msg = customer
MsgBox msg

End Sub

but not this,

Private Sub Command199_Click()

dim msg as integer
msg = workordernum
MsgBox msg

End Sub

When both of the values I'm trying to copy are on the same form, referencing the same table? The first bit of code accurately displays the customer name from whatever record I am on, in the form. The second one displays a box that just says "0", regardless of what record I'm on. I just want to understand this first, then we can delve deeper into my lack of Access understanding! haha

EDIT: I altered the second bit of code to use Text151.value instead of workordernum and it spits out the right value...

[ItsMe]

So, by themselves, neither work. The following code will work on its own.

Dim msg As String   'Declare variable
msg = "customer"  'Initialize variable
MsgBox msg  'display result

Dim msg1 As Integer   'Declare variable
msg1 = 20  'Initialize variable
MsgBox msg1  'display result

The reason
msg = customer

is returning something from behind your form is because there is a bound control named "customer" on your form. This control holds a value. Since you do not explicitly tell Access what is going on, access searches for a solution and applies tha value in the control named, "customer". The more appropriate way to write what you have would be.

dim msg as string
msg = Me.customer
MsgBox msg

As for the other. Perhaps there is a fieldname within the form's Recordset and not a bound control named workordernum. So...

dim msg as integer
msg = Me!workordernum
MsgBox msg


or just
MsgBox Me!workordernum

[ssanfu]

In you add a control to a form by using the "Add Existing Fields" button from the tool bar, access has a nasty habit of naming controls with the same name as the field in the record source. By you changing the VBA code from "msg = workordernum" to "msg = Text151" you are now referencing the control to get the value.

As ItsMe stated, you should use the "Me." syntax. (it is cleaner).

"Me" is a shortcut to get the form name from the forms collection. If you have a form named "Main", with a control on the form named "workordernum", you could reference the control using:
workordernum (not good programming - ambiguous at best)
Me.workordernum - uses the shortcut
Forms!Main.workordernum - fully qualified

BTW, "Me" only works in code behind the form, not in a standard module.


You posted:

.....<snip>I am not trying to create duplicate records in the same form with duplicate primary key values. I am trying to copy the field values from form1 (work orders) to form2 (routers),<snip>

Forms (and reports) are VIEWS into the tables. Forms do not store data! Once the data is in a table, the data is available to any/all forms IF the form record source includes the table/field.

<nitpicking>
So a table stores the data.
A form is unbound or bound. An unbound form does not have a record source and therefore cannot have bound controls.
A bound form has a table or query (preferred due to the ability to sort and filter) as the record source.
A form can have unbound or bound controls. A bound control has the control source set to a field in the record source.
</nitpicking>

Always take the time to give meaningful names to access objects. Examples of bad names are "Text151" or "Combo18". It will save your hair.

The work order is a main form containing customer information with a subform containing one record for each product on the purchase order. These records contain the part number, description, quantity, price, etc. Once I have the work order created, I need to create a router for each product on the work order. This will contain both the customer data AND the product data, and is used as a set of instructions for the workers on the shop floor to create the product.

Since you said you have a main form and a sub-form, I know that you have at least two tables: A table that stores the customer data and a table to store the order details.

Now the question is: what is a "router"? We don't know what your dB structure is, nor do we know what your business rules are. So we need more info.

Can you post the dB? Or screenshots of the relationship window?