[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

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
0
REIHELPDESK
Asked:
REIHELPDESK
  • 7
  • 4
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
REIHELPDESKAuthor Commented:
Okay.  How can I enable the form to  allow for edits?
0
 
REIHELPDESKAuthor Commented:
Here is our database.  
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
REIHELPDESKAuthor Commented:
Actually, here it is.  

Cant figure out how to upload Access 2007.
Development-Database-122909-2003.zip
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... I have the mdb.  Which form ?

mx
0
 
REIHELPDESKAuthor Commented:
Let's focus on the contacts form.
0
 
REIHELPDESKAuthor Commented:
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,
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
REIHELPDESKAuthor Commented:
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,
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Would you mind closing this Q and open a new question ... since that is a different subject.

thx.mx
0
 
REIHELPDESKAuthor Commented:
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.  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now