MsgBox destination db in update query (1 Viewer)

[azhar2006]

welcome all .
I have a button that runs an update query and then relays the data to the db destination somewhere else. When db is not present I get an error message in (VBA) . What I want to show me another message that the interface database does not exist.
Thanks for the help in advance

 DoCmd.OpenQuery "UpdateQueryAll", acViewNormal

 

[Gasman]

Well test if the file exists first?

 

[MajP]

can you check first that the query exists?

Public Function ValidateQuery(qdfName As String) As Boolean
  Dim db As DAO.Database
  Dim qdf As QueryDef
  For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = qdfName Then
     ValidateQuery = True
      Exit For
    End If
  Next qdf
End Function

 

[azhar2006]

Well test if the file exists first?

thank you my dear friend Gasman for replying . yes, I know that. I know it's not in section D so I removed it. What I want is . message stating that the file does not exist.

 

[Gasman]

You would create that yourself with the MSGBOX() function.

 

[azhar2006]

can you check first that the query exists?

Public Function ValidateQuery(qdfName As String) As Boolean
  Dim db As DAO.Database
  Dim qdf As QueryDef
  For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = qdfName Then
     ValidateQuery = True
      Exit For
    End If
  Next qdf
End Function

Thanks MajP the code didn't work

 

[MajP]

I think after rereading your statement I see it is if the destination in the update exists not if the query exists. Is the desitnation a linked table? Can you post the update query sql?

 

[azhar2006]

I think after rereading your statement I see it is if the destination in the update exists not if the query exists. Is the desitnation a linked table? Can you post the update query sql?

I think my idea did not reach. I want to hide the message vba and instead show another message stating that the target database does not exist .
I can use

On Error Resume Next

But the message alternative is what I want

 

[MajP]

In VBA you do not have a try catch block, so using error handling for program flow is a bad idea. Instead of throwing the error you should do what you can to validate that the database exists and provide the message if it does not. Do not try to throw an error and provide a message.

 

[azhar2006]

In VBA you do not have a try catch block, so using error handling for program flow is a bad idea. Instead of throwing the error you should do what you can to validate that the database exists and provide the message if it does not. Do not try to throw an error and provide a message.

Indeed, this is what I want to present a message. I didn't use (On Error Resume Next) so I don't want to hide the message VBA but show another similar message to the user... The user doesn't understand messages VBA maybe he accidentally didn't add the target database in Partition D

 

[theDBguy]

Hi. Can you please post the SQL statement for the query "UpdateQueryAll?"

 

[MajP]

indeed, this is what I want to present a message. I didn't use (On Error Resume Next) so I don't want to hide the message VBA but show another similar message to the user... The user doesn't understand messages VBA maybe he accidentally didn't add the target database in Partition D

I will try this one more time. DO NOT LET THE ERROR OCCUR. Check to see if D:\TIT.accdb exists and tell the user it does not. You do not have to throw the error to then tell them it does not exist.

 

[Gasman]

You can send data to another dB, but are incapable of testing if a file exists and a simple msgbox statement?

 

[azhar2006]

I will try this one more time. DO NOT LET THE ERROR OCCUR. Check to see if D:\TIT.accdb exists and tell the user it does not. You do not have to throw the error to then tell them it does not exist.

I will definitely tell them to do (TIT.db) in Section D. I will go home and any mistake no one calls me because I am with my family.

 

[azhar2006]

You can send data to another dB, but are incapable of testing if a file exists and a simple msgbox statement?

yes, I know that . The problem is that I'm not the one to work on it. People will work on it, all they know is typing on the Word program

 

[theDBguy]

Did you see my request? Just curious...

 

[theDBguy]

Yes, I gave it to you. Didn't you see it? SQL

