Getting field data in data sheet view

[OD]

in a form i would use docmd.openform and refer to the field, this works fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I would
like
to do this like I do in a form.

Thanks
OD

 

[Alex Dybenko]

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[OD]

Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep from
having
to open a form every time I needed data from a table. I just want to open a
table
goto the record I want then get the field or fields data.

Thanks
OD

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I would
like
to do this like I do in a form.

Thanks
OD

 

[John Spencer]

Datasheet view of a query or a form? If you are using datasheet view of
a query, then you are probably out of luck since there are no VBA events
associated with the datasheet view of a query.

Datasheet view of a form does have some events that you can use - for
instance the current event is available,

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep from
having
to open a form every time I needed data from a table. I just want to open a
table
goto the record I want then get the field or fields data.

Thanks
OD

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I would
like
to do this like I do in a form.

Thanks
OD

 

[Alex Dybenko]

Hi John,
actually - there is a trick to get events on datasheets:
http://accessblog.net/2008/12/events-on-query-datasheet.html

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Datasheet view of a query or a form? If you are using datasheet view of a
query, then you are probably out of luck since there are no VBA events
associated with the datasheet view of a query.

Datasheet view of a form does have some events that you can use - for
instance the current event is available,

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep
from having
to open a form every time I needed data from a table. I just want to open
a table
goto the record I want then get the field or fields data.

Thanks
OD

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or
open recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD

 

[Alex Dybenko]

Hi,
see my reply to John, perhaps trick from the link will help you

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep
from
having
to open a form every time I needed data from a table. I just want to open
a
table
goto the record I want then get the field or fields data.

Thanks
OD

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or
open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD

 

[OD]

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to open
the user's table goto the user's name field find the name, then check to see
if it matches what the user entered in the logon form. If the passwords match
then I get the user's security level that's in the level field in the user's
table. This
all works as long as I create a form for the user's table to work with, and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field referred
to in expression.

Is thier another way to refer to the field.

Thanks

Hi,
see my reply to John, perhaps trick from the link will help you

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep
from
having
to open a form every time I needed data from a table. I just want to open
a
table
goto the record I want then get the field or fields data.

Thanks
OD

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or
open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD

 

[Alex Dybenko]

Hi,
I think best way is to use dlookup or open recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to
close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to
open
the user's table goto the user's name field find the name, then check to
see
if it matches what the user entered in the logon form. If the passwords
match
then I get the user's security level that's in the level field in the
user's
table. This
all works as long as I create a form for the user's table to work with,
and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field
referred
to in expression.

Is thier another way to refer to the field.

Thanks

Hi,
see my reply to John, perhaps trick from the link will help you

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks, but I asked how to do this in a DATA SHEET view. Creating a
form
was how I was doing it. The code below works fine. I was tring to keep
from
having
to open a form every time I needed data from a table. I just want to
open
a
table
goto the record I want then get the field or fields data.

Thanks
OD

:

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup
or
open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




in a form i would use docmd.openform and refer to the field, this
works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, ,
acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD

 

[John W. Vinson]

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to open
the user's table goto the user's name field find the name, then check to see
if it matches what the user entered in the logon form. If the passwords match
then I get the user's security level that's in the level field in the user's
table. This
all works as long as I create a form for the user's table to work with, and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field referred
to in expression.

It sounds like you're assuming that you must open a table datasheet or a form
in order to see data in a table. That's not the case!

Instead simply use the DLookUp function to read the value from the table:

If Me!txtPassword = (DLookUp("[Password]", "[yourtable]", _
"[UserName] = '" & Me!Username & "'")) Then
<passwords match, all is well>
Else
<they don't, roll out the cauldron of boiling pitch>
End If

I don't know your form control names or fieldnames so you'll need to adapt of
course.

 

[John Spencer MVP]

Well you could use DLookup and a where argument that tests both the user name
and the password at the same time. Obviously you need to replace the field
and table names with your field and table names.

Dim vLevel as Variant

