Help - really stuck - still!!

[potts]

I've posted this one a couple of times, but have yet to get it answered!!

I'm trying to run an update query with an on_click event in a form. I've got the query running alright - that's not a big problem - but I want the query to only update records I select in a multi-select list box.

As I'm relatively new to databases, I don't really understand much of the code and my usual copy and alter approach has failed me on this one.

Any help would be much appreciated as I'm fast running out of time to have this done - the workload is piling up, and I've another one to build.

I've managed to filter records to open other forms, but using the same code with the DoCmd.OpenQuery "UpdateQuery" command has been unsuccessful.

does anyone else suffer this?

 

[Rich]

You have to use something like this
Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
Dim MyDB As DATABASE
Dim qdf As QueryDef
Dim I As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()
Select Case Me.optVal
Case Is = 1
strSQL = "INSERT INTO tblMail ( fldLtrPK, CustID )SELECT tblLetters.fldLtrPK, Addresses.CustID FROM Addresses, tblLetters "




'create the IN string by looping thru the listbox
For I = 0 To List0.ListCount - 1

If List0.Selected(I) Then
If List0.Column(0, I) = "All" Then
flgAll = True
End If
strIN = strIN & Chr(34) & List0.Column(2, I) & Chr(34) & ","
End If
Next I

'create the WHERE string, stripping off the last comma of the IN string
strWhere = "WHERE [LastName] In (" & left((strIN), Len(strIN) - 1) & " ) And ([tblLetters].[fldLtrPK] In(" & Me![Text7] & "))"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

DBEngine(0)(0).Execute strSQL, dbFailOnError

 

[pono1]

Potts, there is another way, though not as neat and smart as Rich's code, relying on the RunSQL command to fire an update query.

Like this: DoCmd.RunSQL "SQL Code goes here"

Good thing about RunSQL, you don't need to be a SQL expert to make it work.

Let me know if you'd like to see an example.

Regards,
Tim

 

[potts]

An example would be great - I don't understand Rich's example; which fields/tables are referred to where.

I'm lost in the world of computer talk!!!!

 

[potts]

OK, I've done what you said, but am getting a run-time error '3114':

Syntax error in UPDATE statement

and when I go to debug it opens up on the line

DoCmd.RunSQL strSQL

Any ideas??

 

[pono1]

Are any of your fields in the query mistakenly (or otherwise) formatted as memo, OLE, or hyperlink?

If so, there are at least two approaches: change the formatting or see the following for the fix.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q303838

Additionally, I'd be happy to e-mail you my sample, if you like.

Regards,
Tim

 

[cogent1]

sorry to butt in, but...

May I suggest that there's probably an error in your SQL string?In general, the string doesn't need all the brackets as it does in the QBE grid, or the final semicolon. They don't prevent the query from working but they just confuse things.

Before the RUN SQL statement, insert a

Debug.print strSQL

statement. Set a breakpoint on the RUNSQL line and open the immediate window. When the code breaks, you will see the SQL statement in the IW, and can see if there are any missing spaces or misplaced quotation marks. if you're not sure, post the statement so we can inspect it.

 

[potts]

Hmmm, still not working.

I've combed through everything in your example and mine. They're identical - yours works, mine doesn't. don't quite understand it.

Anyhoo, in testing the two, I did notice that your example only updates one record at a time. I need to be able to update many records at the same time, i.e. the multi-select needs to be "extended".

Can you make it work like this??

 

[pono1]

Once I spent a day and a half trying to get a relatively simple formula to work. In the process I scolded my wife, kicked the damned dog and threatened the kids (clearly, it was their fault). I think Cogent1 is probably correct: there's a transposed, extra, or missing character in your SQL statement -- or maybe one of your controls is just one letter off.

At this point, if you're open to a suggestion, why not forget the non-working copy and put the working copy into a folder, referring to it as you move forward with your program?

Regarding multiple selections, the current sample will work with a multi-select list box, whether simple or extended. While the code behind the form runs an update query multiple times, as far as the user is concerned, it's a single operation, initiated with one click of a button and executed in a blink, or two, of the eye. Or maybe I'm missing the point of your question...let me know, if so.

Regards,
Tim
-- joking in the 2nd sentence, of course

 

[Jeff Bailey]

This all looks too complicated for me. I use a technique I developed many years ago (before multi select thingys).

Put a yes/no field (called Selected, maybe) in your table and on the form (as a check box). Run an update query to set all selected to no when the form loads. When using the form, tick the records you want and put Selected = True as a criterion in your update query. QED.

You can provide 'Select All' and 'Clear all Selected' command buttons if you wish, just get them to run the appropriate update queries.

 

[potts]

Tim

Got it together - it's all working great now. Thanks a million

 

[pono1]

You're welcome -- glad to hear you not only survived the
information bombardment but then went on to win the battle.

Tim