Use Mainform checkbox uncheck to clear subform and subform table

  • Thread starter Thread starter iamrdbrown
  • Start date Start date
I

iamrdbrown

I have 2 tables:

tblBrazerIDs (contains people information)
tblBrazerStatus (contains history for those people)

I have a form (frmBrazerID) with a subform (sfrmBrazerStatus). The main
form feeds into the tblBrazerIDs. The subform feeds into the table
tblBrazerStatus. I have a foreign key set up to link them so that records
can be added and new status reports created. There can be many status
records for one person.

I have a checkbox on the main form (frmBrazerID) that gets checked when a
person is trained and entered into the database. I need this to clear the
subform AND underlying tblBrazerStatus if a person leaves the company or
becomes inactive as a brazer.

I have a delete query that will delete the information, but I can't figure
out how to get it to run on the click event of the checkbox. The query uses
the BrazerID primary key to know which sub-records to delete. I can get the
records to delete, but the subform doesn't clear all records until I close
and reopen the whole form.

Is there an easier way to work around this than having to close the whole
form manually & reopen it to see the changes?

I really appreciate in help I can get...

TIA,
Ruth
 
Do not run the delete query.

Instead use checkbox like this for the sfrmBrazerStatus --

SELECT [tblBrazerStatus].*
FROM [tblBrazerStatus] INNER JOIN tblBrazerIDs ON [tblBrazerStatus].ID =
[tblBrazerIDs].ID
WHERE [tblBrazerIDs].CheckBox = 0;
 
Ok... I recognize this as a SQL type of query, but I don't do SQL very well
(that is an understatement)... How will this clear the data I want to have
'gone'? There are times when an inactive brazer does come back in and gets
re-activated. I don't want the old records from their previous activation to
be available when we re-certify them.

TIA,
Ruth

KARL DEWEY said:
Do not run the delete query.

Instead use checkbox like this for the sfrmBrazerStatus --

SELECT [tblBrazerStatus].*
FROM [tblBrazerStatus] INNER JOIN tblBrazerIDs ON [tblBrazerStatus].ID =
[tblBrazerIDs].ID
WHERE [tblBrazerIDs].CheckBox = 0;

--
Build a little, test a little.


iamrdbrown said:
I have 2 tables:

tblBrazerIDs (contains people information)
tblBrazerStatus (contains history for those people)

I have a form (frmBrazerID) with a subform (sfrmBrazerStatus). The main
form feeds into the tblBrazerIDs. The subform feeds into the table
tblBrazerStatus. I have a foreign key set up to link them so that records
can be added and new status reports created. There can be many status
records for one person.

I have a checkbox on the main form (frmBrazerID) that gets checked when a
person is trained and entered into the database. I need this to clear the
subform AND underlying tblBrazerStatus if a person leaves the company or
becomes inactive as a brazer.

I have a delete query that will delete the information, but I can't figure
out how to get it to run on the click event of the checkbox. The query uses
the BrazerID primary key to know which sub-records to delete. I can get the
records to delete, but the subform doesn't clear all records until I close
and reopen the whole form.

Is there an easier way to work around this than having to close the whole
form manually & reopen it to see the changes?

I really appreciate in help I can get...

TIA,
Ruth
Click to expand...
Click to expand...
 
I don't do SQL very well
Ok, open your query that feeds sfrmBrazerStatus in design view.
In the space above the grid will be tblBrazerStatus.
Click in the table, grab bar at top of table display and drag to the right.
Click on the 'Show Tables' icon and select tblBrazerIDs.
Click on the tblBrazerIDs ID field and drag to the ID field of
tblBrazerStatus.
Click on the connecting line and select the option 'Include all records from
tblBrazerIDs and those from tblBrazerStatus where the joined fields are
equal.'
Click on the CheckBox fields and drag to an empty space of the FIELD row of
the grid. Click on the checkbox of its 'Show' row. Type the number 0
(zero) in the Criteria row.
To keep the history without displaying it on return of the individual, add a
Yes/No field to tblBrazerStatus. Then create an Update query to update this
checkbox with a -1 (minus one) following your action to check the employee as
gone in tblBrazerIDs.
Add the field to the query as above with the same criteria.
On return and uncheck of tblBrazerIDs, the tblBrazerStatus records will not
show.

If you really want to wipeout the history then backup the database.
Next build a delete query by joining the two tables and using criteria on
the checkbox.
I always run the query as a select query just to analyze the data that I am
about to destory.

--
Build a little, test a little.


iamrdbrown said:
Ok... I recognize this as a SQL type of query, but I don't do SQL very well
(that is an understatement)... How will this clear the data I want to have
'gone'? There are times when an inactive brazer does come back in and gets
re-activated. I don't want the old records from their previous activation to
be available when we re-certify them.

TIA,
Ruth

KARL DEWEY said:
Do not run the delete query.

Instead use checkbox like this for the sfrmBrazerStatus --

SELECT [tblBrazerStatus].*
FROM [tblBrazerStatus] INNER JOIN tblBrazerIDs ON [tblBrazerStatus].ID =
[tblBrazerIDs].ID
WHERE [tblBrazerIDs].CheckBox = 0;

--
Build a little, test a little.


iamrdbrown said:
I have 2 tables:

tblBrazerIDs (contains people information)
tblBrazerStatus (contains history for those people)

I have a form (frmBrazerID) with a subform (sfrmBrazerStatus). The main
form feeds into the tblBrazerIDs. The subform feeds into the table
tblBrazerStatus. I have a foreign key set up to link them so that records
can be added and new status reports created. There can be many status
records for one person.

I have a checkbox on the main form (frmBrazerID) that gets checked when a
person is trained and entered into the database. I need this to clear the
subform AND underlying tblBrazerStatus if a person leaves the company or
becomes inactive as a brazer.

I have a delete query that will delete the information, but I can't figure
out how to get it to run on the click event of the checkbox. The query uses
the BrazerID primary key to know which sub-records to delete. I can get the
records to delete, but the subform doesn't clear all records until I close
and reopen the whole form.

Is there an easier way to work around this than having to close the whole
form manually & reopen it to see the changes?

I really appreciate in help I can get...

TIA,
Ruth
Click to expand...
Click to expand...
Click to expand...
 
Back
Top