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

[vinwin06]

Now I have changed the code as per given by you. But now requisition number are not showing in home screen drop down. Kindly help me

[HansV]

That is not a result of this change; the problem already existed because you had changed the setup of the cmbSAPReqNo combo box without modifying the After Update event of CmbTendor to match the new setup.

Code: Select all

Private Sub CmbTendor_AfterUpdate()
    Me.Cmbbuyer = Null
    Me.Cmbcategory = Null
    Me.cmbSAPReqNo = Null
    If IsNull(Me.CmbTendor) Then
        Me.Cmbbuyer.RowSource = "SELECT DISTINCT qryBuyer.BuyerID, qryBuyer.Buyer " & _
           "FROM qryMaster_data INNER JOIN qryBuyer " & _
           "ON qryMaster_data.BuyerID = qryBuyer.BuyerID"
        Me.Cmbcategory.RowSource = "SELECT DISTINCT qryCategory.CategoryID, qryCategory.Category " & _
            "FROM qryMaster_data INNER JOIN qryCategory " & _
            "ON qryMaster_data.CategoryID = qryCategory.CategoryID"
        Me.cmbSAPReqNo.RowSource = "SELECT DISTINCT qryMaster_data.Tender_Number, " & _
            "Requisition_table.SAP_Requisition_Numbers " & _
            "FROM qryMaster_data INNER JOIN Requisition_table " & _
            "ON qryMaster_data.Tender_Number=Requisition_table.Tender_NO"
    Else
        Me.Cmbbuyer.RowSource = "SELECT DISTINCT qryBuyer.BuyerID, qryBuyer.Buyer " & _
            "FROM qryMaster_data INNER JOIN qryBuyer " & _
            "ON qryMaster_data.BuyerID = qryBuyer.BuyerID " & _
            "WHERE Tender_Number = " & Me.CmbTendor
        Me.Cmbcategory.RowSource = "SELECT DISTINCT qryCategory.CategoryID, qryCategory.Category " & _
            "FROM qryMaster_data INNER JOIN qryCategory " & _
            "ON qryMaster_data.CategoryID = qryCategory.CategoryID " & _
            "WHERE Tender_Number = " & Me.CmbTendor
        Me.cmbSAPReqNo.RowSource = "SELECT DISTINCT qryMaster_data.Tender_Number, " & _
            "Requisition_table.SAP_Requisition_Numbers " & _
            "FROM qryMaster_data INNER JOIN Requisition_table " & _
            "ON qryMaster_data.Tender_Number=Requisition_table.Tender_NO " & _
            "WHERE Requisition_table.Tender_NO = " & Me.CmbTendor
    End If
End Sub

[vinwin06]

Now its works fine.

While using the export also if I want to refresh the data newly entered or deleted I can use the same codes before the export codes it will work know?

[HansV]

The export to Excel uses the query Query_Export; this should always return the current situation, unless a record is still open in the Master_Data form.

[vinwin06]

Hi band
Good morning
THe current event is not working when the use click the add new records from the home form, but the same its working fine when I tried to use the edit existing records.
But if the current event is there then it should work on both the cases know. Especially this issue occurs when cb cub mission id = 1.
Can you tell me what causing this issue I have checked it but can't able to find the reason.

[HansV]

You mentioned that you used the line

Code: Select all

    If Me.NewRecord Then Exit Sub

This means that On Current won't do anything at all for new records. Please try my alternative suggestion to Me.CB_SubmissionID with Nz(Me.CB_SubmissionID,0).

[vinwin06]

On current event if I replace all then I will not get the field hided for the condition cb cub mission equal to one.

[HansV]

Why not?

[vinwin06]

Find attached the database copy for your reference .

https://www.dropbox.com/s/4eutw3o4x29d9 ... 6_Open.zip" onclick="window.open(this.href);return false;

[HansV]

The On Current event of the Master_Data form contains the line

Code: Select all

    ElseIf Nz(Me.CB_SubmissionID, 0) = 1 Then

I'd change this to

Code: Select all

    Else

[vinwin06]

I have changed as per your suggestion but still those fields are not showing up foe add new records.

[HansV]

You have the following code in On Current:

