Pulling Up Appt ID

[Daniel]

I am trying to make a procedure that brings up an
appointment when the user enters any part of the ApptID.
I have an unbound field and a button. I am having trouble
coding the 'any part' part. I have tried to make an SQL
statement and then set the Form.RecordSource to that
statement but it does not seem to want to work.

Any suggestions?

Here is the SQL statement that I have:
ApptIDSQL = "SELECT tAppt.fApptID FROM tAppt " & _
"WHERE ((tAppt.fApptID) LIKE '*' & '" & Trim(Me!
txtApptID) & "' & '*')"
DoCmd.OpenForm "frmAppt", , , Form.RecordSource =
ApptIDSQL, acFormEdit

In addition can I set the recordsource from the
DoCmd,OpenForm Where statement? Or can I only do that on
the Load event of the form?

Thanks

Dan

 

[Ken Snell]

To use the "LIKE" comparison, fApptID must be a text-formatted field.

You can't use the DoCmd.OpenForm's argument to set the recordsource. But you
can filter the form's recordsource in the DoCmd.OpenForm statement:

DoCmd.OpenForm "frmAppt", , , "[tAppt.fApptID] LIKE '*' & '" &
Trim(Me!txtApptID) & "' & '*'", acFormEdit

 

[John W. Vinson]

Here is the SQL statement that I have:
ApptIDSQL = "SELECT tAppt.fApptID FROM tAppt " & _
"WHERE ((tAppt.fApptID) LIKE '*' & '" & Trim(Me!
txtApptID) & "' & '*')"

Take a look at the SQL string you are getting here. If the textbox
contains (say) XYZ, this will evaluate to

SELECT tAppt.fApptID FROM tAppt WHERE ((tAppt.fApptID) LIKE '*' &
'XYZ' & '*')

In other words, you're mixing ' and " and mixing variables and string
constants. Easy to do! It's confusing!

I'd suggest just thinking of ' as a character like any other
character, and working backwards from the desired string: that is, for
XYZ you want the SQL string to be

SELECT tAppt.fApptID FROM tAppt WHERE ((tAppt.fApptID) LIKE '*XYZ*')

(assuming you want the extra parentheses which Access query designer
throws in).

You can build this up from three pieces: a string constant

"SELECT tAppt.fApptID FROM tAppt WHERE ((tAppt.fApptID) LIKE '*"

a variable (your Trim function call)

and another string constant

"*')"

so the entire expression would be

strSQL = _
"SELECT tAppt.fApptID FROM tAppt WHERE ((tAppt.fApptID) LIKE '*" _
& Trim(Me!>txtApptID) & "*)"

John W. Vinson [Access MVP]

 

[Dirk Goldgar]

I am trying to make a procedure that brings up an
appointment when the user enters any part of the ApptID.
I have an unbound field and a button. I am having trouble
coding the 'any part' part. I have tried to make an SQL
statement and then set the Form.RecordSource to that
statement but it does not seem to want to work.

Any suggestions?

Here is the SQL statement that I have:
ApptIDSQL = "SELECT tAppt.fApptID FROM tAppt " & _
"WHERE ((tAppt.fApptID) LIKE '*' & '" & Trim(Me!
txtApptID) & "' & '*')"
DoCmd.OpenForm "frmAppt", , , Form.RecordSource =
ApptIDSQL, acFormEdit

In addition can I set the recordsource from the
DoCmd,OpenForm Where statement? Or can I only do that on
the Load event of the form?

Thanks

Dan

You can't set the form's recordsource in the manner you're trying, but
you don't really need to set the form's recordsource anyway. You can
let frmAppt have its recordsource set to the table tAppt or to a simple
query that selects all records from tAppt, and use the OpenForm
statement to automatically filter it to show just the appointment(s) you
want; like this:

DoCmd.OpenForm "frmAppt", _
WhereCondition:="fApptID LIKE '*" & Me!txtApptID & "*'"