Recordset is read only problem Results

I have a Form that when opened runs through a Recordset, using the code below. However, it looks like the Query is uneditable because I get the following error:

cannot update. database or object is read-only 3027

Is it not possible to update records using a Recordset if your Form is based upon a Query?

Dave


	Code:
	    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryAdwordsAzoogledCleaned", dbOpenDynaset)

    rs.MoveLast
    rs.MoveFirst

    Do Until rs.EOF

        rs.Edit

        Select Case Profit

        End Select

        rs.Update
        rs.MoveNext
    Loop

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing



Hello!

I'm using Access 2003 and MyODBC 5.1 driver to connect to a remote MySQL server.

I have to connect from code, because I have timeout issues (which can not be resolved from MyODBC 5.1 driver interface; no more "Initial statement" option).

Anyway after a painful process of getting this to work from code I have a problem that I seem to be opening the tables in read-only mode.

Where is the problem? I want to have read/write acces.

Here's the code:


	Code:
	    Dim wsODBC As Workspace
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim conODBC As Connection
    Dim qdef As QueryDef
    
    Dim baza As String
    Dim user As String
    Dim pass As String
    Dim connect As String
    Dim dsn As String
    
    baza = "baza"
    user = "user"
    pass = "pass"
    dsn = "PD"
    
    connect = "ODBC;" & "DATABASE=" & baza & ";DSN=" & dsn & ";UID=" & user & ";
                  _PWD=" & pass & ";STMT=180"
               
    Set wsODBC = CreateWorkspace("NewWorkspace", "admin", "", dbUseODBC)
    Set db = CurrentDb
       
    ' še s povezavo
    Set conODBC = wsODBC.OpenConnection("con1", dbDriverNoPrompt, False, connect)
       
    Set rs = conODBC.OpenRecordset("SELECT * FROM cscart_users;", dbOpenDynamic)
    
    With rs
        .MoveFirst
        Do Until rs.EOF
            If rs![user_id] = 23 Then
                .Edit
                ![firstname] = "Aljosa"
                .Update
            End If
            .MoveNext
        Loop
    End With
    
    rs.Close
    Set rs = Nothing
    qdef.Close
    Set qdef = Nothing
    db.Close
    Set db = Nothing
    conODBC.Close
    Set conODBC = Nothing
    wsODBC.Close
    Set wsODBC = Nothing

I'm getting Run-time ERROR 3027 - "Cannot update. Database or object is read-only."

Thanks in advance!

Miha

I am currently having some problems. I have moved an Access 97 application from windows NT to windows 2000. I have some mail merge data that are temporarily created by a query, before being dumped to a word document. Now when the query is called, I get the following message "Cannot update. Database or Object is read only"

Below is the code that is run before the mailmerge document is called.


Private Sub PrintLetter(LetterTemplate As String, UsePrinter As String)
Dim db As Database
Dim rstsys As Recordset
Dim rstMerge As Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set rstsys = db.OpenRecordset("tblsys", , dbReadOnly)
rstsys.MoveFirst

' Display status form
glrstatus "Printing Document", "Please Wait"


Set qdf = db.CreateQueryDef("", "DELETE * FROM tblMailItems")
' Execute QueryDef.
qdf.Execute

Set rstMerge = db.OpenRecordset("tblMailItems")
rstMerge.AddNew
rstMerge!Surname = Forms!frmapplypost.Surname
rstMerge!Title = Forms!frmapplypost.Title
rstMerge!Initials = Forms!frmapplypost.Initials
rstMerge!Address1 = Forms!frmapplypost.Address1
rstMerge!Address2 = Forms!frmapplypost.Address2
rstMerge!Address3 = Forms!frmapplypost.Address3
rstMerge!Address4 = Forms!frmapplypost.Address4
rstMerge!Address5 = Forms!frmapplypost.Address5
rstMerge!Address6 = Forms!frmapplypost.Address6
If Not Forms!frmapplypost.PostCode = "Unknown" Then
rstMerge!PostCode = Forms!frmapplypost.PostCode
End If
rstMerge!ApplicantID = Forms!frmapplypost.ApplicantID
rstMerge!PostNumber = Forms!frmapplypost!sbfPostApp.Form!PostNumber
rstMerge!PostTitle = Forms!frmapplypost!sbfPostApp.Form!PostTitle
rstMerge!DateAppReturned = Forms!frmapplypost!sbfPostApp.Form!DateAppReturned
rstMerge!ClosingDate = Forms!frmapplypost!sbfPostApp.Form!ClosingDate
rstMerge!DisclosureText = Forms!frmapplypost!sbfPostApp.Form!DisclosureLevel .Column(2)
rstMerge!Selected = True
rstMerge.UPDATE
rstMerge.Close

