coding questions

[cager]

Hi Everyone,

I have written some code for end users that when they click a command button
on a form a msgbox appears. If the user clicks yes, then my code works fine
and runs through all the lines perfectly. But if the user clicks "NO" I want
the code to open a table up so the user can update the table. My problem is
when this happens I want the rest of the code to halt until the user updates
the table and then closes that table. Upon the close of the table, then I
want the rest of the code to execute. Is this possible?

Here is my code. Any help would be greatly appreciated.

Option Compare Database
Option Explicit




Public Sub Top25()

Dim strMsgBox As String

DoCmd.SetWarnings False

strMsgBox = MsgBox("Did you update the Top 25 Weekly Data Spreadsheet
yet?", vbYesNo, "STOP!!!")

If strMsgBox = vbYes Then

DoCmd.OpenQuery "qdel_Weekly_Comparison_Data"
DoCmd.OpenQuery "qapp_Weekly_Comparison_Data"
DoCmd.OpenQuery "qdel_Dept75_2007_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept75_2007_Unit_Sales"
DoCmd.OpenQuery "qdel_Dept75_2008_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept75_2008_Unit_Sales"
DoCmd.OpenQuery "qdel_Dept175_2007_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept175_2007_Unit_Sales"
DoCmd.OpenQuery "qdel_Dept175_2008_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept175_2008_Unit_Sales"
DoCmd.OpenQuery "qdel_2007_Unit_Sales"
DoCmd.OpenQuery "qapp_2007_Unit_Sales"
DoCmd.OpenQuery "qdel_2008_Unit_Sales"
DoCmd.OpenQuery "qapp_2008_Unit_Sales"

'Ranks all books by sales units from 1 through "x"

Call UnitSales2007
Call UnitSales2008
Call UnitSalesDept175_2007
Call UnitSalesDept175_2008
Call UnitSalesDept75_2007
Call UnitSalesDept75_2008

DoCmd.OpenQuery "qdel_Dept75_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept75_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_Dept75_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept75_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_Dept175_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept175_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_Dept175_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept175_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_2008_Unit_Sales_Top25"

'Re-Ranks only top 25 for year 2008 for any title that had to have
qty changed to match STR

Call UnitSalesDept75_2008_Top25_Rerank
Call UnitSalesDept175_2008_Top25_Rerank
Call UnitSales2008_Top25_Rerank

DoCmd.OutputTo acOutputReport, "rptFinal", acFormatSNP,
"F:\Top25\BJs_Top_25_" & Format(Date, "mmddyy") & ".snp", True

DoCmd.SetWarnings True

End If

If strMsgBox = vbNo Then

DoCmd.OpenTable "tbl_Weekly_Comparison"

DoCmd.OpenQuery "qdel_Weekly_Comparison_Data"
DoCmd.OpenQuery "qapp_Weekly_Comparison_Data"
DoCmd.OpenQuery "qdel_Dept75_2007_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept75_2007_Unit_Sales"
DoCmd.OpenQuery "qdel_Dept75_2008_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept75_2008_Unit_Sales"
DoCmd.OpenQuery "qdel_Dept175_2007_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept175_2007_Unit_Sales"
DoCmd.OpenQuery "qdel_Dept175_2008_Unit_Sales"
DoCmd.OpenQuery "qapp_Dept175_2008_Unit_Sales"
DoCmd.OpenQuery "qdel_2007_Unit_Sales"
DoCmd.OpenQuery "qapp_2007_Unit_Sales"
DoCmd.OpenQuery "qdel_2008_Unit_Sales"
DoCmd.OpenQuery "qapp_2008_Unit_Sales"

'Ranks all books by sales units from 1 through "x"

Call UnitSales2007
Call UnitSales2008
Call UnitSalesDept175_2007
Call UnitSalesDept175_2008
Call UnitSalesDept75_2007
Call UnitSalesDept75_2008

DoCmd.OpenQuery "qdel_Dept75_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept75_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_Dept75_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept75_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_Dept175_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept175_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_Dept175_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_Dept175_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_2007_Unit_Sales_Top25"
DoCmd.OpenQuery "qdel_2008_Unit_Sales_Top25"
DoCmd.OpenQuery "qapp_2008_Unit_Sales_Top25"

'Re-Ranks only top 25 for year 2008 for any title that had to have
qty changed to match STR

Call UnitSalesDept75_2008_Top25_Rerank
Call UnitSalesDept175_2008_Top25_Rerank
Call UnitSales2008_Top25_Rerank

DoCmd.OutputTo acOutputReport, "rptFinal", acFormatSNP,
"F:\Top25\BJs_Top_25_" & Format(Date, "mmddyy") & ".snp", True

DoCmd.SetWarnings True

End If


End Sub

 

[Rick Brandt]

Hi Everyone,

I have written some code for end users that when they click a command
button on a form a msgbox appears. If the user clicks yes, then my
code works fine and runs through all the lines perfectly. But if the
user clicks "NO" I want the code to open a table up so the user can
update the table. My problem is when this happens I want the rest of
the code to halt until the user updates the table and then closes
that table. Upon the close of the table, then I want the rest of the
code to execute. Is this possible?

Here is my code. Any help would be greatly appreciated.

Can't be done with a table or query. Use a FORM (that's what they're for)
that you open using the acDialog option. The code will halt at the line
that opens the form until that form is closed or hidden.

 

[cager]

Thank makes perfect sense Rick. Thank you very much

 

[cager]

Rick,

I must be doing something wrong b/c on my if vbno line.

It opens up the form as you suggested but it goes right to the next line of
code without me closing the form.

Can you point me in the right direct as to what I'm doing wrong?

If strMsgBox = vbNo Then

DoCmd.OpenForm "frmWeeklyComparison", acPreview, , , , acDialog

MsgBox "test"

 

[cager]

I'm an idiot...duh. I needed to tie the rest of the code to the "on close"
of the form that opened up b/c of the vbno instead of keeping all the rest of
the code in the same procedure.

Sorry bout that.

 

[Rick Brandt]

I'm an idiot...duh. I needed to tie the rest of the code to the "on
close" of the form that opened up b/c of the vbno instead of keeping
all the rest of the code in the same procedure.

Sorry bout that.

No you should not have to do that. That would defeat the whole point of
using the dialog mode of the form.

Perhaps it is because you specified acPreview. You need to specify acNormal
(the default). The form also cannot be a datasheet form. Those cannot be
opened in acDilaog mode.

 

[cager]

Okay I'll give it a try and let you know if it works.