asked on

Clear form fields after saving

I want my form to clear its field after saving. The code that I have so far is not functioning properly:

Private Sub cmdSav_Click()
 
 
    Dim conCars As ADODB.Connection
    
    Set conCars = Application.CurrentProject.Connection
    
 
    conCars.Execute "INSERT INTO Monitor(SerialNo, ManufacturerSerialNo, Make, " & _
                    "Model, DatePurchased, WarrantyPeriod, MonitorSize, " & _
                    "AssignedOfficer) VALUES('', '', '', '', '', '', '', '')"
 
      
    conCars.Close
    DoCmd.GoToRecord , , acNewRec
    Set conCars = Nothing
End Sub

Open in new window

silemone

why not pass null as as value and when pulling, use isNull(field, '')

silemone

by the way, more than likely if its not working, it's due to an sql error...try to place in a string first and debug and put a breakpoint at that string and see if maybe it's leaving off an ')' or something...copy and paste result and test directly in access

peter57r

The only line of the code shown which could have anything to do with a form is the line:
DoCmd.GoToRecord , , acNewRec

This will only have any effect if you are using a bound form, since there is no 'new record' concept for an unbound form.

LEBAONEMUN

ASKER

I have a 5-tabs form with each tab having a subform that have a table as its source object. Each tab a save button and there is an overall save button. Now I want to make sure each time I press save form fields are cleared. Its not an sql error because it saving fine only that its not clearing fileds.

peter57r

If the form/subform is bound the only way you can 'clear' the textboxes is to move to a new record.
For a bound form there must always be a record visible - either an existing saved record or a new record.

LEBAONEMUN

ASKER

All the subforms are bound. How can I use a for....next loop to achieve this. I have a feeling that can be done through that route..

peter57r

I'm not clear what you are asking- are you asking can you loop through a list of subreports moving each one to a new record?

LEBAONEMUN

ASKER

One way to clear unbound forms is to use a for....next loops. So is it not possible to use this, in this case. I know I said each subform is bound because its 'source object' is pointing to the tables. But again is it possible to loop through each subform moving each one to a new record.

LEBAONEMUN

ASKER

Ok, I thought that since DoCmd.GoToRecord,,acNewRec wont do it for me I could use the following code. Would it work.

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox) Then
        ctl.Value = Null
    End If
Next ctl

Open in new window

peter57r

No it will not work.

If you are using a bound form, setting the control value is changing the visible record.

For each of your form you can move to a new record after adding a record by usib=ng the afterinsert event procedure.

You would use the same code in every form:

Private Sub Form_AfterInsert()
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub

LEBAONEMUN

ASKER

Thanx for your response. But I cant figure out what Name would be in thi case (Me.Name, acNewRec)

peter57r

It is literally Name

LEBAONEMUN

ASKER

I have put the given code in each form. But when I get to the main form after inserting the records nothing happens still.

peter57r

If you have done as I described, as you save each new record in a subform you should move to a new empty record in that subform.

ASKER CERTIFIED SOLUTION

LEBAONEMUN

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer