Checking for value in a table

[BobbyS]

How would I write an SQL statement that would find the value (from a variable
strCheck) in a table (tblNames) in field named [Value]? and if that exists
then GoTo GetMeOut?

Thank you.

 

[tina]

suggest you try using a DLookup() function, rather than opening a recordset
based on a SQL statement. see Access Help for more information on the
function. and btw, if you really have a field in your table named "Value",
suggest you change it. Value is a property of controls in forms and reports,
and using it as the name of something you created (a field in a table) may
very likely cause problems.

hth

 

[BobbyS]

Thanks tina, but I guess I didn't explain well enough what I wanted to do.

I have a table with three fields.

I need to search the first field/column (Criteria) for a specific value, say
the text value "Username" then return the the value in the second column
(Setting) of that record an open recordset (rstNewUser). Thanks

suggest you try using a DLookup() function, rather than opening a recordset
based on a SQL statement. see Access Help for more information on the
function. and btw, if you really have a field in your table named "Value",
suggest you change it. Value is a property of controls in forms and reports,
and using it as the name of something you created (a field in a table) may
very likely cause problems.

hth

How would I write an SQL statement that would find the value (from a variable
strCheck) in a table (tblNames) in field named [Value]? and if that exists
then GoTo GetMeOut?

Thank you.

 

[TC]

I have a table with three fields.
I need to search the first field/column (Criteria) for a specific value, say
the text value "Username" then return the the value in the second column
(Setting) of that record ....

As tina said:

msgbox dlookup ("[f2]", "[tbl]", "[f1]="""username""")

where f1 is the name of the first field, tbl is the name of the table,
and f2 is the name of the second field.

... an[d?] open recordset (rstNewUser)

I don't understand what you mean. Dlookup will find the record with a
specified value for the first field & return the value from the second
field in that record. What else do you want to do? Where does a
recordset come in?

HTH,
TC

 

[Ofer]

If I understand what you are trying to do, you want to check if the record
exist and if it doesn't add it to the table

Dim MyDB As DAO.DataBase, MyRec As Dao.RecordSet

Set MyDB=CurrentDB
Set MyRec = MyDB.OpenRecordSet("Select * From TableName Where
UsernameFieldName = '" Me.Username "'")
If MyRec.Eof Then
' Doesn't exist
MyRec.AddNew
MyRec!Username = Me.Username
MyRec.Update
Else
MsgBox "Username exist"
End If
===================================================
' I hope that will get you started, if the field you are looking for is
number type use that
Set MyRec = MyDB.OpenRecordSet("Select * From TableName Where
UsernameFieldName = " Me.Username)
==================================================
I assumed that you are looking for a field value in a form and this is why I
used Me.Username, if it is a different variable then use yours

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

Thanks tina, but I guess I didn't explain well enough what I wanted to do.

I have a table with three fields.

I need to search the first field/column (Criteria) for a specific value, say
the text value "Username" then return the the value in the second column
(Setting) of that record an open recordset (rstNewUser). Thanks

suggest you try using a DLookup() function, rather than opening a recordset
based on a SQL statement. see Access Help for more information on the
function. and btw, if you really have a field in your table named "Value",
suggest you change it. Value is a property of controls in forms and reports,
and using it as the name of something you created (a field in a table) may
very likely cause problems.

hth

How would I write an SQL statement that would find the value (from a variable
strCheck) in a table (tblNames) in field named [Value]? and if that exists
then GoTo GetMeOut?

Thank you.

 

[David C. Holley]

DLookup([field1], [tableName], "[field2] = criteria")

Thanks tina, but I guess I didn't explain well enough what I wanted to do.

I have a table with three fields.

I need to search the first field/column (Criteria) for a specific value, say
the text value "Username" then return the the value in the second column
(Setting) of that record an open recordset (rstNewUser). Thanks


:

suggest you try using a DLookup() function, rather than opening a recordset
based on a SQL statement. see Access Help for more information on the
function. and btw, if you really have a field in your table named "Value",
suggest you change it. Value is a property of controls in forms and reports,
and using it as the name of something you created (a field in a table) may
very likely cause problems.

hth

How would I write an SQL statement that would find the value (from a
variable

strCheck) in a table (tblNames) in field named [Value]? and if that exists
then GoTo GetMeOut?

Thank you.

 

[TC]

Wrong way 'round!

TC

 

[tina]

if you're simply want to check for the existence of value in a table, so as
to take an action based on whether or not the value exists, a DLookup should
do it for you. it's easier, and shorter, to write than opening a recordset
and searching it; and AFAIK, there's little or no difference in speed.

perhaps you're trying to do this the hard way around. using a GoTo statement
in your code suggests that possibility, since there's rarely a real need to
use it outside of error handling. suggest you post the VBA code; maybe we
can suggest an easier way to accomplish your goal.

hth

Thanks tina, but I guess I didn't explain well enough what I wanted to do.

I have a table with three fields.

I need to search the first field/column (Criteria) for a specific value, say
the text value "Username" then return the the value in the second column
(Setting) of that record an open recordset (rstNewUser). Thanks

suggest you try using a DLookup() function, rather than opening a recordset
based on a SQL statement. see Access Help for more information on the
function. and btw, if you really have a field in your table named "Value",
suggest you change it. Value is a property of controls in forms and reports,
and using it as the name of something you created (a field in a table) may
very likely cause problems.

hth

How would I write an SQL statement that would find the value (from a variable
strCheck) in a table (tblNames) in field named [Value]? and if that exists
then GoTo GetMeOut?

Thank you.