"Unrecognized database format" error happening once a day

[clarencemo]

Hello,

I currently have a split db and I keep getting this error after about a day's worth of use. Once I've repaired it, I have created a clean db, imported the tables from a previous backup and everything works with no issues, until the next day when it gets corrupted again. I thought at first it was a bad form. so I rebuilt it.

Can u look at the code on this form?

What this form does is it inserts data from end-users (who have the FE on their desktops) for channel issues. If there is 3 instances of the channel number in the same market it will send an email to a supvervisor informing of possible issues. Any more instances of that same channel in the market, will pop up a msg saying we have enough issues and close out.

In this same form there is also a "clear channel outage" button, which is used once said channel issue in market has been resolved, so if there is another instance of that channel in that market then the end-user can submit it again (as it maybe another issue, not related to the 'cleared" one)

In the code of that form, am I not exiting the application gracefully? I use LDB Viewer and when the app exits, it says I exited the app but I'm leaving the DB in a possible "suspect" state...anyway to avoid that?

Here is the code for that form...
------------------------------------------------------------

Option Compare Database
Private Sub FldCustName_LostFocus()
 
If IsNull(Forms![frmLeadChannelForm]![FldCustName]) Then
MsgBox "You must enter the customer's name!", vbCritical
 
End If
 
End Sub
Private Sub FldAcctNumb_LostFocus()
If IsNull(Forms![frmLeadChannelForm]![FldAcctNumb]) Then
MsgBox "You must enter the 16 digit account number!", vbCritical
 
End If
End Sub
 
 
 
Private Sub FldAddress_LostFocus()
If IsNull(Forms![frmLeadChannelForm]![FldAddress]) Then
MsgBox "You must enter the customer's address!", vbCritical
End If
 
End Sub
Private Sub FldPhoneNumb_LostFocus()
If IsNull(Forms![frmLeadChannelForm]![FldPhoneNumb]) Then
MsgBox "You must enter the customer's main phone number!", vbCritical
End If
 
End Sub
 
 
Private Sub ClearChannel_Click()
DoCmd.SetWarnings False
 
Dim ClearChannel As String
ClearChannel = "UPDATE TBLMaster SET TBLMaster.FldCleared = 'yes'"
ClearChannel = ClearChannel & " WHERE (([TBLMaster]![FldMarket]=[Forms]![frmLeadChannelForm]![fldMarket] And [TBLMaster]![FldChannelNumb]=[Forms]![frmLeadChannelForm]![FldChannelNumb]));"
 
DoCmd.RunSQL (ClearChannel)
DoCmd.SetWarnings True
 
MsgBox " Channel " & Me.FldChannelNumb & vbCrLf & "Market Affected: " & Me.FldMarket & vbCrLf & vbCrLf & " OUTAGE CLEARED!", vbOKOnly
 
End Sub
 
'Private Sub FldChannelNumb_AfterUpdate()
 
'Finish1
 
'Dim Value2 As Variant
'Value2 = DLookup("[CountOfChannelProblem]", "TblChannelCount", "[FldMarket]='" & Forms![frmLeadChannelForm]![FldMarket] & "' And [FldChannelNumb]='" & Forms![frmLeadChannelForm]![FldChannelNumb] & "' AND [CountOfChannelProblem]>=3")
'If Value2 >= 3 Then
'MsgBox "We have enough examples of this issue! Please inform customer.", vbOKOnly & vbExclamation, "Known Issue!"
'Finish3
'Application.Quit
'ElseIf Value < 3 Then
'Finish3
'End If
 
'End Sub
 
 
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub btnCheckFinish_Click()
On Error GoTo Err_btnCheckFinish_Click
 
If IsNull(Forms![frmLeadChannelForm]![FldAcctNumb]) Then
MsgBox "You must enter the customer's name!", vbCritical
Exit Sub
 
ElseIf IsNull(Forms![frmLeadChannelForm]![FldCustName]) Then
MsgBox "You must enter the 16 digit account number!", vbCritical
Exit Sub
 
ElseIf IsNull(Forms![frmLeadChannelForm]![FldAddress]) Then
MsgBox "You must enter the customer's address!", vbCritical
Exit Sub
 
ElseIf IsNull(Forms![frmLeadChannelForm]![FldPhoneNumb]) Then
MsgBox "You must enter the customer's main phone number!", vbCritical
Exit Sub
End If
 
SysCmd acSysCmdInitMeter, "Processing...", 10
Finish1
SysCmd acSysCmdUpdateMeter, 5
Finish2
Finish3
SysCmd acSysCmdUpdateMeter, 10
SysCmd acSysCmdRemoveMeter
DoCmd.Quit
Exit_btnCheckFinish_Click:
Exit Sub
 
Err_btnCheckFinish_Click:
MsgBox Err.Description
Resume Exit_btnCheckFinish_Click
 
End Sub
 
Private Sub Finish1()
Me.Dirty = False
 
Dim UpdateDB As String
UpdateDB = "UPDATE TblMaster, TblMarket SET TblMaster.FldMarket = TblMarket!FldMarket"
UpdateDB = UpdateDB & " WHERE ((TblMarket!FldSysprin=Left(TblMaster!FldAcctNumb,6)));"
 
 
Dim MakeTable As String
MakeTable = "SELECT TBLMaster.FldChannelNumb, TBLMaster.FldMarket, Count(TBLMaster.FldChannelNumb) AS CountOfFldChannelName, Count(TBLMaster.FldMarket) AS CountOfChannelProblem INTO TblChannelCount"
MakeTable = MakeTable & " FROM TBLMaster WHERE (((TBLMaster.FldChannelNumb) Is Not Null) AND (TBLMaster.FldDate)=Date() AND (TBLMaster.FldCleared) Is Null)"
MakeTable = MakeTable & " GROUP BY TBLMaster.FldChannelNumb, TBLMaster.FldMarket;"
 
DBEngine(0)(0).Execute (MakeTable)
DBEngine(0)(0).Execute (UpdateDB)
 
End Sub
 
Private Sub Finish2()
 
Dim Value As Variant
Value = DLookup("[CountOfChannelProblem]", "TblChannelCount", "[FldMarket]='" & Forms![frmLeadChannelForm]![FldMarket] & "' And [FldChannelNumb]='" & Forms![frmLeadChannelForm]![FldChannelNumb] & "' And [CountOfChannelProblem]=2")
If Value = 2 Then
DoCmd.SendObject acSendReport, "rptAutoChannel2", acFormatXLS, "[e-mail]", , "[e-mail]; [e-mail]", "****TESTING AutoOutage Report TESTING****" & "Channel " & Me.FldChannelNumb & " / " & Me.FldMarket, "Please see the attached report for issue details. TS3000 Development Team", 0
MsgBox "This issue has been sent for DNOC and Lead/Supervisor follow-up.", vbExclamation, "Notification Sent!"
 
End If
End Sub
 
Private Sub Finish3()
 
DoCmd.RunSQL ("Drop Table TblChannelCount")
End Sub

-------------------------------------------------------------------

Thanks,

Clarence

[puppydogbuddy]

Clarence,

One thing I would definitely do is test my code with the warnings turned on during testing and see just what kinds of warnings you are getting:

Secondly, I took a quick "rough" look at your code and saw two things that look problematic.

1. Your use of the LossFocus event for validation. Generally validation is done in the Form_BeforeUpdate event, which is the event that occurs just before any data is saved. Using the lostFocus event does not guarantee that the controls you want filled will be filled.....>>>>>>simply said, if a control never gets the focus, the lostfocus event will never fire for that control,.and if some of the controls cause an error if they are not filled....that could be a source for corruption<<<<<<< In addition, the before update event can be canceled for legitimate reasons ....like the person has to look up some information before entering. See this link for a step by step "howTo" do data validation using the beforeUpdate event of the Form.

http://www.databasedev.co.uk/validate_textbox.html

2. the second thing that looks potentially problematic is this line:

ClearChannel = "UPDATE TBLMaster SET TBLMaster.FldCleared = 'yes'"

Genrally yes/no fields are declared as a boolean data type; if FldCleared is defined as boolean in your table, then setting equal to the string "Yes" is incorrect...it should be set to Boolean True or False. Consequently, it is possible that your update statement is causing the corruption.


Hope this helps.

[clarencemo]

Puppy,

If I removed the LostFocus code from there and just do the validation at btnCheckFinish...would that be better??

The issue I run into with using the vaidation code that u are suggesting is that after I do the btnCheckFinish, if no data is present in the field I'm validating, It'll pop up the "data needed" box and then give me a msg that says "No current record" and puts me back to the form. How can I get it where it gives me the validation message, give focus back to the form and won't continue the btnCheckFinish sub.

Thanks,

Clarence

Clarence,

One thing I would definitely do is test my code with the warnings turned on during testing and see just what kinds of warnings you are getting:

Secondly, I took a quick "rough" look at your code and saw two things that look problematic.

1. Your use of the LossFocus event for validation. Generally validation is done in the Form_BeforeUpdate event, which is the event that occurs just before any data is saved. Using the lostFocus event does not guarantee that the controls you want filled will be filled.....>>>>>>simply said, if a control never gets the focus, the lostfocus event will never fire for that control,.and if some of the controls cause an error if they are not filled....that could be a source for corruption<<<<<<< In addition, the before update event can be canceled for legitimate reasons ....like the person has to look up some information before entering. See this link for a step by step "howTo" do data validation using the beforeUpdate event of the Form.

http://www.databasedev.co.uk/validate_textbox.html

2. the second thing that looks potentially problematic is this line:

ClearChannel = "UPDATE TBLMaster SET TBLMaster.FldCleared = 'yes'"

Genrally yes/no fields are declared as a boolean data type; if FldCleared is defined as boolean in your table, then setting equal to the string "Yes" is incorrect...it should be set to Boolean True or False. Consequently, it is possible that your update statement is causing the corruption.


Hope this helps.

[puppydogbuddy]

Clarence,
I have a better idea. Create a Form_BeforeUpdate proc that calls (and re-executes) your lostFocus subs (as shown below). That way you can leave your lostFocus events as they are, except for modifications to the error message, which is also show below. Executing the Form_BeforeUpdate event in this way will act as a safety net for any validation errors not captured and/or corrected via your lostFocus event procedures, while piggybacking on corrections already made that no longer require correction.

The before update will concatenate each of the validation failure messages returned by the lostFocus subs and will advise the user with a single message box for all the remaining errors, with each message having its own row in the message box. The return value will cancel the update if any validation returns an error string and the user will be prevented from leaving the record. This is a necessary evil in order to force correction on any critical items. So, if the missing info that you are trying to trap in the lostFocus events is not critical to the integrity of your database, don’t call the lostFocus event for that item in your Form_beforeUpdate proc. This way you will have the best of both worlds, so to speak.


In the general declarations section, Dim a strMsg for each one of your messages in the lost focus events:

Option Compare Database
Option Explicit
 
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg3 As String
Dim strMsg4 As String
 
initialize the strMsg variables:
strMsg1 = "You must enter the customer's name!"
strMsg2 = "You must enter the 16 digit account number!"
strMsg3 = "You must enter the customer's address!"  
strMsg4 = "You must enter the customer's main phone number!"
 
2. In your Lost Focus 
 
Private Sub FldCustName_LostFocus()
     If IsNull(Forms![frmLeadChannelForm]![FldCustName]) Then
               MsgBox strMsg1, vbCritical
    End If
End Sub
 
Private Sub FldAcctNumb_LostFocus()
     If IsNull(Forms![frmLeadChannelForm]![FldAcctNumb]) Then
              MsgBox strMsg2 , vbCritical
     End If
End Sub
 
Private Sub FldAddress_LostFocus()
     If IsNull(Forms![frmLeadChannelForm]![FldAddress]) Then
              MsgBox strMsg3, vbCritical
     End If
End Sub
 
Private Sub FldPhoneNumb_LostFocus()
      If IsNull(Forms![frmLeadChannelForm]![FldPhoneNumb]) Then
               MsgBox strMsg4 , vbCritical
       End If
End Sub
 
Here is the Form_BeforeUpdate procedure:
 
Private Sub Form_BeforeUpdate(Cancel As Integer) 
Dim strMsg As String          ‘consolidates individual messages into one string
Dim x As Integer                 ‘ message subscript
 
 ‘call and re-execute lostFocus validation procedure as final check
      FldCustName_LostFocus
    FldAcctNumb_LostFocus
    FldAddress_LostFocus
    FldPhoneNumb_LostFocus
 
For x = 1 to 4
 strMsg = strMsg & (strMsg & x) 
Next x
 
If Len(strMsg & "")  <> "" Then
         Cancel = True
         MsgBox strMsg
End If
End Sub

[clarencemo]

Puppy,

U rock...I will try that...

In response to your message about possible code driven corruption routines,

How do I change this sub to instead update the fldMarket during data entry instead of the following code, which updates the whole table?

Private Sub Finish1()
 
    Me.Dirty = False
 
Dim UpdateDB As String
    UpdateDB = "UPDATE TblMaster, TblMarket SET TblMaster.FldMarket = TblMarket!FldMarket"
    UpdateDB = UpdateDB & " WHERE ((TblMarket!FldSysprin=Left(TblMaster!FldAcctNumb,6)));"
 
 
Dim MakeTable As String
    MakeTable = "SELECT TBLMaster.FldChannelNumb, TBLMaster.FldMarket, Count(TBLMaster.FldChannelNumb) AS CountOfFldChannelName, Count(TBLMaster.FldMarket) AS CountOfChannelProblem INTO TblChannelCount"
    MakeTable = MakeTable & " FROM TBLMaster WHERE (((TBLMaster.FldChannelNumb) Is Not Null) AND (TBLMaster.FldDate)=Date() AND (TBLMaster.FldCleared) Is Null)"
    MakeTable = MakeTable & " GROUP BY TBLMaster.FldChannelNumb, TBLMaster.FldMarket;"
 
DBEngine(0)(0).Execute (MakeTable)
DBEngine(0)(0).Execute (UpdateDB)
 
End Sub

[puppydogbuddy]

I found some errors in my previous, made the corrections here:

Clarence,
I have a better idea. Create a Form_BeforeUpdate proc that calls (and re-executes) your lostFocus subs (as shown below). That way you can leave your lostFocus events as they are, except for modifications to the error message, which is also show below. Executing the Form_BeforeUpdate event in this way will act as a safety net for any validation errors not captured and/or corrected via your lostFocus event procedures, while piggybacking on corrections already made that no longer require correction.

The before update will concatenate each of the validation failure messages returned by the lostFocus subs and will advise the user with a single message box for all the remaining errors, with each message having its own row in the message box. The return value will cancel the update if any validation returns an error string and the user will be prevented from leaving the record. This is a necessary evil in order to force correction on any critical items. So, if the missing info that you are trying to trap in the lostFocus events is not critical to the integrity of your database, don’t call the lostFocus event for that item in your Form_beforeUpdate proc. This way you will have the best of both worlds, so to speak.


In the general declarations section, Dim a strMsg for each one of your messages in the lost focus events:

Option Compare Database
Option Explicit
 
Dim strMsg As String
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg3 As String
Dim strMsg4 As String
 
initialize the strMsg variables:
strMsg = “”
strMsg1 = "You must enter the customer's name!"
strMsg2 = "You must enter the 16 digit account number!"
strMsg3 = "You must enter the customer's address!"  
strMsg4 = "You must enter the customer's main phone number!"
 
2. In your Lost Focus 
 
Private Sub FldCustName_LostFocus()
     If IsNull(Forms![frmLeadChannelForm]![FldCustName]) Then
            strMsg = strMsg1   
MsgBox strMsg, vbCritical
    End If
End Sub
 
Private Sub FldAcctNumb_LostFocus()
     If IsNull(Forms![frmLeadChannelForm]![FldAcctNumb]) Then
      strMsg = strMsg2   
           MsgBox strMsg , vbCritical
     End If
End Sub
 
Private Sub FldAddress_LostFocus()
     If IsNull(Forms![frmLeadChannelForm]![FldAddress]) Then
            strMsg = strMsg3  
MsgBox strMsg, vbCritical
     End If
End Sub
 
Private Sub FldPhoneNumb_LostFocus()
      If IsNull(Forms![frmLeadChannelForm]![FldPhoneNumb]) Then
            strMsg = strMsg4   
MsgBox strMsg , vbCritical
       End If
End Sub
 
Here is the Form_BeforeUpdate procedure:
 
Private Sub Form_BeforeUpdate(Cancel As Integer) 
 
Dim x As Integer                 ‘ message subscript
 
strMsg = “”
 ‘call and re-execute lostFocus validation procedure as final check
      FldCustName_LostFocus
    strMsg1 = strMsg
    FldAcctNumb_LostFocus
    strMsg2 = strMsg
FldAddress_LostFocus
strMsg3 = strMsg
    FldPhoneNumb_LostFocus
    strMsg4 = strMsg
strMsg = “”
For x = 1 to 4
 strMsg = strMsg & (strMsg & x) 
Next x
 
If Len(strMsg & "")  <> “” Then
         Cancel = True
         MsgBox strMsg
End If
End Sub

[clarencemo]

Now I tried that way u suggested and it gives me the error:

Compile error - Invalid Outside Procedure

--------------------------

[puppydogbuddy]

Now I tried that way u suggested and it gives me the error:

Compile error - Invalid Outside Procedure

--------------------------

Clarence,
The lines you need to correct are shown below. LInes 10, 17, 47, 51 (starting with message subscript), and 54 are not code and should be commented out. Lines 11, 53, 63 , and 68 should contain standard double quotes instead strange looking double quotes shown. For some reason, when I copy and paste, the single and double quote characters are not coming out quite right. Standard double quotes should look like this: " , 2 double quotes should look like this: "" not like this: “”. After you fix, run it again.. If there are errors, try to at least locate the error line and provide as much detail about the error message as you can..

Line 10 initialize the strMsg variables:
Line 11 strMsg = “”
Line 17 2. In your Lost Focus
Line 47 Here is the Form_BeforeUpdate procedure:
Line 51 ‘ message subscript
Line 53 strMsg = “”
Line 54 ‘call and re-execute lostFocus validation procedure as final check
Line 63 strMsg = “”
Line 68 If Len(strMsg & "") <> “” Then

[clarencemo]

Puppy,

Great looking out on the code...works great.

Now u said that update statement is a little leery so, can u help me clean up this sub

Private Sub Finish1()
    Me.Dirty = False
 
Dim UpdateDB As String
    UpdateDB = "UPDATE TblMaster, TblMarket SET TblMaster.FldMarket = TblMarket!FldMarket"
    UpdateDB = UpdateDB & " WHERE ((TblMarket!FldSysprin=Left(TblMaster!FldAcctNumb,6)));"
 
 
Dim MakeTable As String
    MakeTable = "SELECT TBLMaster.FldChannelNumb, TBLMaster.FldMarket, Count(TBLMaster.FldChannelNumb) AS CountOfFldChannelName, Count(TBLMaster.FldMarket) AS CountOfChannelProblem INTO TblChannelCount"
    MakeTable = MakeTable & " FROM TBLMaster WHERE (((TBLMaster.FldChannelNumb) Is Not Null) AND (TBLMaster.FldDate)=Date() AND (TBLMaster.FldCleared) Is Null)"
    MakeTable = MakeTable & " GROUP BY TBLMaster.FldChannelNumb, TBLMaster.FldMarket;"
 
DBEngine(0)(0).Execute (MakeTable)
DBEngine(0)(0).Execute (UpdateDB)
 
End Sub

It would be Ideal from a processing standpoint to instead do the update before the click. It currently works "on click" where it updates the whole tblMaster table. Can u help me change the code where it would update the field called fldMarket in tblMaster after focus is lost on the fldAcctNumb text box

fldMarket is based populated based on a table called tblMarket where the 1st 6 digits (fldSysPrin) of the 16 digit acct number = the market name (fldMarket)

example:

123456 = Denver, CO
789456 = Vail, CO

so if someone enters an account number of 1234560000000000 then the field called fldMarket in the tblMaster would be populated with Denver, CO upon lost focus of fldAcctNumb

Thanks for all your help,

Clarence

[puppydogbuddy]

Puppy,

Great looking out on the code...works great.

Now u said that update statement is a little leery so, can u help me clean up this sub

Private Sub Finish1()
    Me.Dirty = False
 
Dim UpdateDB As String
    UpdateDB = "UPDATE TblMaster, TblMarket SET TblMaster.FldMarket = TblMarket!FldMarket"
    UpdateDB = UpdateDB & " WHERE ((TblMarket!FldSysprin=Left(TblMaster!FldAcctNumb,6)));"
 
 
Dim MakeTable As String
    MakeTable = "SELECT TBLMaster.FldChannelNumb, TBLMaster.FldMarket, Count(TBLMaster.FldChannelNumb) AS CountOfFldChannelName, Count(TBLMaster.FldMarket) AS CountOfChannelProblem INTO TblChannelCount"
    MakeTable = MakeTable & " FROM TBLMaster WHERE (((TBLMaster.FldChannelNumb) Is Not Null) AND (TBLMaster.FldDate)=Date() AND (TBLMaster.FldCleared) Is Null)"
    MakeTable = MakeTable & " GROUP BY TBLMaster.FldChannelNumb, TBLMaster.FldMarket;"
 
DBEngine(0)(0).Execute (MakeTable)
DBEngine(0)(0).Execute (UpdateDB)
 
End Sub

It would be Ideal from a processing standpoint to instead do the update before the click. It currently works "on click" where it updates the whole tblMaster table. Can u help me change the code where it would update the field called fldMarket in tblMaster after focus is lost on the fldAcctNumb text box

fldMarket is based populated based on a table called tblMarket where the 1st 6 digits (fldSysPrin) of the 16 digit acct number = the market name (fldMarket)

example:

123456 = Denver, CO
789456 = Vail, CO

so if someone enters an account number of 1234560000000000 then the field called fldMarket in the tblMaster would be populated with Denver, CO upon lost focus of fldAcctNumb

Thanks for all your help,

Clarence

Hi Clarence,

Glad the code worked out for you.

In reference to your request above, what you want can be accomplished several ways, but I need more information from you, before pursuing..

Is your form bound or unbound (with respect to the table)? If your form is bound, you can move the update of fldMarket via the Form in one of two ways.

1. by means of users selecting the fldMarket from dropdown list whose rowSource would be your table.
2. by means of a Dlookup directly to the table based on your criteria

In either case above. I would recommend that you use the AfterUpdate (vs the lostFocus) of the control fldAcctNum on your form to trigger the update of fldMarket.

Let me know what you think.


Puppy

[clarencemo]

The form is bound to the tblMaster, however the first field is a UNBOUND field with a label called "Select Market", all other fields are bound to tblMaster

the Select market field is just used to determine how many instances of the same channel and same market have occurred

So I would like the acct number fisrt 6 to determine the market based on that table I told u about earlier

I think Dlookup would work better...as I'm trying to update the "dirty" record on the fly instead of having to update the whole table

Thanks again,

Clarence

[puppydogbuddy]

The form is bound to the tblMaster, however the first field is a UNBOUND field with a label called "Select Market", all other fields are bound to tblMaster

the Select market field is just used to determine how many instances of the same channel and same market have occurred

So I would like the acct number fisrt 6 to determine the market based on that table I told u about earlier

I think Dlookup would work better...as I'm trying to update the "dirty" record on the fly instead of having to update the whole table

Thanks again,

Clarence

Ok, Clarence. Try the following code in place of your applicable code. I think you should change the name property of the effected textboxes on your form to eliminate ambiiguity between the control name and the name of the control source, which is the field. So, as per naming conventions txtAcctNumb and txtMarket refer to the textbox controls on the form and fldAcctNumb and fldMarket refer to the control source of the textboxes, which are the fields in the table.

Private Sub txtAcctNumb_AfterUpdate()
 
If Not IsNull(Me![txtAcctNumb]) ThenMe![txtMarket].Value = DLooKup("[fldMarket]", "tblMarket", "[fldSysPrin] = " & Left(Me![txtAcctNumb],6) End If 
End Sub
 

The above syntax assumes fldSysPrin is a numeric data type. If it is a text data type, the syntax would be like this:

Me![txtMarket].Value = DLooKup("[fldMarket]", "tblMarket", "[fldSysPrin] = '" & Left(Me![txtAcctNumb],6) & "'"

[clarencemo]

Puppy,

That code works great...thanks for all your help this week...have a great weekend

Thanks,

Clarence

[puppydogbuddy]

Puppy,

That code works great...thanks for all your help this week...have a great weekend

Thanks,

Clarence

Clarence,
You are most welcome. I hope the new code solved your problem with the database corruption. Let me know if the database is still stable after a week or so.

Puppy