Erratic Pop-up forms behavior ?

[abatra]

Hi All,

I am new to this forum so please pardon me if I miss anything.I am also pretty new to MS Access.
So I am sure I am doing something very stupid and just cant figure out what it is that I am doing wrong.

Context I have 2 forms.

1. Main Form (Upload) : Lets users browse and upload data. Also checks for errors. If there are errors then it opens Form 2 (Fix Errors)

1. Form 2 (Fix Errors) : Shows the records with errors and allows user to update incorrect data. Also checks for errors. If there are errors then it reopens Form 2 (Fix Errrors) with remaining errors.

Problem When I make a change in Form 2 (something even as small as adding a blank space in a string), the Form 2 pop up stops showing.

Code For Main Form

 
...Declarations...
 
'Import from excel
If Len(strFile) > 0 Then ' strPathFile = strPath & strFile
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPath, blnHasFieldNames 
End If
 
 
' Open data imported for checks
Set uDB = CurrentDb
Set rs_main = Nothing
Set rs_main = uDB.OpenRecordset("SELECT * FROM PROMO_DETAILS_ERROR_TABLE ORDER BY EVENT_NAME, ITEM_CODE, START_DATE")
 
' Checks
If Not (rs_main.EOF And rs_main.BOF) Then
    Do Until rs_main.EOF = True
    '....Multiple Checks like the one below...
 
    '''''''''''''''' Checks : No missing Status + Status matches list + test that there is only 1 status ''''''''''''''
            If IsNull(rs_main!Status) Then
                rs_main.Edit
                If IsNull(rs_main!ERROR_MSG) Then
                    rs_main!ERROR_MSG = "Missing Status."
                Else
                    rs_main!ERROR_MSG = rs_main!ERROR_MSG & vbNewLine & "Missing Status."
                End If
                rs_main!LAST_UPDATE = Now()
                rs_main.Update
            Else
                Set rs_status = uDB.OpenRecordset("SELECT VARIABLE_VALUE FROM AB_PROMO_APP_ALTERNATIVE_PARAMETER_REF_TABLE" & _
                                                        " WHERE VARIABLE = 'STATUS' AND VARIABLE_VALUE = '" & rs_main!Status & "'")
                If rs_status.EOF Then
                    rs_main.Edit
                    If IsNull(rs_main!ERROR_MSG) Then
                        rs_main!ERROR_MSG = "Incorrect Status."
                    Else
                        rs_main!ERROR_MSG = rs_main!ERROR_MSG & vbNewLine & "Incorrect Status."
                    End If
                    rs_main!LAST_UPDATE = Now()
                    rs_main.Update
                End If
            End If
 
            rs_status.Close
            Set rs_status = Nothing
 
    rs_main.MoveNext
    Loop
 
Else
   MsgBox "There are no records in the recordset."
End If
 
rs_main.Close 'Close the recordset
Set rs_main = Nothing 'Clean up
 
'....Some more declarations/ initializations...then the code to open form if there are no errors
 Set rs_main = uDB.OpenRecordset("SELECT COUNT(ERROR_MSG) AS COUNT_ERROR_MSG FROM PROMO_DETAILS_ERROR_TABLE WHERE ERROR_MSG IS NOT NULL")
 
If rs_main!COUNT_ERROR_MSG = 0 Then
        MsgBox "No Errors Found. Awesome!!!"
        'Replace_Promo_Flag = 0
        strSQL = "INSERT INTO AB_PROMO_APP_ALTERNATIVE_DETAILS_TABLE (BUSINESS_TERRITORY, INTERNATIONAL_KEY_ACCOUNT, EVENT_NAME, " & _
                "PROMOTION_FAMILY_NAME, START_DATE, END_DATE, VISIBILITY, STATUS, ITEM_CODE, CURRENCY_ID, BASE_PRICE,  DISCOUNT_VALUE, " & _
                " ADDITIONAL_DISCOUNT_VALUE, FREE_WEEKEND_FLAG, BOGO_FLAG, UNIQUE_ID, ITEM_COUNT, LAST_UPDATE, LAST_UPDATED_BY_USER, UPDATED_FLAG) " & _
                "SELECT BUSINESS_TERRITORY, INTERNATIONAL_KEY_ACCOUNT, EVENT_NAME, " & _
                "PROMOTION_FAMILY_NAME, START_DATE, END_DATE, VISIBILITY, STATUS, ITEM_CODE, CURRENCY_ID, BASE_PRICE,  DISCOUNT_VALUE, " & _
                " ADDITIONAL_DISCOUNT_VALUE, FREE_WEEKEND_FLAG, BOGO_FLAG, UNIQUE_ID, ITEM_COUNT, LAST_UPDATE, LAST_UPDATED_BY_USER, UPDATED_FLAG " & _
                " FROM PROMO_DETAILS_ERROR_TABLE"
        uDB.Execute strSQL
        strSQL = "DELETE * FROM PROMO_DETAILS_ERROR_TABLE"
        uDB.Execute strSQL
        Forms!PromoMainPageForm!TeradataSyncButton.Enabled = True
    Else
            DoCmd.Close acTable, "PROMO_DETAILS_ERROR_TABLE"
            DoCmd.Close acTable, "AB_PROMO_APP_ALTERNATIVE_DETAILS_TABLE"
            DoCmd.OpenForm ("PromoDetailsCorrectionForm")
End If
 
DoCmd.Close acTable, "PROMO_DETAILS_ERROR_TABLE"
DoCmd.Close acTable, "AB_PROMO_APP_ALTERNATIVE_DETAILS_TABLE"
 
uDB.Close
Set uDB = Nothing
 
End Sub
 
 

Code for Form 2

 
' Exactly the same as Form 1 except open recordset only shows records with error instead of everything
Set dcDB = CurrentDb
Set rs_error_main = dcDB.OpenRecordset("SELECT * FROM PROMO_DETAILS_ERROR_TABLE WHERE ERROR_FLAG = -1 ORDER BY EVENT_NAME, ITEM_CODE, START_DATE, END_DATE")
 
' All checks same as Form 1
 
DoCmd.Close acTable, "PROMO_DETAILS_ERROR_TABLE"
DoCmd.Close acTable, "AB_PROMO_APP_ALTERNATIVE_DETAILS_TABLE"
 
dcDB.Close
Set dcDB = Nothing
 
End Sub
 

Thanks for your help in advance!

[twinnyfo]

abatra,

Welcome to Bytes!

1) Why do you keep closing your Tables? a) they are not being opened and b) opening a table does nothing to edit that table. These lines of code can be deleted.

2) I am not exactly sure what it is you are "trying" to do with these forms, as I see no place for the user to make updates (and you certainly ought not to have users edit the tables directly--which may be why those tables are open).

3) WHy are you using recordsets for these forms when you could just have the form bound to a table and filter the records by those that have errors?

I also see no place for your Form 2 to close and preform these checks.

A lot of confusion for me here, but I am willing to work through understanding so that we can hepp.