Solved Issue with acDialog on Adding a Record (1 Viewer)

[Sonnydl]

Hello,

I'll try to make this clear, but I'm not sure if I'll be able to. Maybe I've got too much background, but I don't know how else to get an answer without giving the context. I hope it helps.

I'm using Category, Product and Type as my data buckets, but that's for simplicity of explanation only. That's not the industry I'm supporting, but I'm using them so I don't also have to also explain my industry.

What I'm doing is importing a decent amount of data from an Excel file that is related to a certain Category. To do anything in my database, you first have to select a Category, only then can you do everything else. Anyway, to do this import, I've gone down this path:

1. Grab the Excel file using FileDialog and import the data straight into a TEMP table (works great)
2. Clean the data so that it aligns with my tables (works great)
3. Where applicable use the aligned data to put in a Foreign Key (FK) that normalizes the imported data to my database (mostly works very well, except for the issue below.)
4. Once all the data is normalized with FKs, import it into the proper tables.

So at one of the steps I'm adding a ProductID_FK into the TEMP table. Do do this, I'm using a function that returns a 1, 2, or 3 as a response (this works fine).

1. If the database already has this Product (a text field in the TEMP table) listed for this Category (I may have the same product name under two different categories--that's outside data I cannot control. But there is a 1:1 relationship between a category and a product. This check works great.), then use an update query to put the ProductID_FK in the TEMP table and return the value 3, which tells the Function and calling subroutine to keep going.
2. If there is no matching Product for this Category, the function keeps the value at 1 (where it was set), and a MesssageBox pops up to ask the user if they want to add the product to this category:
   • If they say NO, the function sets its value to 2, and returns that to the calling subroutine, where the a check of the value shows it's 2 and the whole process is halted, as it cannot continue without this product FK. (works great).
   • If they say YES, then the ADD PRODUCT form pops up.

So here was my first challenge: I need to wait until the ADD PRODUCT form is closed before continuing any more code in the function. After a quick google search, it seems like I need to make this ADD PRODUCT form open as a dialogue box, using acDialog. Perfect. Works like a charm... except for ONE thing.

There is one combobox (let's call it TYPE) on the form that is required and draws from one of my normalized tables. If you try to enter a value that isn't in the list, you are permitted to do so, and a quick "ADD NEW TYPE" form pops up. This all works fine when I'm using this form in another pathway. However, if you are an experienced programmer you already see what's wrong:

Because I'm using the acDialog parameter the "ADD NEW TYPE" form won't pop up. In essence, I'm stuck. I know I can back out and add the new Product and Type through another pathway--and tell my users "Before you can proceed you must...." but I am:

1. Trying to learn about my options and other/better ways of doing things (isn't that what this forum is for?), AND
2. Trying to allow flexibility in the workstream, if possible.

Here's the essence of the code, btw:

Private Sub ImportStuff()

'Do all the fixing

'Add ProductID foreign key
If AddPrIDFK = 2 then
    'Tell them the import was cancelled
    Exit Sub
End if

'Continue the import
End Sub

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

Function AddPrIDFK() as Byte

'I use Allen Browne's ELookup and ECount functions

Dim strProduct as String
Dim strMessage as String

strProduct = ELookup("Product", "TempTable")
strMesssage = "No product for this category. Do you want to add the product? blah blah blah. You'll keep seeing this until you fix it or say [No]"

AddPrIDFK = 1

Do
    If ECount("*", "qryCatProd", "[Cat] = " & lngCat & " AND [Prod] = '" & strProduct & "'") = 0 then
        If MsgBox(strMessage) = vbYes then
            DoCmd.Openform "pfrmAddNewProduct", , , , acDialog '<--If I don't use acDialog here, the code keeps running. So that's why I did it.
        Else
            AddPrIDFK = 2
        End if

    Else
        AddPrIDFK = 3
    End If

Loop Until AddPrIDFK > 1

End Function

 

[CJ_London]

you have typo's in your code which either means you have not set option explicit, or you may be getting unexpected results. Or you have free-typed your code in which case we cannot trust that you have provided an accurate representation of your code.

Not tried it, but in your pfrmAddNewType form open event put

me.modal=false

this should turn off the dialog mode so you can go to other forms. Perhaps the calling code still runs, perhaps not. Try it and see

Alternative is to set the form modal property to true and save (still need the code in the open event to turn it off)

then just use the openform without the dialog parameter. Again, don't know if that will halt the execution of the following code but try it and see

 

[Sonnydl]

you have typo's in your code which either means you have not set option explicit, or you may be getting unexpected results. Or you have free-typed your code in which case we cannot trust that you have provided an accurate representation of your code.

Not tried it, but in your pfrmAddNewType form open event put

me.modal=false

this should turn off the dialog mode so you can go to other forms. Perhaps the calling code still runs, perhaps not. Try it and see

Alternative is to set the form modal property to true and save (still need the code in the open event to turn it off)

then just use the openform without the dialog parameter. Again, don't know if that will halt the execution of the following code but try it and see

I'm just left scratching my head.

So typos in my simplified faux code aside (again, it was for demo purposes, but the flow was accurate), I did what you suggested and added the Me.Modal = False code into my OnLoad event for the ADD PRODUCT form.

The result I got was:

1. If I did try to add a new TYPE , I did get the additional pop-up form, BUT
2. Something else went drastically wrong when I closed (Cancel or Save) the ADD NEW PRODUCT to close out of the process, which had worked before: The sub or function never stopped running (the Run button stayed greyed-out). It was kind of stuck in stasis, and I had to [ESC] twice to get the VBA window to allow me to do anything. Meanwhile, I could click on any forms because the me.modal was false.

So, I commented out the Me.Modal = False code and tried stepping through the processes again. Lo and behold, if I entered an unknown TYPE, it just worked as expected--popping up the Add New TYPE mini-form, allowing me to add it to the TYPES table, allowing me to finish adding the new PRODUCT and (with the additional, proper code) adding the new product's FK to the TEMP table.

So... I just don't know.

I'll mark this as solved but I don't know how.

 

[CJ_London]

I did what you suggested and added the Me.Modal = False code into my OnLoad event for the ADD PRODUCT form.

No, you didn't - I said the open event, not the load event.

Don't understand what you are saying in point 2. What process? What sub or function?

Anyway, seems you have a solution

 

[ebs17]

Trying to learn about my options and other/better ways of doing things

An import with a lot of dialogs wouldn't be a good solution for me, nor would it be a good solution at all if it were to import a lot of data that is available in a standardized form.

I have attached an example database where the data from a (simplified) Excel table, i.e. a denormalized table, is imported into the three tables of a many-to-many relationship.

The import procedure uses one append query per target table. That's all. Simply. Direct. Fast.

This system can of course be expanded
- if there is more than one relationship and therefore more tables in the database schema
- if updates to existing records become necessary in addition to the additions.