Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2005
    Posts
    28

    Unanswered: Visual Basic Help Needed

    I have got some visual basic code running when I exit a combo box. The code builds an SQL string based on the contents of the combo box:


    strSQL = "SELECT tblStock.* " & _
    "FROM tblStock " & _
    "WHERE tblStock.SupplierName" & strOffice & _
    "AND tblStock.StockType" & strDepartment & _
    "AND tblStock.StockCategory" & strGender & _
    "ORDER BY tblStock.DateReceived;"

    I need to include another line in the string where it includes the field "despatch" from table "tblStock" where it is equal to -1

    My knowledge of VB code is very limited - can anyone help?

    Thanks
    John

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if your strXyz stuff is text, what you have already is broken!

    it should read:
    strSQL = "SELECT tblStock.* " & _
    "FROM tblStock " & _
    "WHERE tblStock.SupplierName'" & strOffice & _
    "' AND tblStock.StockType'" & strDepartment & _
    "' AND tblStock.StockCategory'" & strGender & _
    "' ORDER BY tblStock.DateReceived;"
    ...several single quotes and spaces added

    add your new line
    strSQL = "SELECT tblStock.* " & _
    "FROM tblStock " & _
    "WHERE tblStock.SupplierName'" & strOffice & _
    "' AND tblStock.StockType'" & strDepartment & _
    "' AND tblStock.StockCategory'" & strGender & _
    "' AND tblStock.despatch = -1 " & _
    " ORDER BY tblStock.DateReceived;"

    you should replace the -1 with True (if that's what you mean)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I wonder is the strXyz has already been assigned to include single quotes further up the code ?? That would explain it working.
    e.g. strOffice="'" & [forms]![myform]![myOfficeCombo].value & "'"

    John, could you supply all the code.

    Chris

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sneaky!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2005
    Posts
    28
    Hi! Please find attached all of the code! Look forward to hearing from you!


    Private Sub cboSupplierName_Exit(Cancel As Integer)
    ' Pointer to error handler
    On Error GoTo cboSupplierName_Exit_err
    ' Declare variables
    Dim db As Database
    Dim qdf As QueryDef
    Dim strOffice As String
    Dim strDepartment As String
    Dim strGender As String
    Dim strSQL As String
    ' Identify the database and assign it to the variable
    Set db = CurrentDb
    ' Check for the existence of the query, create it if not found,
    ' and assign it to the variable
    If Not QueryExists("qrySearchStock") Then
    Set qdf = db.CreateQueryDef("qrySearchStock")
    Else
    Set qdf = db.QueryDefs("qrySearchStock")
    End If
    ' Get the values from the combo boxes
    If IsNull(Me.cboSupplierName.Value) Then
    strOffice = " Like '*' "
    Else
    strOffice = "='" & Me.cboSupplierName.Value & "' "
    End If
    If IsNull(Me.cboStockType.Value) Then
    strDepartment = " Like '*' "
    Else
    strDepartment = "='" & Me.cboStockType.Value & "' "
    End If
    If IsNull(Me.cboStockCategory.Value) Then
    strGender = " Like '*' "
    Else
    strGender = "='" & Me.cboStockCategory.Value & "' "
    End If
    ' Build the SQL string
    strSQL = "SELECT tblStock.* " & _
    "FROM tblStock " & _
    "WHERE tblStock.SupplierName" & strOffice & _
    "AND tblStock.StockType" & strDepartment & _
    "AND tblStock.StockCategory" & strGender & _
    "ORDER BY tblStock.DateReceived;"
    ' Pass the SQL string to the query
    qdf.SQL = strSQL
    ' Turn off screen updating
    DoCmd.Echo False
    ' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qrySearchStock") = acObjStateOpen Then
    DoCmd.Close acQuery, "qrySearchStock"
    End If
    ' Open the query
    DoCmd.GoToControl "cmdOK"
    cboSupplierName_Exit_exit:
    ' Turn on screen updating
    DoCmd.Echo True
    ' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    cboSupplierName_Exit_err:
    ' Handle errors
    MsgBox "An unexpected error has occurred." & _
    vbCrLf & "Please note of the following details:" & _
    vbCrLf & "Error Number: " & Err.Number & _
    vbCrLf & "Description: " & Err.Description _
    , vbCritical, "Error"
    Resume cboSupplierName_Exit_exit
    DoCmd.GoToControl "cmdOK"

    End Sub

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Izzy has suggested

    " AND tblStock.despatch = -1 " & _

    should do the job, equally if you are in effect testing for TRUE / FALSE

    Making the assumption that -1 is a true / false value, also as Izzy, sagely, advised it would be better to use =TRUE in place of -1. Its unlikely that JET will change the value of TRUE / FALSE, but you never know.

    Using the constant TRUE caters for the remote possibility that it may change - its also portable - so if as and when you move the app to another db back end then it will still work.

  7. #7
    Join Date
    Apr 2005
    Posts
    28
    Brilliant! It works! Thank you for your help

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    John
    a suggestion: you have, I'm guessing, grabbed some code from a third party, with I'm guessing, not a lot of knowledge of what the code is doing. It may be worthwhile taking some time out to try to understand what that code is actually doing.

    A brief comment your code tests to see if the user has specified something in a box - if there is then there must be a match if there isn't then you add a parameter to your where clause like '*'. This could cause a performance hit Ie make the applicaiton slower - it may not depends n the quality of the SQL optimiser. However I'd sugest that there is no point in using the like '*' predicate unless you need to.

    it can be usefull if you want to search for something within a column eg look for Piet* - to look for anything with starting with Piet.

    There are other ways of building the search parameters, none is especialy better that the others.

    personally I'd do something similar to:-
    strSQL = "SELECT tblStock.* FROM tblStock "
    strWhereClause=""
    If not IsNull(Me.cboSupplierName.Value) Then
    strWhereClause = "tblStock.SupplierName='" & Me.cboSupplierName.Value & "' "
    Endif
    If NOT IsNull(Me.cboStockType.Value) Then
    if len(strWhereClause)>0 then strWherClause=strWherClause & ' AND '
    strWhereClause = "tblStock.StockType='" & Me.cboStockType.Value & "' "
    End If
    'etc....
    if len(strWhereClause)>0 then 'weve added some where conditions
    strSQL=strSQL & " WHERE " & strwhereclause & " ORDER BY tblStock.DateReceived;"
    HTH

Posting Permissions

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