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:
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:
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:
- Grab the Excel file using FileDialog and import the data straight into a TEMP table (works great)
- Clean the data so that it aligns with my tables (works great)
- 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.)
- Once all the data is normalized with FKs, import it into the proper tables.
- 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.
- 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.
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:
- Trying to learn about my options and other/better ways of doing things (isn't that what this forum is for?), AND
- Trying to allow flexibility in the workstream, if possible.
Code:
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
Last edited: