Update Button in a Form

[Stevestan]

Hi,
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:

Option Compare Database
 
Private Sub command94_Click()
On Error GoTo Err_sSave_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
     DoCmd.Close
 
Exit_sSave_Click:
    Exit Sub
 
Err_sSave_Click:
    MsgBox Err.Description
    Resume Exit_sSave_Click
 
End Sub

Thanks for any help!

[patjones]

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:

Dim strSQL As String
Dim rst As DAO.Recordset
 
strSQL = "SELECT * FROM [table name] WHERE [key value] = " & Me.txtSearchID 
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
 
Me.AllowAdditions = True
 
If rst.EOF Then
    MsgBox "No matches!", vbInformation + vbOKOnly, "ID Search"
    Me.DataEntry = True
Else
    Me.DataEntry = False
    Me.RecordSource = strSQL
    Me.AllowEdits = True
End If
 
rst.Close
Set rst = Nothing
 
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:

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.

Pat

[Stevestan]

How would you write the after event line? I am not very familiar with the syntax of VBA coding.

[patjones]

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.

Pat

[Stevestan]

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.

[patjones]

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".

Pat

[Stevestan]

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?

[patjones]

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.

Pat

[Stevestan]

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:

Private Sub command94_Click()
On Error GoTo Err_sSave_Click
 
If Me.Dirty Then RunCommand acCmdSaveRecord
Me.[txtSearchID].SetFocus
RunCommand acCmdFind
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
     DoCmd.Close
 
Exit_sSave_Click:
    Exit Sub
 
Err_sSave_Click:
    MsgBox Err.Description
    Resume Exit_sSave_Click
 
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

[patjones]

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.

Pat

[Stevestan]

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.

[patjones]

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:

If rst.EOF Then
    MsgBox "No matches for that product ID.", vbInformation + vbOKOnly, "Product ID Search"
    Me.DataEntry = True
 
    Me.[name of bound ID box] = Me.txtSearchID
    Me.[name of first data entry box].SetFocus
 
Else
 
    ...
 
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.

Pat

[Stevestan]

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.

[patjones]

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?

Me.[txtSearchID].SetFocus
RunCommand acCmdFind

Pat

[Stevestan]

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.

[patjones]

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.

Pat