clear contents of all controls on a form after update. 2

[bhoran]

Hi I am designing a form for updating a price list. The prices have effective dates so instead of updating the current record I add a new record and update certain fields.

I have it setup so the user selects the record they want to update. This record is displayed (uneditable) along with another row that duplicates the 1st record except for 4 fields that they are able to update; price1, price2, effFromDate, effToDate.

I run an append query to append the data and I use the goto records command to goto a new record afterwards which essentially causes the selected (read only) line to clear. However, I cannot get the contents of the combo box that the user uses to select the record or the 4 text boxes that they updated for the previous record to clear/refresh so they are again blank.

After the user hits the save button I want the form to be totally blank again. I could close the form and re-open it. Is there some other way?

Thanks in advance.

 

[RoyVidar]

Hi!

Does that mean that the four text controls and the combo are unbound? If so, try to assign Null:

[tt]Me!txtSomeTextControl.Value=Null
Me!cboSomeCombo.Value=Null[/tt]

Roy-Vidar

 

[LNBruno]

You can loop through the controls on the form (specifying the control type) and reset the values like this (I use a generic function to do this):

    Dim ctl As Control
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox
                ctl.Name.Value = ""                   
            Case Else
        End Select
    Next

You can add the other control type properties that may be on your form to the select statement and set the appropriate value (in this case, I think you have to set the ctl.selected = 0? -- sorry, on someone else's computer so I don't have my own source handy...)

 

[bhoran]

Excellent, thanks guys both good solutions

I like the idea of the generic function.

However, I have another similar issue. I also have a form where the user can anter a whole new price if that price item does not exist.

If I try either of the above methods it will not validate as the record will have nulls or a blank record.

I am using the 2 forms inside a main form with tabs. The update form (as discussed earlier) and the new form if the record already exists it cannot be entered on the new form so i want that record to be cancelled and to change focus to the update form.

Currently I have the validation on the before update event (so it occurs as soon as they move to the next field).

Any ideas there?

 

[bhoran]

LNBruno

When I tried to run the function I get an error "object required".

I have defined ctr at the beginning of all my code and tried it further amongst the code with the same result (not that I expected it to make a difference).

Cheers

 

[RoyVidar]

This is confusing. Is this a new question, or is it related to the previous? Do you use my code or a corrected version of LNBruno's code? Does the answers to the previous question work or not? What does the sentence The update form (as discussed earlier) and the new form if the record already exists it cannot be entered on the new form so i want that record to be cancelled and to change focus to the update form. mean?

If this is another form, this one bound to the recordset, wouldn't the ordinary straight forward, wizard generated

[tt]docmd.gotorecord,,acnewrec[/tt]

do?

If not - more deatails, we can't guess on bound/unbound validations (controls/forms before update) other relevant details.

BTW - For LNBruno's code to work (remove .Name):

[tt] Dim ctl As Control

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = ""
Case Else
End Select
Next[/tt]

But - it'll bomb on calculated controls, and would probably either bomb or alter the values of the bound controls, so take care.

Roy-Vidar

 

[bhoran]

Sorry I have got a little confusing there.

1. I was looking to use LN Bruno's loop but I have found it is not ideal as I have many controls on the form that are bound but not editable. I have only 5 unbound controls 4 test boxes and 1 combo box.

I will use your code for the specific controls in question.

2. yes the second post is regarding a new question and a new form. All controls are bound to a table.

Here the standard code docmd.gotorecord,,acnewrec
does not work, I get a message that "I can't go to the specified record". Presumably due to the table primary index (cosisting of two of the controls on this form) not allowing nulls? So I guess it is still trying to add a record.

So I tried using the Cancel = true command then trying to to move to a new record also with no luck.

Thanks for your help.

 

[RoyVidar]

Have you checked that forms AllowAdditions propert is set to yes? How the PK is constructed should not matter, as long as the previous record contains valid values - and since the other subform only contains unbound controls, there shouldn't be any problems with that either.

Roy-Vidar

 

[bhoran]

Sorry Roy Vidar maybe I have not explained this very well.

Main form = soley a form with 2 tabs, each contains a subform. But no other links to subforms.

subform1 - update existing Records
Subform2 - add a new record

scenario 1 (Subform1)
has now been sorted. I simply used your code to set the contents of the controls to nulls after the update had occurred.

scenario 2 (Subform2)

This form has got additions allowed in the data property.

Here the controls are bound to the underlying table the user enters the new codes for the new price item and as they exit that field (OnBeforeUpdate) I have a dlookup that checks to see if the record already exist and if it does I return a message saying this record exists please use the update form.

Ideally I would then like it to automatically switch to the tab that contains the update form.

At the moment I get stuck at the point where the record exists as it does the check then it won't allow me to move to a new record as it still contains the data that already exists on the table. If I try to manually delete this data it still doesn't allow me to move to the new record presumably as nulls are not allowed on this field in the table indexes.

I have a button to exit which has a message to confirm that data will be lost then uses the 'cancel = true' command and the close form command which works fine.

But when I try to use the same coding to goto the new record it doesn't work and I get the "can't go to specified record" error. I don't know how to move to the subform1 without exiting the main form so I haven't tried that yet.

Thanks again for your help I really appreciate it.

 

[RoyVidar]

Aaaahhh - have you tried using a me.undo after the msgbox? Problem is you're having an existing record which violates the rules set upon it, so undo should undo that and allow to either reenter new record or manually move to the other tab. Haven't done any tab stuff in a while, need to check out syntax for that (I might post back;-)).

Roy-Vidar

 

[bhoran]

Perfect - So simple when you know how. I kept looking for clear and delete contents but didn't even think to look for an undo.

Thank you very much for all your time here today.

 

[RoyVidar]

I know there are more elegant ways around, just can't find them. You should be able to use the form control name and setfocus:

[tt]Me!SubFormControlName.SetFocus[/tt]

Roy-Vidar

 

[bhoran]

That doesn't seem to work as the the 2 forms I am working on are subforms on the 1 main form but not of each other.

I will continue to look out of interest but I have actually decided to keep the focus on the entry field incase it is a typo that causes the price item to match (a lot of out price items are quite similar) and user may wish to re-enter the correct code.

Thank you for all your help RoyVidar.

 

[RoyVidar]

Works with me (Same setup, one form, tab control, two tabs each containing a different subform), but here's something else. Here assuming a tab control with the name MyTab, and the a tab MyTab1.

[tt]Me!MyTab.Pages("MyTab1&quot.SetFocus
Me!MyTab.Pages(0).SetFocus[/tt]

Roy-Vidar

 

[bhoran]

RoyVidar, you are a star, I was searching for that for a good chunk of the day yesterday and came up with nothing.

Thank you once again.