Too many inverted coma's (1 Viewer)

[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

 

[Uncle Gizmo]

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.

Mind you, it would require the Set prefix to assign an object, but str2 is a string, so that wouldn't work ...

However VBA worked out that the forms default value is a string which provides its name.

Interesting, because "Value" is the normal default, although, obviously, a form would not have a "Value" property.

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" ?

 

[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

 

[Uncle Gizmo]

he is assigning the value of the Genus control on the form

I missed that! I was seeing what I wanted to see, I'm interested in "Value" property, as I noticed it does not Always work as advertised. So I'm on the lookout for instances of inconsistencies.... Thought I'd found another!

Edit:- (more info)

Value - Nifty Access

Use "Value" or Not? I've noticed a couple of minor glitches caused by the default of a 'textbox', the value; not being assigned explicitly. It's not a big problem and I don't think it will affect many people, possibly nobody! However I think there's a good blog in it for someone, possibly me...

www.niftyaccess.com

 

[Uncle Gizmo]

So, I can't figure out what exactly you are asking with the second question.

I'm not sure either! I hope my reply to Chris throws some light on it..

It's interesting to note that controls behave differently in different locations, as I mentioned in my Quiz Question:-

New Year Quiz!!

1) - Which Controls Click Events cannot be disabled?

www.access-programmers.co.uk

I make a lot of assumptions about how things work, without knowing for sure, hence my interest in apparent inconsistencies...

Your insight is most welcome...

 

[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)

 

[Uncle Gizmo]

You can determine the default member of any object through the object explorer.

I suspect there is also a way to print them out, or shove them into a table for a more detailed analysis. I'm sure I've seen some code somewhere that would do this!

 

[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.

 

[Uncle Gizmo]

TempVars example you quoted, it works as expected.

The issue is not with "TempVar" it's with "Value"
The "Value" Property is the default for most Controls. This means you can assign a Controls Value to a variable just by referring to the Control. You do not need to append the Control name with the Value Property.

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" What I'm pointing out is that the general advice, in that you do not need to append a Control name with "Value" because it's the default doesn't apply when applying a control value to a "TempVar"

TempVars Value Error - Nifty Access​

 

[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.

 

[Uncle Gizmo]

To the contrary, Microsoft (and I) advise to not omit the default property name for 20+ years.

However it is the advice often given in forums. My point is that you do need to use Value in certain situations, particularly when assigning the value from a control to a TempVar...

I first noticed this issue when I converted a macro contained in the Northwind database to VBA code the conversion failed! See Video Clip for More Information:-

TempVars Value Error - Nifty Access​