Something like this:
--- UNTESTED CODE -----
Code:
Private Sub Command18_Click()
On Error GoTo HandleErr
Dim rs As DAO.Recordset
Dim strSQL As String
Dim sCountry As String
Dim LocID As Long
strSQL = "tblUpload"
'get initial LocID number
LocID = Nz(DMax("LocationID", "dbo_tblLocations"), 1)
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
'get Country abbreviation
sCountry = IIf(![Country] = "United States", "USA", IIf(![Country] = "Canada", "CAN", IIf(![Country] = "Aruba", "ARU", IIf(![Country] = "Carribean", "CAR", IIf(![Country] = "Puerto Rico", "PR", "")))))
'create SQL Append query
strSQL = "INSERT INTO dbo_tblLocations ( LocationID, Division, LocationName, DisplayType, DisplayColor,"
strSQL = strSQL & " Address1, City, State, ZIP, Country, Contact, EmailAddress, [Phone#], Rooms )"
strSQL = strSQL & " VALUES( " & LocID & ", " & Left(![Network], 2) & ", '" & UCase("EB - " & ![Name]) & "', '"
strSQL = strSQL & ![Type] & " " & ![Size] & "', " & !Color & ", '" & !Address & "', '" & !City & "', '"
strSQL = strSQL & !State & "', '" & !Postcode & "', '" & sCountry & "', '" & !Contact & "', "
strSQL = strSQL & ![Contact Email] & "', " & ![Contact Phone] & "', " & ![Room Count] & ");"
' Debug.Print strSQL
'insert the record
CurrentDb.Execute strSQL, dbFailOnError
'increment LocID
LocID = LocID + 1
'move to next record in recordset
.MoveNext
Wend
End If
.Close
End With
Exit_Here:
Set rs = Nothing
Exit Sub
HandleErr:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Here
End Sub
I guessed on the field types when creating the SQL string. Un-comment the debug line and single step through the code. Look at the Immediate Window to see if the SQL string is formed correctly. (the delimiter are correct).
Re-comment after testing is done.
Try this on a COPY of your dB.
Remember:
"Type" is a reserved word in Access and shouldn't be used as an object name.
Shouldn't use spaces or special characters in object names. (as in "Phone#")