Run time error 3705 - Syntex error (Missing operator) in que
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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?
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?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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.
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.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
You mentioned that you used the line
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).
Code: Select all
If Me.NewRecord Then Exit Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
On current event if I replace all then I will not get the field hided for the condition cb cub mission equal to one.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
Find attached the database copy for your reference .
https://www.dropbox.com/s/4eutw3o4x29d9 ... 6_Open.zip" onclick="window.open(this.href);return false;
https://www.dropbox.com/s/4eutw3o4x29d9 ... 6_Open.zip" onclick="window.open(this.href);return false;
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
The On Current event of the Master_Data form contains the line
I'd change this to
Code: Select all
ElseIf Nz(Me.CB_SubmissionID, 0) = 1 Then
Code: Select all
Else
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
I have changed as per your suggestion but still those fields are not showing up foe add new records.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
You have the following code in On Current:
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.
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
If you want something else, you can change the code accordingly.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
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
to
throughout the code for CmbTendor_AfterUpdate.
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
Code: Select all
"WHERE Tender_Number = " & Chr(34) & Me.CmbTendor & Chr(34)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
It should be
Code: Select all
If Me.CmbTendor > "" Then
strWhere = strWhere & "[Tender_Number] = " & Chr(34) & Me.CmbTendor & Chr(34) & " And "
End If
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 82
- Joined: 13 Nov 2013, 19:28
Re: Run time error 3705 - Syntex error (Missing operator) in
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
I'll take a look at your database later today.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Run time error 3705 - Syntex error (Missing operator) in
In the code behind the Master_data form, the line
occurs 7 times. You should change all of them to
The opposite of the condition "A Or B" isn't "(Not A) Or (Not B)", but "(Not A) And (Not B)".
Code: Select all
If Me.CB_SubmissionID <> 3 Or Me.CB_SubmissionID <> 4 Then
Code: Select all
If Me.CB_SubmissionID <> 3 And Me.CB_SubmissionID <> 4 Then
Best wishes,
Hans
Hans