Search results

Well spotted on the out of scope call to .RollbackTrans 😬 There isn't much point to using a transaction for a single query (all queries are implicit transactions that are committed on successful completion) - your artificial error seems a bit contrived, but I suppose valid if you perform UI...

Pseudo example Function DoTransaction() As Boolean On Error GoTo ErrHandler Dim ret As Boolean Dim blInTrans As Boolean Dim strSQL As String With CurrentProject.Connection .BeginTrans blInTrans = True strSQL = "INSERT INTO ..." .Execute strSQL, , adCmdText +...

Or if you are passing multi statements to an engine that can handle them.

Using your CreateSQL function per @The_Doc_Man's Post #3 will work if you strip off the trailing semi-colon: sql = "INSERT INTO tblPKs (PK, UserFK) " sql = sql & "SELECT InquiryPK, 1 " sql = sql & "FROM ( " & Replace(CreateSql, ";", "") & " ) " sql = sql & "WHERE InquiredBy=1 AND...

Don't use HAVING if the criteria isn't dependent on an aggregation. Use WHERE to filter before the aggregation (COUNT) is applied.

It goes to the list on the second click - just click once, Pat, don't double-click the bell. (To be more clear, if you click the bell when the list is already visible, then you will go to the list.)

@plog, iif should be If in Post #7 code

Seems to have changed since Acc2007:

It will be slow if you try and scroll directly to the last record because then it needs to load all the records. I agree that you should only load the records you need using a filter. I was just trying to show that it is not true to say that it will load all the records on opening (unless you...

@KitaYama See this post

Actually this is not the case. If you do a trace on what is received by the SQLServer you will see that Access is clever enough to request first just the records that will be displayed, then continue to fetch the rest

Sometimes it can make more sense to open a recordset to add new records in code rather than using a query. So then it's better not to pull any records down the pipe that you aren't going to use. eg With CurrentDb.OpenRecordset("SELECT fld1, fld2, fld3 FROM YourTable WHERE 1=0;") .AddNew...

Try it, and see how many records are returned! (Easiest way to open a recordset from a populated table but without any records in it.) You can also use: SELECT * FROM YourTable WHERE FALSE ;

Can you attach directly? Otherwise you will need 10 more posts before you can post a link! (You now need to have 100 before you can post a link)

Click the button on the reply editor that looks like: </>

Check this part: ' ... ' Filtro per casella di controllo If Me.chkCatEmmeti = True Then strfiltro = strfiltro & "CatEmmeti = True " End If ' ... Adjust to: ' ... ' Filtro per casella di controllo If Me.chkCatEmmeti = True Then strfiltro = strfiltro & "CatEmmeti = True AND " ' <--...

I think this might be the problem: ' ... ' Apply the filter to the subform Me.Filter = filter Me.FilterOn = (filter <> "") ' ... Surely it should be: ' ... ' Apply the filter to the subform Me.smProdotti_Attrezzature_Atetica.Form.Filter = filter...

Are you doing the Debug.Print before you chop off the trailing 'AND'? (All the strings you posted still have 'AND' at the end)

Try changing to: Dim strfilter As String and change all instances of the variable filter to strfilter It's unwise to use a reserved word as a variable name.

sQry = "DELETE FROM Discrepancies as A " & _ "WHERE A.Main_Jpg = A.Accession " & _ " AND A.Main_Raw = A.Accession " & _ " AND A.Main_Family = A.Raw_Family " & _ " AND A.Main_Family = A.Spec_Family " & _ " AND A.Main_Box = A.Raw_Box " & _...