can an update query give feedback if record not found?

[Dumboy]

I have an update query that signs people out by adding the time they sign out. the sql goes as follows:

UPDATE MainSignInTable SET TimeOut = time()
WHERE [TimeOut] is null And [SSN]=[Enter your SSN, DL or Scholl ID number];

The problem is that even if the SSN number doesn't match the query runs... is there anyway to produce feedback to the user in case they make a mistake and the numbers don't match .... something like..... error not found... try again...?

ThanX!

Dumboy.! Ouch.! .my brain.....
spiderdesign@yahoo.com

It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

 

[Databaseguy]

Use a combo box to allow the user to select their SSN. Maybe even in conjunction with their name ? Otherwise, you could create a recordset object based on the SQL statement, and check it like so(97,DAO):

Dim dbs as database
dim rst as recordset
dim strSQL as string
set dbs = currentdb
strSQL = "Select [SSN] From yourtable WHERE [TimeOut] is null And [SSN]=" & me.cboSSN.columns(x).value
Set rst = dbs.openrecordset(strSQL)
rst.movelast
if rst.recordcount <> -1 (or .eof and .bof)
then
set rst = nothing
strSQL = &quot;UPDATE MainSignInTable SET TimeOut = time()
WHERE [TimeOut] is null And [SSN]=[Enter your SSN, DL or Scholl ID number]&quot; 'miodify to include variables...
dbs.execute(strSQL)
'insert code here....
Else
Etc blah blah


Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Dumboy]

Thanks Tyrone... I will try the second option, we can not have a combo box showing the SSNs ..... thanks again.....

Dumboy.! Ouch.! .my brain.....
spiderdesign@yahoo.com

It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

 

[Databaseguy]

You can display the name in the combo, and still use the SSN without displaying it. Just make the combobox have two fields(columns). Field 1 (index (0)) would be SSN, and have it's width set to 0. Feild 2 (or more) would be the name, and have it's width set to whatever width will fit the name.... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy