delete a record with a combo box with multiple columns 1

[signallt03]

How do I delete a record using a combo box with multiple columns i.e. Last Name, First Name. I can get the delete query to work when there is just one column ie Social Security number.

Thanks,
SignalLT03

 

[WildHare]

Your combo box SHOULD contain the key to the table from which you wish to delete the selected record. You can always set the WIDTH property of that column to ZERO to keep it from being visible in the combo box.

Then you refer to the content of this column from the selected row using the combobox.columns() property. This property is zero based, so if the key field is the third column, it's .Columns(2)

Then your delete query can reference this value in it's where criteria.

"Delete * from Whatever where KeyField = " & Formname.combobox.columns(2)

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[signallt03]

WildHare
Thanks for the help! However, when i click on the delete button nothing happens. it goes through the prcocess and displays my pop up msg but the record is not actually deleted from the table.
The following is the del qruery sql code:

DELETE tbl_Cadet.SSN, tbl_Cadet.*, tbl_ROTC.*
FROM tbl_Cadet
WHERE (((tbl_Cadet.SSN)=Forms!frm_CDTDel!cmbo_Cadet![columns(2)]));

tbl_Cadet contains personal info like; social security, name, Date of Birth

tbl_ROTC contains info for different years they were in the program.

it is a one to many relationship with tbl_ROTC on the many side

Thanks

 

[signallt03]

I am now getting a could not delete from specified tables pop up. Comes after my confirmation pop up.

 

[WildHare]

YOu may need to single-quote your key guy - if the Cadet SSN field is TEXT, (which it should be) then your code should look like this, NOTE the RED single quotes..

WHERE tbl_Cadet!SSN = '[/red]" & Forms!frm_CDTDel!cmbo_Cadet.columns(2) & "'[/red];"

Also, you don't need the reference to the Cadet.SSN field in your Delete :

Delete tbl_Cadet.* from tbl_Cadet where....

If the Cadet-ROTC relationship is set to CASCADE deletes, then they (ROTC) will go away automatically as well.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm