Set Warnings Off

Just upgraded to Access 2007. There used to be a feature in Macros to Set Warnings to off - so when your macro was running update or append queries you didn't get a message.

How to you turn those off in Access 2007?


Post your answer or comment

comments powered by Disqus
is there a Macro for docmd.setwarning off instead of writing code in module? I can't find the Macro docmd? I am using Access2010.

thanks.

I have a form with a button which calls a macro to open a query that performs a single record append into a table. The table has a field that is indexed and does not allow duplicates.
I want to add the following to the macro: Set warnings off to eliminate all the message boxes that pop up--1 to inform that an action is being taken, 1 to inform that 1 record is being appended and 1 with very confusing text to inform the action cannot be taken if the record is already in the table. TMI and potentially confusing.
After setting warnings off I want to provide a message box that informs 'Item is Already in table' if the record exists in the table or a message box that informs '1 record has been added' if a record does not exist in the table.
I know this can't be all that difficult. I just can't get the sequencing right.
Help, please

I am developing an Access database to keep track of recurring tasks. When a task is completed, a date is entered and a button clicked. The code then has the record copied to a history table, then resets the start and due dates based on the frequency. I am getting an error when I run the code. The message just says "Error", so I am not sure what's wrong. Below is the code for the button. I realize it's rather complex, so if anyone is willing to help and could PM me, I would be glad to provide a link where you can download my project.

I really appreciate the help. Below is the code:

Private Sub Button96_Click()
'1. The record is copied to the "History" table for historical records.
'2. The Current Due Date changes to the next due date based on the recurring requirement specified
' (i.e. weekly, monthly, etc.), or if it is "one time", it deletes the record.
'3. If recurring, the Completed Date field goes blank (because it is reset).

On Error GoTo Button69_Err

Dim MyStr As String
Dim intErrNum As Integer


' Save the record first, by going to the next record, and then back to the current one.
' This is OK even if there is no next record, since it will go to a new record

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

If IsNull([completion date].Value) = True Then
MsgBox "The completion date is empty", vbExclamation, "Need completion date"
[completion date].SetFocus
Exit Sub
End If

If IsNull(Combo116.Column(0)) = True Then
MsgBox "Don't have a frequency field", vbExclamation, "Need frequency info"
Combo116.Dropdown
Exit Sub
End If


MyStr = "INSERT INTO History ( [OLD_ID#], [SUPERINTENDENT], [TASK DESCRIPTION], [REQUESTED_BY], [DUE DATE], [START DATE], " & _
"[COMPLETE DATE], [STATUS_COMMENTS], [COMMITTMENT], [GROUP], [INDIVIDUAL], [FREQUENCY] ) " & _
"SELECT " & Str([TASK NUMBER].Value) & " AS Expr1, " & Str([SUPERINTENDENT].Value) & " AS Expr2, " & _
Chr(34) & [TASK DESCRIPTION].Value & Chr(34) & " AS Expr3, " & Chr(34) & [REQUESTED_BY].Value & Chr(34) & " AS Expr4, " & _
"#" & [Text128].Value & "#" & " AS Expr5, " & "#" & [START DATE].Value & "#" & " AS Expr6, " & _
"#" & [completion date].Value & "#" & " AS Expr7, " & Chr(34) & [STATUS_COMMENTS].Value & Chr(34) & " AS Expr8, " & _
Chr(34) & [COMMITTMENT].Value & Chr(34) & " AS Expr9, " & Chr(34) & Combo116.Column(0) & Chr(34) & " AS Expr10, " & _
Chr(34) & GROUP_COMBO.Column(0) & Chr(34) & " AS Expr11, " & Chr(34) & [INDIVIDUAL].Value & Chr(34) & " AS Expr12;"


DoCmd.SetWarnings False ' Set warning off
DoCmd.RunSQL MyStr ' Add current record to History table


If Combo116.Column(2) = 0 Then ' If the frequency is "One Time" or "Other", delete current record
'MsgBox "This record will be deleted"
MyStr = "Delete Task.[ID#] FROM Task WHERE (((Task.[ID#])=" & Str([TASK NUMBER].Value) & "));"
DoCmd.GoToRecord , , acNext ' Go to a new record
DoCmd.RunSQL MyStr ' Delete the record that was active before
Me.Requery ' Requery the form after a record is deleted
DoCmd.GoToRecord , , acFirst ' Go to the first record

Else ' Otherwise, it's a recurring task, so set its properties

'MsgBox "This record will be incremented with " & Str(Combo116.Column(2)) & " " & Combo116.Column(1)
Text128.Value = DateAdd(Combo116.Column(1), Combo116.Column(2), Text128.Value) ' increment the date, if recurring
[START DATE] = DateAdd(Combo116.Column(1), Combo116.Column(2), [START DATE])
[completion date].Value = Null ' Completion date goes blank
DoCmd.GoToRecord , , acNext ' save the record
DoCmd.GoToRecord , , acPrevious
End If



DoCmd.SetWarnings True ' Set warnings on




Button69_Exit:

Exit Sub




Button69_Err:

intErrNum = Err
Select Case intErrNum
Case 2105
MsgBox "This is a new task. Need more data to complete it", vbExclamation, "Need more data"
Case Else
MsgBox "Error!"

End Select
Resume Button69_Exit
End Sub

Whenever I make changes to objects in my Access 2000 database, I no longer receive the prompt to confirm changes (or deletions).

I have seen other threads addressing this issue and followed the advice posted. I do have macros that set warning off, but I always turn them on again at the end of the macro. Also, in my main form that gets loaded when
the database is opened, I have the following code in the form_load event:

DoCmd.SetWarnings (WarningsOn)

Also, in the Edit/Find tab under Tools, Options, the confirm boxes are all checked.

I must be missing something. On a related note, is it possible to display what the current Warnings setting is?

Thank you.

I am able to run an access “program” which prints a report using a stored procedure, this works fine. (the "program" is just a .bat file which calls an access database that has an AutoExec macro which sets warnings off, opens a form. On the open form event, it prints a report, closes the form (no save), set warnings on, and closes the access application (as exit, no saves again).

I am able to link sql tables into that report and pull specific data into the report and print that just fine.

When I add another linked table (could be the 2nd, 4th, 10th, or first, doesn’t’ matter), which is linked to access and not to sql, the “program” craps out. What happens is msaccess.exe is loaded in the task manager, an .ldb file is created, but nothing happens. As soon as it gets to the part where it would print the report, it fails. I know this also because I have done a kill(“c:test.doc”) before the printing and that part would work (just as a test to see where it was failing). However, running the .bat file (which only calls the database to run) works fine…only when you try to run the database as a stored procedure is when it fails.

So, one solution is to import everything into sql, which should be done anyways, but could cause multiple problems down the road. Have you ever heard / ran into this problem? Could this be some sort of weird permissions issue?

Do you think setting up a trigger would resolve this issue? (run executable program upon insert into sql table).

I am not sure where to post this as it is specifically a problem with access linked table, but only doesn't work from a sql stored procedure.

I am running access 2003 and am thinking about upgrading to 2007 to see if that works. The linked tables will be coming from an access 97 database.

After user enter the begining date and ending date, how to tell the report print out those date had enter? And how to set a warning if the query doesn't have any record that user want to see? Please help. Thank you!

I have a simple Output macro that exports a table to Excel. My warnings are off, but I still get asked if I want to replace the existing file. How do I eliminate this?

Hi everybody,

I have a database which opens a separate Excel file and modifies the contents such that it can be imported using the DoCmd.TransferSpreadsheet command.

Because that modification requires transposition, I have to use copy & paste, which means data is copied to the clipboard at various stages.

Unfortunately, this means when I then close the Excel workbook, I get a prompt asking if I want the contents of the clipboard to be retained.

Obviously I don't, but I want to skip / bypass the warning altogether.

If I was writing VBA directly in Excel, I would just use :


	Code:
	Application.CutCopyMode = False

but this is not recognised by Access.

I've tried defining Excel as an application object and passing the command through that, but also to no avail :


	Code:
	Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.CutCopyMode = False

I've even tried turning the warnings off temporarily within Access, no joy :


	Code:
	DoCmd.SetWarnings False
wbkMyWorkbook.Close savechanges:=True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strMyTable, strMyFile, True, strMySheet & "!"
DoCmd.SetWarnings True

Does anybody know how I can avoid this warning message?

Thanks

AOB

Hello,

I have some VBA code that is starting an instance of Excel, and running an excel macro against a txt file. It works fine, except as part of the macro I'm merging multiple cells in the same row together. Each time I attempt to do this I get an Excel warning message stating that only the data in the leftmost cell will be kept.

I've disabled warnings in my code after starting excel as follows:

' Start an Excel session
Set objxl = New Excel.Application
objxl.Visible = False
Workbooks.Open filename:="c:stock_tracker.xls"

' Turn warnings off
DoCmd.SetWarnings False

' Excel Macro Code
With objxlwrksht
Rows("1:1").Select
Selection.Insert Shift:=xlDown

etc....


Is there anything else I need to code to disable all excel warnings?

Thanks.

Hi, Big Jim here:

Most bizarre problem. I open Access from VB using the following code:

Public gAccess As New Access.Application

gstrDatabaseFullPath = [Whatever database I am opening.mdb]
gAccess.OpenCurrentDatabase gstrDatabaseFullPath
gAccess.DoCmd.SetWarnings True
gAccess.Visible = True

(References = DAO 3.51 Object Library and Access 10.0 Object Library)

Now when I do this, the application opens up just fine and I can see the database. However, the warnings are off, because I am forced to save dummy queries I just want to close out of and no warning messages come up when I attempt to modify data (DDL).

Any thoughts?

Thanks in advance,

Big Jim

Hello all,

Finally moved to 2010 from 2007 and I have one big problem - how can I turn off all security for my simple, quick-and-dirty application that is used strictly in house. The front-end on the PC, back-end on server.

When users open the database, they get two security warnings.

1) Application add-ins have been disabled.
2) Some active content has been disabled. The AutoExec macro is stopped here with error number 2001

