Confirm Changes in a Query

[161]

Hi, does anyone know if it is possible to have a "confirm changes" message come up in query results?

Right now, I have a search query that is open for editing, but I want a "confirm changes" message to pop up before a field is saved.

[pbaldy]

I don't think so. This is one of the reasons most of us never let users into tables or queries, just forms and reports. You don't have much control when they're in a table or query.

[orange]

As Paul said, this is usually done with a form.
There is a sample here
http://www.databasedev.co.uk/confirm-record-update.html

[161]

Yeah, that could be problematic as I have many many entries. I'm thinking that my QBF would be the ideal place to search for entries and then make edits to them, rather than sifting through a 1000 entry table.

Now, I can make this query read-only, or editable. I was thinking of somehow setting up a password that (when entered) would change the query to editable, and when closed, would change it back to a read-only.

Is this possible? And what sort of command should I be using to alter the properties of a query?

[pbaldy]

I don't know that you can change the properties while it's open. You can use a DAO QueryDef to change them. I'd still use a form. The sample db here demonstrates a form/subform that filters records. There are many other methods. Once you have your data in a form, you have the control you seek.

http://www.baldyweb.com/BuildSQL.htm

[161]

Indeed I am using a form, queries for whatever they input into the form. So, the query shouldn't be opened when they go to search.


I should be able to figure out how to get the password check set up. (I'm just going to put an if/then/else to check for the right word in a field...)

You mentioned DAO QueryDef. How would I add this to my code to open the query as a dynaset? (or snapshot?) That way, I can say *if password right, open dynaset. *if password wrong, open snapshot, and maybe pop up a message.

Thanks so much for the help guys,

[161]

Update! Solved it!

I was able to create a password status in the control source of a field on my form:
=IIf([Password]="ARI1","Password Accepted. Search Results Unlocked","Password Incorrect. Search Results Locked")


Then, I created the code to open my QueryByForm on the OnClick event for my search button.

Private Sub QBF_SearchButton_Click()
On Error GoTo Err_QBF_SearchButton_Click
Dim stDocName As String

If Forms![Search & Modify Database]![Password] = "ARI1" Then
stDocName = "QBF_Query"
DoCmd.OpenQuery stDocName, acNormal
Else
stDocName = "QBF_Query"
DoCmd.OpenQuery stDocName, acNormal, acReadOnly
End If
Exit_QBF_SearchButton_Click:
Exit Sub
Err_QBF_SearchButton_Click:
MsgBox Err.Description
Resume Exit_QBF_SearchButton_Click

End Sub