'if local report then run else mail merge to word
If Not Me.cbodocument.Column(3) = "" Then
Select Case Left(Me.cbodocument.Column(3), 3)
Case "rpt"
printreport Me.cbodocument.Column(3)
Case "qry"
If Nz(DCount("*", Me.cbodocument.Column(3))) = 0 Then
glrstatus
MsgBox "Nothing to print or details incomplete"
Exit Sub
End If
'export table to rtf format with random file name
ExportMergeData Me.cbodocument.Column(3), rstsys!documentpath, LetterTemplate & ".dot", rstsys!templatepath, 0, UsePrinter, 0, LetterTemplate & Format(Date, "yyyymmdd") & ".doc"
rstsys.Close

Case Else
End Select
Else

'export table to rtf format with random file name
Dim intPrint As Integer
If IsNull(UsePrinter) Or UsePrinter = "" Then
intPrint = 1
Else
intPrint = 2
End If
If Nz(DCount("*", "tblMailItems")) = 0 Then
glrstatus
MsgBox "Nothing to print or details incomplete"
Exit Sub
End If
ExportMergeData "tblMailItems", rstsys!documentpath, LetterTemplate & ".dot", rstsys!templatepath, intPrint, UsePrinter, 0, LetterTemplate & Format(Date, "yyyymmdd") & ".doc"
End If

glrstatus

End Sub

I create a front end and back end that resides on the lan. Front has everything besides the table which resides on the back end. Front ends table are linked to the backend. I have two computer that i am testing it on. Both of them runs Access 2003 runtime and windows has been update to the latest version, both xp machine aswell.Both has user right to read and write to that directory on the lan. Ldr exist when either of them uses access file. The strange thing is when i run a command to copy some data from one table to another one. one of the machine give me the following error "3027 Cannot Update. database or object is read only". While on the other machine it runs flawless. Another weird thing is if i modified some values in table by using forms it works great on both so i am a bit clueless where the problems is. its seems my problem is copying from one table to another. i dont have problem modyfing one table. Here is the code i use to that halt my ms access database.


Dim dbs As Database, rsProposal As Recordset, TES As String, stdocname As String, stLinkCriteria As String

TES = Me![TESID]

If TES = DLookup("TESID", "Proposals", "TESID =" & "'" & TES & "'") Then
MsgBox "Proposal Already Exists for TES ID: " & vbCrLf & _
" " & TES, vbOKOnly, "Proposal Already Exists"
GoTo Image264_Click_Exit
Else
If MsgBox("Do You Really Want to Create" & vbCrLf & "a New Proposal for TES ID " & vbCrLf & " " & TES & " ?", 289, "Create New Proposal?") = vbOK Then
Set dbs = CurrentDb
Set rsProposal = dbs.OpenRecordset("Proposals")
With rsProposal
.AddNew
![Long_Desc] = Me![Description]
![Short_Desc] = Me![Opportunity]
![Dest_Site] = Me![Install Site]
![TESID] = Me![TESID]
![End_User] = Me![Contractor/Purchaser Name]
![Date_Due] = Me![Proposal Due Date]
![Date_Completed] = Me![Close Date]
![Status] = Me![Status]
.Update
.Close
Set rsProposal = Nothing
dbs.Close
Set dbs = Nothing
End With
stLinkCriteria = "[TESID] = " & "'" & TES & "'"
stdocname = "Form Prop - Detail"
DoCmd.OpenForm stdocname, , , stLinkCriteria
DoCmd.Close acForm, "Form TES - Detail"
End If
End If

Hi,
I'm trying remove actual record by this VB code:
Private Sub Command41_Click()
On Error GoTo Err_EditDelBox_Click
Dim rs As Recordset
Dim strBMark As String
Set rs = Forms!Media.Form.RecordsetClone
With rs
Do Until .EOF
.Delete
.MoveNext
Loop
End With
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
Exit_EditDelBox_Click:
DoCmd.SetWarnings True
Exit Sub
Err_EditDelBox_Click:
MsgBox Err.Description
Resume Exit_EditDelBox_Click
End Sub

After click I get error message Cannot update database or object is read-only. And record isn't been removed. Well I founded this script on internet.... I'm really not good in VB.

But problem could be somewhere else. I opened form by macro and I couldn't fill some fields in form. It looks like that form is really locked (but subforms are OK). I just used simple macro "OpenForm" and set only Name of form to be opened.
Could anybody helps?
Thanks

I have a query that by nature is read-only. This data is used to populate a form in datasheet mode.

Somehow I need to be able to edit the entry and update the source table. Any Ideas.

I appreciate the fact that I will need vba code to do the updating. that is no problem.

I have tried to create a disconnected recordset and clone a recordset they all come out readonly.

The main thing is to be able to able to change the entry.

I have also considered using the readonly form as a subform and use the onclick event to update text boxes on the parent form. this will work but it is not the solution I was hoping for

