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

[vinwin06]

How i can do the same for all other fields? because for each field i need to create a user entry restriction.

[Rudi]

Sounds like you need a For Each loop to test each control inside the forms BeforeUpdate event.
Something in the form of the code in this thread by John "Big Booty"

Just a idea.

[vinwin06]

Thanks for the example. But my case is different . But i got an idea like in the after update event for each field i will make all other field locked then once filled up then i can activate the fields to fill up. but my assumption is i already have that code but still i cannot able achieve what i needed.. lets see what i got it end of my codes.

[HansV]

Since the conditions for each control are different from those for the others, you'll have to write code for each of those controls separately. And since only you know exactly what the conditions are, we cannot write the code for you.
Personally, I wouldn't bother since I think it's too much work. I'd rely on the form's Before Update event to check all the conditions. If that's not acceptable to you, that is fine, but it means that you will have to write a LOT of code.

[vinwin06]

Yes I understood that its lot of codes need to written. Now before update event is working fine.

Can you give one sample code for how we can restrict user not to skip the fields. That's only one request from me. If you have provided then I would write codes myself.

[HansV]

We're going round in circles. The form's On Current event locks all controls that shouldn't be filled in before the preceding ones have been filled. The After Update event of the individual controls unlock the next control if appropriate.
Together with the form's Before Update event for a final check, that's all you need. So I don't see what code I should provide.

[vinwin06]

Then please provide me the codes for lock in all controls that would fine for me.

[HansV]

But you already have that code in the On Current event procedure that I posted earlier in this thread!

[vinwin06]

OK as per your suggestion I will lock all the fields in the current event then once each field filled up using after update event I will enable the next field. So now I need to create each field after update event only.

Thanks for all your help. Really appreciated.

[vinwin06]

It work like a charm thank you so much for your patience and explanation. Let me give and test it with users. You have saved me. :)

[vinwin06]

Hi Hans,

One final help from you if user enter into main form and add new record and come back to main form the newly added record is not appearing on the search form how I can do the refresh automatically. I have tried me.requery in the main form but still its not working. The same problem I face while exporting the data into excel. Because its missing the newly enter data's.

Please help...

[Rudi]

You would either have to close the Main Form, or navigate to a new or previous record so that the new record gets written to the table first before it will appear in the search form. If you are dealing with VBA code it looks like the update method can work. Try something like this:

Code: Select all

Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset("Table Name")
    ...
    rst.Update
    rst.Close

[vinwin06]

Your correct but without closing the main form how I can refresh the data. Its like user will see the home form from there they will go into data entry form once they are done with data entry they will come back to home form. Without closing and reopen the open form can we do the refresh the data and get the new records in home form?

[vinwin06]

I got an error on the codes you have provided how I can declare the mydb as ?
I have declared rest as dao.recordset
What about my dB?

[HansV]

Instead of using Rudi's code, change the On Click event procedure for the Add_New_Record button to

Code: Select all

Private Sub Add_New_Record_Click()
    DoCmd.OpenForm FormName:="Master_data", DataMode:=acFormAdd, WindowMode:=acDialog
    Me.CmbTendor.Requery
    CmbTendor_AfterUpdate
End Sub

[vinwin06]

Thanks for this. But I am getting error on form current event because we have code looking cb submission I'd criteria there but when we enter new record there is no cb submission is selection so its giving error of invalid use of null.

[vinwin06]

I think on the form current we need to give additional criteria for new records so that it will work only when editing records

[HansV]

You can add a line at the beginning of the On Current event of the Master_Data form:

Code: Select all

    If Me.NewRecord Then Exit Sub

or you can replace Me.CB_SubmissionID with Nz(Me.CB_SubmissionID,0)

[vinwin06]

I have used the me.new record code there.

One more clarification I have changed tender_no field into number format so in the after update event of CMB tendor showing data type mismatch because its referring the text values

Me.cmbSAPReqNo.RowSource = "SELECT DISTINCT Requisition_table.SAP_Requisition_Numbers FROM Requisition_table WHERE Tender_NO = '" & Me.CmbTendor & "'"

Do I need to remove the double quotes here to make it as number field.?

[HansV]

Change it to

Me.cmbSAPReqNo.RowSource = "SELECT DISTINCT Requisition_table.SAP_Requisition_Numbers FROM Requisition_table WHERE Tender_NO = " & Me.CmbTendor