Solved

How do I get rid of the message box telling me I am going to append a record?

Posted on 2012-04-02
18
382 Views
Last Modified: 2012-04-11
The database I am working on is in Access 2007. I want the user to be able to add activities that he/she has performed during the week. The code below will add the activities to either one or two lists, depending on if the activity has been added previously.

Right now, for ever record that is being added to either list, I get a message box telling me I am about to append a record to the table. Is there a way to get rid of this? I don't want my users to have to click "Ok" 15 times when they fill out the form.

Thank you!





Private Sub btnAdd_Click()


Dim strSQL As String
Dim db As Database
Dim rst As DAO.Recordset
Dim LngItem As Long

Set db = CurrentDb()




Dim rowc As Integer


With Me.LstNewAct
  For rowc = 0 To .ListCount - 1
    strSQL = "SELECT * FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    
    Set rst = db.OpenRecordset(strSQL)
    If (rst.BOF And rst.EOF) Then ' There are no records if Beginning-Of-File and End-Of-File are both true.
      DoCmd.RunSQL "INSERT INTO tbl_Activities (actName) VALUES ('" & .Column(0, rowc) & "')"
      rst.Close
    End If
    strSQL = "SELECT actID FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    Set rst = db.OpenRecordset(strSQL)
    DoCmd.RunSQL "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, '" & rst.Fields(0).Value & "', WkDate, actType1)"
  Next rowc
End With



With Me.LstAct
  For Each varItem In .ItemsSelected
    LngItem = .Column(0, varItem)
    strSQL = "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, " & LngItem & ", WkDate, actType1)"
    DoCmd.RunSQL strSQL, dbFailOnError
    
  Next varItem
End With



End Sub

Open in new window





Any and all help is greatly appretiated!!!!!
0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +2
18 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37796927
Use DoCmd.Execute strsql, dbfailonerror   ( instead of RunSql)
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37796959
I agree with Peter.

The best technique is to use the execute method, and don't forget to add the dbFailOnError argument, which will raise an error if something in your SQL query generates an error.  You will also need to add an error handler to address possible errors.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37797042
i suggest that you use

currentdb.execute strSQL, dbFailOnError
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37797045
i suggest that you use

currentdb.execute strSQL, dbFailOnError


or

db.execute strSQL, dbFailOnError
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37797140
<No Points wanted>

 what the Experts above have posted is the most reliable method...

To be clear, there is a setting that does what you are asking for directly, but "managing" this setting can become confusing.

;-)

JeffCoachman
0
 

Author Comment

by:Megin
ID: 37802270
I have added that piece of code, but now it is coming up with errors and highlighting that piece of code when I try to run it.

The message I get is "The expression On Click you entered as the event property setting produced the following error: Method or data member not found"

?????
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37802302
Post the exact, full code you tried please...
0
 

Author Comment

by:Megin
ID: 37802314
It is in my original post, but I will post it again (I put the code that gives me the append record notification back in because, while it is annoying, it doesn't give me an error message):

Private Sub btnAdd_Click()


Dim strSQL As String
Dim db As Database
Dim rst As DAO.Recordset
Dim LngItem As Long

Set db = CurrentDb()




Dim rowc As Integer


With Me.LstNewAct
  For rowc = 0 To .ListCount - 1
    strSQL = "SELECT * FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    
    Set rst = db.OpenRecordset(strSQL)
    If (rst.BOF And rst.EOF) Then ' There are no records if Beginning-Of-File and End-Of-File are both true.
      DoCmd.RunSQL "INSERT INTO tbl_Activities (actName) VALUES ('" & .Column(0, rowc) & "')"
      rst.Close
    End If
    strSQL = "SELECT actID FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    Set rst = db.OpenRecordset(strSQL)
    DoCmd.RunSQL "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, '" & rst.Fields(0).Value & "', WkDate, actType1)"
  Next rowc
End With



With Me.LstAct
  For Each varItem In .ItemsSelected
    LngItem = .Column(0, varItem)
    strSQL = "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, " & LngItem & ", WkDate, actType1)"
    DoCmd.RunSQL strSQL, dbFailOnError
    
  Next varItem
End With



End Sub

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37802323
use either:

currentdb.execute strSQL, dbFailOnError

or

DoCmd.Execute strsql, dbfailonerror

Or, you could declare a variable for the datbase object and instantiate it:

dim db as DAO.database
set db = currentDb()

But if you do this, you should also set it to nothing at the end of the code module, to clean-up after yourself

set db = nothing

If you have a bunch of queries to execute in the same code module, adding the db object and declaration will probably speed up your code some.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37802366
Change your declaration statement to:

Dim db as DAO.Database
0
 

Author Comment

by:Megin
ID: 37803870
I made the changes, but now I am getting the error message "Run-time error '3061': Too Few parameters. Expected 4"

Here is what I now have for code. I am still getting one 'append' message when I try to update the report. Then I get the error message.

I am sure this is just me making the wrong changes. I am really, really new to this. Thank you for all of the help!

Private Sub btnAdd_Click()


Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim LngItem As Long

Set db = CurrentDb()




Dim rowc As Integer


With Me.LstNewAct
  For rowc = 0 To .ListCount - 1
    strSQL = "SELECT * FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    
    Set rst = db.OpenRecordset(strSQL)
    If (rst.BOF And rst.EOF) Then ' There are no records if Beginning-Of-File and End-Of-File are both true.
      DoCmd.RunSQL "INSERT INTO tbl_Activities (actName) VALUES ('" & .Column(0, rowc) & "')"
      rst.Close
    End If
    strSQL = "SELECT actID FROM tbl_Activities WHERE actName='" & .Column(0, rowc) & "'"
    Set rst = db.OpenRecordset(strSQL)
    DoCmd.RunSQL "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, '" & rst.Fields(0).Value & "', WkDate, actType1)"
  Next rowc
End With



With Me.LstAct
  For Each varItem In .ItemsSelected
    LngItem = .Column(0, varItem)
    strSQL = "INSERT INTO tbl_ActCmb1 (toID, stoID, actID, actDate, actType) VALUES (cmbTO, cmbsto, " & LngItem & ", WkDate, actType1)"
    CurrentDb.Execute strSQL, dbFailOnError

  Set db = Nothing
  Next varItem
End With



End Sub

Open in new window

0
 

Accepted Solution

by:
Megin earned 0 total points
ID: 37816651
This was solved by using the code:

DoCmd.SetWarnings False at the beginning and
DoCmd.SetWarnings True at the end.

Thank you to everyone who took the time to help me out!
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37816940
Megin,

I would recommend against using SetWarnings False and True unless you have really good error handling.  If you don't you could exit the code module without reseting it to true, and then you would see NO warnings whatsoever, including when you are debugging your code or writing queries...

Jeff's post

"To be clear, there is a setting that does what you are asking for directly, but "managing" this setting can become confusing."

is a vague indicator of the existence of this (SetWarnings) technique, but most of the experts recommend against using it.
0
 

Author Comment

by:Megin
ID: 37816956
I haven't been able to make anything else work. Can you tell me what is wrong with it now that the codes I have been trying aren't working? I am sure I don't have really good error handling.
0
 

Author Comment

by:Megin
ID: 37816958
Also, thank you for the warning!
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37817244
Megan,

Is the code you posted just above your latest code?
0
 

Author Comment

by:Megin
ID: 37817253
Yes, with the exception of the DoCmd.SetWarnings False.
0
 

Author Closing Comment

by:Megin
ID: 37831680
I received help from a co-worker and, now that the question was answered, I didn't want it sitting here open.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question