Regards Phill

ps my current code :

Private Sub Form_Open(Cancel As Integer)

'On Error Resume Next

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim query As String

Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & CurrentDb.Name
If err 0 Then
MsgBox "A connection error has occured." & vbNewLine & err.Description, vbCritical
err = 0
Exit Sub
End If
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Properties



query = "SELECT Item_Query.PHANDLE, Item_Query.TEXTSTRING AS ITEM, Descr_Query.TEXTSTRING AS DESCR " & _
"FROM Item_Query INNER JOIN Descr_Query ON Item_Query.PHANDLE=Descr_Query.PHANDLE;"

rst.Open query, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Set Me.Recordset = rst



End Sub

I have the following code:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim rst2 As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("MainRptDataASD Totals")

rst.MoveFirst
.
.
.
If myVal 0 And rst.Fields("PmtYr") < 2005 Then
'Find the next year matching ASD(myCat) and IPR(myIRP)
Set rst2 = db.OpenRecordset("SELECT " & monVal & " from MainRptDataASD where PlanName = '" & myplan & "' AND PmtYr = " & myYear + 1 & " AND ASD = '" & myCat & "' AND IPR = '" & myIPR & "'")
calcVal = (rst2.Fields(monVal) / myVal - 1)
rst.Edit
rst.Fields(totval) = calcVal
rst.Update
Else
rst.Edit
rst.Fields(totval) = 0
rst.Update
End If

When I reach the rst.edit lines I get the error message - database or object is read-only. This is not a read only db.
Any thoughts on how to correct this?
Thanks,
k

Hello,

I have a form that updates a record set. The form works most of the time, but gives this error intermittently:
Microsoft JET Database Engine (0x80040E09)
Cannot update. Database or object is read-only.

The error message points to this line of code:
RS_Select_From_Event("Reception_Guest_1") = getBoxValue("Reception_Guest_1")

A more complete view of the code is viewable below. I'm really not sure how to solve this. This has been going on for days. Any help appreciated!


If Request.ServerVariables("Request_Method") = "POST" Then
set objConn3=Server.CreateObject("ADODB.Connection")
objConn3.Provider="Microsoft.Jet.OLEDB.4.0"
objConn3.Open "E:wwwdbevents.mdb"

Q_Select_From_Event="SELECT * FROM event_registration WHERE Registree_Index=" & Registree_Index
Set RS_Select_From_Event = Server.CreateObject("ADODB.Recordset")
RS_Select_From_Event.open Q_Select_From_Event, objConn3, 3,3

If not RS_Select_From_Event.eof then
Registree_Index_From_Recordset = RS_Select_From_Event("Registree_Index")
else
Registree_Index_From_Recordset = -99999999999
end if


IF Registree_Index_From_Recordset = Registree_Index then

RS_Select_From_Event("Reception_Guest_1") = getBoxValue("Reception_Guest_1")
RS_Select_From_Event.Update

ELSE

response.Write("Record not found.")
response.End()

END IF

RS_Select_From_Event.Close()
objConn3.Close()
Set objConn3 = Nothing

End If

Function getBoxValue(whatBox)
if request.form(whatBox) = "1" then
getBoxValue = 1
else
getBoxValue = 0
end if
End Function

Hello,

I have a commercial application that I have been asked to modify. It is an accdb over sql server 2005. For the most part things have been going pretty well, until...

This is the situation. We have two tables, tblPeople, and tblPeopleTypes. I added a new field to tblPeopleTypes called "ControlGroupYN". This field is a bit in sql server for the backend, and seen as a yes/no in access.

