hey
i have a form and subform and on the form i have a combo box that u can choose student names from. eventually the form will be to assign reports for extra curricular achievements if i can ever get it workin! i want the subform to display the previous records for the student when chosen in the combo box...does anybody kno how i can do this??

also on the subform i have a button to delete a record but it deletes both the record i want it to delete, and the activity from a different table. im guessin its the relationships, but not completely sure how i can fix it so it only deletes from one table??

i have 3 tables (tblParticipationAwards, tblActivities and tblStudents) the tblParticipation is the many and the others r the one

any help would be great!!

Thanks

caz

Are the child and parent fields linked?



mmcdonal

thanx 4 the reply!

they werent but now they are but now my subform wont show up in form view...?????

also is there a simple way to lookup values in a text box then delete those values from a record using vba??? Thanks!

I am not sure about the subform issue. I have never seen that.

What do you want to delete from your records? Do you want to delete from all records, or just a selected record?

mmcdonal

just a selected record

in the subform i have a record and then the delete button, so i just want it to get the values from the text boxes in that row and delete them from the table its supposed 2!

You would do something like:

Create the necessary delete queries to remove the data from the sub tables. They are based on the PK/FK between the parent and child tables.

Create the delete queries, and then add criteria for the FK to get it from your form's PK field:

[Forms]![frmYourFormName].[PKFieldName]

Then when the delete query runs, it will pull the FK from the form and delete those records.

The last thing you want to do is close the form and delete the record currently selected. Then possibly open the form again.

The way you would do this is, before you close the form, take the PK like this:

iPK = Me.PKFieldName

Then pass it to a delete query that does not have a parameter, like this:

sLink = "[PK] = " & iPK ' I am assuming it is a number field

DoCmd.OpenQuery "qryDELETEQueryName", , sLink 'I can't remember the comma position

So all your code would look like:

'-----Code Starts-----

Dim iPK As Integer
Dim sLink As String
Dim sDocName As String

sDocName = "frmMyCurrentFormName"
iPK = Me.PKFieldName
sLink = "[PK] = " & iPK

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETEFirstDeleteQuery"
DoCmd.OpenQuery "qryDELETESecondDeleteQuery"
...
DoCmd.Close 'closes the current form
DoCmd.OpenQuery "qryParentDeleteQuery", , sLink
DoCmd.SetWarnings True
DoCmd.OpenForm sDocName, etc

'-----Code Ends-----

I think this covers it...

Any comments anyone?

HTH

mmcdonal

You are a champion thank you sooooooooooo much!!!!!!

De nada amigo.

mmcdonal