secondangel
Registered User.
- Local time
- Today, 09:42
- Joined
- Nov 6, 2008
- Messages
- 52
Ive got some code i have been using on a button fine involving scrolling through 1 recorset and created a new recordset using variables froma form and data from the first recordset.
It all works fine.
My second bit of code does the same, but at the end i then have a transfertext acdelim to export the recordset as a tab delim text file but it says the db is read only. I can do it manually from the export menu but i wish it to do it automatically.
here is my code with the export text. it runs thru fine until that option.
i even tried putting the export code on a seperate button but it still said read only so im presuming either the delete qry to clear the recordset first or the way i open the recordset is setting it as a problem somewhere
It all works fine.
My second bit of code does the same, but at the end i then have a transfertext acdelim to export the recordset as a tab delim text file but it says the db is read only. I can do it manually from the export menu but i wish it to do it automatically.
here is my code with the export text. it runs thru fine until that option.
i even tried putting the export code on a seperate button but it still said read only so im presuming either the delete qry to clear the recordset first or the way i open the recordset is setting it as a problem somewhere
Code:
DoCmd.SetWarnings (False)
'takes the order from wherever - make sure its csv - and then generate a proper PO which can be imported direct into myriad and creates a PO numebr etc.
DoCmd.OpenQuery "qryDELCSVPO"
'this has now emptied what was in b4
MsgBox "Make sure you have saved the order file as csv as well as the usual XL that is sent to the supplier", vbOKOnly, "CSV"
fpath = fncGetFilePath
DoCmd.TransferText acImportDelim, "CSVPO", "tblCSVPO", fpath
'this brings in the new file
DoCmd.OpenQuery "qrydelpotemp"
DoCmd.OpenQuery "qryPOorderqty" 'empty tables and then copy only anything that has a value in the order level above 0
DoCmd.OpenQuery "qryDELpoexport" ' empties the exp[ort table before we record set construct it
' POtemp has the values from the order
' po export will take the values from the combo boxes, the list box, potemp and some default values.
'Me.POsup - which supplier is picked from combo box
'Me.POstore - which store is picked from the listbox
Dim rst5 As DAO.Recordset
Dim rst6 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst5 = db.OpenRecordset("tblPOexport")
Set rst6 = db.OpenRecordset("tblPOtemp")
Dim supln As String
Dim stname As String
RecordsetUpdatable = True
supln = Me.POsup
stname = Me.POstore
Dim prodcost As Integer
Dim prodsell As Integer
Dim dat As String
If rst6.BOF And rst6.EOF Then
MsgBox "empty order", vbCritical
Else
rst6.MoveFirst
Do Until rst6.EOF
dat = Date
prodcost2 = CInt(rst6.Fields(2))
prodsell2 = Round(prodcost2 * 1.68, 0)
'read from the temp and copy into the export file
rst5.AddNew
'insert new record
rst5.Fields(0) = stname
rst5.Fields(1) = dat
rst5.Fields(2) = rst6.Fields(1) ' catnum
rst5.Fields(3) = rst6.Fields(3) ' qty
rst5.Fields(4) = rst6.Fields(2) ' cost
rst5.Fields(5) = Str(prodsell2) ' retail ( must calc this)
rst5.Fields(6) = "ROS"
rst5.Update
rst6.MoveNext
Loop
End If
Dim filen As String
Dim dt As String
Dim dt1 As String
Dim dt2 As String
Dim dt3 As String
Dim fulldate As String
dt = Date
dt1 = Left(dt, 2)
dt3 = Right(dt, 4)
fulldate = dt1 & "" & dt3
filn = "C:\Documents and Settings\backoffice\Desktop\" & "STORE " & stname & " " & supln & " " & fulldate & " POfile"
DoCmd.TransferText acExportDelim, "poexport", "tblPOexport", filn
MsgBox supln & " PO file is on the desktop for Store " & stname, vbOKOnly, "PO created"
' now also create the file to send to the supplier to place the actual order
' also msgbox