Sorry, guy's i didn't change my lng to str
please ignore
Sorry, guy's i didn't change my lng to str
please ignore
don't know how to delete post but sorted
sorry for confusion
I don't think you can delete here but you can elsewhere - not that it's a good thing. All I'd do is comment like you did.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thanks Micron, i just done a silly billy thing, Set lngNo
changed it to strNo to use string delimiters then didn't change the criteria in my recordset from lngNo to strNo
HI Micron and guy's, I thought i had this but I am having a problem with this, perhaps am I doing something wrong ?
I am trying to add data to next available excel cell but coming up with remote server doesn't exist and sometimes trying to open a locked Excel file
The line that is causing the problem is the (LR) last row, then i am trying to add data to next row, i can appear to get it working once but at 2nd attempt, it is coming up remote server not recognized ?
The Item No in stock is always 6 digits but seems to work better with string for delimiters ?
Code:Dim LR As Long, NR As LongDim strType As String Dim txtBox As String, strPath As String, strFile As String, sSQL As String, strItemNo As String Dim rs As DAO.Recordset Dim apXL As Object, xlWb As Object, xlWs As Object If Me.Stored = False Then Exit Sub End If If Me.Stored = True Then strItemNo = Me.ItemNo strFile = "T:\DMT Ltd\XL Files\Item Stock.xlsx" Set xl = CreateObject("Excel.Application") Set xlWrkBk = xl.Workbooks.Open(strFile) Set xlsht = xlWrkBk.Worksheets("Sheet1") xl.Visible = False LR = Cells(Rows.Count, 1).End(xlUp).Row (DEBUGGING) ' LR = Range("A:A").SpecialCells(xlCellTypeLastCell).Row NR = LR + 1 'Set rs = CurrentDb.OpenRecordset("Select tblStock.StartQty, tblStock.ItemType, tblStock.ItemNo, tblStock.PONumber, tblStock.OrgQty, tblStock.SortNo " _ & "From tblStock " _ & "WHERE ItemType Is Not NULL Order By SortNo;") 'CREATED TO ADD ALL ITEMS Set rs = CurrentDb.OpenRecordset("Select tblStock.StartQty, tblStock.ItemType, tblStock.ItemNo, tblStock.PONumber, tblStock.OrgQty, tblStock.SortNo " _ & "From tblStock " _ & "WHERE ItemNo = '" & strItemNo & "'") ' CREATED TO ADD A NEW ENTRY ' xlsht.Cells(2, 1).CopyFromRecordset rs 'CREATED TO ADD ALL ITEMS xlsht.Cells(NR, 1).CopyFromRecordset rs ' TO ADD A NEW ENTRY xl.ActiveWorkbook.Save xl.ActiveWorkbook.Close xl.Quit End If Debug.Print rs.Fields Set xl = Nothing Set xlWrkBk = Nothing Set xlsht = Nothing
I ask questions not as criticism but as potential changes while lacking certain knowledge of what someone is doing.
If Me.Stored = false then it is automatically true? Or can it be null or zls? I suppose it doesn't matter about the latter 2 points since code is only supposed to execute if True.
Do you have to use late binding?
Why not just set strPath (or strFile) to the complete path?
You are not using Option Explicit are you? As someone once posted, then you deserve what you get. Look at your app variable and Set statement. I stopped when I saw that.
EDT - there are other similar mistakes, not to mention a bunch of unused variables.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Hi Micron, thank you your honesty
yes if the check box is false then exit sub otherwise execute
i can change this after updating another field but kept to a check box more so for checking it working unchecking and rechecking just so i can see data going to the sheet
once i get this, i want to change the event to another field that is updated and hide the check box field, I'd rather tick a box than play with critical data
using option compare database when scrolling to the top of the vb screen
the extra variables i can get rid of as it used to do different commands but now not used, i didn't think they would harm as they are sat dormant, have now removed them
Just changed so the there is only 1 line for file strFile = the path and the file name.xlsx
This is not good enough IMO. Add it below the compare statement and try compiling your code to see why. Then maybe try the following (but do add & compile otherwise you miss out on a learning opportunity). If I was a betting man, I'd bet you've been told this before your 816th post. This code is untested and not compiled (written in Notepad) and contains 1 or more traps to let me know if you tried to compile it using Option Explicit or not (unless you just find them anyway). Don't blow away your current procedure.using option compare database when scrolling to the top of the vb screen
EDIT - I moved Quit line; make sure you didn't copy the old version if you use that code.Code:Dim strItemNo As String, sql As String Dim LRow As Long, NRow As Long Dim rs As DAO.Recordset Dim apXL As Object, xlWb As Object, xlWs As Object If Me.Stored = True Then strLiftNo = Me.LiftNo Set apXL = CreateObject("Excel.Application") xl.Visible = False Set xlWB = apXL.Workbooks.Open("T:\DMT Ltd\XL Files\Item Stock.xlsx") Set xlWs = xlWb.Worksheets("Sheet1") LRow = Cells(Rows.Count, 1).End(xlUp).Row '(DEBUGGING) 'LRow = Range("A:A").SpecialCells(xlCellTypeLastCell).Row NRow = LR + 1 'sql = CurrentDb.OpenRecordset("Select tblStock.StartQty, tblStock.ItemType," _ & " tblStock.ItemNo, tblStock.PONumber, tblStock.OrgQty, tblStock.SortNo From" _ & " tblStock WHERE ItemType Is Not NULL Order By SortNo;") 'CREATED TO ADD ALL ITEMS sql = "Select tblStock.StartQty, tblStock.ItemType, tblStock.ItemNo, tblStock.PONumber," _ & " tblStock.OrgQty, tblStock.SortNo From tblStock WHERE" _ & " ItemNo = '" & strItemNo & "'") ' CREATED TO ADD A NEW ENTRY Set rs = CurrentDb.OpenRecordset(sql) 'xlWs.Cells(2, 1).CopyFromRecordset rs 'CREATED TO ADD ALL ITEMS xlWs.Cells(NRow, 1).CopyFromRecordset rs ' TO ADD A NEW ENTRY With apXl .DisplayAlerts = False .xlWb.Save .xlWb.Close .DisplayAlerts = True 'maybe not req'd. Forget if app would reopen with alerts turned off .Quit End With End If Debug.Print rs.Fields Set apXl = Nothing Set xlWb = Nothing Set xlWs = Nothing
Last edited by Micron; 08-15-2022 at 12:28 PM. Reason: added comment
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
using option compare database when scrolling to the top of the vb screen
Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit
You are defining variables and not even using them just making up new ones and they are not dimmed.
Nothing seems to be learned.
I doubt very much you would get access to an excel locked file, so you need to make sure it is not open.
I know I have said this so many times, (especially with you) that it should be tattooed on my chest, but walk though your code and see what you actually have, not what you blithely think you have.
How does the LR= line even work, (Debugging) is not commented out?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Thanks Guy's for some reason it worked once then the 2nd time failed, so I have found changing this part totally fixed it
Given an opportunity i am going took into setting Option Explicit and early / late binding because I haven't really concentrated on this before
however, this problem is now solved by changing
this
To thisCode:lRow = Cells(Rows.Count, 1).End(xlUp).Row
thanks again for your help, always appreciatedCode:With xlWS lRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With nRow = lRow + 1
Go into the VBA window, then Tools/Options and make sure the Require Variable Declaration is ticked.
That will add Option Explicit to all new code event etc. It will NOT add it to existing code, so you need to do that manually.
This will highlight a lot of your error eraly on.
When using Excel (or Word) commands from within Access, you have to qualify the object of that command.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Thanks WGM, so it's good practice to that moving forwards regardless of new/old code ?
ie: do it at next opportunity ?
The With block is a bit of overkill? This does not tell your code what sheet your cell reference refers to:
lRow = Cells
Any valid sheet reference would suffice, such as ActiveSheet.Cells or Sheets("Sheet1").Cells or either of those with a qualified .Range reference.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thanks Micron, i can take back off the with block but it appeared to work every time after trying, i need to read more into binding as well and took on what WGM and checked the Require Variable Declaration