Query and Old Records

[sondriven]

I think there is a little bit of everything in this question so I hope this is an okay forum...

Heres what I have and what do I still need to do?

Purchase Order Database


I have a table (Part Number/Description Table) with 3 Fields: 1. ID 2. Part Number 3. Description


I have a query Part Number Query) built on this Table:

1st Field is the Part Number - Ascending
2nd Field is the Description

I have a Subform that has a combo box: (For the Part Number)

Row Source Type: Table/Query
Row Source: Part Number Query
Bound Column: 2
Control Source: Part Number/Description from the table

I have a text box: (For the Description)
Control Source: Part Number/Description from the table


When I view the form, and select the part from the combo box, it automatically brings up the description into the text box I created.

What I need is this: When I edit the Part Number/Description Table, I dont want the old records to be updated at all or changed. For the reason, "that at the time I ordered that part number at that size".

Can this be done and what else do I need to do?

I need help. Thanks.

John

 

[David R]

You've come across an exception to not storing related data

The exception is, of course, when you cannot go back later and recover the same related data. Examples include prices, pay scales, and in your case, time-sensitive descriptions.

Your other option:
You can avoid having to do this with a little bit of trickery. When I noticed you had a separate Primary Key in your lookup table, there is another option that occurs to me. Add a checkbox field to your lookup table called [Active]. Now modify the query your subform combobox is based on so that it only shows those records in which [Active] = True.
Now when you have to update an entry, you actually leave the old one alone (aside from unchecking the [Active] field). Instead you create a new entry with the same PartNumber, and the new Description. Your old records will refer to the old one, and your new records will refer to the new one, until it becomes another old one, etc. No code required in this method, and it's cleaner.

If you want to actually store it:
You'll have to actually store the associated data that can change in your table along with the [PartNumber]. Note that I mean in your data table, NOT the lookup table "Part Number/Description".

You can do this via the AfterUpdate event of your subform combo box.

Private Sub ComboName_AfterUpdate()
  Me.TextBoxDescription = Me.ComboName.Column(x)
End Sub

You'll have to of course add a field to that subtable for Description. Hopefully the process of populating this field for your already-existing records won't be too painful.

 

[sondriven]

Im a little confused.....

Do I need to have 2 tables?

One for lookup and the other to store data?


Actually how I have it now kinda works. The old records show the old description, but the part number disappears.

Any suggestions on why?

Thanks David for replying, you helped me a great deal in the past.

John

 

[David R]

I'm a little confused..

I was under the assumption you had three tables already. Neither of my solutions should involve creating a new table, just adding a field to one or the other.

Here's what I'm expecting:
"PurchaseOrders" table: All the data on the Purchase Order in general.

"Parts" table: All the PartNumbers and Descriptions, for use in looking up with your combo box. I think you called this (Part Number/Description Table).

"PartsOrdered" table: This is what your subform is based on. It's the table that links the PurchaseOrder to the things ordered in it. It would have, among other fields, the PurchaseOrderID, and the PartID (not the PartNumber, if you're going with my first selection).

The idea I had without code means adding a Checkbox field, [Active], to the "Parts" table, and modifying the query that serves as your Row Source for the combo. It should have the PartID (PK for the "Parts" table), the PartNumber, the PartDescription, and the Active field. Set criteria on the [Active] field: True. Make the first and last columns 0" width to hide them. The bound column will now be PartID, not PartNumber (if you do this with the combobox wizard, it should do it automatically). Note! This will be a Pain In the Arse if you've already got real data in this database, because you'll have to go back and modify all those stored PartNumbers in PartsOrdered into PartID. In that case go with the code solution unless you want to be precise.

The code solution means adding a PartDescription field to "PartsOrdered" table. Use the code in my above post, with the proper field names of course. You'll have to use a Update query to put all the current Descriptions into the table in existing records, and make sure any that are "already" outdated get fixed up.

If you need further help post back. I can probably cook up a short example of what I'm talking about in a small Acc2k database.

 

[sondriven]

David,

I tried the code and what Im getting now is when I put in the Part Number, the same part number comes up in the next field.

If you could cook up an example, it would be greatly appreciated!

Thanks.

John

 

[sondriven]

Note:

Its a new database, so I dont have any old records so to speak. I have just been doing tests with it and these are the results Im getting, so Im trying to plan for the future when I know I will have to edit descriptions. It always happens here.

What I have is a 2 column list made from Excel that Im importing for tables.

Column 1 - Part Number
Column 2 - Description

 

[sondriven]

I just got the Query to work okay. I ran it and its showing me all the "Active" Parts that I checked in the table.

 

[David R]

Sorry, does that mean you fixed it all, or just the query?

You seem to be combining my two suggestions into one. Since you're still in the testing phase I'd say go with the non-code solution, as it is 'cleaner' and will be easier to understand later.

To resummarize:
"PurchaseOrders" table: All the data on the Purchase Order in general.
"Parts" table: All the PartNumbers and Descriptions, for use in looking up with your combo box. I think you called this (Part Number/Description Table).
"PartsOrdered" table: This is what your subform is based on. It's the table that links the PurchaseOrder to the things ordered in it. It would have, among other fields, the PurchaseOrderID, and the PartID (not the PartNumber).

