By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,215 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,215 IT Pros & Developers. It's quick & easy.

Multiple If Statements with multiple Cmd's

Brilstern
100+
P: 208
I am having trouble getting a full understanding of how to use multiple if statements. I want to first use the msgbox, and if vb yes then import the two spreadsheets below with if statements here checking first if a file is present, is so then import them, if not import the blank file for both imports. Not really sure how to word this correctly but I and getting a compile error, Block If without End If.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command27_Click()
  2.  
  3. If MsgBox("Are you sure you want to import today's JRC Manifest?", vbYesNo, "Importing") = vbYes Then
  4.     If Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Inbound\In.xlxs") = "" Then
  5.         DoCmd.RunSavedImportExport "Import-In"
  6.       Else
  7.         DoCmd.RunSavedImportExport "Import-Inblk"
  8.         If Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Outbound\Out.xlxs") = "" Then
  9.           Else
  10.             DoCmd.RunSavedImportExport "Import-Outblk"
  11.             DoCmd.Requery ""
  12.  
  13. End If
  14.  
  15. End Sub
Dec 22 '11 #1

✓ answered by TheSmileyCoder

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command27_Click()
  2. 'Get confirmation from user
  3.   If vbYes=MsgBox("Are you sure you want to import today's JRC Manifest?", vbYesNo, "Importing") Then
  4.     'Check for file existance Inbound
  5.       If Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Inbound\In.xlxs") = "" Then
  6.           'File does not exist, import blank sheet      
  7.           DoCmd.RunSavedImportExport "Import-In"
  8.         Else
  9.           'File exists, import
  10.           DoCmd.RunSavedImportExport "Import-Inblk"
  11.       End If
  12.  
  13.  
  14.     'Check for file existance outbound
  15.       If Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Outbound\Out.xlxs") = "" Then
  16.           'File does not exist, do nothing
  17.         Else
  18.           'File exists
  19.           DoCmd.RunSavedImportExport "Import-Outblk"
  20.        End If
  21.  
  22.    'Done importing, update screen
  23.      DoCmd.Requery ""
  24.   End If
  25.  
  26. End Sub

Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command27_Click()
  2. 'Get confirmation from user
  3.   If vbYes=MsgBox("Are you sure you want to import today's JRC Manifest?", vbYesNo, "Importing") Then
  4.     'Check for file existance Inbound
  5.       If Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Inbound\In.xlxs") = "" Then
  6.           'File does not exist, import blank sheet      
  7.           DoCmd.RunSavedImportExport "Import-In"
  8.         Else
  9.           'File exists, import
  10.           DoCmd.RunSavedImportExport "Import-Inblk"
  11.       End If
  12.  
  13.  
  14.     'Check for file existance outbound
  15.       If Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Outbound\Out.xlxs") = "" Then
  16.           'File does not exist, do nothing
  17.         Else
  18.           'File exists
  19.           DoCmd.RunSavedImportExport "Import-Outblk"
  20.        End If
  21.  
  22.    'Done importing, update screen
  23.      DoCmd.Requery ""
  24.   End If
  25.  
  26. End Sub
Dec 22 '11 #2

Brilstern
100+
P: 208
I had to change a few things but I understand it a little bit better now. I am sorry on my lack of code logic, I a have only really built two databases for work in the military and it is nowhere in my normal job field. I appreciate the quick reply everything works now. Not used to posting code as well. I had to google code tags...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command27_Click()
  2.     'Get confirmation from user
  3.       If vbYes = MsgBox("Are you sure you want to import today's JRC Manifest?", vbYesNo, "Importing") Then
  4.         'Check for file existance Inbound
  5.           If Len(Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Inbound\In.xlsx")) = "0" Then
  6.              'File does not exist, import blank sheet
  7.               DoCmd.RunSavedImportExport "Import-Inblk"
  8.             Else
  9.               'File exists, import
  10.               DoCmd.RunSavedImportExport "Import-In"
  11.  
  12.           End If
  13.  
  14.  
  15.         'Check for file existance outbound
  16.           If Len(Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Outbound\Out.xlsx")) = "0" Then
  17.               'File does not exist, import blank sheet
  18.               DoCmd.RunSavedImportExport "Import-Outblk"
  19.             Else
  20.               'File exists, import
  21.               DoCmd.RunSavedImportExport "Import-Out"
  22.            End If
  23.  
  24.        'Done importing, update screen
  25.          DoCmd.Requery ""
  26.       End If
  27.  
  28.     End Sub
Dec 22 '11 #3

NeoPa
Expert Mod 15k+
P: 31,707
Stevan Bias:
Not used to posting code as well. I had to google code tags...
Well, that sets you apart from most members then :-) Most just ignore them. You've done a good job even without any prior understanding.

As a tip for a new user who obviously has the intelligence and attitude to get things done, check out Context-Sensitive Help. You will find so much to help you get going, even with things you've not come across before in VBA.
Dec 22 '11 #4

Brilstern
100+
P: 208
NeoPa,

I appreciate the Tip. I will definitely utilize that. I have a lot to learn in Access and any help Is depreciated.
Dec 22 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.