INSERT INTO tblmastr ( StatFig, Rtba, FullName, Department, StatFigArmyOfficer, Gender, DateBirth, PlaceBirth, MinisteriIDNumber, Academic, MotherNam, GraduateArmyPolice, AppoNumber, DateAppo, EntryOrder, DateEntryOrder, NumberUpgradeAfter, DateUpgradeAfter, City, Elimination, Side, District, PhoneNumber, Notes, PenaltiesSentences, JobStatus, UnitBefore2003, DateEntryService, AccoJustice, PistolNumber, NationalityNumber, DateNational, NationaliIssuer, RecordNumber, NewspaperNumber, CertificateNumber, CertificateDate, MatiCertificate, CertifPortNumber, MaritalStatus, WifeName, Children, Religion, Nationalism, [Position], RankFirst, RankFirstNumber, RankFirstDate, RankSecond, RankSecondNumber, RankSecondDate, RankThird, RankThirdNumber, RankThirdDate, RankFourth, RankFourthNumber, RankFourthDate, RankFifth, RankFifthNumber, RankFifthDate, RankSixth, RankSixthNumber, RankSixthDate, RankSeventh, RankSeventhNumber, RankSeventhDate, RankEighth, RankEighthNumber, RankEighthDate, TransferFrom, TransferTo, TransferNumber, TransferDate, TransporIssuer, WhichHeWorked, Variables, DateUpdated ) IN 'D:\TIT.accdb'
SELECT tblmastr.StatFig, tblmastr.Rtba, tblmastr.FullName, tblmastr.Department, tblmastr.StatFigArmyOfficer, tblmastr.Gender, tblmastr.DateBirth, tblmastr.PlaceBirth, tblmastr.MinisteriIDNumber, tblmastr.Academic, tblmastr.MotherNam, tblmastr.GraduateArmyPolice, tblmastr.AppoNumber, tblmastr.DateAppo, tblmastr.EntryOrder, tblmastr.DateEntryOrder, tblmastr.NumberUpgradeAfter, tblmastr.DateUpgradeAfter, tblmastr.City, tblmastr.Elimination, tblmastr.Side, tblmastr.District, tblmastr.PhoneNumber, tblmastr.Notes, tblmastr.PenaltiesSentences, tblmastr.JobStatus, tblmastr.UnitBefore2003, tblmastr.DateEntryService, tblmastr.AccoJustice, tblmastr.PistolNumber, tblmastr.NationalityNumber, tblmastr.DateNational, tblmastr.NationaliIssuer, tblmastr.RecordNumber, tblmastr.NewspaperNumber, tblmastr.CertificateNumber, tblmastr.CertificateDate, tblmastr.MatiCertificate, tblmastr.CertifPortNumber, tblmastr.MaritalStatus, tblmastr.WifeName, tblmastr.Children, tblmastr.Religion, tblmastr.Nationalism, tblmastr.Position, tblmastr.RankFirst, tblmastr.RankFirstNumber, tblmastr.RankFirstDate, tblmastr.RankSecond, tblmastr.RankSecondNumber, tblmastr.RankSecondDate, tblmastr.RankThird, tblmastr.RankThirdNumber, tblmastr.RankThirdDate, tblmastr.RankFourth, tblmastr.RankFourthNumber, tblmastr.RankFourthDate, tblmastr.RankFifth, tblmastr.RankFifthNumber, tblmastr.RankFifthDate, tblmastr.RankSixth, tblmastr.RankSixthNumber, tblmastr.RankSixthDate, tblmastr.RankSeventh, tblmastr.RankSeventhNumber, tblmastr.RankSeventhDate, tblmastr.RankEighth, tblmastr.RankEighthNumber, tblmastr.RankEighthDate, tblmastr.TransferFrom, tblmastr.TransferTo, tblmastr.TransferNumber, tblmastr.TransferDate, tblmastr.TransporIssuer, tblmastr.WhichHeWorked, tblmastr.Variables, tblmastr.DateUpdated
FROM tblmastr;

Sorry, no I didn't see it. How did you give it to me?

 

[theDBguy]

Okay, so in your code where you have the following:

DoCmd.OpenQuery "UpdateQueryAll", acViewNormal

How about adding this line?

If Dir("D:\TIT.accdb")="" Then
    MsgBox "Your customer message here"
Else
    DoCmd.OpenQuery "UpdateQueryAll", acViewNormal
End If

Hope that helps...

 

[azhar2006]

Okay, so in your code where you have the following:

DoCmd.OpenQuery "UpdateQueryAll", acViewNormal

How about adding this line?

If Dir("D:\TIT.accdb")="" Then
    MsgBox "Your customer message here"
Else
    DoCmd.OpenQuery "UpdateQueryAll", acViewNormal
End If

Hope that helps...

 

[theDBguy]

Hi. Glad we could assist. Good luck!