Both have enable contect buttons that seem to do nothing. I have set everything off that I can in the Trust Center and it works fine on my PC. I've also done the same for a second PC. But the issue is that this will be going out to about 30 people and I shouldn't have to change them all.

Is there a simple way to shut down everything so this could run and give me some breathing room to set it up properly?

Thanks much to all who respond.

In my visual basic application I am accessing my Access macro. I set the Warnings Off. But, there is this parameter that needs be to be passed. So when I run the macro, the parameter does not come up and errors occur. How do I over come this?


John-

The confirm actions is set to off while running access to prevent the popup of windows with macro actions.
After creation of a runtime version of the application this settings is not valid in the runtime version. Where should I set this confirmations off for runtime ?

I have this macro set to run from the Close Form button as the form closes but the warnings still occur.

Should I have this attached to a different event? Am I using the code incorrectly? In Excel I would use BeforeClose but it doesn't appear to be an option here.

Private Sub Form_Close()
On Error GoTo Error
DoCmd.SetWarnings False
DoCmd.RunMacro "mcrUpdatePolicyByEmpIDAfterNewAdd"
DoCmd.SetWarnings True

Error:
DoCmd.SetWarnings True
End Sub

I would like to suppress the following warning:

"The text you entered is an item in the list
Select an item from the list, or enter text that matches one of the items in the list"

this comes up when i am pasting data from an excel spreadsheet into a data sheet in ms access.

The field in MS access is set to "limit to list" so that no garbage data can be stored.

In my code below i have created my own message box for the user to note the details of the failed paste, i only want my message to show.

I also seem to get my message pop up twice, once with the ATMID and then immediatly afterwards with no ATMID in the message for each failed paste.

my code is : Private Sub ATMID_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
On Error GoTo AtmID_BeforeUpdate_Err


Me.KnownGLCode = DLookup("BankGlCode", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)
Me.Van = DLookup("CITVanName", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)

AtmID_BeforeUpdate_Exit:
Exit Sub

