View Single Post
  #3  
Old November 6th, 2009, 03:57 PM posted to microsoft.public.access.forms
scottyboyb
external usenet poster
 
Posts: 36
Default Delete record when form control becomes invisible

Daryl,

Thank you! It was much simpler that I expected. It works like a charm.

Best,
Scott

"Daryl S" wrote:

Scottyboyb -

Change each occurrence of
Me!txtPosition.Visible = False
to
Me!txtPosition.Visible = False
Me!txtPosition.Value = ""

That will remove the current value.

--
Daryl S


"scottyboyb" wrote:

Greetings,

I have 2 forms (frmContibutors & frmEnterNewContrib) that each allow data to
be entered into a table (tblContributors). This table has a relationship with
another table (tblPositions) that holds the list of names of board positions.
On both forms I have a combo box that uses the linked table of list of names
of board positions and saves the long integer result to the field PositionID
in the tblContributors table.

The forms each have a check box that use as its' source a y/n field in the
tblContributors table. When I check the check box, it adds a yes to the
tblContributors table field for BoardMember and makes the combo box
txtPosition field visible on the form visible. I then choose the board
position from the combo box list to update the PositionID field in the table
tblContributors.

This all works fine when I add or edit a board member position record. The
record either appears or changes in the tblContributor table. But if I
uncheck the y/n check box, the txtPosition field disappears from the form
which is right, but I need to know how to get the value deleted from the
PositionID field in tblContributors table. Right now the PositionID field
retains the entry for the board position I chose in the form. This makes
sense, since the check box code only acts on the visible property. How do I
get it to also clear the PositionID field if I uncheck the board member check
box. I understand that the combo box and the check box are not currently
related in any way to each other, other than that they are in the same table.

Code for check box:
Private Sub chkBoardMember_AfterUpdate()
If Me!chkBoardMember = False Then
Me!txtPosition.Visible = False
Else
Me!txtPosition.Visible = True
End If
End Sub

Private Sub Form_Current()
If Me!chkBoardMember = False Then
Me!txtPosition.Visible = False
Else
Me!txtPosition.Visible = True
End If
End Sub

Table relationship of you need it . . .
tblContributors
ContributorID = autonumber primary key
PostionID = long integer Linked to
more fields . . .
BoardMember = y/n field
tblPositions (only 2 fields)
PositionID = autonumber primary key - Linked to
PositionName = text field listing board position names

Thanks,
Scott