Too many inverted coma's

[John Sh]

Please correct the syntax of the rs.findfirst line in the code below. I'm fairly sure I've got the inverted comma's all wrong.

Public Sub inDB(str As String)
    Dim db As Database
    Dim rs As Recordset
    Dim frm As Form
    Dim str2 As String
    Set frm = Screen.ActiveForm
    Set db = CurrentDb
    str2 = frm.Genus
    Set rs = db.OpenRecordset("main", dbOpenSnapshot)
    rs.MoveFirst
    rs.FindFirst "Accessionnumber ='" & str & "'" & "Genus='" & str2 & "'"
    If rs.EOF Then
        frm.txtmain.BackColor = RGB(200, 0, 0)
    Else
        frm.txtmain.BackColor = RGB(0, 200, 0)
    End If
    rs.Close
End Sub[
/CODE]

 

[The_Doc_Man]

    rs.FindFirst "Accessionnumber ='" & str & "'" & " And Genus='" & str2 & "'"

It wasn't too many apostrophes (that is the correct name for what you call an inverted comma). The apostrophes look OK. However, what would have happened during that substitution would be that you had no space before Genus and therefore, if the str argument came in without a trailing space, you would have concatenated the contents of str with "Genus" - but even if you had a space, you needed an operator to make that expression possible to parse.

 

[John Sh]

It wasn't too many apostrophes (that is the correct name for what you call an inverted comma).

Yeah, I knew that but at 82+ my brain doesn't always come up with the right stuff!!
That said, thanks for the tip but I finished up with the code below.

rs.FindFirst "Accessionnumber =" & Str & " and Genus='" & str2 & "'"

John

 

[John Sh]

I was surprised that this line worked without complaint:-

str2 = frm.Genus

Hey Uncle Gizmo, Thanks.
It was throwing an error and I was having some difficulty locating it. I should have had str2 = frm.cboGenus. Now it works as expected.

 

[sonic8]

Dear @Uncle Gizmo, I think your head is still in need of recovery from the New Year celebrations.

I was surprised that this line worked without complaint:-

str2 = frm.Genus

I would have expected it to say you can't assign the form object to a string variable.

Why? The assignment clearly references the Genus property of the form. - Looks like you scored a lucky hit at the problem's cause, but there is nothing in that line of code indicating that.

Be interesting to know which controls have a default of value, and those with a default of string.

I would guess it depends on whether the control accepts data?

Are there any other default properties other than "Value' and "String" ?

The first question can be easily answered and may imply the answer to the second.
The default property of a control (and any class in general) is determined by the developer of the control when designing the control.
For all built-in Access controls, which do have a Value property, the Value property is actually the default. This is by convention only. It would have been technically possible to make any other property the default property. - This would make little sense and cause a lot of confusion though.
The data type of the Value property is Variant for all Access controls to allow for NULL values.


"Value" is the name of a property. "String" is a data type. - So, I can't figure out what exactly you are asking with the second question.
As mentioned above, the data type of Value is usually a Variant. So, there are obviously default properties with a data type other than string.
There is also at least one Access control which has a default property other than Value: The SubForm control. It's default property is the Controls collection of the nested form object.

 

[CJ_London]

@Uncle Gizmo

I was surprised that this line worked without complaint:-

str2 = frm.Genus

he's not - he is assigning the value of the Genus control on the form
edit: been beaten to it

 

[MajP]

Be interesting to know which controls have a default of value, and those with a default of string

You can determine the default member of any object through the object explorer. The default member has the blue box icon. Not sure what that icon is supposed to be. Example, the default for a Form object is the "Controls" collection. The other icons are

The other icons in the image show
Libraries (ex. Access)
Classes (ex. Field)
Constants (accommand)
Properties (close button)
Events (commandbeforeexecute)
Types (ExcepIfo)
Modules (not shown)

 

[sonic8]

I'm interested in "Value" property, as I noticed it does not Always work as advertised.

When exactly?
In the TempVars example you quoted, it works as expected.

The default property is invoked, when a member of an object must be invoked to execute an assignment, either because of the target type of the assignment expression and/or because of the Set operator missing.

 

[sonic8]

The default member has the blue box icon. Not sure what that icon is supposed to be.

It's supposed to be a small blue dot. This is how it was displayed in previous version of Office:

 

[NauticalGent]

Looking at your code, I question why you chose to make this a separate, public Sub instead of within the Form's Class Module?

There is no Form being passed into it and it is clear this Sub is specially designed to work with a certain form.

I don't think it will effect performance either way but why separate the functionality unnecessarily?

 

[sonic8]

I don't think it will effect performance either way but why separate the functionality unnecessarily?

I don't see any indicator that it is unnecessary.
The two most common reasons are probably:

• To reuse the code.
• To name the code.

 

