Edit table through a form (1 Viewer)

awade · Jul 1, 2015

Good Afternoon,

I have been developing a form that will allow me to input, edit and delete data in a table.

I have the input, delete, clear, and close command buttons working ok, but I am having trouble getting the edit function to work.

What happens is that I highlight the line in the table (shown on the form as a sub form) click the edit command button, and the field are populated with the current data. I then edit the field that I want, and the add, button, changes to Update. When I go to update I keep getting an error message "Cannot update 'ID'; field not updatable.
Below is the code I am using, when I go to the debug the arrow points to the "WHERE ID=" line.

Private Sub cmdAdd_Click()
'when we click on button Add there are two options
'1. for insert
'2. for update
If Me.txtID.Tag & "" = "" Then
'this is for insert new
'add data to table
CurrentDb.Execute "INSER INTO tbllessonslearned(ID, Aircraft, TailNumber, Day, Classification, AddedBy, LessonsLearned) " & _
" VALUES(" & Me.txtID & ",'" & Me.cboAircraft & "','" & _
Me.txtTailNumber & "','" & Me.txtDay & "','" & Me.cboClassification & "','" & Me.txtAddedBy & "','" & Me.txtLessonsLearned & "')'"
Else
'otherwise (Tag of txtID store the ID of tbllessonslearned to be modified)
CurrentDb.Execute "UPDATE tbllessonslearned " & _
" SET ID=" & Me.txtID & _
", Aircraft='" & Me.cboAircraft & "'" & _
", TailNumber='" & Me.txtTailNumber & "'" & _
", Day='" & Me.txtDay & "'" & _
", Classification='" & Me.cboClassification & "'" & _
", AddedBy='" & Me.txtAddedBy & "'" & _
", LessonsLearned='" & Me.txtLessonsLearned & "'" & _
" WHERE ID=" & Me.txtID.Tag
End If
'clear form
cmdClear_Click
'refresh data in list on form
frmlessonslearnedsub.Form.Requery
End Sub

Any assistance is finding where I have gone wrong would be most appreciated.

MarkK · Jul 1, 2015

Are you aware that you can just bind a table to a form, and bind control to fields in the table, and then you can update, insert and delete in a form with no code? For what Access is able to do, this seems extremely laborious.

DennisOJensen · Jul 1, 2015

Egads no no bind -- if you can do it without binding I applaud you. Binding has its own set of issues and problems

And while it takes a bit more effort up front to do it this way you have a solution that has no binding bug issues and such and can be much more easily ported to a VB.Net environment later on if ever desired.

BTW did you notice that your INSERT is spelled INSER ??

spikepl · Jul 1, 2015

@DennisOJensen

Please do remember whom you are advising about what, and otherwise keep doing a good job.

Probably 90% of the posters here are not programmers and are not interested in programming as such, but simply do Access programming due to circumstances. For ad-hoc programmers there is quite a difference between using the built-in facilities of Access such as CRUD, a free feature of bound forms, and doing the extra processing required for use of unbound forms.

awade · Jul 1, 2015

Thanks for picking up the spelling mistake @DennisOJensen. I have fixed that up and still have the same issue.

When I debug the code all of this is highlighted yellow......

CurrentDb.Execute "UPDATE tbllessonslearned " & _
" SET ID=" & Me.txtID & _
", Aircraft='" & Me.cboAircraft & "'" & _
", TailNumber='" & Me.txtTailNumber & "'" & _
", Day='" & Me.txtDay & "'" & _
", Classification='" & Me.cboClassification & "'" & _
", AddedBy='" & Me.txtAddedBy & "'" & _
", LessonsLearned='" & Me.txtLessonsLearned & "'" & _
" WHERE ID=" & Me.txtID.Tag

Still cant get it to edit!!

DennisOJensen · Jul 1, 2015

Okay spikepl -- I did say it with a smile

and while I do realize that most people that use Access are adhoc programmers I have always encouraged them to do things in a manner that facilitates maintenance and less headaches. Mainly because I have to do this with actual programmers as well. Got to remind them to have one eye on how easily it is going to be able to troubleshoot and maintain that piece of code they wrote. Further I would be greatly surprised if I changed anyones preferences on how they choose to code. I might influence them a bit but change generally comes from within not without.

Back to the issue at hand. The following code should help you clean up and perhaps fix some of your issues. First off I am going to assume that ID is your PrimaryKey to tblLessonsLearned as such when doing an Update you cannot Update the PrimaryKey so I removed it. Next "Day" is a reserved word in SQL so I changed the field to "SomeDay" I would strongly suggest you change it to whatever it reflects more closely. Further if it is a Date type then the code below might not work as I think Date types have to be Numbered rather than Single Quoted for Access SQL but you can look that up. I have included a constant for Number just in case -- just replace the Quotes with Nmbr if it is a Date field and the Quote does not work or if you find that indeed Date types need to be Numbered in Access SQL. And then other than making it look pretty for ease of readability -- that was about all I did to what you had. Give that a try and see what happens.

Code:

[FONT="Courier New"]
Constant Quote As String = Chr(39);
Constant Nmbr As String  = Char(35);