Code: Select all

    Dim f As Boolean
    f = Nz(Me.CB_SubmissionID, 0) > 1
    Me.CBsubmissiondateIssueTender.Visible = f
    Me.CBsubmissiondateTechnical.Visible = f
    Me.CBsubmissiondateCommercial.Visible = f
    Me.CBapprovaldateIssueTender.Visible = f
    Me.CBapprovaldateTechnical.Visible = f
    Me.CBapprovaldateCommercial.Visible = f

In a new record, CB_SubmissionID is NOT greater than 1, so f will be False. Hence, the controls listed above will be hidden. This is according to your own request that these controls will only be displayed if CB_SubmissionID is greater than 1.

If you want something else, you can change the code accordingly.

[vinwin06]

Here is the code I have used it. Hope this will work

Code: Select all

If Me.NewRecord Then

    Me.CBsubmissiondateIssueTender.Visible = True

    Me.CBsubmissiondateTechnical.Visible = True

    Me.CBsubmissiondateCommercial.Visible = True

    Me.CBapprovaldateIssueTender.Visible = True

    Me.CBapprovaldateTechnical.Visible = True

    Me.CBapprovaldateCommercial.Visible = True

    Else

    f = Nz(Me.CB_SubmissionID, 0) > 1

    Me.CBsubmissiondateIssueTender.Visible = f

    Me.CBsubmissiondateTechnical.Visible = f

    Me.CBsubmissiondateCommercial.Visible = f

    Me.CBapprovaldateIssueTender.Visible = f

    Me.CBapprovaldateTechnical.Visible = f

    Me.CBapprovaldateCommercial.Visible = f

    End If

[vinwin06]

Hi Hans,

Thanks for all you support on this , it really helpful for me yo understand lot of things in access VBA codes.

Now I am having another issue of tender number not allowing user to add tender number begins with 0 like in my database its allowing me to add 12345678 as tender number but if I want to add 01234567 then its not allowing to add the tender number.

For this I have two solutions like
1. Changing the tender no field from number to text data type?
I can do this but I'm facing some problem on the home form on search option not working for me. Because I am getting data type mismatch.
2. Can we able to change the data type into accept 0 before the tender number?

For reference I have added the database . please help me one more time.

[HansV]

If you want to store the tender number with leading zeros, it must indeed be a text field.
If you merely want to display 1234567 as 01234567, it can be a number field with Format set to 00000000
If you change it into a text field, you must change

Code: Select all

            "WHERE Tender_Number = " & Me.CmbTendor

to

Code: Select all

            "WHERE Tender_Number = " & Chr(34) & Me.CmbTendor & Chr(34)

throughout the code for CmbTendor_AfterUpdate.

[vinwin06]

Thanks Hans I did the same as per the code mentioned above but when I am clicking edit existing button I am getting type mismatch error . here is the code below

Code: Select all

If Me.CmbTendor > "" Then

        strWhere = strWhere & "[Tender_Number] =  " & Chr(34) & Me.CmbTendor & Chr(34) And ""

    End If

[HansV]

It should be

Code: Select all

    If Me.CmbTendor > "" Then
        strWhere = strWhere & "[Tender_Number] = " & Chr(34) & Me.CmbTendor & Chr(34) & " And "
    End If

[vinwin06]

Hi Hans

Thanks for the above codes its really great. But sorry again I'm facing some issue on the databasel like I have control check for each fields like if cb submission is equal to 3 or 4 then one field cannot be entered if the previous field is blank.but this logic is works fine for all other fields except like if u see tender issue date can able to enter If the previous field is blank also. I have attached the database for your reference. Please help what i need to avoid this issue.

[HansV]

I'll take a look at your database later today.

[HansV]

In the code behind the Master_data form, the line

Code: Select all

    If Me.CB_SubmissionID <> 3 Or Me.CB_SubmissionID <> 4 Then

occurs 7 times. You should change all of them to

Code: Select all

    If Me.CB_SubmissionID <> 3 And Me.CB_SubmissionID <> 4 Then

The opposite of the condition "A Or B" isn't "(Not A) Or (Not B)", but "(Not A) And (Not B)".