Data Type Definitions

[Middlemarch]

? Me.RecordsetClone.Fields("id").Type

Depending on the field I get back numbers like 4, 8 10... how can i find what these numbers represent?
I know what they are/should be, but I want to confirm it.

[Micron]

Try

?TypeName(Me.RecordsetClone.Fields("id"))

[Middlemarch]

For some odd reason that returns "Field3" no matter what field name is used. It may be as I'm Stopped in a sub trying tp work this out.
There was a list (may have been in Excel) but can't find the same thing for Access where "type" seems to mean many things,
Do you know if 8 indicates a Time/date ?

[Micron]

OK, have never done this against a recordsetclone so I thought I had nailed it. Try this syntax instead

typename(forms![formNameHere].recordsetclone.fields("fieldNameHere").Name)
or
TypeName(Me.recordsetclone.fields("fieldNameHere") .Name)

If I had a nickel for every time I used the Name property to validate a reference, I could live on Easy Street.

EDIT - I just realized that might return the typename of the field name, which would of course be a string. You will have to validate that one way or another. I presume you want the type name of the field data, which begs the question of why drill down to a form's recordset clone when you could simply ask for the typename of the form field recordsource?

this should give you the data type name for a form field
typename(forms![formNameHere].FieldNameHere.value)

or try Me instead of Forms! - but .Value will be required.

[Edgar]

You can look these up in the object browser too. Leaving the list for future reference.

DataTypeEnum
https://learn.microsoft.com/en-us/of...numeration-dao

dbBoolean	1	Boolean (True/False) data
dbByte	2	Byte (8-bit) data
dbInteger	3	Integer data
dbLong	4	Long Integer data
dbCurrency	5	Currency data
dbSingle	6	Single-precision floating-point data
dbDouble	7	Double-precision floating-point data
dbDate	8	Date value data
dbBinary	9	Binary data
dbText	10	Text data (variable width)
dbLongBinary	11	Binary data (bitmap)
dbMemo	12	Memo data (extended text)
dbGUID	15	GUID data
dbBigInt	16	Big Integer data
dbVarBinary	17	Variable Binary data (ODBCDirect only)
dbChar	18	Text data (fixed width)
dbNumeric	19	Numeric data (ODBCDirect only)
dbDecimal	20	Decimal data (ODBCDirect only)
dbFloat	21	Floating-point data (ODBCDirect only)
dbTime	22	Data in time format (ODBCDirect only)
dbTimeStamp	23	Data in time and date format (ODBCDirect only)
dbAttachment	101	Attachment data
dbComplexByte	102	Multi-valued byte data
dbComplexInteger	103	Multi-value integer data
dbComplexLong	104	Multi-value long integer data
dbComplexSingle	105	Multi-value single-precision floating-point data
dbComplexDouble	106	Multi-value double-precision floating-point data
dbComplexGUID	107	Multi-value GUID data
dbComplexDecimal	108	Multi-value decimal data
dbComplexText	109	Multi-value Text data (variable width)

acControlType
https://learn.microsoft.com/en-us/of....accontroltype

acLabel	100	Label control
acRectangle	101	Rectangle control
acLine	102	Line control
acImage	103	Image control
acCommandButton	104	CommandButton control
acOptionButton	105	OptionButton control
acCheckBox	106	CheckBox control
acOptionGroup	107	OptionGroup control
acBoundObjectFrame	108	BoundObjectFrame control
acTextBox	109	TextBox control
acListBox	110	ListBox control
acComboBox	111	ComboBox control
acSubForm	112	SubForm control
acObjectFrame	114	Unbound ObjectFrame control
acPageBreak	118	PageBreak control
acCustomControl	119	ActiveX control
acToggleButton	122	ToggleButton control
acTabCtl	123	Tab control
acPage	124	Page control
acAttachment	126	Attachment control
acEmptyCell	127	EmptyCell control
acWebBrowser	128	WebBrowserControl control
acNavigationControl	129	NavigationControl control
acNavigationButton	130	NavigationButton control

[Middlemarch]

Many thanks for the List, Edgar. Printed out.

Micron here's the 2 results from your example
? TypeName(Me.recordsetclone.fields("Chart").Name)
String
? typename(forms![frmShowRecord].recordsetclone.fields("Chart").Name)
String

To me that's odd as it's a date (but in a Text Box - is that why?). This returns Datatype 8 as expected
Me.RecordsetClone.Fields(t.ControlSource).Type

