Open a form and insert data SQL

[andymartin3186]

Hi,
I'm trying to open a form from my main menu to a new record and pre insert some fields.
The form opens at a new record however I'm not sure why nothing happens from there on, it opens with everything blank including the autonumber, is there something I need to add to create the record?

Dim ManufacturerTxt As String
Dim ModelTxt As String
Dim SerialTxt As String
Dim intStatus As Integer
Dim Question As Integer

ManufacturerTxt = "TBC"
ModelTxt = "TBC"
SerialTxt = "TBC"
DescriptionTxt = "TBC"
intStatus = 9

DoCmd.OpenForm "Job Form"
DoCmd.GoToRecord acDataForm, "Job Form", acNewRec

strsql = "INSERT INTO Job (Manufacturer, Model, Serial, Description, StatusID) Values ( '" & ManufacturerTxt & "', '" & ModelTxt & "', '" & SerialTxt & "','" & DescriptionTxt & "' , " & intStatus & ")"
Debug.Print strsql
CurrentDb.Execute strsql

 

[Minty]

I would just set those values as the default values on the form controls.
When you then go to a new record they will be the values in those fields, no need for the insert statement at all.

 

[arnelgp]

if form, "Job Form" Textboxes has the same name as your Field name, you can try:

DoCmd.OpenForm "Job Form"
DoCmd.GoToRecord acDataForm, "Job Form", acNewRec
With Forms![Job Form]
    !Manufacturer = "TBC"
    !Model = "TBC"
    !Serial = "TBC"
    !Description = "TBC"
    !StatusID = 9
End With

 

[andymartin3186]

I've tried that but still nothing.
I've tried setting default customer and contact ID's to 1 but no change
When the form loads its like its not created the record yet as everythiing is blank including the autonumber and the check boxes are all ticked, once you click into a field an autonumber appears and the tick boxes are unchecked like the should be

 

[andymartin3186]

if form, "Job Form" Textboxes has the same name as your Field name, you can try:

DoCmd.OpenForm "Job Form"
DoCmd.GoToRecord acDataForm, "Job Form", acNewRec
With Forms![Job Form]
    !Manufacturer = "TBC"
    !Model = "TBC"
    !Serial = "TBC"
    !Description = "TBC"
    !StatusID = 9
End With

That works, thank you!

 

[Ken Sheridan]

Rather than setting the Value property of each control, which is the default property, so is usually omitted, I would suggest that you set its DefaultValue property, and that you do this conditionally in the form's Current event procedure, which will be executed however the form is opened. The values will then be inserted into each control when the form is moved to a new record. Unlike setting the Value property, however, the DefaultValue property does not initiate a new record, so the user can abort the insertion of the new record simply by moving to another existing record or closing the form.

The code for the form's Current event procedure would be:

If Me.NewRecord Then
    Me.Manufacturer.DefaultValue = """TBC"""
    Me.Model.DefaultValue = """TBC"""
    Me.Serial.DefaultValue = """TBC"""
    Me.Description.DefaultValue = """TBC"""
    Me.StatusID.DefaultValue = """9"""
End If

Note that the DefaultValue property is always a string expression regardless of the data type of the column in question, so should be wrapped in literal quote characters as in the above code, in which each literal quote character is represented by a contiguous pair of quote characters. In most cases, this is not actually essential, and it will suffice if the values are delimited (if necessary) in the usual way for the data type in question. There are circumstances, however, where this is not sufficient. Dates are a case in point, particularly for those of us not using US date formats. If I wished to carry the value just entered into a TransactionDate control for instance, then if I were to put the following in the control's AfterUpdate event procedure:

Me.TransactionDate.DefaultValue = Me.TransactionDate

and I were to enter today's date 30/09/2025 into the control, the value carried forward to a new record would be 30 December 1899 00:02:22. This is because the value I entered would be treated as an arithmetical expression, which would evaluate to the floating point number by which the date/time value of 30 December 1899 00:02:22 is stored in Access, 1.64609053497942E-03. If I were to delimit the value with the # date delimiter character:

Me.TransactionDate.DefaultValue = "#" & Me.TransactionDate & "#"

and I enter 04/07/2025 (4 July in UK format), the default value inserted into the control at a new record would be 07/04/2025 (7 April in UK format). If, on the other hand, I wrap the value in literal quote characters:

Me.TransactionDate.DefaultValue = """" & Me.TransactionDate & """"

The date is carried forward correctly as 04/07/2025.

 

[Pat Hartman]

Dirtying a record with code before a user types anything into the new record will almost certainly lead to bad data being saved. Since the defaults are fixed, just add them to the table so they are applied to new records. OR add them to the form controls. If you must write unnecessary code, populate the controls in the Before Insert event. That event runs only once per record and doesn't run until the user types the first character into a new record.

 

[andymartin3186]

Hi,
Just an update I decided to develop this further.
Rather than clicking a button and it opening the main form at a new record with prefilled data from code, I decided to firstly open a small form which asks for key required data - customer, contact, description, ref etc and upon a button click then does the new record and inputs this data in. Hopefully this is a better way of doing it.

 

[Pat Hartman]

Hopefully this is a better way of doing it.

Nope. The best way is to use validation to ensure that all required data is entered. Make sure you set the required property to yes at the table level and leave the default as null (NOT zero) unless you actually have a specific default. Also make sure that allow Zero Length strings is set to no for any text field that is required. This is realllllllly important.

The videos talk about why you need to validate data and the sample database helps you to understand how events fire and what each is designed to handle. Watch at least one of the videos before trying to understand the database.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk