Update form

[duke_44]

Hello, I am working on a form to update a table with many fields. I have 2 questions.
1. The SQL statement is quite large is there a way to shorten this an accomplish the update to the table?
2. As I attempt to enter alpha-numeric information into the text boxes in my form I get an error saying the value entered isn't valid. I can't seem to figure out why.

Thank you for any help provided.

[HansV]

Welcome to Eileen's Lounge!

1) Is SQL statement the record source of the form, or is it an update statement?

2) Are those text boxes bound to fields in the record source of the form, or are they unbound?

[duke_44]

I am new to VBA, SQL etc, so I think its an update statement but here is my SQL statement:
'Private Sub btnSave_Click()
' CurrentDb.Execute "INSERT INTO tblVehRec (InspDate, InspType, ServID, ServName, CACC, VIN, License, MTO, Vehicle, VehType, ModYear, Make, Model, FuelType, Odometer, GVWR, Length, Hours, CMVSS, ESA, ConvMan, ConvProdNum, Mnt1, Cot1, Mnt2, Cot2, Veh1, Veh2, Veh3, Veh4, Veh5, Veh6, Veh7, Veh8, Veh9, File1, File2, File3, File4, File5, File6, File7, File8, File9, Add1, Add2, Add3, Add4, Add5, Add6, CertNum, Version) " & _
' " VALUES (" & Me.txtInspDate & ", ' " & Me.cboInsType & " ', '" & Me.txtServID & "', '" & Me.txtServName & "', '" & Me.txtCACC & "','" & _
' Me.txtVIN & "', '" & Me.txtLic & "', '" & Me.txtMTO & "', '" & Me.cboVeh & "', '" & Me.cboType & "', '" & Me.txtMY & "', '" & _
' Me.txtMake & "', '" & Me.txtModel & "', '" & Me.txtModel & "', '" & Me.cboFuel & "', '" & Me.txtOdometer & "', '" & Me.txtGVWR & "', '" & _
' Me.txtLength & "', '" & Me.txtHours & "', '" & Me.cboCMVSS & "', '" & Me.txtESA & "', '" & Me.txtConvMan & "', '" & Me.txtConvNum & "','" & _
' Me.cbocotnum & "', '" & Me.cboMount1 & "', '" & Me.cboMount2 & "', '" & Me.cbocot1 & "', '" & Me.cbocot2 & "', '" & Me.cboCert & "', '" & _
' Me.txtVersion & "', '" & Me.cboVeh1 & "', '" & Me.cboVeh2 & "', '" & Me.cboVeh3 & "', '" & Me.cboVeh4 & "', '" & Me.cboVeh5 & "', '" & _
' Me.cboVeh6 & "', '" & Me.cboVeh7 & "', '" & Me.cboVeh8 & "', '" & Me.cboVeh9 & "', '" & Me.cboFil1 & "', '" & Me.cboFil2 & "', '" & _
' Me.cboFil3 & "', '" & Me.cboFil4 & "', '" & Me.cboFil5 & "', '" & Me.cboFil6 & "', '" & Me.cboFil7 & "', '" & Me.cboFil8 & "', '" & _
' Me.cboFil9 & "', '" & Me.txtAdd1 & "', '" & Me.txtAdd2 & "', '" & Me.txtAdd3 & "', '" & Me.txtAdd4 & "', '" & Me.txtAdd5 & "', '" & Me.txtAdd6 & "')"
'End Sub

The text boxes are unbound.

Thank you for having me here and any help!

[HansV]

That is the SQL for an append query: if it works, it will add a new record to tblVehRec.

Wouldn't it be easier to use a bound form? That way, values are automatically saved to the table.

With an unbound form, an alternative to executing a SQL string would be

- Open a recordset on tblVehRec.
- Add a new record.
- Set the value of each field in the recordset to that of the corresponding control.
- Update the record.
- Close the recordset.

The code wouldn't be shorter, but easier to read.

Some remarks:

1) If InspDate is a date/time field, the value of Me.txtInspDate should be formatted as mm/dd/yyyy and enclosed in # characters:

... VALUES (#" & Format(Me.txtInspDate, "mm/dd/yyyy") & "#, ...

2) You enclose ALL other values in single quotes, implying that ALL other fields are text fields. Is that correct?

3) You include a space before and after the value of Me.cboInsType:

, ' " & Me.cboInsType & " ',

I think you should remove those spaces:

, '" & Me.cboInsType & "',

