Run time error 3705 - Syntex error (Missing operator) in que

[vinwin06]

Hello,

I am trying to edit the data to open on a form based on selection on the combo box, here is the code below;

Code: Select all

Private Sub CmdEditExistingRecord_Click()

        DoCmd.OpenForm "Master_data", acNormal, "", BuildFilter, acFormEdit, acWindowNormal

    
End Sub


Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varstrat As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    varstrat = Null  ' Subfilter used for colors
    
     
        
    ' Check for Tendor_no
    If Me.CmbTendor > "" Then
        varWhere = varWhere & "[Tender_Number] Like """ & Me.CmbTendor & """ And "
    End If
    
    ' Check for buyer
  If Me.Cmbbuyer > "" Then
        varWhere = varWhere & "[BuyerID] Like """ & Me.Cmbbuyer & """ AND "
   End If
    
   ' Check for CategoryID
    If Me.Cmbcategory > "" Then
        varWhere = varWhere & "[CategoryID] Like """ & Me.Cmbcategory & """ AND "
    End If
               
    ' Test to see if we have subfilter for colors...
    If IsNull(varstrat) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varstrat, 4) = " OR " Then
            varstrat = Left(varstrat, Len(varstrat) 
        End If
        
        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varstrat & " )"
    End If
   
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE" & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere)  
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

I am getting a error in this codes as "Run time error 3705 - Syntex error (Missing operator) in query expression". can you please tell me what i am doing wrong in this codes.

Another question i want to make this combo box inter related to each other like one tendor _No selected its related data's only have to show it in other combo's. This applies to all other combo's as well.

[HansV]

Welcome to Eileen's Lounge!

You don't assign a value to varstrat in the code, so that part will never be used.

In the part near the end, part of the code is missing. It should be

Code: Select all

    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5) 
        End If
    End If

For related combo boxes, see Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms and/or Synchronizing Combo Boxes on Forms in Access 2007.

[vinwin06]

Sorry actually a part of the code has been deleted actually the code you have mentioned only part of my database, but still i am getting the syntex error only.

[HansV]

Which line is highlighted when you get the error?

[vinwin06]

Actually debug starts from this code, but when i try to break it then it showing the error at the end only.

Code: Select all

DoCmd.OpenForm "Master_data", acNormal, "", BuildFilter, acFormEdit, acWindowNormal

[vinwin06]

A small suggestion from me, i think this should be some issue on the do cmd open form? because i think i am trying to open another form based on the click event ... i think i should have record set define before opening the form. I think my suggestion make sense?

[HansV]

The problem must be with the BuildFilter function.

Does this version work for you?

Code: Select all

Private Function BuildFilter() As Variant
    Dim strWhere As String

    ' Check for Tendor_no
    If Me.CmbTendor > "" Then
        strWhere = strWhere & "[Tender_Number] Like """ & Me.CmbTendor & """ AND "
    End If

    ' Check for buyer
    If Me.Cmbbuyer > "" Then
        strWhere = strWhere & "[BuyerID] Like """ & Me.Cmbbuyer & """ AND "
    End If

    ' Check for CategoryID
    If Me.Cmbcategory > "" Then
        strWhere = strWhere & "[CategoryID] Like """ & Me.Cmbcategory & """ AND "
    End If

    ' Check if there is a filter to return...
    If strWhere <> "" Then
        ' strip off last "AND" in the filter
        If Right(strWhere, 5) = " AND " Then
            strWhere = Left(strWhere, Len(strWhere) - 5)
        End If
    End If

    BuildFilter = strWhere
End Function

[vinwin06]

Yes its works, but it's not opened with selected record on the form, instead it opens a blank form for entering the new data.

[HansV]

Could you create a copy of the database, remove sensitive information, zip it and then attach the zip file to a reply? Thanks.

[vinwin06]

yeah sure. Find attach my copy of database

[HansV]

Thank you. There were two distinct problems:

1) The Format property of the Tender_Number field. Because of the format, the values in the combo box were not equal to the values in the field itself.

2) The BuyerID and CategoryID fields are number fields. So the combo boxes for these on the Home form should have two columns: one for the ID (hidden), and one for the name (displayed).

The BuildFilter function had to be adjusted for this. See the attached version. I have also "linked" the buyer and category combo boxes on the Home form to the tender_number combo box.

Testing_DB_V2.zip

[vinwin06]

Thank you so much its works now.... :)

[vinwin06]

I have another question in the home form if i want to add the field from sub form then what is the code need to be used in build filter function. Can you please tell me because i tried with normal me.field name but it does not work for me. i knew need to refer the sub form but not sure how i can do it.

[HansV]

How would you want to use fields from the subform in the filter?

[vinwin06]

As a selection criteria like a combo box to show the existing numbers.

[HansV]

Yeah, OK, but how should the filter work? Should the user always select a tender_number, buyer and category first? Or should it be possible to select a SAP Requisition number by itself?

[vinwin06]

Actually they should try either with tender no or sap requisition no... is it possible to do like if they selected tender no then its related req NGOs should display and if they selected req no then related tender no should display on the combo box.

[HansV]

I'm not convinced that this is a useful exercise, but here goes:

Testing_DB_V2.zip

[vinwin06]

Thanks for this version. But when I am selecting from req no and click on edit existing records button it works first time then when I am trying to search second time its showing blank sub form not showing any values on it.

[HansV]

If I select a Tender Number first, then a SAP Requisition Number, it works OK.
If you only select a SAP Requisition Number, and not a Tender Number, you'll still see all records in the main form but only the selected SAP Requisition Number in the subform, so the subform may be empty.
In the attached version, the filter has been extended to select only records in the main form for which the selected SAP Requisition Number occurs in the subform.

Testing_DB_V2.zip