RunSQL without confirmation question

[bg-consult as, Leif Hauge]

Hi !
I have made a Macro with a RunSQL command in my Access Database where I
update a table with information from my form page. This works fine, but I
get a confirmation question before the info is added to the table. I have
read somewhere that a VBS code can solve this issue, but cannot see how it
is done. Please help.

RunSQL command :
INSERT INTO Table (Value) VALUES ('Test')

Best Regards
Leif Hauge

 

[Stefan Hoffmann]

hi Leif,

I have made a Macro with a RunSQL command in my Access Database where I
update a table with information from my form page. This works fine, but I
get a confirmation question before the info is added to the table. I have
read somewhere that a VBS code can solve this issue, but cannot see how it
is done. Please help.

You can use DoCmd.Echo and DoCmd.SetWarnings, if like to use VBA. These
methodes can also be invoked directly in your macro. Take a closer look
at the macro action combobox.


mfG
--> stefan <--

 

[Brian Wilson]

Hi !
I have made a Macro with a RunSQL command in my Access Database where I
update a table with information from my form page. This works fine, but I
get a confirmation question before the info is added to the table. I have
read somewhere that a VBS code can solve this issue, but cannot see how it
is done. Please help.

RunSQL command :
INSERT INTO Table (Value) VALUES ('Test')

Best Regards
Leif Hauge

While you may want to avoid showing the user any question, you will still
want to know whether your code actually added the record or not. Perhaps
some error occurs (duplicate primary key, invalid value, etc) which means
that your record cannot be added.
The way to do this is to create a function which returns a True/False value
to let you know. So in your code, you can write:

If AddMyRecord()=True Then
Debug.Print "Good"
Else
Debug.Print "Bad"
End If


where the function AddMyRecord is something like this:


Public Function AddMyRecord() As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO MyTable (MyField) VALUES ('Test')"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffected = 1 Then
AddMyRecord = True
End If

Exit_Handler:

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

 

[bg-consult as, Leif Hauge]

hi Leif,


You can use DoCmd.Echo and DoCmd.SetWarnings, if like to use VBA. These
methodes can also be invoked directly in your macro. Take a closer look
at the macro action combobox.


mfG
--> stefan <--

The SetWarnings was the solution for this, thanks.

I entered a new problem once this was solved. When I press "Save" to save
the settings into the current table + the new table, and I have more then 1
user, I get error message saying "You have no access to perform changes due
to other users logged in" or something similar.

How can I avoid this problem ? The information _IS_ saved correctly even if
I get this error message, and now I have used SetWarnings = No and it still
give error message during this function.

Regards
Leif Hauge

 

[Douglas J Steele]

I entered a new problem once this was solved. When I press "Save" to save
the settings into the current table + the new table, and I have more then 1
user, I get error message saying "You have no access to perform changes due
to other users logged in" or something similar.

How can I avoid this problem ? The information _IS_ saved correctly even if
I get this error message, and now I have used SetWarnings = No and it still
give error message during this function.

Sorry, that's the way Access has been since Access 2000.

Your application should be split into a front-end (containing the queries,
forms, reports, macros and modules) and a back-end (containing the tables
and relationships). Only the back-end should be on the server: each user
should have his/her own copy of the front-end, preferably on their hard
drive.

When you're making changes to the application, you should be working on a
test version of the back-end. Once you know that all of the changes are
going to work, you should ensure that each user is out of their copy of the
application in production, make the changes to the production back-end, and
give everyone a copy of the new front-end.

 

[Stefan Hoffmann]

hi Leif,

I entered a new problem once this was solved. When I press "Save" to save
the settings into the current table + the new table, and I have more then 1
user, I get error message saying "You have no access to perform changes due
to other users logged in" or something similar.

I assume you are using DoCmd.Save. This method is saving the actual
Access object, thus you are saving your form.
If you like to save the date only you can use

If Me.Dirty Then Me.Dirty = False


mfG
--> stefan <--

 

[bg-consult as, Leif Hauge]

hi Leif,


I assume you are using DoCmd.Save. This method is saving the actual
Access object, thus you are saving your form.
If you like to save the date only you can use

If Me.Dirty Then Me.Dirty = False


mfG
--> stefan <--

I like this suggestion, sounds doable. Saving only the data would most
likely resolve the issue. How do I use this "Me.Dirty" command, do I add it
into the macro before the "Save" command ? Details is always nice to have =)

PS! I don't use DoCmd.Save, I have selected the macro action "Save", which
basically is the same thing I guess.

Regards
Leif Hauge

 

[Stefan Hoffmann]

hi Leif,

I like this suggestion, sounds doable. Saving only the data would most
likely resolve the issue. How do I use this "Me.Dirty" command, do I add it
into the macro before the "Save" command ? Details is always nice to have =)

If you're only using the "save" command in your macro, use an event
procedure (VBA) instead of the macro. The Me.Dirty = False forces the
form to save its data. It is a replacement for your "save" macro.

Private Sub cmdSave_Click

If Me.Dirty Then Me.Dirty = False

End Sub

PS! I don't use DoCmd.Save, I have selected the macro action "Save", which
basically is the same thing I guess.

Afaik, yes.

mfG
--> stefan <--

 

[bg-consult as, Leif Hauge]

hi Leif,

have =)
If you're only using the "save" command in your macro, use an event
procedure (VBA) instead of the macro. The Me.Dirty = False forces the
form to save its data. It is a replacement for your "save" macro.

Private Sub cmdSave_Click

If Me.Dirty Then Me.Dirty = False

End Sub

Afaik, yes.

mfG
--> stefan <--

That did the trick, now it saves even if I have multiple users in the
database. Many thanks for your quick help =)

Regards
Leif Hauge