Megin
asked on
How do I get rid of the message box telling me I am going to append a record?
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!
Any and all help is greatly appretiated!!!!!
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
Any and all help is greatly appretiated!!!!!
Use DoCmd.Execute strsql, dbfailonerror ( instead of RunSql)
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.
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.
i suggest that you use
currentdb.execute strSQL, dbFailOnError
currentdb.execute strSQL, dbFailOnError
i suggest that you use
currentdb.execute strSQL, dbFailOnError
or
db.execute strSQL, dbFailOnError
currentdb.execute strSQL, dbFailOnError
or
db.execute strSQL, dbFailOnError
<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
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
ASKER
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"
?????
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"
?????
Post the exact, full code you tried please...
ASKER
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
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.
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.
Change your declaration statement to:
Dim db as DAO.Database
Dim db as DAO.Database
ASKER
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!
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
ASKER
Also, thank you for the warning!
Megan,
Is the code you posted just above your latest code?
Is the code you posted just above your latest code?
ASKER
Yes, with the exception of the DoCmd.SetWarnings False.
ASKER
I received help from a co-worker and, now that the question was answered, I didn't want it sitting here open.