Clear fields after inserting records

[Lebbsy]

I have a 5-tabbed form that is used to add records in five different tables. The tables are excel-linked. When I press the save button the records goes to different tables but the fields are not cleared. So I want a way to clear the fields after inserting the records. Pls Help!!!

[MindBender77]

Your situations is a little unclear but, I'll answer the best i can. The simplest way to accomplish what you ask is to add the following to the OnClick event of your save button:
[code=vb]
Textbox1 = "" 'The name of each control on your form supplying a record.
Textbox2 = ""
etc.
[/code]
OR

Textbox1 = Null
Textbox2 = Null

This could also be done using a loop or by using DoCmd.gotorec,, AcNewRec if the 5 tabbed forms are linked to the 5 tables.

Hope this points you in the right direction,
Bender

[Lebbsy]

Thank you very much for your help. Even though I had to go a bit deeper but I managed to solve it as follows:

Forms!Computer!TabCtl0.Pages!CPUInfo.Controls!cpu.Text = ""

[Lebbsy]

Well for a minute I thought that worked but going on the backend I realised that putting that code in the OnClick button would make the fields in the table blank as well whereas I want it to save the records in the table and leave the form fields blank then go to next record. So the code is right but where do I put it because its not in the OnClick event?

[MindBender77]

Ok, it seems that all of your forms and there corresponding fields are linked to tables. What you ask could easily been done using DoCmd.

On the OnClick event try using: DoCmd.GoToRec,, AcNewRec.

This should save your record then move to a new record. This will save your records then clear the fields.

HTH,
Bender

[Lebbsy]

I have tried that a long time ago to no help. I have an insert statement to insert values into the different tables and that works successfully. The only problem is after inserting the values the fields do not clear...

[MindBender77]

Ok, lets see if I understand your project.

None of your forms' record sources are link directly to a table and your using an insert query to update several tables via a command button. However, after changing a field (text = "") you also change the data in the table.

From what I understand, it seems like the insert query is running twice or your forms ARE linked to the tables and your running an insert query also. This would produce alot of duplicate entries and/or null records in your tables.

Please provide the code your using with your save records button and the record source of each table. This might shed some light on the problem.

Regards,
Bender

[Lebbsy]

Private Sub cmdSav_Click()

Dim ctl As Control
Dim stDocName As String
 
    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.Execute "INSERT INTO Officer(Ministry, Department, Division, AssignedOfficer, " & _
                    "OfficeNumber, TelephoneNumber) VALUES('', '', '', " & _
                    "'',  '',  '')"

    conCars.Execute "INSERT INTO Printer(SerialNo, Make, Model, DatePurchased, " & _
                    "WarrantyPeriod, ManufacturerSerialNo, AssignedOfficer) VALUES('', '', '', " & _
                    "'', '', '', '')"

   conCars.Execute "INSERT INTO Scanner(SerialNo, Make, Model, DatePurchased, " & _
                    "WarrantyPeriod, ManufacturerSerialNo, AssignedOfficer) VALUES('', '', '', " & _
                    "'', '', '', '')"

    conCars.Execute "INSERT INTO Processor(SerialNo, ManufacturerSerialNo, Make, Model, " & _
                    "DatePurchased, WarrantyPeriod, CPURAM, Speed, " & _
                    "HDDSize, MouseMake, OperatingSystem, OSProductKey, AssignedOfficer) VALUES('', " & _
                    "'', '', '', '', '',  '',  '',  '',  '', '', '', '')"

   'DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    conCars.Close
    Set conCars = Nothing

End Sub

And the source object of each subform is the respective linked tables:
Processor, Scanner, Printer, Officer and Monitor