4) You have several series of repeated fields, for example Veh1, Veh2, ..., Veh9. That is not an optimal design. It would be better to store these values in a separate table with a field to link to tblVehRec and a single Veh field. One record in tblVehRec would correspond to multiple records in this new table.

[duke_44]

I like the sounds of your ideas HansV however I'm not really sure how to make this happen. The reason I had multiple fields (Veh1, Veh2, etc) is to allow my user to enter multiple vehicle inspection findings. However if you feel that having them in their own table would be more efficient I have not issue making that happen.

Adding a new record to tblVehRec is what I am trying to do ultimately. So it sounds as though my SQL append statement is ok? I will make the changes to the data format as suggested. The majority of the fields are text fields that my user is to enter.

The form will do used as an inspection tool to record vehicle details as inspected and I hope to include a continuous form to display previous inspections on specific vehicles.

[HansV]

For numeric and yes/no fields, omit the ' around the values.

[duke_44]

Any chance you can assist me in creating the recordset?

[HansV]

It could look like this (I didn't write out all the details, but I did add comments):

Code: Select all

Private Sub btnSave_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    ' Reference to the active database
    Set dbs = CurrentDb
    ' Open a recordset on tblVehRec
    Set rst = dbs.OpenRecordset("tblVehRec", dbOpenDynaset)
    ' Create a new record
    rst.AddNew
    ' Set field values
    rst!InspDate = Me.txtInspDate
    rst!InspType = Me.cboInspType
    rst!ServID = Me.txtServID
    ' etc.
    ' etc.
    rst!Add6 = Me.txtAdd6
    ' Save the new record
    rst.Update
    ' Close the recordset
    rst.Close
    ' Release the objects
    Set rst = Nothing
    Set dbs = Nothing
End Sub

[duke_44]

Thank you very much Hans!

Is it possible to have certain text boxes automatically make all the letters in the field upper case?

[HansV]

Yes, for example

Code: Select all

    rst!Add6 = UCase(Me.txtAdd6)

[duke_44]

You help is very much appreciated!!
Sorry for all the questions, but I will have more and more

Is it possible to leave a field empty and still utilize the save button? There will be instances where the data would be saved with fields left blank. Then I would need my user to be able to bring up the entry at a later time and finish filling in the fields.

[HansV]

If you also need to be able to edit existing records, it would be much easier to use a form bound to the table. That way, you don't need any code. The user can add new records and edit existing records, and Access will automatically save the record.
If you set the Format property of a text box to the single character >, all text entered in the text box will automatically be displayed in upper case.

[duke_44]

I like the addition of the > in the format, thank you.

I'm sorry but is it possible to help guide me through your suggestion of having the form bound to my table?

[HansV]

The easiest way:
- Select tblVehRec in the Navigation Pane.
- On the Create tab of the ribbon, in the Forms group, click Form.
- Or, if you want to display multiple records simultaneously, click More Forms > Multiple Items.
- Switch to design view and customize the design of the form if you wish.

[duke_44]

Additional. The form is an inspection form. If possible I would like to have a continuous form below the entry area where once the VIN matches previous inspections to show the previous records. Also where there are findings I would like to have a check box in the event a finding has been resolved and only unresolved findings displayed in the cont form.

If this is too much please let me know but I am hoping to get this tool up and running! Thanks again

[HansV]

You could create two forms:

1) A form that displays a single record. Create some empty space on this form. The Record Source of this form is tblVehRec.
2) A form that displays multiple records.

Set the Record Source of the second form to a query that returns only records for which Resolved is False,
Place this form as a subform in the empty space on the first form.
Set the Link Master Fields and Link Child Fields properties of the subform on the main form to VIN.

[duke_44]

Thanks Hans!

I am happy with how the DB is saving and storing the info. I have made it a continuous form to show results below the data entry. I would like the display area to filter based on a vehicles unique ID number so we can see previous inspections of that vehicle. Also in the display area I would like to show any issues that were outstanding after an inspection for tracking purposes. Any suggestions on the filter and how to sort and only display outstanding issues

[HansV]

I'd have to know more about the structure of the database...

[duke_44]

Would it be possible to ask you to have a look at my DB? I can give you a quick run down of what I would like to do with it and hopefully you could provide some feedback and advice to move forward with it?

[HansV]

You can create a stripped-down copy of the database without sensitive information, zip the copy and attach the zip file to a reply (max. size 250 KB).