Using 2 Foreign Keys to link to a Primary Key in 1st Table

[ballj_35]

Everyone,

I have a challenging situation and have not been able to read where anyone has encountered this situation. It is as follows:

1. I need to create a Transfer of Bond Information. The Main table is the Member table with a primary key of Mbr_Bond_Number.
2. The 2nd table is the Transfer table. I need to have a Trans_Bond_Number_From and Trans_Bond_Number_To. Unsure if these
should be created as Primary or Foreign keys? Since the value in them will both link back to the Primary Key of Mbr_bond_Number.
3. I want to create a Combo Box for the user to Select who to transfer the Bond From. Then move in other fields from the Member
table, i.e. Bond Name, Last Name, First Name to fields on the From_Side.
i.e. Trans_Bond_Nbr_From, Trans_Bond_Name_From, Trans_Bond_Last_Name_From and Trans_Bond_First_Name_From.
4. Then I will have a second Combo Box for the user to Select who to transfer the Bond To. Then move in other fields from the
member table on the To side.
i.e. Trans_Bond_Nbr_To, Trans_Bond_Name_To, Trans_Bond_Last_Name_To and Trans_Bond_First_Name_To.
5. When the user Clicks on the "SAVE" button, the information is written to the Transfer table and then goes back and performs
a "DELETE" on the Member table, using the Trans_Bond_Nbr_From as the key to delete the record in the Member table.

Can someone plz help me with this? If I were doing this in Cobol with DB2, it would be a none issue, but in Access 2010, I am
getting confused of how to create the Transfer table, setting up the combo boxes and then getting the values in place and performing
the code. Any assistance wuould be greatly appreciated!
Thanx in advance.

Jerry

[June7]

The bond number is not exclusive to a member but is transferrable? Consider:

tblBonds

tblMembers

tblMembBonds
BondID (foreign key)
MemberID (foreign key)
DateAssigned

The tblMembBonds allows rentention of history on each bond. No 'transfer' table and code would be needed. Even if you do the 'transfer', no reason to store names, just the IDs.

[ballj_35]

June7,

The Bond Number is exclusive to a member! I thought about history also, but the user wants the records removed from all tables when a Bond is
transferred and just stored in a Transfer table. This way the only records in the member table are records for current members only. The requirement
and process is as follows today:

The transfer process goes as such:

1) Members contact me about a transfer and I get them the forms needed.
2) Once they find a purchaser, forms are filled out and submitted to me for processing/.
3) I remove the current members info from the main table to a "transfered"(Simple cut
and paste of their line) and mark the transfer date in the "transfer date" field.
4) I add the new members info to the main table and issue a new bond number for their
use on the entry system.

I am trying to automate this for them. By allowing the user to do the following:

1. Create a new Member Record.
2. Go to the Transfer Screen.
3. Select the Bond Number to transfer from, using a Combo Box to select the Bond_Nbr_From.
4. Select the Bond Number to transfer to, using a Combo Box to select the Bond_Nbr_To.
5. Click on a Save Button.
6. Store appropriate information in the Transfer table.
7. Then Delete the Bond_Nbr record in the Member Table using the Bond_Nbr_From key.

Any ideas? Thanx in advance.

Jerry

[June7]

Deleting records should rarely be done, and only with strong justification. Are there other tables dependent on the member bond number - payments for instance? If you are deleting records from member table then the From number in transfer will have nothing to link to. Only the To number will have a link and only until that member does a transfer. Instead of deleting, consider an Active/Inactive field. What if the old member returns? Do they get a new Bond Number or should you just reactivate them?

Okay, if you must do transfer and delete, use VBA and a sequence of SQL actions. After entering new member info by normal data entry, use INSERT to add record to transfer table, DELETE to remove the old member.

Do you want a single transfer record or two records?

Review http://www.blackwasp.co.uk/SQLSelectInsert.aspx

CurrentDb.Execute "INSERT INTO transfers(<each destination field separated by commas>) VALUES(" & <concatenate variables for the data to populate record, don't forget comma separators> & ")"

