Update form
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Update form
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.
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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?
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?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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!
'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!
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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.
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
Any chance you can assist me in creating the recordset?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
Thank you very much Hans!
Is it possible to have certain text boxes automatically make all the letters in the field upper case?
Is it possible to have certain text boxes automatically make all the letters in the field upper case?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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.
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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?
I'm sorry but is it possible to help guide me through your suggestion of having the form bound to my table?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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.
- 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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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
If this is too much please let me know but I am hoping to get this tool up and running! Thanks again
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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.
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.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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
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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Update form
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?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Update form
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).
Best wishes,
Hans
Hans