Stumped on a SQL lookup

[bjverzal]

I have an Access 2003 DB I'm creating and I'm having a hard time with one aspect of it.

I have a form that prompts for a user code and password. The usercode is a combo box that prefills with all known user-ids in the user table. That part works fine. When they fill in the password and click 'OK', I need to query the table with the user-id as the key, find the password, and compare them. I can't seem to get the VBA code to work. Can anyone help ?

BV

 

[gol4]

depends on how you want to do this.

Is the pass word also part of the combo just hidden
if so just compare

if password <> me.combo1.column(1)

or dlookup

if password <>dlookup("password",Table","userid = " & combo1 & "'")

or
a recordset using sql to limit to one record or open and use findfirst

good luck

 

[bjverzal]

That is the problem - the SQL on the user name lookup does not work.

BV

 

[gol4]

let me be much more specific

I assume in your database you have a table something like this

Tblsers
userid password etc..
sally jones qwerty
tom smith terssdf2

you probably load the combobox with a query that only includes the userid

combo1.rowsource = "select userid from tblusers"

what you need to do is load passwords as well

combo1.rowsource = "select userid,password from tblusers"

set the combos column count to 2 then hide the password column by setting its width to 0

column width = 1";0" this shows the first column only

now to compare if the password entered in 2nd textbox matches the userid selected in the combobox just refer to the 2nd column of the combobox

if me.text2 = me.combo1.column(1) then 'we use 1 because 0 is the first column
pass code
else
failcode
end if

hope this helps

 

[bjverzal]

I'll try it - thanks.