SQL Search String

[Dustman]

Ack.. I'm not familiar with SQL syntax. I've got an Access 2000 DB with a table called RoutineStudy. When a command button is pressed to add a new Specimen to the list, I need it to search through the Specimen# column in RoutineStudy for the number that is entered by the user. If it is not found then it needs to just open a form and add a new one, if it is found, it needs to prompt the user if they want to duplicate the specimen number and make a new report or just load the old one. How do I do this? I know how to do all the VB stuff (ie: open forms...) I just need to know the exact syntax for the SQL statement and how to implement it.

Thanks in advance!
-Dustin

 

[Amiel]

Using the same form to display and add records.
With the table [RoutineStudy], as the row source for the form. In the form's OnOpen event, add the code...


Private Form_OnOpen(Cancel as integer)
dim db as database
dim rs as recordset
userValueEntered as String


set db = DBengine(0)(0)
userValueEntered = InputBox("Enter Routine Study","&quot
'Validate and/or convert user input at this point
...
...
...

set rs = db.OpenRecordset("Select * from [RoutineStudy] where [Specimen#] =" & me![[red]Number[/red]Entered])
..or
set rs = db.OpenRecordset("Select * from [RoutineStudy] where [Specimen#] ='" & [[red]Text[/red]Entered] "'&quot

if not rs.eof and not rs.bof then
'The item exists. Get that specific record(s)and fill the form.

me.RecordSource = "Select * from [RoutineStudy] where [Specimen#] ='" & [userValueEntered] "'"
me.requery 'using criterion entered by user.

Else
'The item doesn't exist. open form in 'append' mode.
'The statement below is done to make everyting go smoothly.
Me.RecordSource = "Select Top 1 from [RoutineStudy]" 'need a record so form doesn't cause problems.

me.AllowAdditions = TRUE
me.DataEntry = True
me.refresh
End If

rs.close
End Sub ' It's done !

Amiel
amielzz@netscape.net