Search results

  1. E

    When Update Query update No rows in a field (Because Condition not met) Error Message not Appear

    Use the code from @The_Doc_Man, with the small correction mentioned. Dim CurDB As DAO.Database ' => this is a DAO object DoCmd => this is an Access object Two objects that know nothing about each other. If you let one object work, the other one doesn't know what's happening. So DoCmd.OpenQuery...
  2. E

    When Update Query update No rows in a field (Because Condition not met) Error Message not Appear

    RecAff = CurDB.RecordsAffected A value other than 0 can only be generated here if the database object (CurrDb) is also used in the query. Using OpenQuery is something completely different and particularly counterproductive here. Not just copy a little bit, copy everything. Small correction...
  3. E

    Update multiple filtered invoices as "paid" and add payment notes without opening each invoice individually

    - This would also be the same for recordset.edit - Saving edits beforehand is now no longer a challenge.
  4. E

    Update multiple filtered invoices as "paid" and add payment notes without opening each invoice individually

    Dim sSQL As String sSQL = "UPDATE YourTable SET PaidField = True, PaymentNote = 'hello'" & _ " WHERE " & Me.subFormNeme.Form.Filter CurrentDb.Execute sSQL, dbFailOnError The first question that comes to mind is always a query.
  5. E

    Mail merge query with multiple records

    @AlliCarr Thinking about a modified mail merge and how to fill it out only makes sense if you have an idea of what it should look like in the end. It's nice when @Gasman shows something visual. But something like that should actually come from you, since it's about your task. I derive methods...
  6. E

    Use form filter text in SQL

    I said that if you make things easy at the beginning, you'll pay more later with more effort. I would of course offer my own interface for selecting filters and could therefore evaluate them because I know them. If you want to use the built-in helpers in Access, you have to know their name and...
  7. E

    Use form filter text in SQL

    But that doesn't sound simple or efficient when it comes to updating something. I would replace the hard-coded values with parameters in a statement as shown, the parameters would be taken directly from the ComboBoxes, and a clear query would be ready.
  8. E

    Use form filter text in SQL

    UPDATE MainTable SET FieldX = 123 WHERE ForeignID1 IN ( SELECT ID FROM LookupTable1 WHERE FieldY = "hello" ) AND ForeignID2 = ( SELECT ID FROM LookupTable2...
  9. E

    Use form filter text in SQL

    First of all, a filter is SQL, namely the content of a WHERE clause without the keyword WHERE. So nothing needs to be converted. For a better overview, you can remove the many unnecessary brackets. Therefore, the developer will not rely on superficial help from Access to put something together...
  10. E

    Convert the query contents

    If I use it, then yes. TRANSFORM MIN(X.SHID) AS xy SELECT X.m_box_id FROM ( SELECT T.m_box_id, T.SHID, "F" & DCOUNT("*", "tblNaobao", "m_box_id = '" & T.m_box_id & "' AND SHID < '" & T.SHID & "'") + 1 AS PivotColumn FROM...
  11. E

    Convert the query contents

    Or use a cross tab query. This allows you to convert rows into columns.
  12. E

    Mail merge query with multiple records

    Can you use a cross tab query directly as a source for a mail merge? Probably not. But you could wrap this query in a select query. Hopefully the PIVOT columns have consistent names. For higher demands, as already mentioned, automation would be used. - You use a Word template with bookmarks for...
  13. E

    Optimizing complex queries and reports - Emmanuel Katto

    Before using temporary tables, you should first look at the queries. A blanket statement "complex" is meaningless. For some, complexity occurs when more than two tables are linked or the total length of the SQL statement exceeds 35 characters.
  14. E

    VBA RecordSet if EOF

    About the technology: with rs .movefirst if .EOF = False Then The order is wrong. If the recordset is empty, a MoveLast/MoveFirst will generate an error.
  15. E

    Splitting text box text into sentences

    Insert code into a standard module and start with the F5 key. The output ends up in the immediate window (Ctrl+G).
  16. E

    Splitting text box text into sentences

    Sub test_datatypes() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset( _ "SELECT AnyText AS F1, Replace(AnyText, '.', '.' & Chr(13) & Chr(10)) AS F2," & _ " Left(Replace(AnyText, '.', '.' & Chr(13) & Chr(10)), 65335) As F3 FROM YourTable") With rs...
  17. E

    ENGLAND

    The Germans say the same thing, and they had Spain on the verge of defeat. Unfortunately, they met Spain in the quarter-finals.
  18. E

    ENGLAND

    Considering the beauty of the game and the performances throughout the EURO2024 tournament, football is at home in Spain.
  19. E

    Format

    The field properties in the design view of a query are only for external viewing, but play no role in a table creation query and are not taken into account. Only what is actually in the SQL statement is taken into account. You don't need formats in tables either, they only distort the actual...
  20. E

    .execute then .openrecordset

    I see two things: - Filtering on a DateTime value can quickly go wrong because of the floating point problem for the time values. if not rs.EOF then lockedID = rs.fields("ID").value else ' look for big surprise end if - An update query does not create a new ID, but rather it is...
Back
Top Bottom