(What I am trying to do is pull across a new field from tblPersonType into the form based on a tblPerson's relationship to tblPeopleTypes (1:1), so that when the new field is Y, do something, and when the new field is N to do something else to the controls on the form... anyway...)

After making some changes to the form's record source to bring across the new field's value, and upon opening the form things are ok. Records are able to be searched for (using the search code included below - which I believe to be the culprit here) and updates are able to be made to the records.

But, when I use the search function a second time, the values in the controls are unable to be modified, and I recieve in the status bar the following msg: "This recordset is not updateable."

Note: I googled that error, and it said that I don't have a pkey, but I checked and I do for both tables - I even dropped and re-linked the two tables just in case esomething got hosed - but no joy...


Ok - the steps I took to get to this problem are this:

I went to the form that controls the tblPeople info and looked for the record source info. In the form's record source field, the original info was: "SELECT * FROM tblPeople WHERE [tblPeople].[Person ID]=-1; "

Ok - give me everything from the people table where there is an id... sounds good...

I changed that to be:


	Code:
	SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople, tblPeopleTypes WHERE [tblPeople].[People
Type]=[tblPeopleTypes].[People Type ID]

That should (and does) value the check box for the additional field I dropped on the form.

Then I found the below in the Form_Open event...


	Code:
	' MGG Dec 01, 2008 - change the form's select to include the bishop control grp field...
'   mstrBaseRS = "SELECT * FROM tblPeople "
   mstrBaseRS = "SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople, tblPeopleTypes WHERE
[tblPeople].[People Type]=[tblPeopleTypes].[People Type ID] AND tblPeople.[Person ID]=-1"

The original line was what I have commented out - the select * from tblpeople. The new line with the where join seemed reasonable based on the changes I want...

That part seems ok...

Then...

There was another area where the mstrBaseRS was being set, in the search function on the form, that will bring back individual records into the view of the form.


	Code:
	Private Sub Find_People_Button_Click()
   'Purpose    :
   'Parameters :
   '    -
   'Created    :
   'Sample Call:
   'Modified   :
   '--------------------------------------------------------
   Dim lngNewPersonId As Long
   Dim strWhereText As String
   Dim rstPeople As Recordset
   Dim rstAddress As Recordset
   On Error GoTo Error_PeopleButtonClick
   lngNewPersonId = InvokePeopleWizard()

   
   ' 1/30/2002 ef Add option to return only one record
   If lngNewPersonId  0 Then
     If gbolFormReturnOne Then
        '#817 05-07
' MGG Dec 01, 2008 - change the form's mstrBaseRS to become the below, always, not just when blank
'        If mstrBaseRS = "" Then
        If mstrBaseRS  "" Then
' MGG Dec 01, 2008 - change the form's select to include the bishop control grp field...
'           mstrBaseRS = "SELECT * FROM tblPeople "
           mstrBaseRS = "SELECT tblPeople.*, [tblPeopleTypes].[ControlGroupYN] FROM tblPeople, tblPeopleTypes WHERE
[tblPeople].[People Type]=[tblPeopleTypes].[People Type ID] "
        End If
        '#817 end
' MGG Dec 01, 2008 - change the form's select to include the bishop control grp field...
'         Me.RecordSource = mstrBaseRS & " Where tblPeople.[Person ID] = " & lngNewPersonId
         Me.RecordSource = mstrBaseRS & " AND tblPeople.[Person ID] = " & lngNewPersonId

     Else
         ' original code
         strWhereText = "[Person Id] = " & str(lngNewPersonId)
         Set rstPeople = Me.RecordsetClone
         rstPeople.FindFirst strWhereText
         If Not rstPeople.NoMatch Then
             Me.Bookmark = rstPeople.Bookmark
         End If
      
      strWhereText = "[Address Id] = " & CStr(glngAddressIdSelect)
      Set rstAddress = Me!PeopleSubform.Form.RecordsetClone
      rstAddress.FindFirst strWhereText
      If Not rstAddress.NoMatch Then
         Me!PeopleSubform.Form.Bookmark = rstAddress.Bookmark
         Me!PeopleSubform.Form.Refresh
      End If
      Me.Refresh
    End If
    ' ef end of mods
  
   
   
   End If
   Exit Sub
Error_PeopleButtonClick:
   ReportError
   Exit Sub
End Sub

Similarly, I commented out the original lines, and added the new query for the record source as being set in the mstrBaseRS. The first change was - well, to be honest - to ensure that this was happening - I couldn't understand why mstrBaseRS would be blank, so I countered that... the next two lines were to garner all the data needed for the forms data controls, and the latter to keep the " And " statements inline with the new query...


So...

Again, in short. The form opens and runs ok. Hitting the search button calls up a search form that returns a single record works ok. Updating that returned record works ok... BUT when I re-run the search function pased in above the record that is returned is returned in efectively a read-only state.

Thoughts?

Thanks for reading this far...

-Matt G.

Everything works fine on my harddrive, but when I put it on my company server, problems arose. I'm using Access 2010. It asks two things on the top when I open it, "Enable Content" and "This database has been opened read-only. You can only make changes in linked tables. To make design changes, Save a copy." I always Enable Content and I don't want to make design changes, I'm just trying to input a date, project, and hours. The combobox works (I guess because it's linked to a table), but how would I get the others to work. I'm using an update query to put the data into my main table, then run reports off it. Any ideas?

Hi there !
I have a problem with my Access Project...

This Access project (.adp) is connected to a SQL Server database, with Windows NT Integrated security login...
I can see all my tables, views and store proc. of my SQL Server database, but i can't make any Insert, update or delete operation...

Can someone help me ?

Ho yeah, and i also give to my user the permision to select, insert, update and delete on my tables... (in SQL Server)

I still don't understand why the recordset of my tables are in read-only mode in access