AtmID_BeforeUpdate_Err:
MsgBox "Atm : " & Me.ATMID & " : Does not exist", vbOKOnly, "Error is processing..."
Me.Undo
Resume Next


End Sub I also seem to get my message pop up twice, once with the ATMID and then immediatly afterwards with no ATMID in the message for each failed paste.

Hi

I have a macro that runs a make table query, once the table is created it then runs an update query on the table just created. It appears that every now and then the update fails to occur for no apparent reason (no notification as set warning is turned off). Do macros wait until each line is complete before moving on to the next? If not how can I create a delay until the make table is complete.

Many Thanks

This should be an interesting question which if answered should help people out in future. I've just about finished my first A2007 project which I'm deploying with the runtime licence.

In full access its easy to switch those warnings off but that cant be done, to my knowledge, in runtime version.

Equally, cant use setwarnings = false because its not an option in this case.

Any ideas out there?

Hi All

I would really appreciate any help you can give - I am not very good with the modules side of things in Access so laymans terms would be useful

I have multiple make table queries that are being linked to excel. The only problem is that the macro that intiates these queries has 3 message box/warnings per report (and Set Warnings can't be turned off in the macro in MSA2007).

I was told that you can create a module to run on "click" on a form say, and turn off warnings and turn them back on after the module has executed. My problem is I don't know how to do this!

For example my Make Table query names are:

XLAttendancePathways
XLAttendanceCasework
XLAttendees

Could anyone share the required code and explain how I would link this to a button control?

Any help would be so gratefully received.

Many thanks

Paul

Hi. I've got a Word document which has an embedded Excel object. I am changing values of the word document and its excel object from VBA in Access.

So far I have:

	Code:
	For Each oIShape In .ActiveDocument.InlineShapes                 
    If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then 
          oIShape.OLEFormat.Activate                 
          Set oWB = oIShape.OLEFormat.Object     
          'Replace tHE Values                     
          oWB.Sheets(1).Range("B3").Value = "Test"                             
    End If             
Next oIShape

This works fine, but the excel object remains activated, which means I can't use the "savetopdf" command. All i want to do is close the object and go back to word (just like clicking away from the excel object if I was doing it manually).

Anyone have an idea of how to do this?? it took me ages to get this far and thisis the last hurdle :-D

Hi all,

Basically I'm trying to import each worksheet, but each one needs something different done to it on import so I'm using a for loop:


	Code:
	
Private Sub Command32_Click()

'import master data click
     Dim MyXLApp As Excel.Application
    Dim MyXLWorkBook As Excel.Workbook
 Dim MyRange As String

       
    'import the data
Call import

     Dim WrksheetName As String
     Dim i As Integer
     Dim xl As Object
     
     Set xl = CreateObject("Excel.Application")
     xl.Visible = True
     xl.Workbooks.Open strPathAndFile
     With xl
     .Visible = False
     With .Workbooks(.Workbooks.Count)
     For i = 1 To .Worksheets.Count
     
     WrksheetName = .Worksheets(i).Name
     If WrksheetName = "Master" Then
 
       MyRange = WrksheetName & "!A:ZZ"
      
      'Create the import table

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim fld2 As DAO.Field
    Dim fld3 As DAO.Field
    Dim fld4 As DAO.Field
    Dim fld5 As DAO.Field
    Dim fld6 As DAO.Field
    Dim fld7 As DAO.Field
    Dim fld8 As DAO.Field
    Dim fld9 As DAO.Field
    Dim fld10 As DAO.Field
    Dim fld11 As DAO.Field
    Dim fld12 As DAO.Field
    Dim fld13 As DAO.Field
    Dim fld14 As DAO.Field
    Dim fld15 As DAO.Field
    Dim fld16 As DAO.Field
    Dim fld17 As DAO.Field
    Dim fld18 As DAO.Field
    Dim fld19 As DAO.Field
    Dim fld20 As DAO.Field
    Dim fld21 As DAO.Field
    Dim fld22 As DAO.Field
    Dim fld23 As DAO.Field
    Dim fld24 As DAO.Field
    Dim fld25 As DAO.Field
    Dim fld26 As DAO.Field
    Dim fld27 As DAO.Field
    Dim fld28 As DAO.Field
    Dim fld29 As DAO.Field
    Dim fld30 As DAO.Field
    Dim fld31 As DAO.Field
    Dim fld32 As DAO.Field
    Dim fld33 As DAO.Field
    Dim fld34 As DAO.Field
    Dim fld35 As DAO.Field
    Dim fld36 As DAO.Field
 
    
    Set tdf = CurrentDb.CreateTableDef("importtable")
    Set fld = tdf.CreateField("Asset Number", dbText)
    Set fld2 = tdf.CreateField("CoCd", dbDouble)
    Set fld3 = tdf.CreateField("Class", dbText)
    Set fld4 = tdf.CreateField("Asset Description", dbText)
    Set fld5 = tdf.CreateField("Serial No#", dbText)
    Set fld6 = tdf.CreateField("Invent No", dbText)
    Set fld7 = tdf.CreateField("CostCentre", dbDouble)
    Set fld8 = tdf.CreateField("Plnt", dbDouble)
    Set fld9 = tdf.CreateField("LOCATION", dbText)
    Set fld10 = tdf.CreateField("F10", dbText)
    Set fld11 = tdf.CreateField("F11", dbDouble)
    Set fld12 = tdf.CreateField("FundTyp", dbText)
    Set fld13 = tdf.CreateField("ProgSrc", dbText)
    Set fld14 = tdf.CreateField("SubClass", dbDouble)
    Set fld15 = tdf.CreateField("Vendor", dbDouble)
    Set fld16 = tdf.CreateField("Manufacturer", dbText)
    Set fld17 = tdf.CreateField("COST", dbCurrency)
    Set fld18 = tdf.CreateField("W Start", dbDate)
    Set fld19 = tdf.CreateField("Lenovo W Start", dbDate)
    Set fld20 = tdf.CreateField("Remarks", dbText)
    Set fld21 = tdf.CreateField("Remarks 2", dbText)
    Set fld22 = tdf.CreateField("Formula", dbDouble)
    Set fld23 = tdf.CreateField("F23", dbText)
    Set fld24 = tdf.CreateField("F24", dbText)
    Set fld25 = tdf.CreateField("F25", dbText)
    Set fld26 = tdf.CreateField("F26", dbText)
    Set fld27 = tdf.CreateField("F27", dbText)
    Set fld28 = tdf.CreateField("F28", dbText)
    Set fld29 = tdf.CreateField("Remarks1", dbText)
    Set fld30 = tdf.CreateField("F22", dbText)
    Set fld31 = tdf.CreateField("sub class", dbText)
    Set fld32 = tdf.CreateField("HP W Start", dbDate)
    Set fld33 = tdf.CreateField("F21", dbText)
    Set fld34 = tdf.CreateField("F20", dbText)
    Set fld35 = tdf.CreateField("F19", dbText)
    Set fld36 = tdf.CreateField("F1", dbText)
   
    
    
    tdf.Fields.Append fld
    tdf.Fields.Append fld2
    tdf.Fields.Append fld3
    tdf.Fields.Append fld4
    tdf.Fields.Append fld5
    tdf.Fields.Append fld6
    tdf.Fields.Append fld7
    tdf.Fields.Append fld8
    tdf.Fields.Append fld9
    tdf.Fields.Append fld10
    tdf.Fields.Append fld11
    tdf.Fields.Append fld12
    tdf.Fields.Append fld13
    tdf.Fields.Append fld14
    tdf.Fields.Append fld15
    tdf.Fields.Append fld16
    tdf.Fields.Append fld17
    tdf.Fields.Append fld18
    tdf.Fields.Append fld19
    tdf.Fields.Append fld20
    tdf.Fields.Append fld21
    tdf.Fields.Append fld22
    tdf.Fields.Append fld23
    tdf.Fields.Append fld24
    tdf.Fields.Append fld25
    tdf.Fields.Append fld26
    tdf.Fields.Append fld27
    tdf.Fields.Append fld28
    tdf.Fields.Append fld29
    tdf.Fields.Append fld30
    tdf.Fields.Append fld31
    tdf.Fields.Append fld32
    tdf.Fields.Append fld33
    tdf.Fields.Append fld34
    tdf.Fields.Append fld35
    tdf.Fields.Append fld36
    
    CurrentDb.TableDefs.Append tdf
    
    'turn warnings off ready for import and transfer into newly created table
    MsgBox (strPathAndFile & " " & MyRange)
    Debug.Print
DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, MyRange


DoCmd.SetWarnings (warningsoff)
   ' delete blank rows
    strSQL = "Delete FROM importtable Where Len(Trim([asset number] & ' '))=0 "
    'runSQL for strsql (deleting the blank rows)
    DoCmd.RunSQL (strSQL)
    'append from import table to master table
DoCmd.RunSQL "INSERT INTO Master ([Asset Number], CoCd, Class, [Serial No#], [Invent No], CostCentre, Plnt, LOCATION, COST,
[W Start], [HP W Start], [Lenovo W Start], Remarks ) SELECT importtable.[Asset Number], importtable.CoCd, importtable.Class,
importtable.[Serial No#], importtable.[Invent No], importtable.CostCentre, importtable.Plnt, importtable.LOCATION,
importtable.COST, importtable.[W Start], importtable.[HP W Start], importtable.[Lenovo W Start], importtable.Remarks FROM
importtable;"

'update based on serial number remarks1 and remarks 2 all to remarks
DoCmd.RunSQL "UPDATE importtable INNER JOIN Master ON importtable.[Asset Number] = Master.[Asset Number] SET
importtable.[Remarks 2] = [master].[remarks];"
DoCmd.RunSQL "UPDATE importtable INNER JOIN Master ON importtable.[Asset Number] = Master.[Asset Number] SET
importtable.Remarks1 = [master].[remarks];"

'delete any error tables
   Dim tblDef As TableDef
     
    On Error Resume Next
   For Each tblDef In CurrentDb.TableDefs
If InStr(1, tblDef.Name, "$A:ZZ_ImportErrors") > 0 Then
   DoCmd.SelectObject acTable, tblDef.Name, True
 DoCmd.DeleteObject acTable, tblDef.Name
   Else
      
   End If
   Next tblDef
   
   On Error GoTo Errhandler
   
' clear import table ready for next import
strSQL = "DROP TABLE importtable"
DoCmd.RunSQL (strSQL)

'clear the edit list combobox
Combo4.ListItemsEditForm = ""
DoCmd.SetWarnings (warningson)

Errhandler:
Debug.Print Err.Number & Err.Description & Err.HelpContext
'MsgBox Err.Number & Err.Description & Err.HelpContext

'if import table exists drop it

For Each tblDef In CurrentDb.TableDefs
If InStr(1, tblDef.Name, "importtable") > 0 Then
   DoCmd.SelectObject acTable, tblDef.Name, True
 DoCmd.DeleteObject acTable, tblDef.Name
   Else
      
   End If
   Next tblDef
 

Else: End If

    Next i
    End With
    End With
    Set xl = Nothing
    xl.Quit
    MsgBox ("nexti")
    


 End Sub

the error I'm currently getting is

Run-time error 3011
the Microsoft Access database engine could not find the object 'Master$A:ZZ'. Makre sure the object exists etc.

Then when I click on help it says:

Cannot open the file: ,lc@MSITStore:C:Program Filer(x86)Common FilesMicrosoft SharedOFDFICE121033jeterr40.chm

even though the last message box before that error correctly shows the correct file path and range.

Any ideas?

Hello,
Is it possible to docmd.setwarnings false for some types of warnings but not others. I am running an update query using .execute and before the execution line, I want the warnings to be suppressed if it's just the regular 'you are about to update...' message, but I want it to display if it's a warning about an error.
Thank you!
(Access 2003, vb 6.5)

Hi Folks,

I have a search form that opens up as soon as the database is opened, if the search renders results those results are displayed in a different (results) form otherwise the "results" form opens but it is blank/void of any records but still useful in that the command buttons on the top of the form can be used to perform another search, open reports, enter a new record, etc...

My problem is that if nothing is returned in the search and that blank results form is opened and a user selects a command button from that results form in order to perform another search or enter a new record or browse records, the following message is returned:

Microsoft Access: You entered a expression that returns no value

This error NEVER happens if anything is returned in the search.

In attempting to figure out this issue, I noticed that I could perhaps use a DCount and if the DCount is greater than zero to open the results form otherwise I would simply open a prompt stating nothing is found to the user and to remain on the search module because all other functions can be gotten to via that module anyways ...

I am running into a problem with my code, not sure how to state this ... I tried a dozen different ways:


The original code is the following:

Private Sub SearchViaSSNCommandButton_Click()

DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX"

DoCmd.Close acForm, "SEARCHRECORDS"

End Sub

Again, the above works fine if result returned, but if no results returned I am trying to get a prompt to come up stating that fact to the user ... so I tried the following code and similar ones like it to no avail:


Private Sub SearchViaSSNCommandButton_Click()

IF DCount("*","maintable") = 0 Then
MsgBox ("No Records Match Your Search Criteria, Please Try Again!")
else
DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX"

DoCmd.Close acForm, "SEARCHRECORDS"
end if

End Sub

I think my issue is that I am not quite sure how to tell DCount exactly how to get the records I want it to count. The table in question is "maintable" but that isn't even what I want, I just want to determine if any results would be returned via the "DoCmd.OpenForm "MainForm", , , "[SSN] = Forms!SEARCHRECORDS!SSNTEXTBOX" portion.

Is there some easy way to do this? Or can I disable the warning of the "... you entered an expression with no value" ... I tried set warnings to off, it didn't work. The prompt doesn't harm or hurt anything or make anything work wrong per se, it is just I don't want the user to see it anytime no results are returned and they use command/navigation buttons of the main form that opens with no records returned as none meet the search criteria....


Thanks a lot for any help!

Joe

hi,
I'm working on a database that should update its data from other databases (access 2007). The update command code looks something like this:

Private Sub Update_Button_Click()
On Error GoTo Err_Update_Button_Click
Dim msg As String
Dim resp As String
Dim path As String

msg = "This will first remove, and then update ALL information for " & [District] & " District from the " & [District] & " District Database. Are you sure you want to do this?"
resp = MsgBox(msg, vbYesNo + vbQuestion)
If resp vbNo Then

path = FindDatabase([District])

If Nz(path) "" Then

DoCmd.OpenForm ("Update Database Progress")
Forms![Update Database Progress]![District] = [District]
Forms![Update Database Progress].Repaint

On Error Resume Next

DoCmd.DeleteObject acTable, "Imported Ward List"
DoCmd.DeleteObject acTable, "Imported Village List"
DoCmd.DeleteObject acTable, "Imported Infrastructure"
DoCmd.DeleteObject acTable, "Imported Population District"
DoCmd.DeleteObject acTable, "Imported Travel Times"
On Error GoTo Err_Update_Button_Click

DoCmd.TransferDatabase acImport, "Microsoft Access", _
path, acTable, "Ward List", _
"Imported Ward List"

DoCmd.TransferDatabase acImport, "Microsoft Access", _
path, acTable, "Village List", _
"Imported Village List"

DoCmd.TransferDatabase acImport, "Microsoft Access", _
path, acTable, "Infrastructure", _
"Imported Infrastructure"

DoCmd.TransferDatabase acImport, "Microsoft Access", _
path, acTable, "Population District", _
"Imported Population District"

DoCmd.TransferDatabase acImport, "Microsoft Access", _
path, acTable, "Travel Times", _
"Imported Travel Times"

Forms![Update Database Progress]![Import].Visible = True
Forms![Update Database Progress].Repaint
' Set the Warnings off
DoCmd.SetWarnings False


DoCmd.RunSQL ("DELETE [Ward List].LLG FROM [Ward List] WHERE ((([Ward List].LLG) In (SELECT DISTINCT [Imported Ward List].LLG FROM [Imported Ward List]));")

DoCmd.RunSQL ("INSERT INTO [Ward List] ( LLG, [Ward Number], [Ward Name], Councillor, Notes ) SELECT [Imported Ward List].LLG, [Imported Ward List].[Ward Number], [Imported Ward List].[Ward Name], [Imported Ward List].Councillor, [Imported Ward List].Notes FROM [Imported Ward List];")


Forms![Update Database Progress]![Ward].Visible = True
Forms![Update Database Progress].Repaint

DoCmd.RunSQL ("DELETE [Village List].[LLG Name] FROM [Village List] WHERE ((([Village List].[LLG Name]) In (SELECT DISTINCT [Imported Ward List].LLG FROM [Imported Ward List]));")

DoCmd.RunSQL ("INSERT INTO [Village List] SELECT [Imported Village List].* FROM [Imported Village List];")


Forms![Update Database Progress]![Village].Visible = True
Forms![Update Database Progress].Repaint

DoCmd.RunSQL ("DELETE [Infrastructure].LLG FROM [Infrastructure] WHERE ([Infrastructure].[District] = Forms![Update Database]![District]);")

DoCmd.RunSQL ("INSERT INTO [Infrastructure] SELECT [Imported Infrastructure].* FROM [Imported Infrastructure];")

Forms![Update Database Progress]![Infrastructure].Visible = True
Forms![Update Database Progress].Repaint

DoCmd.RunSQL ("DELETE [Population District].[District] FROM [Population District] WHERE ([Population District].[District] = Forms![Update Database]![District]);")

DoCmd.RunSQL ("INSERT INTO [Population District] SELECT [Imported Population District].* FROM [Imported Population District];")


Forms![Update Database Progress]![Population].Visible = True
Forms![Update Database Progress].Repaint

DoCmd.RunSQL ("DELETE [Travel Times].LLG FROM [Travel Times] WHERE ([Travel Times].District = Forms![Update Database]![District]);")

DoCmd.RunSQL ("INSERT INTO [Travel Times] SELECT [Imported Travel Times].* FROM [Imported Travel Times];")


Forms![Update Database Progress]![Accessibility].Visible = True
Forms![Update Database Progress].Repaint

DoCmd.DeleteObject acTable, "Imported Ward List"
DoCmd.DeleteObject acTable, "Imported Village List"
DoCmd.DeleteObject acTable, "Imported Infrastructure"
DoCmd.DeleteObject acTable,
DoCmd.DeleteObject acTable, "Imported TravelTimes"

Forms![Update Database Progress]![Delete].Visible = True
Forms![Update Database Progress].Repaint
' Set the Warnings back on
DoCmd.SetWarnings True

[Last Updated] = Date

DoCmd.Close acForm, "Update Database Progress"

resp = MsgBox([District] & " District Information successfully refreshed from District Database. The database will now be compressed.", vbInformation)

SendKeys "%TDC"
End If
End If

Exit_Update_Button_Click:
Exit Sub
Err_Update_Button_Click:
If Err.Number = 3024 Then resp = MsgBox("Error 3024 - cannot find the " & [District] & " Database you specified.", vbExclamation)
Else
MsgBox Err.Description
End If
Resume Exit_Update_Button_Click

End Sub
_________________________________
when I clicked the update button, the message box came up asking me if I was wanted to update the database. but when I clicked on "Yes", nothing happened. The window opening the path to which the database gets its update did not come up.

can someone help me?

thanks in advance

I have an access application that iterates through a bunch of excel files to read data that will be written to some tables. Each of these Excel files is linked to another file. When each workbook is opened, it prompts me with a "Do you want to refresh the links" dialog. I can turn the dialog off be setting "Application.AskToUpdateLinks = False". The problem is that, although this setting turns off the dialog, it's defaults to refreshing the links. Relinking over the LAN makes the process far too slow for my purposes. What I need to do is:
1. Turn off the links; and
2. Turn off the dialog.


Not finding an answer? Try a Google search.