! and Me

[dhstein]

I'm trying to update a table from data that I read from a linked text file.
I'm having problems with it, and maybe it would help if I understood what the
"!" is for and the "Me." I have two recordsets open and that is confusing
either me or confusing Access - probably me. I'll repeat my code here in
case you haven't seen it in the other posts. Thanks.

Dim rs As DAO.Recordset
Dim rsProducts As DAO.Recordset


Set rsProducts = CurrentDb.OpenRecordset("tblProduct")
rsProducts.Index = "ProductSKU"

..
.. Other Code here
..


Set rs = CurrentDb.OpenRecordset("Set_Product_Table", dbOpenSnapshot)



rsProducts.Seek "=", ![ProductSKU]
If rsProducts.NoMatch Then
MsgBox "This is a new item"
MsgBox ![ProductSKU]
With rsProducts
.AddNew
'?????????????????????????????????
' What code here sets the ProductSKU field in the
products table
' to the value of ProductSKU that is displayed in the
MsgBox above?
' ???????????????????????????????
.Update
End With

 

[Clifford Bass]

Hi,

Me is a way to tell Access that the thing that follows belongs to the
current object (form, report, what have you). ! is a way of specifying the
object of a specific name within the object preceeding the !. I have not
used the ! outside of a With section. My guess is that there is an implied
Me in front of it and you are getting the text box or whatever the control
is. In truth, the ! without a preceeding object is pretty much superfluous
outside of a with section. You can just use the object directly:
[ProductSKU]. Once inside the With section, the ! without anything in front
indicates an item within the object identified by the With statement. Your
example demonstrates one of the annoyances I have with Access's default
naming of bound controls. In code or elsewhere, it is not immediately
obvious that you are referencing a control or the actual field. For that
reason I almost always change the control names name to include type type
abbreviation at the beginning, such as txtProductSKU. So, as coded you would
use ![ProductSKU] = [ProductSKU]. Or to make it more abvious you could do
..Fields("ProductSKU") = Me![ProductSKU]. You could even do
..Fields("ProductSKU") = Me.Controls("ProductSKU"). Or rename the field and
use ![ProductSKU] = [txtProductSKU]. Or be explicit even within the With
section: rsProducts![ProductSKU] = Me![ProductSKU]. Or... well, you get the
idea

Clifford Bass

 

[banem2]

I'm trying to update a table from data that I read from a linked text file.  
I'm having problems with it, and maybe it would help if I understood whatthe
"!" is for and the "Me."  I have two recordsets open and that is confusing
either me or confusing Access - probably me.  I'll repeat my code here in
case you haven't seen it in the other posts.  Thanks.

Dim rs As DAO.Recordset
Dim rsProducts As DAO.Recordset

Set rsProducts = CurrentDb.OpenRecordset("tblProduct")
rsProducts.Index = "ProductSKU"

.
.    Other Code here
.

Set rs = CurrentDb.OpenRecordset("Set_Product_Table", dbOpenSnapshot)

            rsProducts.Seek "=", ![ProductSKU]
                If rsProducts.NoMatch Then
                   MsgBox "This is a new item"
                   MsgBox ![ProductSKU]
                   With rsProducts
                .AddNew
                '?????????????????????????????????
                 '    What code here sets the ProductSKU field in the
products table
                 '    to the value of ProductSKU that is displayed in the
MsgBox above?
                '  ???????????????????????????????
                .Update
                End With

To answer question about Me and ! simplified: "Me" can be used to
reference control of object in focus (forms and reports) while "!" can
be used to reference control on object which is not in the focus.
Like:

Me.ctrlName

will refer to control ctrlName on form in focus, while

Forms!frmName!ctrlName

will refer to control ctrlName on form frmName which does not have
focus.

There are some other uses of . and ! like:

rst.fields(1)

or

rst!fldName

In second case we are refering to field name fldName in recordset.

Also try this (I assume you have opened another recordset and used
"With rs2" before this code):

Set rs = CurrentDb.OpenRecordset("Set_Product_Table", dbOpenSnapshot)

rsProducts.Seek "=", !ProductSKU
If rsProducts.NoMatch Then
MsgBox "This is a new item"
MsgBox ![ProductSKU]
rsProducts.AddNew
rsProducts!ProductSKU = !ProductSKU
rsProducts.Update
End With

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[dhstein]

I'm trying to update a table from data that I read from a linked text file.
I'm having problems with it, and maybe it would help if I understood what the
"!" is for and the "Me." I have two recordsets open and that is confusing
either me or confusing Access - probably me. I'll repeat my code here in
case you haven't seen it in the other posts. Thanks.

Dim rs As DAO.Recordset
Dim rsProducts As DAO.Recordset

Set rsProducts = CurrentDb.OpenRecordset("tblProduct")
rsProducts.Index = "ProductSKU"

.
. Other Code here
.

Set rs = CurrentDb.OpenRecordset("Set_Product_Table", dbOpenSnapshot)

rsProducts.Seek "=", ![ProductSKU]
If rsProducts.NoMatch Then
MsgBox "This is a new item"
MsgBox ![ProductSKU]
With rsProducts
.AddNew
'?????????????????????????????????
' What code here sets the ProductSKU field in the
products table
' to the value of ProductSKU that is displayed in the
MsgBox above?
' ???????????????????????????????
.Update
End With

To answer question about Me and ! simplified: "Me" can be used to
reference control of object in focus (forms and reports) while "!" can
be used to reference control on object which is not in the focus.
Like:

Me.ctrlName

will refer to control ctrlName on form in focus, while

Forms!frmName!ctrlName

will refer to control ctrlName on form frmName which does not have
focus.

There are some other uses of . and ! like:

rst.fields(1)

or

rst!fldName

In second case we are refering to field name fldName in recordset.

Also try this (I assume you have opened another recordset and used
"With rs2" before this code):

Set rs = CurrentDb.OpenRecordset("Set_Product_Table", dbOpenSnapshot)

rsProducts.Seek "=", !ProductSKU
If rsProducts.NoMatch Then
MsgBox "This is a new item"
MsgBox ![ProductSKU]
rsProducts.AddNew
rsProducts!ProductSKU = !ProductSKU
rsProducts.Update
End With

Regards,
Branislav Mihaljev
Microsoft Access MVP
Clifford and Branislav,

Thanks so much for taking the time to explain this - it's early morning
here and I have to get to an appointment, but I'll try this out later. Have
a great day.