sqlQuery = ""
sqlQuery = sqlQuery & "UPDATE tbllessonslearned"
sqlQuery = sqlQuery & "      SET Aircraft       = " & Quote & Me.cboAircraft       & Quote 
sqlQuery = sqlQuery & "         ,TailNumber     = " & Quote & Me.txtTailNumber     & Quote
sqlQuery = sqlQuery & "         ,SomeDay        = " & Quote & Me.txtDay            & Quote
sqlQuery = sqlQuery & "         ,Classification = " & Quote & Me.cboClassification & Quote
sqlQuery = sqlQuery & "         ,AddedBy        = " & Quote & Me.txtAddedBy        & Quote
sqlQuery = sqlQuery & "         ,LessonsLearned = " & Quote & Me.txtLessonsLearned & Quote
sqlQuery = sqlQuery & " WHERE ID = " & Me.txtID.Tag

CurrentDb.Execute sqlQuery
[/FONT]

awade · Jul 1, 2015

Thanks fro the reply @DennisOJensen.

So not being really fluent in all this code writing. I just replace the yellow highlighted area with what you have provided?

The Day bit is a date, but I have it as a txt field, is that going to cause issues or will it be ok to leave it like that?

DennisOJensen · Jul 1, 2015

First I would like to point out what I have presented is no more code than what you had presented it is just dressed slightly different. Which is to say its the same man just in different clothes.

Constant Quote As String = Chr(39); << This is a Single Quote = '

Constant Nmbr As String = Char(35); << This is a Number Symbol = #

Code:

sqlQuery = ""
sqlQuery = sqlQuery & "UPDATE tbllessonslearned"
sqlQuery = sqlQuery & "      SET Aircraft       = " & Quote & Me.cboAircraft       & Quote 
sqlQuery = sqlQuery & "         ,TailNumber     = " & Quote & Me.txtTailNumber     & Quote
sqlQuery = sqlQuery & "         ,LessonDate     = " & Quote & Me.txtDay            & Quote
sqlQuery = sqlQuery & "         ,Classification = " & Quote & Me.cboClassification & Quote
sqlQuery = sqlQuery & "         ,AddedBy        = " & Quote & Me.txtAddedBy        & Quote
sqlQuery = sqlQuery & "         ,LessonsLearned = " & Quote & Me.txtLessonsLearned & Quote
sqlQuery = sqlQuery & " WHERE ID = " & Me.txtID.Tag

This is just your Query with a few minor changes one of which is I replaced your ' with variable Quote. Just hoping that will help you be more comfortable with what I posted.

Now back to your questions -- No you can store a Date as Text it just will not be viewed as a Date which is only an issue if you planned to do things with that Date other than just print it out or just look at it.

Keep in mind that if you just copy/paste what I presented you are going to have to change the value you have for the Day field in your database to SomeDay but I would suggest changing both to maybe LessonDate (which I did above)

MarkK · Jul 2, 2015

This is not valid VBA . . .

Code:

Constant Quote As String = Chr(39);
Constant Nmbr As String  = Char(35);

Probably you mean . . .

Code:

Const Quote As String = Chr(39)
Const Nmbr As String = Char(35)

If you are a beginner at this I recommend you bind your forms. Then you have very little code to write.

If you are going to create action queries on the fly, it is more reliable to create a QueryDef and let that object handle your delimiters. All the code posted so far will fail if the user enters data like . . .

Code:

8' x 2" x 4"
[COLOR="Green"]'or[/COLOR]
O'Leary

Consider code like . . .

Code:

    Const SQL_UPDATE As String = _
        "UPDATE tbllessonslearned " & _
        "SET Aircraft = p0, TailNumber = p1, SomeDay = p2, " & _
            "Classification = p3, AddedBy = p4, LessonsLearned = p5 " & _
        "WHERE ID = p6;"

    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        .Parameters(0) = Me.cboAircraft
        .Parameters(1) = Me.txtTailNumber
        .Parameters(2) = Me.txtDay
        .Parameters(3) = Me.cboClassification
        .Parameters(4) = Me.txtAddedBy
        .Parameters(5) = Me.txtLessonsLearned
        .Parameters(6) = Me.txtID
        .Execute dbFailOnError
        .Close
    End With

. . . which is clearer, more robust, will raise DAO errors (if they occur), and most of all delimiters are automatic, so you can use any number of quotes in your data entry (as would be the case if you bound your forms).
HTH

awade · Jul 2, 2015

Thanks @Markk and @DennisOJensen.

This is all a bit over my head, as im really new to this.

I am unsure where to insert this code you have provided. I will start having a play around with it all, but any advice would be most appreciated.

awade · Jul 2, 2015

@Markk I have added you code and I keep getting "Too few parameters. Expected 8" error message.
When I debug the following line is highlighted.

.Execute dbFailOnError

Can you please help with this.

@DennisOJensen I added you code and nothing happens when I try to update a row in the table. I press the edit button and all the fields a re populated, I change the fields as required then hit the update button and nothing changes.

DennisOJensen · Jul 2, 2015

