Run-time Syntax Error (1 Viewer)

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi All,

I am in desperate need of some help.

I am fairly new to creating access databases, I have a Search page that filters the results in a Subform below, and from this form I would like to double click on the code and have it open the details of that particular Code.

I have added the below code on the double click event:

Private Sub Internal_Unit_Code_DblClick(Cancel As Integer)
DoCmd.OpenForm "PrecedentDetails", , , "Internal Unit Code = " & Me.Internal_Unit_Code
End Sub


However when i double click on the relevant Code to open it is coming up with he following:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Internal Unit Code = COMP1004'.

Could someone please help.

Thank you
 

essaytee

Need a good one-liner.
Local time
Today, 20:43
Joined
Oct 20, 2008
Messages
512
Hi All,

I am in desperate need of some help.

I am fairly new to creating access databases, I have a Search page that filters the results in a Subform below, and from this form I would like to double click on the code and have it open the details of that particular Code.

I have added the below code on the double click event:

Private Sub Internal_Unit_Code_DblClick(Cancel As Integer)
DoCmd.OpenForm "PrecedentDetails", , , "Internal Unit Code = " & Me.Internal_Unit_Code
End Sub


However when i double click on the relevant Code to open it is coming up with he following:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Internal Unit Code = COMP1004'.

Could someone please help.

Thank you


Try this:
Code:
[Internal Unit Code] =
Also, I think you may need to surround your search string in quotes.
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi Steve,

Thank you very much for your reply. I tried the following:

Private Sub Internal_Unit_Code_DblClick(Cancel As Integer)
DoCmd.OpenForm "PrecedentDetails", , , "[Internal Unit Code] = " & Me.Internal_Unit_Code
End Sub


and now when I double Click I am getting a Enter Parameter Value error.
The Value it is displaying in the First ID entered on the subform.
 

essaytee

Need a good one-liner.
Local time
Today, 20:43
Joined
Oct 20, 2008
Messages
512
In the form you are attempting to open, check that the field names are correctly spelt. Does your field name have spaces in it, if so, that is the reason for the brackets surrounding them, if not, brackets are not required. Check your recordsource of the form to be opened, the searched field must be returned, check correct spelling.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:43
Joined
Feb 28, 2001
Messages
27,001
The reason for the 'Missing operator" earlier is because of having spaces in a named object. In the absence of overriding punctuation, a space is a "break" to most parsers. Therefore, the VBA parser saw the word Internal followed by the word Unit with nothing between them. To the parser, that was TWO variables and variables need to be separated by operators. This is why you don't put spaces in names. Applying the square brackets gave the parser reason to treat the name with spaces as a single name. So that let the code compile. BUT the compilation of the substitution code didn't come across an error because substitution is a run-time thing, not a compile-time thing.

So... you changed the string to include the brackets and got a different error having to do with that filter expression. You need to determine what it is you are trying to put in that line as your filter criterion. Put a breakpoint on the DoCmd.OpenForm line so that the code stops BEFORE trying to open the form. Then hover the mouse cursor over Me.Internal_Unit_Code to verify what you are trying to execute.

Then, I have a second, related question: What is the format of [Internal Unit Code] supposed to be? IF it is text, then you have a "multi-layered" substitution expression. Your statement: "when I double Click I am getting a Enter Parameter Value error. The Value it is displaying in the First ID entered on the subform." makes me think this is a substitution error where you are inadvertently letting a value become a variable name. Try

Code:
DoCmd.OpenForm "PrecedentDetails", , , "[Internal Unit Code] = [B]'[/B]" & Me.Internal_Unit_Code & "[B]'[/B]"

Watch out for apostrophes and double quotes here. I strongly suspect that your Internal_Unit_Code value looks like a text string which, in that syntax, gets substituted into the filter. But without the quotes, it still looks like a text item that COULD be the name of something rather than a literal value. When you try to open the form (a deferred run-time action), only at that time does Access realize it has no idea what value you are using because that unit-code value is in a position to legally represent a variable.
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi Steve,

Yes my field names have spaces in them, so I have therefore entered the brackets.
In regards to the brackets in the 'Me.' part of the code, do I put the opening bracker before Me. or after the Me. ?

I have tried the following:

Private Sub Internal_Unit_Code_DblClick(Cancel As Integer)
DoCmd.OpenForm "PrecedentDetails", , , "[Internal Unit Code] = " & [Me.Internal_Unit_Code]
End Sub

This time I get a different error again:

Run-time error '2465':

Microsoft Access can't find the field '|1' referred to in your expression.


This is not in the expression ?
 

Attachments

  • Runtime error 2.PNG
    Runtime error 2.PNG
    8 KB · Views: 142

essaytee

Need a good one-liner.
Local time
Today, 20:43
Joined
Oct 20, 2008
Messages
512
Try the code snippet that The_Doc_Man has supplied, that should see you right.
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi The_Doc_man,
Thank you so much, that worked perfectly, you have no idea how many hours I have spent trying to fix that.

Thank you
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi The_Doc_Man,

So we had it working perfectly, and then made another error in our database. We have had to rebuild the query and the subform, and now when we put the code in dblClick event, nothing is coming up at all when we double click. What are we doing wrong
 

essaytee

Need a good one-liner.
Local time
Today, 20:43
Joined
Oct 20, 2008
Messages
512
Hi The_Doc_Man,

