Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access: Edit data in a form 1

Status
Not open for further replies.

ShabanaHafiz

Programmer
Jun 29, 2003
72
PK
I am using MS Office Access 2003.

I created a table (name: Item) with three fields; ItemID, Active and Description. ItemID is Primary Key. Then I created a form (name: Item) and chose Item table in New Form dialog box. In the form, Item (Combo Box), Active (Text Box) and Description (Text Box) are displayed. Form has two command buttons cmdAddRecord and cmdSaveRecord.

cboItemID has a Row Source property set to Item. What I need to accomplish is when a user selects an existing ItemID from cboItemID instead of adding a new record, then all attributes of that record should be populated on the form.

I wrote the following code in cboItemID AfterUpdate event:

Code:
Private Sub cboItemID_AfterUpdate()

Dim rs As Object

If Not Me.NewRecord Then
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ItemID] = '" & Me![cboItemID] & "'"
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
End If

End Sub

When this code is executed, I get a Run-time error ‘3022’ at the execution of this line:
Code:
Me.Bookmark = rs.Bookmark

Error Message is:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.
 
Maybe instead of this:
Code:
If Not rs.EOF Then
...this?
Code:
If Not rs.[b][red]NoMatch[/red][/b] Then
HTH,

Ken S.
 
For me, a combo used as a navigation tool should be unbound.
So, I suggest a textbox for the bound ItemID control and to follow the combo wizard (3rd option) for the nav combo in the header section of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Thanks PHV.

I right-clicked cboItemID and Changed it to Text Box. Now the control's name is txtItemID and control source is ItemID.

I did not get the other part of your suggestion:
[blue] follow the combo wizard (3rd option) for the nav combo in the header section of the form. [/blue]

Can you please explain it more?
 
In the header section of your form create a combo and choose the 3rd option proposed by the wizard.
BTW, the wizard should be activated (wand button).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Thanks. It worked and the wizard generated the following code:

Code:
Private Sub Combo20_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ItemID] = '" & Me![Combo20] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top