Form Input from 2 unbound fields to table.

[spidge32]

I have a form which has 2 unbound fields(ComboBoxes) which both relate to the same table. The user is to enter a part # in the first and then another selected Part# in the second.


I then wish to have both Part#'s saved to a table which has 2 fields. fldOldPartNo, fldNewPartNo. The idea is to get the user input to copy all the data (3 tables) of Old to New.
Can you suggest away to do this?

[June7]

Not understanding situation. If you only want two values entered into a single record, why not bind form to table?

If you want to 'batch' create records with code, that would probably require an INSERT SELECT sql statement.

Provide sample of data and desired result. If you want to provide db, follow instructions at bottom of my post.

[Micron]

Would depend on whether or not you've already designed table(s) to support this. IMO, if you are transitioning from old to new part numbers, you probably should have a new table to record the transitions; something like

OldPartNo			NewPartNo
OpnIDpk			NpnIDpk	OpnIDfk	NewPartNo
1	11234		1	1	43211
2	23456		2	2	65432
3	5678		3	3	8765
4	14789		4	4	98741

Ignore that the new part number pk field values here are the same as anything else. The important matches would be the old PK id's (left red) match the old FK values (right red), thus you can link the new and old part numbers. That way, you could find/know the other as long as you know one.

If you haven't set it up this way and a part number can ever get a 3rd value, then you will have problems.
The direct answer to your question may be that in the AfterUpdate event of the 2nd combo, you will have to execute an UPDATE sql to write those values to your table. However, I don't understand how there can be 3 tables providing choices when there are only 2 components - old and new numbers.

The idea is to get the user input to copy all the data (3 tables) of Old to New.

[spidge32]

OK, as usual a bad explanation. The form Copy Part updates the table CopyPart with the 2 values from the form. The 2 unbound Combo are linked to the tblpart.
What I require when the button Copy Part is clicked is for the 2 values to be put into the table CopyPart. Then with a series of queries the 3 tables which make up the data required is copied with the data from the old part. (This part is easy)


Thank you in advance for your help.

[June7]

I am still lost. Which are the 3 tables? I see 2 tables with 'old' in their names.

Saving two pieces of data into a new record of table is simple. Run an INSERT sql.

If executing a series of queries is the easy part, exactly what is the issue you face?

CopyPartNo form makes no sense. It is bound to qryLookupJobsheet with two texboxes bound to the same field. Button macro does SaveRecord - why? Why allow edit of data via this form?

[spidge32]

The source for the form can be changed to one just for it self. i.e. qryPartCopy1.
The parts are from the tblParts which is the master table for all parts, all it has is the Part No and Description. A lot of the database is not complete, each part has 3 tables which have all the components. The database was originally set up in 2005., so I'm trying to leave the 3 tables as is. To copy a part I need to copy the HDR,WDG, TERM components of the part to temp tables, change the Part No from the 1st to the second and appended to the HDR, WDGand Term tables with the new Part No. This is easy to do so its no problem.

I use the master table tblParts to ensure that the Part does exist before copying,

So all I would like to do is copy the 2 Part No's from the form to the CopyPart table as Old and New.
I can do the rest.

[davegri]

spidge-davegri-v01.zip

See if the abbreviated DB above will work for you. It has the bare bones tables, query and form.

Edit: Notice that the form does not need a recordsource at all.

[spidge32]

Thanks Davegri, I do not have any knowledge of VBA, I think I need a good resource to learn.