uncheck checkbox to delete fields within record

[Guest]

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

 

[Guest]

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

bc,

You have to clear numeric fields using Null, so it would be
Me.[Item_ID#] = Null

Also, as advice, don't use symbols such as "#" in your field names, and
don't call a field a reserved word, e.g. "Date". I also avoid putting blanks
in the field/form/control names. It saves typing the brackets each time.
I'd suggest something like:

lngItem or intItem for the Item (long or short integer)
dteLent or dateLent for the "Lend Date"
strBorrower or BorrowerName for the "Borrower's Name"

You can then reference them as "Me.dteLent =" versus "Me![Date Lent] ="

Good luck with it,
Bruce

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

Hi Bruce,

Thank you very much. I changed the field name to just a single word; "ID".

I've got a small problem:

When unchecked, it only deletes one of the fields, not both. Not sure why.
Any ideas?

Thanks Bruce --

bc,

You have to clear numeric fields using Null, so it would be
Me.[Item_ID#] = Null

Also, as advice, don't use symbols such as "#" in your field names, and
don't call a field a reserved word, e.g. "Date". I also avoid putting blanks
in the field/form/control names. It saves typing the brackets each time.
I'd suggest something like:

lngItem or intItem for the Item (long or short integer)
dteLent or dateLent for the "Lend Date"
strBorrower or BorrowerName for the "Borrower's Name"

You can then reference them as "Me.dteLent =" versus "Me![Date Lent] ="

Good luck with it,
Bruce

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

:

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

bc,
Reply with the code from the After_Update and I'll take a look.
Bruce

Hi Bruce,

Thank you very much. I changed the field name to just a single word; "ID".

I've got a small problem:

When unchecked, it only deletes one of the fields, not both. Not sure why.
Any ideas?

Thanks Bruce --

bc,

You have to clear numeric fields using Null, so it would be
Me.[Item_ID#] = Null

Also, as advice, don't use symbols such as "#" in your field names, and
don't call a field a reserved word, e.g. "Date". I also avoid putting blanks
in the field/form/control names. It saves typing the brackets each time.
I'd suggest something like:

lngItem or intItem for the Item (long or short integer)
dteLent or dateLent for the "Lend Date"
strBorrower or BorrowerName for the "Borrower's Name"

You can then reference them as "Me.dteLent =" versus "Me![Date Lent] ="

Good luck with it,
Bruce

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

:

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

:

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

Thank you Bruce.

Here you go:

Private Sub chkLentOut_AfterUpdate()
'Clear name and ID.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me![ID] = Null
End If
End Sub

bc

bc,
Reply with the code from the After_Update and I'll take a look.
Bruce

Hi Bruce,

Thank you very much. I changed the field name to just a single word; "ID".

I've got a small problem:

When unchecked, it only deletes one of the fields, not both. Not sure why.
Any ideas?

Thanks Bruce --

bc,

You have to clear numeric fields using Null, so it would be
Me.[Item_ID#] = Null

Also, as advice, don't use symbols such as "#" in your field names, and
don't call a field a reserved word, e.g. "Date". I also avoid putting blanks
in the field/form/control names. It saves typing the brackets each time.
I'd suggest something like:

lngItem or intItem for the Item (long or short integer)
dteLent or dateLent for the "Lend Date"
strBorrower or BorrowerName for the "Borrower's Name"

You can then reference them as "Me.dteLent =" versus "Me![Date Lent] ="

Good luck with it,
Bruce


:

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

:

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

:

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

bc,
Don't know...
Try saving the record and requerying. I've put the code into your's, below.
Bruce

Thank you Bruce.

Here you go:

Private Sub chkLentOut_AfterUpdate()
'Clear name and ID.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me![ID] = Null

'Add this code.
Me.Dirty = False 'Saves the record.
Me.Requery 'Reloads the data to the form.

End If
End Sub

bc

bc,
Reply with the code from the After_Update and I'll take a look.
Bruce

Hi Bruce,

Thank you very much. I changed the field name to just a single word; "ID".

I've got a small problem:

When unchecked, it only deletes one of the fields, not both. Not sure why.
Any ideas?

Thanks Bruce --

:

bc,

You have to clear numeric fields using Null, so it would be
Me.[Item_ID#] = Null

Also, as advice, don't use symbols such as "#" in your field names, and
don't call a field a reserved word, e.g. "Date". I also avoid putting blanks
in the field/form/control names. It saves typing the brackets each time.
I'd suggest something like:

lngItem or intItem for the Item (long or short integer)
dteLent or dateLent for the "Lend Date"
strBorrower or BorrowerName for the "Borrower's Name"

You can then reference them as "Me.dteLent =" versus "Me![Date Lent] ="

Good luck with it,
Bruce


:

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

:

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

:

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!

 

[Guest]

That did it! Thanks a million!

bc,
Don't know...
Try saving the record and requerying. I've put the code into your's, below.
Bruce

Thank you Bruce.

Here you go:

Private Sub chkLentOut_AfterUpdate()
'Clear name and ID.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me![ID] = Null

'Add this code.
Me.Dirty = False 'Saves the record.
Me.Requery 'Reloads the data to the form.

End If
End Sub

bc

bc,
Reply with the code from the After_Update and I'll take a look.
Bruce

:

Hi Bruce,

Thank you very much. I changed the field name to just a single word; "ID".

I've got a small problem:

When unchecked, it only deletes one of the fields, not both. Not sure why.
Any ideas?

Thanks Bruce --

:

bc,

You have to clear numeric fields using Null, so it would be
Me.[Item_ID#] = Null

Also, as advice, don't use symbols such as "#" in your field names, and
don't call a field a reserved word, e.g. "Date". I also avoid putting blanks
in the field/form/control names. It saves typing the brackets each time.
I'd suggest something like:

lngItem or intItem for the Item (long or short integer)
dteLent or dateLent for the "Lend Date"
strBorrower or BorrowerName for the "Borrower's Name"

You can then reference them as "Me.dteLent =" versus "Me![Date Lent] ="

Good luck with it,
Bruce


:

Thank you Bruce -- that was very helpful!

One question: I also want to add a number field to the run and have that
delete when the checkbox is unchecked. How would I add that to the statement?

Me.[Item_ID#] = ""

? I tried that and it didn't work. But I'm just guessing. The "Item ID#"
field is purely numerical; no text.

Thanks in advance Bruce! I really appreciate your assistance!
bc

:

Use the AfterUpdate event of the check box:

In design view, click the check box control to highlight it.
Right-click and select Properties.
Click the "Event" tab
Click the blank area to the right of After Update
Click the elipsis ( "..." ) at the far right.
Click "Code Builder" and click OK

Access will automatically open the editor and create an After_Update Sub

Assuming that the control name is "chkLentOut", make it look like this:

Private Sub chkLentOut_AfterUpdate()
'Clear name and date.
If Me.chkLentOut = False Then 'Changed from true to false
Me.Lend_Date = Null
Me.Borrower_Name = ""
End If
End Sub


HTH,
Bruce

:

Hi all.

I'm horrible with code. I think I have a rather simple one for you. I've
got a small db, that involves lending. There are a total of 3 fields that
would be involved with this code:

-Lent out (checkbox)
-Borrower's name (text)
-Date (date/time)

When an item is lent out, the user checks the box, and puts in the name.
This information goes to a query, where the manager of the db can see any
items lent out and know who's got the item.

Once in the query, they uncheck the box when an item is returned, and
currently manually delete the data in the [Borrower's Name] and [Date] Field.


What I'd like to setup: once the box is unchecked, the db would
automatically delete the data in those fields for the user in that record.

Can anyone help me out? Thank you!