Have this bit of code I lifted from a older post (thanks Ed2020) in which I have modified to do my bidding however I cannot get it to complete. L The scenario is this , I have a table of 30 lines called, Ar_Code, with two fields of arrier_name and ar_code . I desire a VBA solution that moves through a make table query, call it Arrier_Open, that the criteria is for each ar_code, and then naming the resulting new table the respective arrier_name.
Now I think the code I have is very close to what I need however I get the following error when stepping through the function and near the end .
Run-time error ‘3067’:
Query input must contain at least one table or query.
Perhaps someone more familiar or with expert powers can see the issue and suggest the solution. I appreciate any suggestion.
Existing code
Public Sub MakeOpenTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim strSQL As String
Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT Ar_Code.Arrier_Nbr, Ar_Code.Carrier_Name FROM Ar_Code;", dbOpenSnapshot)
With rstCriteria
Do While Not .EOF
strSQL = "SELECT Arrier_Open1.* INTO tbl" & !Carrier_Name & " FROM Ar_Code WHERE (((Ar_Code)=" & """" & !Arrier_Name & """" & "));"
ThisDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
rstCriteria.Close
ThisDB.Close
Set rstCriteria = Nothing
Now I think the code I have is very close to what I need however I get the following error when stepping through the function and near the end .
Run-time error ‘3067’:
Query input must contain at least one table or query.
Perhaps someone more familiar or with expert powers can see the issue and suggest the solution. I appreciate any suggestion.
Existing code
Public Sub MakeOpenTables()
Dim ThisDB As DAO.Database
Dim rstCriteria As DAO.Recordset
Dim strSQL As String
Set ThisDB = CurrentDb()
Set rstCriteria = ThisDB.OpenRecordset("SELECT DISTINCT Ar_Code.Arrier_Nbr, Ar_Code.Carrier_Name FROM Ar_Code;", dbOpenSnapshot)
With rstCriteria
Do While Not .EOF
strSQL = "SELECT Arrier_Open1.* INTO tbl" & !Carrier_Name & " FROM Ar_Code WHERE (((Ar_Code)=" & """" & !Arrier_Name & """" & "));"
ThisDB.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
rstCriteria.Close
ThisDB.Close
Set rstCriteria = Nothing