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

[HansV]

Do you mean the Master_data form? To be able to delete records from the Master_data table, you must set Enforce Referential Integrity and also Cascade Delete Related Records for the relationship between Master_data and Requisition_table. In the database that you attached, this has already been done, so you should have no problems deleting a record.

[vinwin06]

OK Hans thanks for the confirmation .

[vinwin06]

Hi Hans,

Sorry to disturb you again for this thread. I have the final database but my field after update code and before update are not working correctly i am not sure what went wrong on this codes. because some of them are working correctly.

After update event used for restricting the user not to skip the fields and before update event used for validation criteria.

Find attached the copy of the database.

[HansV]

Please tell me what exactly I should look at. There is a lot of code in the database.

[vinwin06]

In the master data form for each date field there is after update event and before update event.

After update event used for restricting the user not to skip the fields and before update event used for validation criteria.

So please check whether its working for all of fields because some of the field can be skipped by the users. So i want to restrict that. Like they have fill up the first date field then only they can go into the next date field. Please those two events in date fields.

[HansV]

I tried all the dates on the Master Data form. When I enter an earlier date, I get a warning. I couldn't find an exception.
I do get a VBA error message if I try to clear Tender_Number_Date, on the line

Code: Select all

        If (CDate(Me!Tender_Number_date) < CDate(!max_date)) Then

because CDate(Null) is not valid. So you'd have to test whether Me.Tender_Number_Date is Null before comparing it to max_date.

[vinwin06]

Those things working but when you try to skip any fields after tender date then I have after update event to lock it or unlock the fields but that portion is not working. Like example once you filled tender date and skip the next field and go and enter bid due date it should not allow user to enter because the previous fields not filled up.
I want you to check that for me.

[HansV]

You can't check that in the Before Update or After Update event of those controls. If the user skips a control, neither of these events occurs.
So you have to perform a final check in the Before Update event of the form.

[vinwin06]

But in my case all the fields are inter connected if you see the before update event code each field is based on previous field to enter date. So that all the dates are need to be checked.

But based on your experience can you tell some suggestion how best I can do it. Or give some example codes in my database also.

[HansV]

An alternative could be to start with only the filled date text boxes enabled, and all others disabled. Each time the user fills in a date, its After Update event enables the next text box.
You could use the form's On Current event to enable/disable the text boxes initially.

[vinwin06]

Can you please give me the codes in my database for one field then I can create it for others.

[HansV]

I'll post some code later on.

[HansV]

You already have most of the code in place, but you probably need to expand it for the situation where CB_SubmissionID = 1. Since I don't grasp the setup fully, you'll have to write the code yourself.
Here is code for the On Current event of the form. You'll also have to expand it for CB_SubmissionID = 1.

BTW, please try to indent your code more consistently. Code is extremely difficult to read if the indentation is haphazard.

Code: Select all

Private Sub Form_Current()
    Dim f As Boolean
    f = (Me.CB_SubmissionID > 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
    If Me.CB_SubmissionID > 1 Then
        If IsNull(Me.Bid_Due_date) Then
            Me.Technical_Evaluation_started.Locked = True
        Else
            Me.Technical_Evaluation_started.Locked = False
        End If
        If IsNull(Me.CBapprovaldateCommercial) Then
            Me.POsentSaleAgreementfaxofintentsent.Locked = True
            Me.Tender_cancelled.Locked = True
        Else
            Me.POsentSaleAgreementfaxofintentsent.Locked = False
            Me.Tender_cancelled.Locked = False
        End If
        If IsNull(Me.CBapprovaldateIssueTender) Then
            Me.Tender_Issue_Date.Locked = True
        Else
            Me.Tender_Issue_Date.Locked = False
        End If
        If IsNull(Me.CBsubmissiondateIssueTender) Then
            Me.CBapprovaldateIssueTender.Locked = True
        Else
            Me.CBapprovaldateIssueTender.Locked = False
        End If
        If IsNull(Me.CBsubmissiondateTechnical) Then
            Me.CBapprovaldateTechnical.Locked = True
        Else
            Me.CBapprovaldateTechnical.Locked = False
        End If
        If IsNull(Me.Commercial_Evaluation_completed) Then
            Me.CBsubmissiondateCommercial.Locked = True
        Else
            Me.CBsubmissiondateCommercial.Locked = False
        End If
        If IsNull(Me.Commercial_Evaluation_started) Then
            Me.Commercial_Evaluation_completed.Locked = True
        Else
            Me.Commercial_Evaluation_completed.Locked = False
        End If
        If IsNull(Me.Technical_Evaluation_completed) Then
            Me.CBsubmissiondateTechnical.Locked = True
        Else
            Me.CBsubmissiondateTechnical.Locked = False
        End If
        If IsNull(Me.Technical_Evaluation_started) Then
            Me.Technical_Evaluation_completed.Locked = True
        Else
            Me.Technical_Evaluation_completed.Locked = False
        End If
        If IsNull(Me.Tender_Issue_Date) Then
            Me.Bid_Due_date.Locked = True
        Else
            Me.Bid_Due_date.Locked = False
        End If
        If IsNull(Me.Tender_Number_date) Then
            Me.CBsubmissiondateIssueTender.Locked = True
        Else
            Me.CBsubmissiondateIssueTender.Locked = False
        End If
    ElseIf Me.CB_SubmissionID = 1 Then
        If IsNull(Me.Commercial_Evaluation_completed) Then
            Me.POsentSaleAgreementfaxofintentsent.Locked = True
            Me.Tender_cancelled.Locked = True
        Else
            Me.POsentSaleAgreementfaxofintentsent.Locked = False
            Me.Tender_cancelled.Locked = False
        End If
        If IsNull(Me.Technical_Evaluation_completed) Then
            Me.Commercial_Evaluation_started.Locked = True
        Else
            Me.Commercial_Evaluation_started.Locked = False
        End If
        If IsNull(Me.Tender_Number_date) Then
            Me.Tender_Issue_Date.Locked = True
        Else
            Me.Tender_Issue_Date.Locked = False
        End If
    End If
End Sub

[vinwin06]

Thanks Hans for your suggestion as I am started writing codes now only will try to improvise.

But the one you have given its already in place but I want to know about after update event after each field filled up. Hope your clear on my point.

[HansV]

You already have that code...

[vinwin06]

That why I want your help I'm show me the codes formation.

[HansV]

I'm sorry, I posted code for the On Current event of the form, and you already have code for the After Update event of the text boxes. What more do you need?

[vinwin06]

But that after update event codes are not working in all the fields so that's where I need your help. How to make those codess working.

[vinwin06]

Let me explain a bit clear to you. On the first record if i skip the field Tender issue date and try to enter Bid due date then my after update event in bid due date should restrict the entry because tender issue date not been filled up. But in my database its allows me to enter even when the after update event code is active.

Code: Select all

Private Sub Tender_Issue_Date_AfterUpdate()
   If Me.CB_SubmissionID > 1 Then
    If IsNull(Me.Tender_Issue_Date) Then

        Me.Bid_Due_date = Null
        Me.Bid_Due_date.Locked = True
    Else
        Me.Bid_Due_date.Locked = False
    End If
    End If
        
End Sub

Hope your clear now on my example. So i want to restrict the user by entering the next field if they skip the first one.

[HansV]

The On Current event of the form should take care of that, but only if CB_SubmissionID > 1 - the same condition you have in Tender_Issue_Date_AfterUpdate.