asked on

Access 2007 - Allow Edit on a form (form based on query)

We would like to be able to edit records on our forms. The forms are set to use a query.

I set the form to Dynaset (Inconsistent Updates) and allow edits.

Question: Is it better to create a recordset on form load, and use that recordset for the form? Then when there are data changes update the table? As you can tell I'm trying to learn and any input would be appreciated.

What am I missing?

I will attached a sample database for your reference.

Thanks in advance. Rick Cass

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

What you are doing is the 'typical' to use Access under 'normal' conditions ... a Form bound to a Record Source, in this case a query based on a table(s).

mx

REIHELPDESK

ASKER

Okay. How can I enable the form to allow for edits?

REIHELPDESK

ASKER

Here is our database.

REIHELPDESK

ASKER

Actually, here it is.

Cant figure out how to upload Access 2007.
Development-Database-122909-2003.zip

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

ok ... I have the mdb. Which form ?

mx

REIHELPDESK

ASKER

Let's focus on the contacts form.

REIHELPDESK

ASKER

Open the contact form - frmContactInformation. We want to be able to edit the fields starting with Organization and down.

The top three fields are combobox that allow us to search through the records. We just want to be able to edit fields when we get to the right record.

Thanks,

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You have a Group By query for a Record Source. GroupBy (aggregate) queries are *not* updatable ... so you will have to change the record source to a regular SELECT query.

mx

REIHELPDESK

ASKER

Okay, I changed the query qryContacts removing the sort by and that worked.

Question: What would be the best way to warn a user that they are changing the data? I was thinking about inserting a message, an onclick event, stating their changing the data; however, I'm looking for a more practible and standard.

Thanks,

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

REIHELPDESK

ASKER

Here is what I did and I know it can be done better.

Private Sub Company_Organization_Click()
Call AlertMessage
End Sub
Private Sub First_Name_Click()
Call AlertMessage
End Sub
Private Sub Last_Name_Click()
Call AlertMessage
End Sub
Private Sub Title_Click()
Call AlertMessage
End Sub
Private Sub Addressee_line_Click()
Call AlertMessage
End Sub
Private Sub address_1_Click()
Call AlertMessage
End Sub
Private Sub Address_2_Click()
Call AlertMessage
End Sub
Private Sub City_Click()
Call AlertMessage
End Sub
Private Sub Type_Click()
Call AlertMessage
End Sub
Private Sub Zip_Click()
Call AlertMessage
End Sub
Private Sub County_Click()
Call AlertMessage
End Sub
Private Sub Town__City__County_Click()
Call AlertMessage
End Sub
Private Sub Phone_Click()
Call AlertMessage
End Sub
Private Sub Fax_Click()
Call AlertMessage
End Sub
Private Sub E_mail_Click()
Call AlertMessage
End Sub
Private Sub Notes_Click()
Call AlertMessage
End Sub

Function AlertMessage()
Select Case MsgBox("Your about to change the data in this field! Do you want to continue?", vbYesNo, "Change Data")
Case vbYes
'Do Noting
Case vbNo
'Set focus to top of form, Organization lookup
Me.OrganizationLookup.SetFocus
End Select

End Function

Please tell me how or what I should do to make this better.