469,362 Members | 2,548 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,362 developers. It's quick & easy.

Update Button in a Form

I used the form wizard to create a bound form on Access. On the bound form, there are about 20 text boxes. One of the boxes is the parent key of the linked table. I'd like the form to do 3 things:

1. Be able to add a new record if the parent key number does not exist when a person type in a parent key and corresponding information in the other text boxes.
2. Have information propagate automatically in the text boxes after I enter the parent key if there are records for the key
3. Update the information on a record if the parent key exists and I add new or change information in the other text boxes

I have been able to do #1 with a simple command key. But I have not been able to figure out how to do #2 or #3. I've tried several codes I found to have the Add command button to also update, but after applying them, I always get the error that "The changes...would create duplicate records in the table." Here's the code I have for my current Add button:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. Private Sub command94_Click()
  4. On Error GoTo Err_sSave_Click
  7.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  8.      DoCmd.Close
  10. Exit_sSave_Click:
  11.     Exit Sub
  13. Err_sSave_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_sSave_Click
  17. End Sub
Thanks for any help!
Jul 30 '10 #1
15 9988
931 Expert 512MB
What I would suggest is placing an unbound text box on your form, which is what you'll type the search parameter into. Call it txtSearchID. In the After Update event for this box, you can do something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim rst As DAO.Recordset
  4. strSQL = "SELECT * FROM [table name] WHERE [key value] = " & Me.txtSearchID 
  5. Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  7. Me.AllowAdditions = True
  9. If rst.EOF Then
  10.     MsgBox "No matches!", vbInformation + vbOKOnly, "ID Search"
  11.     Me.DataEntry = True
  12. Else
  13.     Me.DataEntry = False
  14.     Me.RecordSource = strSQL
  15.     Me.AllowEdits = True
  16. End If
  18. rst.Close
  19. Set rst = Nothing
  21. End Sub

Here you would put [key value] = whatever the name of your primary key column is. I have assumed that this is a number. If it is a string you will need to enclose the search parameter in ' ' like this:

Expand|Select|Wrap|Line Numbers
  1. WHERE [key value] = '" & Me.txtSearchID & "'"

What this code does is opens a recordset containing only records with the ID you specified. It then checks if there are any matches (by checking rst.EOF). If there are no matches, the form clears for data entry of a new record. If there are matches, the form's recordsource is set to the records containing those matches, and you will be able to navigate through, and edit, those records.

Jul 30 '10 #2
How would you write the after event line? I am not very familiar with the syntax of VBA coding.
Jul 30 '10 #3
931 Expert 512MB
When you have the form open in design view and go into the property sheet for the text box, there will be an events tab. If you click on the After Update event for the box, select [Event Procedure] and click "...", it will create the event subroutine for you in the VBA window. That's where you'll want to put your code.

Notice that Access is significantly event-driven. This is generally how you work with a control's events.

Jul 30 '10 #4
I typed in the code for this in after update, then entered a value. I got the error, "complie error. User-defined type not defined." After I click ok, it takes me to the VBA coding page and the term, "rst As DAO.Recordset" is highlighted.
Jul 30 '10 #5
931 Expert 512MB
Do you have the reference to the "Microsoft Office 12.0 Access database engine Object Library" reference set? In the VBA window go Tools > References...

If you're using Access 2003 or earlier it would be "Microsoft DAO 3.6 Object Library".

Jul 30 '10 #6
Got everything to work so I deleted all the entries in the table before I put it into operation. But now when I put in a number in the textSearchID field and then click on my Add button, it doesn't save the number as the primary ID; instead the primary number is automatically defaulted to zero. What's more, afterwards when I try to add or update another entry; I get the message "requested changes were not successful because they would create duplicate values in the index, primary key, or relationship." Any idea why the form stopped working after I cleared the table?
Aug 2 '10 #7
931 Expert 512MB
What code do you have set for your Add button?

Is the ID column an autonumber? It sounds like it might be, in which case you would want to make it just a number so that you can assign the ID as you wish.

