Unbound form problem

[Keith]

Hi
I am using an unbound form for data entry to prevent errors getting into the table i.e. if with a form bound to the table, the user exits the form having only entered one field, a record is created with that field completed only.
I use code to write the record to the table but unless the form is closed and reopened any subsequent records are not added to the table. I am probably going about this in completely the wrong way and any advice would be appreciated. Below is the code that I use.

Keith

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

'Validate Data before Save
If IsNull(Me.txtRegNo) Or Me.txtRegNo = "" Then
Call MsgBox("Registration Number is Required", vbInformation, "SAVE ERROR")

Me.txtRegNo.SetFocus
Exit Sub
End If

'Save DATA
Dim SQL As String

SQL = "INSERT INTO tblVehicle(RegistrationNo,VehicleMake,EngineMake,Seats,ChassisNo,DateRegistered,
PurchaseMileage,PurchaseDate,LicenceRenewalDate,TestDue)" &
"VALUES('" & [txtRegNo].Value & "','" & [txtVehicleMake].Value & "','" & [txtEngineMake].Value & "','" & [txtSeats].Value & "','" &[txtChassisNo].Value & "','" & [txtDateRegistered].Value & "','" & [txtPurchaseMileage].Value & "','" & [txtPurchaseDate].Value & "','" & [txtLicRenDate].Value & "','" & [txtTestDue].Value & "')" '"

DoCmd.SetWarnings False

DoCmd.RunSQL (SQL)

DoCmd.SetWarnings True

'Reset Form
Me.txtRegNo = ""
Me.txtPurchaseMileage = ""
Me.txtVehicleMake = ""
Me.txtEngineMake = ""
Me.txtChassisNo = ""
Me.txtDateRegistered = ""
Me.txtPurchaseDate = ""
Me.txtSeats = ""
Me.txtLicRenDate = ""
Me.txtTestDue = ""
Me.txtRegNo.SetFocus


DoCmd.Close
DoCmd.OpenForm "frmVehicle"

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

 

[gemma-the-husky]

I think your code should work, assuming you are not getting errors in your runsql statement.

why are you closing the entry form in the save sub, and then opening the vehicle form. You ought to be able to leave the entry form open for multiple adds.

The other way is to open the vehicle form first, and have an "add new vehicles" button on it, that pops up your entry form. When your entry form is close, then do a requery in the vehicle form, to refresh it for all the new vehicles.

Am I missing something

 

[Keith]

I need the form to write a new entry to the table, which it does, via a save button which runs the sql but then unless I close the form and re-open it it will not write the next entry to the table when I press the save button. Can I refresh an unbound form ?

 

[WCOK2]

Keith,

If I delete Form "frmVehicle" from your database, and delete from the code the below:

DoCmd.Close
DoCmd.OpenForm "frmVehicle"

now the program will run perfect. Also be very careful on the field size of RegistrationNo if you set it Type as Number and/or as Primarykey. Some records might not be posted or showing wrong RegistrationNo if the RegistrationNo in unbound form not meet the criteria.

 

[WCOK2]

Also, if you need to keep the form "frmVehicle" in database for any reason, you can rename each filed's name in the form different from its record source. i.e. add "1" at end of the name will be good enough..

 

[Carl Foster]

Would it not be a lot easier to have your form databound and then create code to catch any errors, such as closing the form without completing entry? Also, if the tables are set up right, certain fields would not allow null values to be saved so would prevent users from incompleting form entry.

 

[Keith]

Thanks for the advice Gemma,WCOK2 & Carl, its back to the drawing board

 

[shadow9449]

Keith:

Have you considered putting a Validation rule (e.g. "Not null") right into your table design that will trigger Validation text saying an appropriate message (e.g. this field cannot be blank)?

That's the way I've found that's easiest to make sure the user does not neglect to fill out required fields in a form.

SHADOW