I have a form to update records in a table.
The problem is that I only want to put the new values into the table when I click a button.
Now, if I enter 'man' into a textbox, it automaticly add this value in the table when I leave the textbox.
You could make the form an unbound form, so it isn't linked to a table then use some VBA code to add the record to the table and reference the textbox names to the field name.
Sean,
in your form, create a textbox, and also a button (onclick)
for example name the textbox "txtNewValue" and the button "clkUpdate" in their properties
On the properties window again, select the clkUpdate button, Event, On Click, [Event Procedure], Module
(I have the Access 2010 so it might be different)
In the VBA module:
Code:
Private Sub clkUpdate_Click()
Dim update As String
Dim newvalue as string
newvalue = me!txtNewValue
update = "UPDATE yourtabletoupdate SET yourfieldtoupdate = '" & newvalue & "'"
DoCmd.RunSQL update
msgbox "Your table is updated"
End Sub
something like that (air code)
Once you have more details like tablename, fieldname, type of value, we will be happy to help you more if needed
The problem is, i got 44 columns in this table.
I think it's not practical to write this code 44 times ?
Next problem: it's not always the same textboxes that are needed to be updated.
I know how to do this when you need to add a new record, but not when you need to update it
I get your point, I did a similar code 2 months ago
Just imagine you have a tabular form (listing all your records). Somewhere you add an unbound field.
Then you have 1 part of the code to 'catch' the key number or unique value of your record. Because of that unique value, you can tell your second part of the code (the one I gave you) where to update.
the SQL line in the previous code can be completed by a
"... WHERE KeyNumber = " & strkeynumber
But really, it all depend the layout/context/format of your form. Say what you want to show, and we can help you more.
For example, you have all your records shown on a form, and a Checkbox for each of them, plus that extra unbound field. You can imagine to add/update the value contained in that unbound field to each record marked by the checkbox.
My 2 cents.
Example:
I have e record which has a 'name' and a 'birthday'.
On the form, there are also other textboxes with for example 'Adress'
For that record, in the form, the textboxes for 'name' and 'birthday' will be displayed, but not for 'adress' because it's not in the table.
another record has for example 'name' and 'adress'.
Again, in the form the 'name' and 'adress' will be displayed but 'birthday' stays empty because it's not in the table for that record.
I want to add 'adress' or 'birthday' or others to the table with a click on a button, and not when I leave the textbox.
I now have an emergency at work, So I'm going to guide you only. no time now for coding.
first note: you can't use "name" as it is a reserved word for Access. You may use Firstname instead.
your form:
firstname=bound to your table
then add:
Birthdate = unbound
Address = unbound
Make sure in fields properties to give a name more explicit than "text23" or any other. For example fldFirstname ; fldAddress ; fldBirthDate
VBA on_load = will update from the table, even if fields are not bound
VBA on_click (sorry, no time for the code now)
(basically, use if from my previous post = enter a value in a field, and update it to your_table in regard of the concerned FirstName)
Make a small action button next to the fldAddress field, and to the fldBirthDate field
Ooops, my emergency was longer than I thought: I needed 2 weeks to solve a bunch of issues all related. And then I forgot to get back to you.
Did you manage... whatever?
The belief that unbound forms are necessary in order to do data validation before "committing" data is simply not true! And entering 'man' into a textbox does not automatically add this value in the table when you leave the textbox! Values entered into a Control on a form are not saved to the table until the entire record is saved, as soon as the Form_BeforeUpdate event runs!
A big part of the reason to use Access for database development is the speed with which it can be created, using bound forms. Several developers I know, experienced in Visual Basic database development and Access development, estimate that development using unbound forms by experienced developers takes two to three times as long as it does when using Access and bound forms.
If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.
You can create an EXE file which gives total protection to your code/design
You can distribute the db to PCs without a copy of Access being on board
Your data security is far, far better than anything you can do in Access
By using a 'save' button you're trying to reinvent the way Access is intended to work, and this becomes a problem with experienced Access data input users. They know that Access saves records when moving to another record or when the form is closed, and expect this behavior. It's a better policy to allow Access to work in its native way and merely check with the user before the record is saved, allowing them to save it or dump the new record or changes to existing records. This piece of code will do just that
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
Me.Undo
End If
Else
If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
Me.Undo
End If
End If
End Sub
You can still add a "Save" button, if some users expect it, merely having it set up to move to a new record. The users will still be asked to confirm the save, from the code above, but experienced Access users can run the app the way they know it's intended to work.
The code works perfect.
I would like to save the modifcations when I press an 'update'-button and I want to execute this beforeupdate-event only when I didn't press the 'update'-button.
How can I do this?
The code works perfect.
I would like to save the modifcations when I press an 'update'-button and I want to execute this beforeupdate-event only when I didn't press the 'update'-button.
How can I do this?
Never Mind, found it ^^
I didn't see this: You can still add a "Save" button, if some users expect it, merely having it set up to move to a new record. The users will still be asked to confirm the save, from the code above, but experienced Access users can run the app the way they know it's intended to work.