I have a database that is split into front and back ends. I have 35-50 users with read/file scan rights only set by our MIS Network Administrator. My department has read/write rights to the database. I am the only person that uses the database though. My problem is that when certain people (and maybe any or all of the people) are in the database, I am not able to update any records. I get "This recordset is not updateable." I have checked under Tools-Options-Advanced and the Open Mode is Shared, the Record Locking is NO Locks. I have checked my front end and others' front ends in this area. I have also checked the back end. There is no security set with the database. I'm at a loss. Please HELP.

Hi all

Consider the following:
I have orders from an order table, a table for delivered items (as customer might not get wat is ordered due to various reasons), a client table and an item table.

My current problem revolves around the packers form, to enter what is delivered in a simple manner. The main form shows a customer and the sub form shows the delivery form. This delivery form shows fields for one customer, and the rows are "per item":
- Item name
- sum Ordered
- packed (unbound)
- delivered (checkbox)
- actual delivered
- Diff (between "sum ordered" and "actual delivered")

My first limitation is that the order numbers are obtained by crosstabbing and various unions and joins, so the rows are not directly updatable. The workflow I vision is:
1. The "packed" field should have a default value of "sum ordered"
2. User verifies or changes the packed field to the number of items being packed
3. User then clicks the "delivered" checkbox and the system inserts a row in the delivered table, refreshes the forrm and the "actual delivered" should be filled in as well as the "delivered" checkbox checked
4. At some point user wants to change delivered item and updates the "packed" field and the system changes the "delivered" checkbox to unchecked)
5 user verifies change of number and checks the "delivered" checkbox and the system updates the delivered record and does a refresh

At first I tried to have the "packed" constrol source to "iif(isNull([actual_delivered]);[sum_ordered];[actual_delivered]". This expression works, but when I try to change the value is says "form is read-only" (when I try to change some other field it says "Th recordset is not updateable"). I then took away the expression and it was updateable, but as soon as the field is updated, all the rows gets the same value.

I thught about setting the "packed" value in an event, but it seems as long as it is unbound, all rows will have the same value (tried on curent). To me it feels like a chicken and egg situation, which usually means something is not done right. I also believe that I can "fake" the checking of the checkbox by using a "mouse down" or "key down" to catch the user's intention and then update the underlaying values and do a refresh. It doesn't feel right, but that's the direction this is taking because of the uneditableness.

Has anybody got an idea of how to mix non-updateable and updatable fields in the same subform and have seperate values in an unboud field? or a bettwe way of doing this?

Thaks

I have the following code in a database but get an 3027 error when I run it.
Everything else works except the bit I underlined
I am trying to write strPreviousScore to the Score field for the current recordset
Being a novice I am stuck on this problem and any help will be appreciated and restore my sanity

EDIT: I changed the code to Set rst = dbs.OpenRecordset(strSQL) and now get a 3020 Error
Now I am more confused, I figured that the 'type' setting was wrong but do not know which option to choose and rst!Score= strPreviousScore bit still does not work

cheers
Wayne


CODE:

Sub subInsertScores(strSQL As String, strMaxScore As String)
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

'**Find the number of records.
rst.MoveLast ' End of the recordset
strRecordCount = rst.RecordCount ' Number of records

strPreviousScore = 0
strPreviousTime = 0

'loop through each record and add score
rst.MoveFirst
For A = 0 To (strRecordCount - 1)
strCurrentPoints = rst![Total Points]
strCurrentTime = rst![TotalTime]
strCurrentScore = rst![Score]


If strCurrentPoints = strPreviousPoints Then rst![Score] = strPreviousScore

'**set currents to previous here before moving to next record
strPreviousPoints = rst![Total Points]
strPreviousScore = rst![Score]
strPreviousTime = rst![TotalTime]
rst.MoveNext
Next

End Sub

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


	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 SettingsbackofficeDesktop" & "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



Good Morning All
I am trying to move forward with some new features to an app I am building for our asset management system.

I have a textbox field on a form that gets focus on load and the idea is for a user to scan a barcode (device serial) into the box.

On clicking the search button next to the field, a recordset is open and the device serial is searched for.

As of right now, the problem I have is that the event is only fired when the user clicks the go button (naturally).
Is there a way as anyone would know to get the search event to fire up after the scan is complete (something similar to sendkeys) but the event is to fire as soon as the scan is complete and the last character from the barcode is captured in the textbox.

Please let me know if you need anything clarified..
Thanks in advance for your help.

I am maintaining a database using SharePoint, with an Access frontend, custom SP views and a backend Access for maintenance. Two fields of type Choice allow the user to list software load using checkboxes. Separated into (Win7x64)Compatible and Incompatible, they help determine if we can refresh/upgrade a user now, or wait for app issues to be fixed. I allowed the multi-value fields to happen because the techs wanted to leverage SharePoint more directly, and wanted data to be contained in a single list. Being new to SharePoint, I didn't see the trouble.