In the case where your record is new (i.e. the search value is not found), you need to set the bound ID text box to the value that you tried to search for. This way, even if you don't enter any other data for that record, the record will still be established with an ID.

Aug 2 '10 #8
ID Column is a regular number; I think it might have something to do with the code in my "Add button." Here's the current VBA code for it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub command94_Click()
  2. On Error GoTo Err_sSave_Click
  4. If Me.Dirty Then RunCommand acCmdSaveRecord
  5. Me.[txtSearchID].SetFocus
  6. RunCommand acCmdFind
  8.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  9.      DoCmd.Close
  11. Exit_sSave_Click:
  12.     Exit Sub
  14. Err_sSave_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_sSave_Click
  18. End Sub
I thought this was the exact code I had before it stopped working; other than this code, I just have the textsearchID_Afterupdate code that you provided before
Aug 2 '10 #9
931 Expert 512MB
I think the code in your Add button is the culprit. Usually you don't need to do anything explicit to save a record in a bound form, because when you make a change to a field in a bound form, the change happens in the corresponding column of the table immediately.

You can see this by entering a new record and then using Access' built-in navigation bar at the bottom of the form to move to a new record. The fields in the form should clear, and when you go to the table and refresh it the record you just entered should be there.

So really there is no technical need for a custom Add button; however the users of your database might expect that functionality. To this end you could program the button to just move to a new record, and if you don't want them to use the built-in navigation buttons you can remove those in form design view.

Aug 2 '10 #10
I think the problem is because my "txtSearchId" is unabound. So when I hit the Add button, all other fields try to update to the table, but they are unable to because there is no field representing the primary key, "Numbers," on the main form and thus the form thinks the primary key is "0" when in reality it is what is supposed to be in the "txtsearchID" field. I just need a code that updates the primary key "Numbers" column in the table with whatever is in the "txtsearchID" field. This form was working properly but for some reason this VBA code had a disconnect and the form stopped running properly.
Aug 2 '10 #11
931 Expert 512MB
As I mentioned earlier today in post #8, when you enter your search ID into txtSearchID, and it is not found, you need to the set your bound ID field to whatever value is in the unbound search field. This could be done in the first branch of the If statement that I suggested. For instance:

Expand|Select|Wrap|Line Numbers
  1. If rst.EOF Then
  2.     MsgBox "No matches for that product ID.", vbInformation + vbOKOnly, "Product ID Search"
  3.     Me.DataEntry = True
  5.     Me.[name of bound ID box] = Me.txtSearchID
  6.     Me.[name of first data entry box].SetFocus
  8. Else
  10.     ...
  12. End If

In this manner, even if you enter nothing else for that record, the record will be established with the ID and you would at least be able to search for it later on and do further data entry on it.

Aug 2 '10 #12
Got it to work. I had deleted the bounded ID box and that was why the primary key was always 0. Can you take a look at my code in post #9 and tell me how to get stop the form from automatically closing every time I hit the Add button? I had inserted that function when I thought I wanted the database to be in a different format. I would instead like the form to display a pop up message every time it successfully adds or updates.
Aug 3 '10 #13
931 Expert 512MB
Well you have the line DoCmd.Close in the button's subroutine, so I would say that is what's causing the form to close.

If you want a message box to pop up, I suggest researching the MsgBox function, and consider using it in the button's code.

Honestly, I'm a little confused by most of the code that you have in the button's click event. What is the purpose of the following two lines?

Expand|Select|Wrap|Line Numbers
  1. Me.[txtSearchID].SetFocus
  2. RunCommand acCmdFind

Aug 3 '10 #14
Took out the docmd.close command. Now everytime I hit the Update button, a find and replace window comes up insetad of the form saving the data and closing itself.
Aug 4 '10 #15
931 Expert 512MB
To be truthful, you need to research your code a little and understand what it is actually doing. And if you want anyone on this forum to help you, you need to respond to the questions that are asked.

For instance, in my last post I inquired about two particular lines of code, but you seem to have ignored that question. If you put a little research into the code that you're using, I think you'll find the answer as to why the Find and Replace dialog is popping up.

Aug 4 '10 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.