[NauticalGent]

don't see any indicator that it is unnecessary.
The two most common reasons are probably:

Agree on the reasons why, but the OP is using specific fields. Easy enough to make it more reusable but as it stands, not so much

 

[NauticalGent]

To reuse the code.

This video was a real game changer for me. In fact, your whole Better VBA was just fantastic.

 

[sonic8]

Please hold my beer while I readjust my nitpicking hat.

The issue is not with "TempVar" it's with "Value"

Neither! The issue is ambiguous code.

However if you attempt to assign a Controls Value to a "TempVar" this will cause an error. "Runtime error 32538 – TempVars can only store data they cannot store objects." You have to append the control with the value:- "MyCtrl.Value"

Neither in your text nor in the video about the problem, you assign the value to the temp var (directly)! You pass the control object into the TempVars.Add method which defines the Value argument (of the Add method) as Variant. There is no reason to invoke the default member when passing an object to a variant argument.
This is neither specific to TempVars nor the Value property (of controls). It happens exactly the same if you write your own procedure expecting a Variant argument.

Now, I have to admit, there is something specific and surprising (to me at least) about TempVars.
The problem (error 32538) also happens, when you actually do a straight assignment to a TempVar value without using the Set keyword.
This I cannot fully explain. However, I very strongly suspect that it is caused by the peculiarity of the TempVars that you can assign a value to non-existing named item in the collection. This implies that a straight assignment also uses the logic of the TempVars.Add method and thus also initially accepts the control object, only to reject it immediately afterwards.

What I'm pointing out is that the general advice, in that you do not need to append a Control name with "Value" ...

I don't consider that to be "general advice". To the contrary, Microsoft (and I) advise to not omit the default property name for 20+ years.
Unfortunately, the Access and Jet/ACE Expression Service cannot handle objects properly, so you cannot explicitly reference properties in expressions. This creates a confusing situation for many Access developers.

 

[John Sh]

Actually the control on the form is "cboGenus". Had it been called "Genus" the line would have worked other than a possible conflict of names as the field in the table is called "Genus"

Looking at your code, I question why you chose to make this a separate, public Sub instead of within the Form's Class Module?

There is no Form being passed into it and it is clear this Sub is specially designed to work with a certain form.

I don't think it will effect performance either way but why separate the functionality unnecessarily?

That code segment, along with many others, will be re-used on other forms. I am not the best coder around so when something does as required I re-use it whenever possible. I have many forms performing different operations on the same table so re-using the code makes sense to me.

 

[John Sh]

    rs.FindFirst "Accessionnumber ='" & str & "'" & " And Genus='" & str2 & "'"

It wasn't too many apostrophes (that is the correct name for what you call an inverted comma).

Having mulled this over a bit, you are incorrect. An apostrophe is the little dohickey that goes before the "S" in "it's".
An inverted comma, either single or double, is what goes around a quote. EG. "It wasn't too many apostrophes "

 

[sonic8]

My point is that you do need to use Value in certain situations [...]

For clarification: I do not disagree with that.
My point is: Always use explicit property names for default properties whenever possible. It improves readability and reduces errors caused by ambiguous code.

 

[The_Doc_Man]

An inverted comma, either single or double, is what goes around a quote.

There is no inverted comma in USASCII-128, which is the default character set for Access. The character is "apostrophe" and is named as such.

Apostrophe and acute accent confusion

Users of German and other European keyboards frequently confuse the ASCII apostrophe with the ISO 8859-1 acute accent.

www.cl.cam.ac.uk

Confusion often occurs when dealing with programs like Word that have "smart quotes" in which the word processor substitutes "open" or "closed" single or double quotation marks using UNICODE characters. There, the resemblance to a comma becomes obvious.

Access, not being a word processor, doesn't give a fig about them. It transparently uses apostrophe as a single-quote mark.

 

[sonic8]

But no-one does,[...]

Great! Now I'm qualified to battle a cyclops.

So is it really dangerous, or personal choice?

So, is it really dangerous to ride a motorcycle without a helmet, or is it personal choice?
As long as you don't crash and hit your head it's not dangerous.
It's the same with writing explicit code. As long as you remember to do it, when it is mandatory, it will not cause a real problem. Nevertheless, I think it's a good practice to always wear your helmet.


@John Sh , if you're unsure when and where to put the delimiters, this tutorial video about SQL strings in VBA might be for you.

 

[The_Doc_Man]

I have often thought that I could understand even the most bizarre ideas, but Uncle Gizmo, the "helmet inside the office" idea has me stumped. How much would it cost to fix the roof and how much would it cost to replace the computers damaged by falling roof pieces? How much would lost productivity cost? It would seem to me that the equation for risk/reward would not take long to make it worthwhile to fix the roof. Some people who get these crazy ideas are their own worst enemies.