CurrentDb.Exceute "DELETE FROM Members WHERE Mbr_bond_Number=" & Me.BondNumFrom

[ballj_35]

It would be a single record transfer. WOuld the best process be to create 2 Combo Boxes to allow the user to select the From & To Bond records?
I am going to try to create a form with the Combo Box in the Form Detail and load to the screen. When the user selects "SAVE", the I am going to try
to create and execute the SQL Code, per your instructions.
Does this sound like a plan or would you recommend another way? Thanx in advance.

[ballj_35]

I have started the Form and really unsure how to do the two Combo Boxes? I get the data from the Member Table. How can I have the information from
cboBondSelectFrom get to the fields in MBR_Bond_Nbr_From (Label) when it is bound to the field Mbr_Bond_Nbr, since the cboBondSelectTo would have its informaiton bound to the same field, MBR_Bond_Nbr and its Label would be MBR_Bond_Nbr_To? Seriously confused on this!
Thanx and looking for ideas/suggestions!

[ballj_35]

I have created the Form and the results are nto what I would like. When I select the record from cboBondNbrFrom, BOTH detail records are populated
with the information from the Member Table. Then when I go to cboBondNbrTo and select a record, then BOTH records are displayed from the Member Table
in eaach detail portion of the screen. What I need to have is each Combo Box, display the approriate detail selection.
I believe I can get the portion from when the users clicks on Save, then I will write the SQL code for what I need to happen. I know I should put in error handling to ensure that both the FROM and TO Bond Numbers are selected and that they are not equal. Since I do not know how to do that, I may tell the
user it is up to them to do it correcly.
Any suggestions?

[June7]

Provide db for analysis. Follow instructions at bottom of my post.

[ballj_35]

The DB is attached. I just zipped it, since I am trying to get this done. If I need to do the other steps, just let me know. The Form name is NWSC_Trans_Member.
Thanx in advance.

EDIT: Attachment removed by moderator because appeared to contain real personal data.

[June7]

The transfer form doesn't work because you have two sets of controls bound to the same fields of the same record so of course they show the same data on both sides. You would have to use unbound controls for one side and do DLookup expression in ControlSource or VBA code to populate them. The transfer form is bound to the member data table, not the transfer table. I don't see any code that will save the data to transfer table.

If you want the from/to data saved into one record, then need two complete sets of fields for the data. For instance:

TRN_FROM_Last_Name
TRN_TO_Last_Name
TRN_FROM_Last_Name_Bond_Nbr
TRN_TO_Last_Name_Bond_Nbr
etc.

Also, copying attachments from one table to another requires VBA code that first saves the files out to a folder and then saves them back into the db.

Why is there two tables of member data?

[ballj_35]

I am going to remove the old one when I complete the project. What I did was create the Mbr_Tbl as I know how from being in IT and then trying to normailze the data using the Dues_Tbl, etc.. The other table Member Data came from the user and I had to load the information into the new db. Took the old system data and exported to an Excel file. Then I imported to a new table (Member Data), modified the fields definitions and appended the data to my table. The best way I could see how to convert there existing data into the new format.
They had the original table with everything in one table, all the information from the Dues table was contained in the Member table, along with other stuff. So I broke it out.
Now that you told me about the attachment issue, I may need to rething this Transfer process. It is getting to be more than what I know how to do or maybe even want to do. For the termination process, I used a Yes/No switch on the Member table to display records that have not been terminated. They will stay in the database, but will not be displayed in the Combo Box Selection. I will create a report for the user and tehn they can see all the records that have been terminated and they can delete them whenever they want later. You are correct, I should leave them out them in case they want to add them back in, they only need to turn the switch off.
Thanx for everything and I really appreciate your time and efforts.

Jerry

[June7]

Another point to keep in mind. Delete the member record and related records in other tables (such as Dues) will be orphaned. Also, if a relationship is established which enforces referential integrity, the record in members table could not be deleted without also deleting the dependent records (cascade delete would have to be activated). That means all the related Dues records would disappear. Would this mess up financial reporting?

Be very careful about allowing record deletion.