However things get murky and I'd like to show the whole procedure. It works fine unless the text box contains a date. I've spent most of the day on this and can't figure it out.
The idea is, if a text box (where its tag = 1) is changed, the backcolor changes. And if that original value is restored so is the color.

ov is a Dictionary that holds the textbox Old Value. Although it isn't used if you don't leave the text box. You can change the values back and forth and the colors follow. Except for a date.

Private Sub txtREDate_Change()
    Wake Me!txtREDate
End Sub


Private Sub Wake(t As TextBox)
    On Error GoTo err_Waken  'In design/test mode if Dictionary ov is not set it causes error
    t.SetFocus  
    If t.Tag = "1" Then
        If StrComp(t.Text, Nz(ov(t.Name)), vbBinaryCompare) <> 0 And StrComp(t.Text, Nz(t.OldValue), vbBinaryCompare) <> 0 Then
                t.BackColor = 6737151
            Else
               If t.Text = Nz(t.OldValue) Then t.BackColor = 16764057: If ov.exists(t.Text) Then ov.Remove (t.Text)
        End If
    End If
    DoEvents
    Exit Sub
err_Waken:
        MsgBox Err & vbCrLf & Err.Description
End Sub


Private Sub txtREDate_BeforeUpdate(Cancel As Integer)
    ov("txtREDate") = txtREDate.OldValue
End Sub

[isladogs]

FYI, another datatype enum was recently added in 365 but hasn't yet been included in the documentation

26=dbDateTimeExtended

[Edgar]

Heads up: .Name properties are always type String.

You would benefit from inspecting your app through code stepping and either looking at your objects in the Locals window or in the Watch window. If you don't know what type something is, those windows tell you without having to type the command in the immediate window. They also tell you the values each property has, as well as a wealth of additional information, so you can stop that guess work.

[moke123]

I think you need to convert the dates to a string in order to compare.

This is quirky but appears to work. Needs a little tweaking.

[Micron]

I think it's because you didn't use .Value as I showed. I edited my response shortly after posting. Value first returns what is in the control before TypeName evaluates it.

[Middlemarch]

Using .value I could id the Date and add another cause to the If..Then to handle it. It's now all working. Much appreciated. It was very worthwhile to work out what was wrong.
Moke123, many thanks for example but still to get it working. Is something disabled ? Code doesn't seem to run (unless I 've done something stupid). Will try rebuilding it as would like to step through it.

[moke123]

Works for me. Did you remember to unblock the download in the file properties?
Also make sure Microsoft Scripting Runtime is selected in the vba references.

[Middlemarch]

Never struck this before. Like VBA is disabled. But not quite with you on unblock the download in the file properties?. Perhaps not. What is that ?
I'm running it from the downloads folder. Microsoft Scripting Runtime is selected.
I added to Form1
Private Sub Form_Load()
MsgBox "Load"
End Sub
But no Msgbox and a Stop anywhere doesn't stop .

[moke123]

But not quite with you on unblock the download in the file properties?. Perhaps not. What is that ?

Microsoft's P.I.T.A. security feature for downloads.



If it is not there then its unblocked already.

[moke123]

You made me look over my code and I realized it could be simplified even further.

In a standard module:

Option Compare Database
Option Explicit

Dim dict As Scripting.Dictionary

Sub CompareOldNew2()

    Dim ctl As Control
    
    Set ctl = Screen.ActiveControl

    If CStr(ctl.Text) <> dict(ctl.Name) Then
    
        ctl.BackColor = 6737151
        
    Else
    
        ctl.BackColor = 16764057
        
    End If
    
End Sub

Sub InitDict(frm As Form)

    If Not dict Is Nothing Then Set dict = Nothing

    Set dict = New Dictionary

    Dim ctl As Control

    For Each ctl In frm.Controls
    
        If ctl.Tag = 1 Then
        
            dict.Add ctl.Name, CStr(Nz(ctl.Value, ""))
            
            ctl.BackColor = 16764057 'ensure background returns to chosen default color
            
        End If
    Next

End Sub

In the form current event:

Option Compare Database
Option Explicit

Private Sub Form_Current()

    InitDict Me 'get dictionary of controls and their values
    
End Sub

In the change event of the control:

Private Sub txt1_Change()

     CompareOldNew2
  
End Sub

Private Sub dte_Change()

     CompareOldNew2

End Sub