So we had it working perfectly, and then made another error in our database. We have had to rebuild the query and the subform, and now when we put the code in dblClick event, nothing is coming up at all when we double click. What are we doing wrong

Make sure that "Event Procedure" is selected in the Double Click event property window.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:43
Joined
Feb 28, 2001
Messages
27,001
Aimn_4U, I have no way to tell because there is a ton of possible errors involved in doing a total rebuild. You are in essence starting from scratch. I believe you need to

(a) verify that the query returns data when using the filter you define as a WHERE clause

(b) verify that the sub-form and form are properly linked

(c) verify that the sub-form's .RecordSource is correct

You say that nothing is coming up, but that is ambiguous. Nothing at all? (As in, the form does not open) Or no data? (As in the form opens but is empty, has no records).

Then, essaytee raises a good point. Do you have reason to believe that the DoubleClick code is actually being run? That isn't a button/form linkage, but an Access event linkage. Those are important too.
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi The_Doc_Man,

Thank you so much for your response again. I have spent all day trying to figure out where I have gone wrong, and using the code above that worked, my double click event is now not even working at all.
Is there anyway I could send you a copy of my database to have a look at and see if you are able to tell me why my double click event is now bringing up nothing ?
Thank you again for all your help it is very much appreciated.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:43
Joined
Sep 12, 2006
Messages
15,614
If you use variables to form SQL statements then this applies.

Numbers - no formatting
Strings - surround with double quotes - you can do this with chr(34) - I find it halps readbility.
Dates - surround with octothorps (hash symbols)


So

"Internal Unit Number = " & Me.Internal_Unit_Number
"Internal Unit String = " & chr(34) & Me.Internal_Unit_Number & chr(34)
"Internal Unit Date = " & "#" & Me.Internal_Unit_Date & "#"

in the last case, the data is always processed in US order mm/dd/yyyy - so in the UK you need to format it iinan appropriate manner.
 

essaytee

Need a good one-liner.
Local time
Today, 20:43
Joined
Oct 20, 2008
Messages
512
Hi The_Doc_Man,

Thank you so much for your response again. I have spent all day trying to figure out where I have gone wrong, and using the code above that worked, my double click event is now not even working at all.
Is there anyway I could send you a copy of my database to have a look at and see if you are able to tell me why my double click event is now bringing up nothing ?
Thank you again for all your help it is very much appreciated.

Have you made sure that "Event Procedure" is selected against the relevant textbox control? If it's not selected your double-click event will never be activated, even though you can see and modify the code.

Sample image for clarity:
 

Attachments

  • 2019-05-15_19-23-36-PropertyEventWindow.jpg
    2019-05-15_19-23-36-PropertyEventWindow.jpg
    47.4 KB · Views: 388

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:43
Joined
Feb 28, 2001
Messages
27,001
essaytee's comment triggered a memory. When you rip out stuff from the form and try to rebuild it, you can have "remnant" code in the class module. The events you programmed into the sub-form's controls got ripped out when you removed that form for the do-over. But that action didn't take the code with it when you did the rip-out because that code was in the separate class module.

It is therefore possible for you to have the double-click event code sitting there left over from the prior iteration but disconnected from everything. If you go into the property sheet and select that you will use an event procedure (as shown by essaytee), the dangling code suddenly recognizes that it is part of an event procedure and reconnects to the entry point. After that, it goes "live" again. Also watch out because if you have the event property sheet open and double-click that event slot, it might try to create a second entry point, which gets really crazy because VBA lets that happen. In essence, whichever one appeared SECOND in the class module is used because the second entry point supersedes the first one. Crazy, eh? But somehow it does.

As to your other question, I do not take databases from other persons. It is a personal preference and represents a precedent I don't wish to set. I don't mind helping and will try hard to think about issues, but I don't work with someone else's code unless I have to. And since I'm retired now, I no longer have to. It is not anything personal about you and please don't take it that way.
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi Essay_Tee & The_Doc_Man,

Thank you both for your assistance.
I definitely have the event procedure attached to the relevant textbox control. Please see screen shot attached.
I believe I also have the correct code against it, and cannot see that there is any other code sitting in the “background”. I have attached a screenshot of this also.
Is there something I am missing or is it possibly a property control that I do not have set up correctly ?
 

Attachments

  • Dbl Click Event Procedure.PNG
    Dbl Click Event Procedure.PNG
    48.1 KB · Views: 86
  • Dbl Click Code.PNG
    Dbl Click Code.PNG
    34.8 KB · Views: 98

essaytee

Need a good one-liner.
Local time
Today, 20:43
Joined
Oct 20, 2008
Messages
512
Something to try and without any other changes the result should be the same. Run the subform directly (not as a subform). Does the double-click event work? On the same subform, create another textbox control, add a double-click event, code a simple message or a debug.print statement, just to see if it works.
 

JHB

Have been here a while
Local time
Today, 10:43
Joined
Jun 17, 2012
Messages
7,732
Post your database with some sample data, zip it because you haven't post 10 post yet.
 

Aimn_4U

Registered User.
Local time
Today, 17:43
Joined
May 14, 2019
Messages
33
Hi JHB,

Please find attached a copy of my database with dummy data. I still can not figure out why it is not opening.

Essay_Tee - Excuse my ignorance, I typed in the Debug.Print Statement - what is this supposed to do ?

thank you everyone for your help.
 

Attachments

  • Practise Database - 14-05-19 2220000000000000000000.zip
    185.1 KB · Views: 96

Users who are viewing this thread

Top Bottom