The big problem I run into is I cannot find a method to 'uncheck' values automatically in Access. This works if the lookup is in a local Access table, but not in SharePoint. I can query on [software.value] and get a breakout by Title, but then I have to go into the record, open the field's combobox in the datasheet and deselect the title. I see no means for Access to go through and uncheck those fields individually.

Simply put, while I can run a query on [Computername] and [software.value] and get a separate row for each Computername-Title, I cannot then select and delete the individual row. Access complains about the resultset is read-only (as opposed to 'recordset not updatable', which I know why that happens). Again, this is in contrast to Access' ability to delete values lieka regular row when local.

Is there a sort of 'Value.Deselect' method I can't find? If there a clear reference out there, lead me to it. There's got to be a volume on interacting with SharePoint in Access VBA that I'm not finding. There are a lot of places that cover the basics that don't cover this topic.

I'm using SP2007, Access2007 for the user app, but maintaining using Access 2010. We're moving to SP2010 by EOY and Off2010 by Q1.


Scott

Sorry for the rookie question. After years of using Access for data analysis this is my first serious application. Been doing a lot of learning as I go.

Good day,

I'm hoping someone can help me here, I two sets of code which are almost identicle, one works fine but I can't work out why the other one doesn't. The code structure for both sets of code are the same with some differences with regards to where they get their information from and where they put and rename the images, please see below

	Code:
	Function SkyQuesImages()
On Error Resume Next
'-------------------------------------------
'References:
'Visual Basic For Applications
'Microsoft Access 9.0 Object Library
'OLE Automation
'Microsoft ActiveX Data Objects 2.1 Library
'Microsoft Scripting Runtime
'Microsoft DAO 3.6 Object Library
'-------------------------------------------
    Dim DB As Database                                                      
    Dim File As File                                                        
    Dim FileLoc As String                                                   
    Dim Folder As Folder                                                    
    Dim FormCode As String                                                  
    Dim FS As FileSystemObject                                              
    Dim ImagePath As String                                                 
    Dim NameOfFile                                                          
    Dim Agent As String                                                     
    Dim SchemeCode As String                                                
    Dim PlanNumber As String                                                
    Dim SchRef As String                                                    
    Dim rst As DAO.Recordset                                                
    Dim SkyTable                                                            
    Dim strTemp As String                                                   
    Dim TifFilePath As String                                               
    Dim TopFolder As String                                                 

    Set DB = CurrentDb                                                      
    Set FS = CreateObject("Scripting.FileSystemObject")                     
    Set rst = DB.OpenRecordset("tblSky")                                    

    SkyTable = DCount("*", "tblSky")                                        

    If SkyTable > 0 Then                                                    
        Do While Not rst.EOF                                                
            If Not rst.EOF Then                                             
                FormCode = rst![strFormType]                                
                Agent = rst![strAgentNo]                                    
                SchemeCode = rst![strSchemeCode]                            
                PlanNumber = rst![strPlanNumber]                            
                SchRef = Agent & "_" & SchemeCode & PlanNumber              
                ImagePath = rst![strImagePath]                              
                TopFolder = Mid(ImagePath, 4, 25)                           
                TifFilePath = "J:" & TopFolder                             

                If Not FS.FolderExists(TifFilePath) Then                    
                    MsgBox "Folder Doesn't Exist", , "Reading Tif Files"    
                    End                                                     
                End If

                Set Folder = FS.GetFolder(TifFilePath)                      
                For Each File In Folder.Files                               
                    NameOfFile = GetAttr("File.Name")                       
                    strTemp = Mid$(File.Name, InStrRev(File.Name, "") + 1) 
                    FileLoc = Folder & "" & File.Name                      
                    If FileLoc = ImagePath Then                             
                        FS.CopyFile Folder & "" & strTemp, "M:Customer SatisfactionStdDGImages" & "" & FormCode & "" & SchRef
& ".tif"     
                    End If

                    DoEvents                                                
                    DoCmd.Echo True, "Copying File to New Loc: " & Folder & "" & strTemp & "To: " & "M:Customer
SatisfactionStdDGImages" & "" & FormCode & "" & SchRef & ".tif"    
                Next                                                        
            End If
            rst.MoveNext                                                    
        Loop
    End If

    SkyTable = 0                                                            
    rst.Close                                                               
    Set rst = Nothing                                                       
    Set DB = Nothing                                                        

    DoCmd.Echo True, "Standard DG 2012 Images Organising Process Complete"

End Function

The above code works fine every single time



	Code:
	Function BiasiImages()
On Error Resume Next

'-------------------------------------------
'References:

'Visual Basic For Applications
'Mircrosoft Access 9.0 Object Library
'OLE Automation
'Microsoft ActiveX Data Objects 2.1 Library
'Microsoft Scripting Runtime
'Microsoft DAO 3.6 Object Library
'-------------------------------------------