The idea I had without code means adding a Checkbox field, [Active], to the "Parts" table, and modifying the query that serves as your Row Source for the combo. It should have the PartID (PK for the "Parts" table), the PartNumber, the PartDescription, and the Active field. Set criteria on the [Active] field: True. Make the first and last columns 0" width to hide them. The bound column will now be PartID, not PartNumber (if you do this with the combobox wizard, it should do it automatically).

Addendum: Make sure you updated your textbox to reflect the proper column of your combobox. If you've added columns the Description may now be column 3 instead of 2, for example.
=ComboBoxName.Column(x)

 

[sondriven]

Heres what I got:

Purchase Order Table:

Parts and Description Table:

Subform Table:

Query based on Parts and Description Table; Containing fields - Part Numbers, Descriptions, Active (True)

It works for only the "Active" Parts will be selectable. But again I tried to edit the list. I unchecked the "old" part/description check box and added a new one and checked it.

I went back to my form and the new part with description was in the combo box, but for the older records the part number was missing from the record. But the old description was there.

I do like this idea and think it will work good once I can figure out how to keep the old records from disappearing.

Im thinking there could be something wrong with the way my combo boxes and text boxes are set up:

The combo box is connected to the Query I had set up. The control source is set to the Subform Table. 2 Bound columns are set.
For the Text Box for the description, I have the control source the same as the combo box. Is this the fault?

I am now only trying to go with the "Active" way to do. I erased the code from the string.

Thanks

I just got your post on where the Subform doesnt contain the Part Number? How then do I link them?

 

[The_Doc_Man]

The problem is REALLY that your part table lacks a necessary field.

If you have something that can change from time to time AND retain the same part number, etc., then a DATE variable needs to be added. I.e. this is the part description at this time. But on this other date it is different.

Your part number field is incomplete because your REAL key isn't the part number. It is the part number and a date field of some type. Hence the confusion.

 

[David R]

That's why I suggested tying it to the PartID, not the PartNumber. If they have to make a new entry every time the Description changes, there's no sense in making it more complicated than it has to be.

 

[sondriven]

I guess I need more detail on how to link the query to the PartID which is a field that actually exists.

And how to set up the Subform Table without the Part Number and the Description, because thats where the parts will be entered. I think thats whats confusing me the most.

 

[David R]

The subform and main form will be linked via the PurchaseOrderID

on the main form, it's the Primary Key. On the subform, it's called a Foreign Key. The subform wizard will ask if you want to only show those records where a certain field(s) match. Say yes, and select PurchaseOrderID.

Your subform table ("PartsOrdered") will only need to have three fields: PurchaseOrderID (a Number/Long Integer field, not an Autonumber), PartID (also a Number/Long Integer field), and a Primary Key of its own - call it OrderedID if you like.

When you make the subform combobox for PartID, you should be prompted to type in your own values or select them from another table. Choose "Lookup values in a table or query", and choose your "Parts" table. Select the PartID, PartNumber, Active, and PartDescription fields. On the next screen there will be a checkbox saying "Hide Bound Column (recommended). Leave that checked). You can make the Active and PartDescription columns hidden by dragging their width down to nothing in that next screen. Your combo should show the PartNumber after you're finished.

I can't recall if you can do it during creation or not, but you can definitely go into the combo box after it's made and edit the Row Source (under Properties>Data). Put the True criteria in the Active field.

Now make a textbox for PartDescription. In the Record Source, put this: =ComboName.Column, where ComboName is the name of the combo we built above, and n is the column number, counting from zero. With the example above, PartDescription should be Column(3), but doublecheck. It'll be obvious if you get the wrong column.

There you go. Now you're displaying the values, but not storing them in two places. Make sure you lock this field so people aren't tempted to try to change the description by hand. They won't be able to, but they'll get a messy little message "You cannot change the value of this field; it is bound to the expression =ComboName.Column(3)"

If you have any further questions, post back.

 

[sondriven]

Hey thanks for sticking with me and working on this with me.

I did all you said and the combo box is working but I cannot get the text box to work. I have this:

=Combo14.Column3

I figured out its 3 for sure by putting a number in the third row for the widths on the combo box. I also tried every number from 0-5. Just to make sure.

Im just getting #Name? in the box. I dont think the text box needs to be anything fancy and dont think missed something in creating it.

 

[Rich]

The correct syntax is =[Combo14].[Column](2), where column 2 is actually the third column in your combo

 

[RV]

You must use brackets around the column number:

=Combo14.Column(3)

RV

 

[sondriven]

Yup, I missed the Parenthesis. I got the text box to show the description now. And I locked it. But I made an attempt to edit a part number and now instead of just the part number disappearing, everything is gone now on the older record. Does this have something to do with the query and the "Active" set to true. Because that works in the sense that the current part number with description will always be available for selection, but if something changes with that part number I need to have the old records reflect the old descriptions.

 

[sondriven]

David

Can I email you my database? Its a little unfinished because I dont want to go any further until I get this problem solved. Let me know. Thanks.

john

 

[Rich]

You shouldn't edit an existing part no if it affects the integrity of your data. Add a new part number even if it duplicates the description. Other than that you will have to store the description with every record

 

[sondriven]

Im going with Davids idea on the Active Part Number Query.

When I change a description, I dont want it active anymore, so I uncheck it and then create a new part number/description and check it active, but in doing this, the old records that had this part number disappear.