vLevel = DLookup("SecurityLevel","Users","UserName=""" & Me.UserName & """ AND
Password = """ & Me.Password & """")

If IsNull(vLevel) then
Msgbox "Invalid login"
Else
'Do stuff
End If

If you don't want to use DLookup, you can create a recordset based on a query
and check the values returned to the recordset. The above is simpler and
probably just as fast.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to open
the user's table goto the user's name field find the name, then check to see
if it matches what the user entered in the logon form. If the passwords match
then I get the user's security level that's in the level field in the user's
table. This
all works as long as I create a form for the user's table to work with, and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field referred
to in expression.

Is thier another way to refer to the field.

Thanks

Hi,
see my reply to John, perhaps trick from the link will help you

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep
from
having
to open a form every time I needed data from a table. I just want to open
a
table
goto the record I want then get the field or fields data.

Thanks
OD

:

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or
open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD

 

[OD]

I'm now using dlookup, but I have to use a muliti dlookups to get more than
1 fields data. I was just hoping that thier was a better way.

Thanks
OD

Hi,
I think best way is to use dlookup or open recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to
close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to
open
the user's table goto the user's name field find the name, then check to
see
if it matches what the user entered in the logon form. If the passwords
match
then I get the user's security level that's in the level field in the
user's
table. This
all works as long as I create a form for the user's table to work with,
and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field
referred
to in expression.

Is thier another way to refer to the field.

Thanks

Hi,
see my reply to John, perhaps trick from the link will help you

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Thanks, but I asked how to do this in a DATA SHEET view. Creating a
form
was how I was doing it. The code below works fine. I was tring to keep
from
having
to open a form every time I needed data from a table. I just want to
open
a
table
goto the record I want then get the field or fields data.

Thanks
OD

:

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup
or
open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




in a form i would use docmd.openform and refer to the field, this
works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, ,
acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD

 

[OD]

Thanks that will help.

I also found another way

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent, FindFirst
DoCmd.GoToControl "UserPassword"
Retval = Screen.ActiveDatasheet.ActiveControl
DoCmd.Close acTable, "Users"

I have not done a lot of testing on this, but it seem to work.

Thanks
OD

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to open
the user's table goto the user's name field find the name, then check to see
if it matches what the user entered in the logon form. If the passwords match
then I get the user's security level that's in the level field in the user's
table. This
all works as long as I create a form for the user's table to work with, and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field referred
to in expression.

It sounds like you're assuming that you must open a table datasheet or a form
in order to see data in a table. That's not the case!

Instead simply use the DLookUp function to read the value from the table:

If Me!txtPassword = (DLookUp("[Password]", "[yourtable]", _
"[UserName] = '" & Me!Username & "'")) Then
<passwords match, all is well>
Else
<they don't, roll out the cauldron of boiling pitch>
End If

I don't know your form control names or fieldnames so you'll need to adapt of
course.

 

[OD]

Thanks to everyone you all have been very helpfull. John I don't know if you
looked at my last post. I found smething that seems to work also. I can also
use it to udate
a value in a []. by using
Screen.ActiveDatasheet.ActiveControl = "something"

I have other forms/tables that keep the last number used, I have been using
a form
to do this. I just did not like having to open and close a form to do this
every time
I needed something. There is always more than one way to do something some
work better than others.

And thank all for the help.
PS

If the way I showed you above is going to give me a problem, please let me
know. I
will continue to check back.

Well you could use DLookup and a where argument that tests both the user name
and the password at the same time. Obviously you need to replace the field
and table names with your field and table names.

Dim vLevel as Variant

vLevel = DLookup("SecurityLevel","Users","UserName=""" & Me.UserName & """ AND
Password = """ & Me.Password & """")

If IsNull(vLevel) then
Msgbox "Invalid login"
Else
'Do stuff
End If

If you don't want to use DLookup, you can create a recordset based on a query
and check the values returned to the recordset. The above is simpler and
probably just as fast.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John and Alex

I think I need to explain better. If I used the trick I would have to close my
open form.

I will explain what I'm doing.

I have a logon form open. I get the users name and password. I need to open
the user's table goto the user's name field find the name, then check to see
if it matches what the user entered in the logon form. If the passwords match
then I get the user's security level that's in the level field in the user's
table. This
all works as long as I create a form for the user's table to work with, and
this works
with opentable too with the exception, I can not find a way to get the
current record
field data. Is there a way to refer to the current record field like
varPassword = [User Password]. I get an error now can't find field referred
to in expression.

Is thier another way to refer to the field.

Thanks

Hi,
see my reply to John, perhaps trick from the link will help you

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Thanks, but I asked how to do this in a DATA SHEET view. Creating a form
was how I was doing it. The code below works fine. I was tring to keep
from
having
to open a form every time I needed data from a table. I just want to open
a
table
goto the record I want then get the field or fields data.

Thanks
OD

:

Hi,
make a new form, bound to your table, then you can open it like:

DoCmd.OpenForm "frmUsers",,"UserName='" & varUserName "'"
varUserName = forms!frmUsers![UserName] '!!!!!
varPassword = forms!frmUsers![[Password] '!!!!
DoCmd.Close acform, "frmUsers"

but if you only need to get these 2 values - then better use dlookup or
open
recordset

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




in a form i would use docmd.openform and refer to the field, this works
fine.
I don't
want to have to create a form just to get the field data.

DoCmd.OpenTable "Users", , acReadOnly
DoCmd.GoToControl "UserName"
DoCmd.FindRecord varUserName, acEntire, , acSearchAll, , acCurrent,
FindFirst
varUserName = [UserName] '??????????
varPassword = [Password] '???????????
DoCmd.Close acTable, "Users"

I know about dlookup() but it only returns the value of one field. I
would
like
to do this like I do in a form.

Thanks
OD