Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statements (A2k)

    I'm working on a list to list selection.(to select items to eventually print to report)
    I have the basic structure and am stuck on the two mySQL statements.

    The following is from the Add part, (Left to Right list)

    Private Sub AddButton_Click()
    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim strWhere As String
    Dim MySQL As String

    strWhere = "EstimateNo = Forms!frmThumbnails!txtEstimateNo And Supp = Forms!frmThumbnails!txtSupp"
    'Return control objct variable pointing to list boxes
    Set ctlList = Me.lstPicList
    Set ctlList2 = Me.lstPrintList

    'Enumerate through selected items
    For Each varItem In ctlList.ItemsSelected
    'Add to selected list

    MySQL = "INSERT INTO tblImage ????????? values (" & ctlList.ItemData(varItem) & strWhere

    DoCmd.SetWarnings False
    DoCmd.RunSQL MySQL
    DoCmd.SetWarnings True

    Next varItem
    ctlList.Requery
    ctlList2.Requery
    End Sub

    And this one to remove from Right List:

    Private Sub cmdFrom_Click()
    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim MySQL As String

    'Return control objct variable pointing to list boxes
    Set ctlList = Me.lstPicList
    Set ctlList2 = Me.lstPrintList

    'Enumerate through selected items
    For Each varItem In ctlList2.ItemsSelected
    'remove from list
    ''mysql="Delete....................

    DoCmd.SetWarnings False
    DoCmd.RunSQL MySQL
    DoCmd.SetWarnings True
    Next varItem
    ctlList.Requery
    ctlList2.Requery
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL Statements (A2k)

    I'm a little unclear on what you're doing here. Are you building a Pick List form with a listbox on each side and trying to remove items from one list and add them to another? Are the controls Access ListBoxes or something else? What are the two lists based on? It appears that one of them, at least, is based on a tblImage, but what fields are you trying to insert into? A Where clause doesn't really make sense in an append query when using the Values syntax, so can you explain specifically what you're trying to accomplish in the SQL? The Where clause *does* make sense in a Delete query but you haven't told us what you're deleting from.
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Statements (A2k)

    You'll need to provide more info, Dave.

    - Is tblImage the row source of lstPrintList?
    - Which field(s) in tblImage must be filled?
    - If you would complete the code as it stands now, items will be added to/removed from lstPrintList, but lstPicList will remain static, i.e. items added to lstPrintList will not disappear from lstPicList and vice versa. Is that OK?

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statements (A2k)

    Hi Charlotte. ( this is another facility that I am incorporating in the jpg Manager form).

    Here's how it goes.The form pulls images from a default folder......... C:BICimage.................

    Left list box populates its self (Path & Filename) through a module ListJPGS and is unbound.
    From this list, the user may want to print 1 or 2 or all the images associated to a particular record.
    So by having a second list to the right, the user selects from the left list and populates the right list box.
    ( The report and it's query are already setup )

    Am I making sense ?

    So the two buttons as first post do just this.
    First button selects and populates right.
    Second button removes from right if user chnges his/her mind.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statements (A2k)

    Hans
    lstPiclist is static, it's already populated by the module.
    The user must select from lstPicList and the selected data copied to lstPrintList.
    These items now transferred, insert into tblImage along with frmThumbnails!txtEstimateNo, frmThumbnails!txtSupp..
    lstPrintList can !! have its control source set to tblImage along with all the field names in the rowsource.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Statements (A2k)

    Dave,

    The example you attached is very nice in itself, but not really appropriate here, since both list boxes in the demo form are based on the same table. Moreover, the first list box won't be modified.

    Your code should look something like this air code, but read the note at the end.

    Private Sub AddButton_Click()
    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim MySQL As String

    ' Return control objct variable pointing to list boxes
    Set ctlList = Me.lstPicList
    Set ctlList2 = Me.lstPrintList

    ' Loop through selected items
    For Each varItem In ctlList.ItemsSelected
    'Add to selected list
    MySQL = "INSERT INTO tblImage (EstimateNo, Supp, PicFile) " & _
    "VALUES (" & Forms!frmThumbnails!txtEstimateNo & ", " & _
    Forms!frmThumbnails!txtSupp & ", " & _
    Chr(34) & ctlList.ItemData(varItem) & Chr(34) & ")"

    DoCmd.SetWarnings False
    DoCmd.RunSQL MySQL
    DoCmd.SetWarnings True
    Next varItem

    ctlList2.Requery
    End Sub

    Private Sub cmdFrom_Click()
    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim MySQL As String

    ' Return control objct variable pointing to list boxes
    Set ctlList = Me.lstPicList
    Set ctlList2 = Me.lstPrintList

    ' Loop through selected items
    For Each varItem In ctlList2.ItemsSelected
    ' Remove from list
    MySQL = "DELETE FROM tblImage WHERE PicFile = " & _
    Chr(34) & ctlList2.Column(0, varItem) & Chr(34) ' see note

    DoCmd.SetWarnings False
    DoCmd.RunSQL MySQL
    DoCmd.SetWarnings True
    Next varItem

    ctlList2.Requery
    End Sub

    Note: ctlList2.Column(0, varItem) refers to the first column in ctlList2 (column and row numbering is zero-based). If the picture path is in another column, change the 0 to the column number minus 1.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statements (A2k)

    Thanks Hans.

    I'm sorry but I get so confused over the structures of SQL statements.
    I understand why we save a statement as a string & having to enclose a text value in Chr(34) but I get blinded over all the

    ", " ", " quotes and stuff.

    I stare at them so long I see millions of them in front of my eyes <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

    Any way, it all works fine now.
    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Statements (A2k)

    In fact, it's not the SQL itself that is confusing, but the fact that you are using code (VBA) to write other code (SQL). So you, as a programmer, are confronted with an extra layer: instead of puzzling out how to do something in code, you must find out how to write code that will create code to do something for you. While doing so, you must try to keep the levels straight in your mind, and that can be very confusing.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statements (A2k)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> Hans. You might as well have written that in Saturn Northern hemisphere dialect <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

    Seriously, yes I did understand it " I think " <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    Just one thing, the left hand list which pulls the static info from the module, has one important part missing. Each entry it fills only has the file name.
    Seeing as it pulls its info from FindJPGS module, is it better to add the string in there, and where ?
    Each file should be prefixed.

    Function ListJPGs(fld As Control, id As Variant, _
    row As Variant, col As Variant, _
    Code As Variant) As Variant
    Static dbs(127) As String, Entries As Integer
    Dim ReturnVal As Variant
    Dim strLeft5 As String
    Dim Path As String

    Path = "C:BICimage" ' But where to put it from here ???
    strLeft5 = Left(Forms!frmDetails![EstimateNo], 5)

    ReturnVal = Null
    Select Case Code
    Case acLBInitialize ' Initialize.
    Entries = 0
    dbs(Entries) = Dir("C:BICimage" & strLeft5 & "*.jpg")
    Do Until dbs(Entries) = "" Or Entries >= 127
    Entries = Entries + 1
    dbs(Entries) = Dir
    Loop
    ReturnVal = Entries
    Case acLBOpen ' Open.
    ReturnVal = Timer ' Generate unique ID for control.
    Case acLBGetRowCount ' Get number of rows.
    ReturnVal = Entries
    Case acLBGetColumnCount ' Get number of columns.
    ReturnVal = 1
    Case acLBGetColumnWidth ' Column width.
    ReturnVal = -1 ' -1 forces use of default width.
    Case acLBGetValue ' Get data.
    ReturnVal = dbs(row)
    Case acLBEnd ' End.
    Erase dbs
    End Select
    ListJPGs = ReturnVal
    End Function</small>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Statements (A2k)

    Although it would be possible to prefix the path in ListJPGs, but I think it is better to do so when you use the file name. Storing a constant path everywhere is redundant.

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statements (A2k)

    Comments noted.
    Thanks again Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •