Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Question Unanswered: Vba Code Help Please..

    Hi,

    I have a combo box named CboGroupList which produces the group names of the items that are stored in table Product_master.

    I am getting the correct MS Access report based on a query.

    I therafter somehow arranged to deploy the data from Access table to Excel sheet successfully in a desired format instead of using access report So that I could be able to email the Excel sheet to whomever I need. Main VBA code line I wrote to read the data from Access and transfer to Excel sheet is as follows;


    'Code here to open Excel Application, Workbooks and sheets
    .....
    ....
    ....
    Set rst = CurrentDb.OpenRecordset("SELECT ProductCode, ProductName, UOM, Groupcode, MadeIn FROM Product_Master " & _
    "WHERE (Groupcode like """ & CboGroupList.Value & "*"") ORDER BY ProductName;")

    .....
    This works very smoothly.

    Now my question is that the above code works well when I select one group from the combo and run the VBA code. But when I need to get the data of all groups at a time, what would be the code ?

    I thought that there should be some specific word in combo itself like the word "(All GroupTypes)" (paranthesis just to come name 'All GroupTypes' at the top of the list). To do this, I tried changing above code line as follows.

    '====
    'Set rst= currentdb.openrecordset("SELECT ProductCode, ProductName, UOM, Groupcode, MadeIn FROM Product_Master " & _
    "WHERE (Groupcode like """ & CboGroupList.Value & "*"") Or "(All Group Types)" = Forms!TEST!CboGroupList) ORDER BY ProductName;")


    But it doesn't work.

    The CboGroupList is based on the query which is as follows:

    SELECT GroupCode,GroupName FROM T_Group_Master UNION SELECT "(ALL)" as GroupCode, "(All Group Types)" as GroupName FROM T_Group_Master
    ORDER BY GroupName;

    Combo is giving me the word "All Group Types". But the code above is not working.

    Where might have gone wrong?

    Thanks in advance.

    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Apr 2006
    Posts
    47
    what about using sql with no where clause when "All groups " is selected?

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Sorry,

    I did not get u..

    Ashfaque

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about Rudy's clever 1=1 trick?

    dim strSQL as string
    strSQL = "SELECT ProductCode, ProductName, UOM, Groupcode, MadeIn FROM Product_Master " & _
    "WHERE ((1=1)"
    if not isnull(cbogrouplist) then
    strSQL = strSQL & " And (Groupcode like '" & CboGroupList & "*')"
    endif
    strSQL = strSQL & ") ORDER BY ProductName;"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    izy

    PS are you sure you need the Like ??
    PPS you don't need the .Value
    Last edited by izyrider; 08-23-06 at 11:58. Reason: moved a <space>
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    probably i should elaborate.

    if this combo is the only criterion then MikeroSoft's suggestion is fine:

    dim strSQL as string
    strSQL = "SELECT ProductCode, ProductName, UOM, Groupcode, MadeIn FROM Product_Master "
    if not isnull(cbogrouplist) then
    strSQL = strSQL & "WHERE Groupcode like '" & CboGroupList & "*'"
    endif
    strSQL = strSQL & " ORDER BY ProductName;"
    Set rst = CurrentDb.OpenRecordset(strSQL)

    but if you have multiple textboxes, combos, blahblah any of which could contain criteria or be empty, the 1=1 solves the "unwanted WHERE" and "excess And" issues elegantly.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Gentlemen,

    I will try your code and let you know.

    Thanks again,

    With kind regards,
    Ashfaque

  7. #7
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I tried following way but still not worked...

    If (Me.CboGroupList) = "(All Client Types)" Then
    Set rst = CurrentDb.OpenRecordset("SELECT ProductCode, ProductName, UOM, Groupcode, MadeIn FROM Product_Master " & _
    "ORDER BY ProductName;")
    Else
    Set rst = CurrentDb.OpenRecordset("SELECT ProductCode, ProductName, UOM, Groupcode, MadeIn FROM Product_Master " & _
    "WHERE (Groupcode like """ & CboGroupList.Value & "*"") ORDER BY ProductName;")
    End If

    Dim RowCount As Double
    RowCount = 1

    rst.MoveFirst

    Do While Not rst.EOF

    objSht.Cells(iRow, 1).Value = RowCount
    objSht.Cells(iRow, 1).HorizontalAlignment = xlCenter
    objSht.Cells(iRow, 1).Borders.Color = vbBlack

    It produces error 3021 'No Current Record' at rst.MoveFirst

    Please advice.

    The Combo containes 5-6 names. It works when select any one of the group. But at the same time I need to select all groups data in one shot.

    How can I do this ?

    With kind regards,
    Ashfaque

  8. #8
    Join Date
    Mar 2006
    Posts
    163
    Why aren't you using TransferSpreadsheet to export to Excel?

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Norie,

    There are many reasons behind this. First I need to use Template file which is already formatted as desired by the user.

    Secondly I found this method lengthy but easy.

    Thanks.

    Any other thoughts?

    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Mar 2006
    Posts
    163
    Yes, use TransferSpreadsheet.

    If you have an existing template you can easily export to a new sheet, then through automation (which you are already using anyway) copy the data from that sheet to the template, then delete the new sheet.

Posting Permissions

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