A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Delete record when form control becomes invisible



 
 
Thread Tools Display Modes
  #1  
Old November 6th, 2009, 12:52 PM posted to microsoft.public.access.forms
scottyboyb
external usenet poster
 
Posts: 36
Default Delete record when form control becomes invisible

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

  #2  
Old November 6th, 2009, 03:24 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Delete record when form control becomes invisible

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

  #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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:54 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.