Resetting Fields with macros (1 Viewer)

[dsomers]

Hello all! I have several fields in a query that I want to be able to click a button on my switchboard at the beginning of each month that will reset fields that I select back to 0 or to an empty string, such as Rent Paid and Date Rent Paid (set as currency and date/time).

I've attempted to set up a macro for this. I've set the macro name to Reset Month. I'm using SetValue for action and pointing it to the rent paid field in my tenants query. For the expression I'm using =0. I'm assuming for reseting the date you would use ="".

But basically I want to set a macro to reset the rent paid field and clear out the date on the date rent paid field. Can anybody help me?

Thanks!
David Somers

 

[dsomers]

I meant all those "=0" and "=""" without the =.

Thanks!
David Somers

 

[IMO]

In the OnClick event of the button try...

Private Sub YourButton_Click()

    Me.YourRentPaidField = ""
    Me.YourDateRentPaidField = ""
    
End Sub

When you say "will reset fields that I select", how are you selecting the fields? With a CheckBox?

HTH
IMO

 

[Rich]

Does this mean that payments are entered automatically and you're correcting existing entries?

 

[dsomers]

Sorry.. that came out wrong. I have 2 fields that I want to reset using this macro. I have [Rent Paid] and [Date Rent Paid]. [Rent Paid] is set for currency and [Date Rent Paid] is set to Short Date. When I reset I was [Rent Paid] = 0 and [Date Rent Paid] to be empty.

How can I configure the macro to accomplish this?

Thanks!
David Somers

 

[dsomers]

Also, the reasoning behind this resetting is because at the first of every month I need the Rent Paid to be 0 and the Date Rent Paid to be cleared out so I can enter new data for the next month.

Thanks!
David Somers

 

[IMO]

Forget the macro, use this code in the OnClick event of the reset button...

    Me.[Rent Paid] = 0
    Me.[Date Rent Paid] = ""

HTH
IMO

 

[dsomers]

How do I get to the switchboard code to insert this code?

Thanks!
David Somers

 

[IMO]

Is this a switchboard created by the switchboard manager or have you created it yourself?

IMO

 

[dsomers]

Switchboard manager.

Thanks!
David Somers

 

[IMO]

Ok, Open the form where you view the data in design view. Create a button cancelling the wizard. Right click on the button and select properties, scroll down to "OnClick" and from the dropdown select "Event Procedure". Just to the right of the drop down arrow is another button " ... " click on that to open the vb editor. Paste the code between the " Private Sub BlahBlah_Click() " and the " End Sub ". Close the editor and the form saving the form. Re-open and give it a try.

HTH
IMO

 

[dsomers]

You mean open the switchboard form? I have several forms where the Rent Paid and Date Rent Paid are used.

Thanks!
David Somers

 

[IMO]

No, not the switchboard. Select one of the forms that use Rent Paid and Date Rent Paid (Like an edit accounts form) and place the button on there.

HTH
IMO

 

[dsomers]

What if I took that macro and converted it to VB code? Would that do the same thing and I could run the code from my switchboard? Reason why I ask is I got a MsgBox that comes up confirming the reset.

Thanks!
David Somers

 

[dsomers]

I want to reset only those 2 fields. I tried converting the macro to VB code and renamed the module Reset Month Code. The first function that appears in the code is Reset_Month(). It handles displaying a MsgBox and then going to reset the code if confirmed. When I try to run this code using Run Code in the switchboard, what name should I use for the code in order for this to work?

Thanks!
David Somers

 

[IMO]

Change the code to...

    If MsgBox("Are you sure you want to reset the selected record?", vbYesNo, "Reset?") = vbYes Then
            Me.[Rent Paid] = 0
            Me.[Date Rent Paid] = ""
    Else
        
    End If

HTH
IMO

 

[dsomers]

How would I call this code in switchboard manager using the Run Code option if I name the module Reset Month Code? However, in the module, the function name is Reset_Month().

Thanks!
David Somers

 

[IMO]

You can't call it from the switchboard manager, how would it know which record to reset? Or do you only have one record in the entire db? The switchboard is related to no records, it acts as the control panel for your other forms etc. It is possible to reset ALL the fields in the table from the switchboard wth the click of a button.

HTH
IMO

 

[Rich]

I'm not sure how you've set up your db, if you only enter rents as they're received then why do you need to re-set them? I think your structure is flawed. You should have a separate table recording payments received as a one to many relationship with tenants and linked by TenantID

 

[dsomers]

Ok, I have a form created and linked to my tenants table. When I run the code from above there's no errors or anything, but when I got back to check to see if anything has been set back to 0 or cleared, there is nothing. The data has not changed.

Any ideas?

Thanks!
David Somers