6 Replies - 875 Views - Last Post: 25 May 2016 - 12:28 PM

#1 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

VBA Text to .fields("")

Posted 25 May 2016 - 06:42 AM

Hello Guys,

I have a problem with the recordset.
I want to transfer the content from strMsg to .fields("Bemerkung") this field is in table "Mieter".
So when i use this code:
with rs 
.fields("Bemerkung") = strMsg
.Update


I get an error with this message: Database or object is read-only.
What can i do?

Here is the complete listing:
Option Compare Database
Option Explicit

Sub Unser_Champion()
'Error_Handler
    On Error GoTo Err_Handler
'Deklaration
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strMsg As String

'Erstellen der Objektvariablen
    Set db = CurrentDb()
    strSQL = "SELECT TOP 1 Belegung.MieterNr, Vorname, Name, Ort, Count(Belegung.MieterNr) AS AnzahlDerBuchungen, Sum(Mietpreis) AS Mieteinnahmen, Bemerkung " & _
                "FROM Belegung, Mieter " & _
                "WHERE Belegung.MieterNr = Mieter.MieterNr " & _
                "GROUP BY Belegung.MieterNr, Vorname, Name, Ort, Bemerkung " & _
                "ORDER BY Sum(Mietpreis) DESC;"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
'Fehlerabfangroutine
    If rs.RecordCount = 0 Then Exit Sub

'Meldungsfenster
    strMsg = "Bester Mieter!" & vbCr & "Mieteinnahmen: " & Format(rs("Mieteinnahmen"), "currency") & vbCr & _
    "Anzahl der Buchungen: " & rs("AnzahlDerBuchungen")
    MsgBox strMsg, vbInformation + vbOKOnly, "Unser Champion: " & rs("Vorname") & " " & rs("Name") & " " & "aus: " & rs("Ort")

'Übertragen der Daten aus der Meldung an Feld Bemerkung
    With rs
        .Fields("Bemerkung") = strMsg
        .Update
    End With
'Schließen des Recordset und entfernen der Daten aus dem Speicher
    rs.Close
    Set db = Nothing
    Set rs = Nothing
    Exit Sub
'Sprungmarke Err_Handler
Err_Handler:
    MsgBox Err.Number & vbCr & _
    Err.Description
End Sub



I hope someone can help me

Is This A Good Question/Topic? 0
  • +

Replies To: VBA Text to .fields("")

#2 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: VBA Text to .fields("")

Posted 25 May 2016 - 11:02 AM

You are using a GROUP BY query, it is not updateable. You would need to use a simpler non-GROUP BY query for it to be updateable, or perform separate UPDATE statements against the database.
Was This Post Helpful? 1
  • +
  • -

#3 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: VBA Text to .fields("")

Posted 25 May 2016 - 11:48 AM

Ah, i think i know what my next step is. Thanks for the help :)
Was This Post Helpful? 0
  • +
  • -

#4 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: VBA Text to .fields("")

Posted 25 May 2016 - 12:00 PM

IT Works, but one last problem. When field "Bemerkung" has content, how can i attach my new content?
strSQL_2 = "SELECT Bemerkung FROM Mieter WHERE MieterNr = " & rs.Fields(0)
    Set rs_2 = db.OpenRecordset(strSQL_2, dbOpenDynaset)
    With rs_2
        .Edit 
        .Fields("Bemerkung") = strMsg
        .Update
    End With


Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: VBA Text to .fields("")

Posted 25 May 2016 - 12:17 PM

You should have covered string concatenation before reaching this stage, it is an essential foundation topic for all programming languages.

Concatenation Operators in Visual Basic
Was This Post Helpful? 1
  • +
  • -

#6 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: VBA Text to .fields("")

Posted 25 May 2016 - 12:25 PM

HaHaHa, that was embarrassing. Now is all working :)
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Re: VBA Text to .fields("")

Posted 25 May 2016 - 12:28 PM

For anyone reading I assume you did this:
.Fields("Bemerkung") = .Fields("Bemerkung") & " " & strMsg

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1