Dim rst As DAO.Recordset
Dim DB As Database
Dim FS As FileSystemObject
Dim Folder As Folder
Dim File As File
Dim TifFilePath As String
Dim NameOfFile As String
Dim strTemp As String
Dim FileLoc As String
Dim TopFolder As String
Dim Title As String
Dim Initials As String
Dim Surname As String
Dim Customer As String
Dim VerifiedDate As String
Dim ImageLoc As String
Dim Biasi70854Query As String

Set DB = CurrentDb
Set FS = CreateObject("Scripting.FileSystemObject")
Set rst = DB.OpenRecordset("qrytblBiasi70854Process_01Images")

Biasi70854Query = DCount("*", "qrytblBiasi70854Process_01Images")

If Biasi70854Query > 0 Then
    Do While Not rst.EOF
        If Not rst.EOF Then
            Title = rst![strTitle]
            Initials = rst![strInitials]
            Surname = rst![strSurname]
            VerifiedDate = rst![CurDate]
            Customer = Title & " " & Initials & " " & Surname & "_" & VerifiedDate
            ImageLoc = rst![ImagePath]

            TopFolder = Mid(ImageLoc, 4, 27)
            TifFilePath = "J:" & TopFolder

            If Not FS.FolderExists(TifFilePath) Then
                MsgBox "Folder Doesn't Exist", , "Reading Tif Files"
                End
            End If

            Set Folder = FS.GetFolder(TifFilePath)

            For Each File In Folder.Files
                NameOfFile = GetAttr("File.Name")
                strTemp = Mid$(File.Name, InStrRev(File.Name, "") + 1)
                FileLoc = Folder & "" & File.Name

                If FileLoc = ImageLoc Then
                    FS.CopyFile Folder & "" & strTemp, "H:BiasiImages" & "" & Customer & ".tif"
                End If
                DoEvents
                DoCmd.Echo True, "Copying File to New Loc: " & Folder & "" & strTemp & "To :" & "H:BaisiImages" & "" &
Customer & ".tif"
            Next
        End If
    Loop
End If

BiasiQuery = 0
rst.Close
Set rst = Nothing
Set DB = Nothing

DoCmd.Echo True, "Biais 70854 Process Complete"

End Function

The line in red is where this code keeps falling over, it's saying that it can't find the tif files although the files do exist, I've checked and double checked, I just stuck on trying to work out why this is, the only differences between this code structure and the first one above are shown in blue in this bit of code, so my thoughts are that this should work just like the other code, but not so far.

Hopefully someone can spot what may be wrong with the second code block.

Assistance/pointers would be most gratefully accepted.

Regards

John

What code would open a record set based on a linked table and take a snapshot of the table. Then export the table contents to another table? I think it may help with the following problem.

I currently use this code which works fine, but the if I run a query based on the final table the data is exported to, I get 'Lock errors' because the table is being written to. This is a very big problem that I need to overcome this week. Any help from you guys would be very kind.

START CODE

Option Compare Database
Function ExportBlocks()


Dim rst As RecordSet
Dim newtable As RecordSet
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.openrecordset("BLOCKS")
Set newtable = db.openrecordset("ACCESS_BLOCKS")

' This will delete all records from the 'ACCESS_BLOCKS' table.
db.Execute "DELETE * FROM ACCESS_BLOCKS;"

If Not rst.EOF Then
rst.MoveFirst
Do
With newtable
.AddNew
!CRANE = rst!CRANE
!COL = rst!COL
!Row = rst!Row
!SIDE = rst!SIDE
!CELLSTATUS = rst!CELLSTATUS
!LOADNO = rst!LOADNO
!PARTNO = rst!PARTNO
!VENDOR = rst!VENDOR
!PACKAGING = rst!PACKAGING
!CONSOL = rst!CONSOL
!DERIVNO = rst!DERIVNO
!QTY_STORED = rst!QTY_STORED
!CONDITION = rst!CONDITION
!PRODSTATUS = rst!PRODSTATUS
!ISW = rst!ISW
!SEQNO = rst!SEQNO
!STIME = rst!STIME
!MIRROR = rst!MIRROR
!ORIGIN = rst!ORIGIN
!TELENO = rst!TELENO
!RTIME = rst!RTIME
!PRIORITY = rst!PRIORITY
!DEST = rst!DEST
!Source = rst!Source
!NOTES1 = rst!NOTES2
!NOTES2 = rst!NOTES2
!QTY_RETRV = rst!QTY_RETRV
!BATCHID = rst!BATCHID

.Update
End With
rst.MoveNext
Loop Until rst.EOF
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:projectsj1427accessSSCSMirrorDataBlk.md b", acTable, "ACCESS_BLOCKS", "BLOCKS", False
End If