Okay first Markk's way of doing it would be the more proper way of doing it and I would go with that if I had my choice. As for either or both of them failing I think we need more details. Things like what does the table structure look like that you are trying to Update and what does the On_Click Event code look like in its entirety -- hopefully those to items (in detail) will paint a big enough picture to help fix your problems.

BTW if what we have presented is "over your head" where did you get the initial SQL call you were making?

awade · Jul 2, 2015

Thanks @DennisOJensen I have attached the database as im working on it now.

Just some of the information and language you and @Markk are using I haven't heard before. I have been watching youtube videos, trawling through the forums getting information and finding new ways to do things, as I am new to some parts of access.

gemma-the-husky · Jul 2, 2015

awade

I have to say, if you are using access, the sensible choice would be to bind the form to the table, as MarkK suggested.

Occasionally I may use an unbound form for a special reason, but 99% of the time, they are bound.

I would learn how to use access with bound forms, and then if you want unbound at some point, it will be much easier.

awade · Jul 3, 2015

@gemma-the-husky I have attached the access database im working on at the moment in a previous post.
I will have to look at how to bound a to a table, and see if that will allow me to edit, add, delete rows from the table via the form fields.

gemma-the-husky · Jul 3, 2015

awade said:
@gemma-the-husky I have attached the access database im working on at the moment in a previous post.
I will have to look at how to bound a to a table, and see if that will allow me to edit, add, delete rows from the table via the form fields.

the easiest way:

use the create form wizard. pick a table or query, select some options, and your bound form will appear. No code at all.

If this is really all new to you, then I think you ought to get hold of a general "using access" book to work through. You just can't "hack" access, in the way you can with excel. (eg, I think I have 1 excel reference book, hardly ever used, and at least 4 or 5 regularly used access books, as well as others that only get an occasional outing. I have never needed to use excel forums)

awade · Jul 3, 2015

@gemma-the-husky. If I bound the form to the table, then how do I make the command buttons, to add, update, clear, and delete data from the table?

DennisOJensen · Jul 3, 2015

Okay I differ in opinion than with gemma -- personally I try to guide newbies away from bound controls because there are so many inherent little issues that if you are not familiar with -- can cause you a world of headaches. I have not only seen this used in access but I have seen it used in other platforms as well and even experienced programmers can run afoul of bound controls. The unbound is, in my opinion, safer for newbies as you have a better handle on what is going to happened and rarely get hit with any hidden surprises. Yes it is a lot easier to use bound controls but to me that is just lazy coding

which I reserve for slapping together a throw away project really quickly.

I will try to look at your database soon and give you feed back on it provided someone else does not beat me to it.

vbaInet · Jul 3, 2015

DennisOJensen said:
...there are so many inherent little issues that if you are not familiar with -- can cause you a world of headaches.

I wonder what sort of issues you speak of?

DennisOJensen said:
...personally I try to guide [bold]newbies [/bold]away from bound controls. The unbound is, in my opinion, safer for [bold]newbies[/bold] as you have a better handle on what is going to happened and rarely get hit with any hidden surprises. Yes it is a lot easier to use bound controls but to me that is just lazy coding

It's quite obvious that awade is quite new to all of this and one cannot expect him/her to start coding without understanding coding basics and most importantly the fundamentals of Access. My suspect is that most of the code awade has presented was cobbled together from the net. You will find that most posters on Access forums are not developers, and as such are taking baby steps until the point where they can learn VBA.

I would imagine that the newbies in your work environment that you refer to are already programmers and it obviously makes sense for them to do things in such a way that they have full control of the form. But in an environment like Access, it's always advised to start off with bound forms, learn some VBA basics and then move on to unbound forms if they so wish. Not everyone is looking to migrate to a .NET application in the near future. Many are just projects for small businesses or hobby projects.

gemma-the-husky · Jul 3, 2015

awade said:
@gemma-the-husky. If I bound the form to the table, then how do I make the command buttons, to add, update, clear, and delete data from the table?

you don't need any. you can choose to design the form to show one record at a time, or a screen full at a time.

either way, you get a navigation control at the bottom to move through your records.

if you move past the last record, you will get a blank record ready for inputting a new record.

on any existing record you can just edit any value to a different value

you can highlight any record, and delete it just by pressing the del key

and you will never need to "clear" a record. If you don't need it, delete it.

----
you really need to just try it. you can't really describe the experience adequately

----

now you start getting clever. you can set form level settings to disallow deletes or additions. you can lock certain controls to prevent them being edited. with a bit of code you can do this selectively depending on the record and/or depending on the user. stuff like that.

this is all pretty basic, and you need to be familiar with all this stuff before starting a project (or learn it at the same time) - but you really need a basic access primer. Don't know if it's still the same, but personally I think Alison Balter's books are as good as any and better than most.

Edit table through a form (1 Viewer)

Registered User.

bit cruncher

Registered User.

Eledittingent Beliped

Registered User.

Registered User.

Registered User.

Registered User.

bit cruncher

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Super Moderator

Registered User.

Super Moderator

Registered User.

Registered User.

AWF VIP

Super Moderator

Similar threads

Users who are viewing this thread