listboxes: available/select, field xfr issue... (1 Viewer)

[wiklendt]

hi,

using access 2007 on a vista home premium machine. i have two listboxes, one for 'available' word definitions, another for 'selected' word defintions. things work fine (-ish; i've had to debug a lot from a sample db i downloaded of uhmm.... somewhere? Utter Access i think).

except now i need to have the code transfer three fields from the available list to the selected list. here is the code i am workign with:

Private Sub cmdGlossaryAdd_Click()
'On Error GoTo Err_cmdGlossaryAdd_Click
    
    'adds glossary items to the 'selected' listbox
    
    Dim selection
    Dim Definition As Variant
    Dim intPosGlossary As Integer
    
    'Grab current selection value, if any
    selection = Me.lstGlossaryAvailable
    Definition = Me.lstGlossaryAvailable.Column(2)
    
    'Grab the selection position
    intPosGlossary = Me.lstGlossaryAvailable.ItemsSelected(0)
    
    'Leave if there is no current selection
    If IsNull(selection) Then Exit Sub
    If IsNull(intPosGlossary) Then Exit Sub

    'Append the selected entry to the associated table
    [red]CurrentDb.Execute "INSERT INTO tblMassageAssignedGlossary (MassageID, TechniqueID, TechniqueDefinition) " & _
                        "VALUES (" & Me.txtMassageID & ", " & selection & ", " & Definition & ")"[/red]
    
    'Update the list controls
    Me.lstGlossarySelected.Requery
    Me.lstGlossaryAvailable.Requery
    
    'Set the similar position as selected in the list
    If Me.lstGlossaryAvailable.ListCount > 0 Then
        Me.lstGlossaryAvailable = Me.lstGlossaryAvailable.ItemData(intPosGlossary)
    End If

Exit_cmdGlossaryAdd_Click:
    Exit Sub

Err_cmdGlossaryAdd_Click:
    Select Case Err.Number
       Case 2480
        'ignore, not an Error, just no selection made.
       Case 2447
        'ignore, though never gave this error before!
       Case 94
        'ignore, but no idea what invalid use of null in this instance is supposed to mean.
       Case Else
        Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
        End Select
    Resume Exit_cmdGlossaryAdd_Click
    
End Sub

my issue is highlighted where i have red text. on triggering the event (attempting to transfer from available listbox to selected listbox), the code chokes at this step (in red above, or highlighted in debug in attached image).

it is the "definition" in particular it is struggling with. when i subsitute 'definition' for a second instance of 'selection', the values are copied fine into the M-M table. however, when i put in "definiton" it causes and error (see attached images).

i have tried dim-ing the definition as string, variant and not defnining it at all (leaving access to use the default), nothing there helps.

i've checked the two fields (from and to) are the same data type and same size (text, 255 char) and they're in the same order in each query that the listboxes are sourced from.

when i hover over the "definition" it returns the string correctly, but for some reason does not want to insert the value into the field.

i'm not sure where to progress from here, i've been trying to fiddle with this for days as well as bringing up LOTS of threads about listboxes (which ahve been extremely helpful for other bits of my database! LOL) but none seem to specifically identify this problem...

any help i'd be greatful. cheers,

 

[boblarson]

Try this:

CurrentDb.Execute "INSERT INTO tblMassageAssignedGlossary (MassageID, TechniqueID, TechniqueDefinition) " & _
                        "VALUES (" & Me.txtMassageID & ", " & selection & ", [color=red][b][size=4]'[/size][/b][/color]" & Definition & "[color=red][b][size=4]'[/size][/b][/color])"

 

[wiklendt]

if i was physically there by your computer with you, i'd lean over and kiss you!!

my god, that fixed it boB!! punctuation to the rescue AGAIN!! AND i would have NEVER thought of it!! (well, mabe not 'never'...)

thank you (again)!

 

[wiklendt]

you are such a genius, boB.

 

[boblarson]

Glad it worked for you. It just takes a bit of these problems to really get the punctuation stuff to sink in. Don't worry about it though. It will come and stay. In fact, these struggles seem to make it actually easier to remember these things.

 

[boblarson]

Not a genius, but just "been there"

you are such a genius, boB.

No, just been through the same struggles over the last 11 years. Fortunately I've picked up enough to be able to help out by posting answers more than I post questions now. Although it wasn't always that way.

Sometimes it takes a while to have the light come on. I had about 600 posts up until about 2 years ago. Now look, it is over 11,100 and growing and very few are in the Watercooler. But, it did take about 9 years for most of it to really sink in.

 

[wiklendt]

    CurrentDb.Execute "INSERT INTO tblMassageAssignedRecommendations (MassageID, RecommendationID, RecommendationPhrase) " & _
                        "VALUES (" & Me.txtMassageID & ", " & selection & ", [COLOR=Red]""[/COLOR]" & action & "[COLOR=Red]""[/COLOR])"

hi bob, i had to change each apostrophe to two double quotation marks, as i ran into trouble with my "action" field that contained apostrophes in the text themselves.... a quick search of access help sorted that out ;-) tried it and it worked :-D

so now i need to find out if i can prevent my end-user from inputting the double quotation mark, or if i can put code into access to accept the double quotation keystroke, but to put in its place two apostrophes as an in lieu solution...!

 

[wiklendt]

ok, i just tested my theory: if i make pretend double quotation marks by using two consecutive apostrophes, then the query still works (even if my string also has apostrophes elsewhere). if i use a double quotation, the query fails.

so my aim would be to prevent a double quote entering into the data istelf. i will need vb code to change any dQuote keystrokes into 2 × sQuote. so that it is seamless and appears to the end user that their pressing of the SHIFT+' button (which produces the double quotation marks) does what they expect, but the code is actually changing it to two apostrophes.... does that make sense?

i'm reading other posts on the double quote issue - i think one touched on the code required for this though i'll have to try it out to know for sure i've understood the code's purpose...

http://www.access-programmers.co.uk/forums/showthread.php?t=50312

 

[wiklendt]

just for cross referencing... this issue is heavily covered in this thread starting from post #15.