End Function




TAC

I am trying to output a set of two files for faxing into Lightning Fax software. The first one (report) is coming out just fine. However, the second one (fax data header) is giving me fits.

Two problems:

(1) On its first pass, it creates the record in the "Header_Master" table OK but when it tries to write the .txt file, it results in a blank file. If I leave the record in the "Header_Master" table and run it again, it creates the .txt file OK, but now I have two identical records in "Header_Master" (my idea is to delete that temorary record after a fax is sent each time, but I haven't gotten to that yet).

(2) I'm getting commas where I don't want them. There has to be a way of outputting this text to a .txt file with only vbCrLf's but I can't figure out how to do it.

Here is the vb code, and how the data is SUPPOSED to look is down below:

Dim stDocName As String

stDocName = "Fax Macro"
DoCmd.RunMacro stDocName

' Setup Recordset for Report_Counter
Dim cn1 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim f1 As ADODB.Field

' Setup Recordset for Header_Counter
Dim cn2 As New ADODB.Connection
Dim rs2 As New ADODB.Recordset
Dim f2 As ADODB.Field

' Setup Recordset for Fax_Master
Dim cn3 As New ADODB.Connection
Dim rs3 As New ADODB.Recordset
Dim f3 As ADODB.Field

' Setup Recordset for Header_Master
Dim cn4 As New ADODB.Connection
Dim rs4 As New ADODB.Recordset
Dim f4 As ADODB.Field

' Open the connection for Report_Counter
cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:/FAX/FAX.mdb;"

' Open the connection for Header_Counter
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:/FAX/FAX.mdb;"

' Open the connection for Fax_Master
cn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:/FAX/FAX.mdb;"

' Open the connection for Header_Master
cn4.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:/FAX/FAX.mdb;"

' Open the forward-only,
' read-only recordset for Report_Counter
rs1.Open "SELECT * FROM Report_Counter", cn1, adOpenForwardOnly, adLockReadOnly
rs1.MoveFirst

' Open the forward-only,
' read-only recordset for Header_Counter
rs2.Open "SELECT * FROM Header_Counter", cn2, adOpenForwardOnly, adLockReadOnly
rs2.MoveFirst

' Open the recordset for Fax_Master
rs3.Open "Fax_Master", cn3, adOpenKeyset, adLockOptimistic

' Open the recordset for Header_Master
rs4.Open "Header_Master", cn4, adOpenKeyset, adLockOptimistic

' Write the report out to the .doc fax file in .rtf format
Dim stReportFileName As String
stReportFileName = "C:/Scott/Fax" & rs1.Fields("NextReportNo").Value & ".doc"
DoCmd.OutputTo acReport, "Fax Report", "RichTextFormat(*.rtf)", stReportFileName

' Find the first Fax_Master record whose Clear (Flag) is null
rs3.Find "Clear=null"

' Add a new Header_Master record
rs4.AddNew
rs4!UserID = "@USER_ID Criminal" & vbCrLf
rs4!UserPassword = "@USER_PASSWORD 1234567" & vbCrLf
rs4!FaxNumber = "@FAX_NUMBER " & rs3.Fields("VendorFaxNumber").Value & vbCrLf
rs4!ConversionFile = "@CONVERSION_FILE " & "C:/Scott/Fax" & rs1.Fields("NextReportNo").Value & ".doc" & vbCrLf
rs4!ConversionMode = "@CONVERSION_MODE LFEngine" & vbCrLf
rs4!Company = "@COMPANY " & rs3.Fields("VendorName").Value & vbCrLf
rs4!End = "@END" & vbCrLf
rs4.Update

' Write the header out to the .txt fax file
Dim stHeaderFileName As String
stHeaderFileName = "C:/Scott/Fax" & rs2.Fields("NextHeaderNo").Value & ".txt"
DoCmd.TransferText acExportDelim, , "Header_Master", stHeaderFileName

' Dim stHeaderFileName As String
' stHeaderFileName = "C:/Scott/Fax" & rs2.Fields("NextHeaderNo").Value & ".txt"
' DoCmd.OutputTo acReport, "Fax Header", acFormatTXT, stHeaderFileName

' Delete Header_Master
' DoCmd.OpenQuery "Delete Header_Master"

' Close the recordsets
rs1.Close
rs2.Close
rs3.Close
rs4.Close

++++++++++++++
This is how the fax header is supposed to look:

@USER_ID Criminal
@USER_PASSWORD 1234567
@FAX_NUMBER 111-222-3333
@CONVERSION_FILE C:/Scott/Fax1.doc
@CONVERSION_MODE LFEngine
@COMPANY AAA
@END

+++++++++++++++

Can anybody help me figure this out? Thanks!

Scott


Not finding an answer? Try a Google search.