Have an 3349 error while exporting data

S

Skip Bisconer

Who Me?
Local time
Today, 15:07
Joined
Jan 22, 2008
Messages
285
I have a queries built on a linked Excel file. The Excel file has a vlookup field that is forced to "" if there is an error. In the linked files, in Access, these numbers show up as #NUM and I believe are creating me grief when trying to export the queries back to Excel. I get an error 3349: "You cannot record your changes because a value you entered violates the settings difined for this table or list. Correct the errror and try again."

Has anyone ran into this and is there a workaround for it? Possibly I could build criteria into the queries GLCode field but I don't know what to use for this.

Any help would be appreciated. The following is my export code for the above mentioned queries in case I have created the problem in the code.
I have commented out the error routine so I could determine which line of code is dying and it is the .Range("B" & i + 1).Value = rs.Fields("GLCode").Value where the halt is highlighted. That is the field in the query that has the #NUM error.

Code: 
Function ExportToExcel()
    ' Direct procedure flow.
'On Error GoTo Error
 
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBisconer.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
 
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesYoung.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11012", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
 
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesMurphy.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11020", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
 
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesHyler.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11038", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesDonati.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11046", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
 
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesWatkins.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11079", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("Cardo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesOboyle.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11095", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesJohnson.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11103", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBourgeois.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11111", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesKowaleski.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges12002", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBriare.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
 
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges12085", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
  .Range("J" & i + 1).Value = rs.Fields("Customer").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
 rs.Close
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesLund.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges13067", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = rs.Fields("GLExpense").Value
  .Range("B" & i + 1).Value = rs.Fields("GLCode").Value
  .Range("C" & i + 1).Value = rs.Fields("LOC").Value
  .Range("D" & i + 1).Value = rs.Fields("Customer").Value
  .Range("E" & i + 1).Value = rs.Fields("LastName").Value
  .Range("F" & i + 1).Value = rs.Fields("FirstName").Value
  .Range("G" & i + 1).Value = rs.Fields("CardNo").Value
  .Range("H" & i + 1).Value = rs.Fields("Date").Value
  .Range("I" & i + 1).Value = rs.Fields("BusinessType").Value
  .Range("J" & i + 1).Value = rs.Fields("Commodity").Value
  .Range("K" & i + 1).Value = rs.Fields("SupplierName").Value
  .Range("L" & i + 1).Value = rs.Fields("Amount").Value
  .Range("M" & i + 1).Value = rs.Fields("Department").Value
  .Range("J" & i + 1).Value = rs.Fields("Customer").Value
 
 
  End With
i = i + 1
rs.MoveNext
Loop
   rs.Close
   db.Close
 
    'Exit Function
'ErrorHandler:
    ' Display error information.
    'MsgBox "Error number " & Err.Number & ": " & Err.Description
    ' Resume with statement following occurrence of error.
    'Resume Next
 
End Function
 
Skip Bisconer said:
The Excel file has a vlookup field that is forced to "" if there is an error.
Click to expand...
Two things that come to mind about this. 1) they are not real values in the fields because they are being looked up. And 2) A zero-length string is not a number (which I'm assuming the cells are formatted as numeric somehow?
Skip Bisconer said:
In the linked files, in Access, these numbers show up as #NUM and I believe are creating me grief when trying to export the queries back to Excel.
Click to expand...
I think this is saying that there is a mismatch, as I stated above (but I could be wrong!)
Skip Bisconer said:
I get an error 3349: "You cannot record your changes because a value you entered violates the settings difined for this table or list. Correct the errror and try again."
Click to expand...
Again here too, same thing. Remember, Access takes the first 3 values (or is it 5?) of the records in an imported or linked table and assigns the data type based on the findings...


One other question too: Do you get the #Num error in the actual Access table that is linked to the Excel table, or do you get it only when you have queried the Access table's data (that is in turn comming from Excel)?
 
thanks for looking at my problem.

The linked table property for that field is numeric Double. When opened the fields not already populated are #NUM. This is the same with the Queries. The fields are generated in Excel with a lookup to a table that forces unfound lookup to "".

This is the criteria for the field in Excel:
Sheets(1).Range("B2").Formula = "=IF(ISNA(VLOOKUP(A2,GlCode,2)),"""",VLOOKUP(A2,GlCode,2))" Where A2 is a text field and is associated in the table with a 4 digit numeric field. It comes about with code that prepopulates some common transactons with a General Ledger code.

During a test of the export process previous to adding this code to the XL workbook worked OK.
 
You must log in or register to reply here.

Similar threads

B
  • Question Question
export and transpose problem
Replies
6
Views
1,263
Gasman
Gasman
L
Export data from Access to Word with VBA
Replies
11
Views
2,531
arnelgp
arnelgp
G
How to fill Listbox with data from another workbook
Replies
3
Views
1,301
goncalo
G
G
Solved Increment a value
Replies
3
Views
981
goncalo
G
G
Solved Export data to another workbook
Replies
4
Views
902
goncalo
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom