air code means approximate code, typically what is provided as a guidance as to what to do. The recipient is expected to be able to substitute and correct table/field/control names etc. e.g. these are examples of aircode
Code:
SELECT *
FROM sometable
docmd.runsql "myqueryname"
with regards your else if's, they could be reduced to 3 or 4 lines of code
the other thing you need to do is to use the code tags when you have more than a few lines of code, this will preserve indenting and make the code easier to read. Going forward if you don't, don't expect much in the way of responses. To use the code tags, after pasting the code, highlight it and click the code button (the # button)
Code:
Try_Again:
strbranch = InputBox("Please Enter your two digit branch number: ")
Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
If strbranch = "32" Then
DoCmd.RunSavedImportExport ("Import-32_AN Group Training Template")
GoTo Importing_Copies
ElseIf strbranch = "14" Then
DoCmd.TransferDatabase acImport, , "C:\\Users\sclbar1\Desktop\14_BL.mdb", acTable, "tbl01Monday", "tbl01Monday1", False
GoTo Importing_Copies
ElseIf strbranch = "17" Then
...
...
Else
MsgBox "Invalid branch number, please try again."
GoTo Try_Again
end if
suggestion for reducing the code - this is aircode because I haven't completed the whole of the case 32,14... line
Code:
Try_Again:
strbranch = InputBox("Please Enter your two digit branch number: ")
Select case strBranch
case 32,14,16,17.....
Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
DoCmd.RunSavedImportExport ("Import-" & strBranch & "_AN Group Training Template")
'you don't need the goto importing_copies line
case else
MsgBox "Invalid branch number, please try again."
GoTo Try_Again
end select
moving now to a solution you can try this - you'll need to supply the password
Code:
at the top of your function put
Dim DB As DAO.Database
...
...
...
Try_Again:
strbranch = InputBox("Please Enter your two digit branch number: ")
Select case strBranch
case 32,14,16,17.....
Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
Set DB = DBEngine.OpenDatabase(Name:="C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb", Options:=False, ReadOnly:=False, Connect:=";PWD=" & myPassword)
DoCmd.TransferDatabase acImport, "Microsoft Access" , "C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb", acTable, "tbl01Monday", "tbl01Monday1", False
case else
MsgBox "Invalid branch number, please try again."
GoTo Try_Again
end select
or alternatively
Code:
at the top of your function put
Dim sqlStr as string
...
...
...
Try_Again:
strbranch = InputBox("Please Enter your two digit branch number: ")
Select case strBranch
case 32,14,16,17.....
Application.AutomationSecurity = msoAutomationSecurityLow 'Turn Security notice off
sqlStr="SELECT * INTO tbl01Monday1 FROM tbl01Monday IN 'C:\\Users\sclbar1\Desktop\" & strbranch & "_BL.mdb'[PWD=mypassword]"
currentdb.execute sqlstr
case else
MsgBox "Invalid branch number, please try again."
GoTo Try_Again
end select
if it doesn't work, post back what you have actually used - only needs the relevant section together with the reason why and any error messages