You have been a great help and I feel like we are nearing the end of our
journey, but I am still getting an error. When I say that I want to add a new
record I get Run-time error '3077':
Syntax error in string expression
The debugger highlights
.FindFirst "[VetSSN] = """ & Me.cboVetSearch & """"""
:
Yes, you corrected my syntax error.
--
Dave Hargis, Microsoft Access MVP
:
I fixed where you had tblVetarn to tbleVeteran. I am still getting the syntax
error. If I change it to ORDER BY instead of ORDERY BY it seems to work. Will
this be ok?
:
I'm getting a syntax error in the from clause when I change from design to
form view.
:
That helps. We need to make some changes.
The query:
VetSSN (text) PK
VetLastName (text)
VetFirstName (text)
SELECT VetSSN, VetLastName & ", " & VetFirstName FROM tblVetarn ORDERY
BY VetLastName, VetFirstName;
Private Sub cboVetSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[VetSSN] = """ & Me.cboVetSearch & """""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
Private Sub cboVetSearch_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
Me.cboVetSearch.Undo
strSQL = "INSERT INTO tblVetarn ( VetSSN )SELECT """ & NewData & """
AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[VetSSN] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboVetSearch.Undo
Response = acDataErrContinue
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
It would probably be helpful if I gave you the table structures and field name.
I admit that what I'm trying to do is ambitious for someone of my limited
background, but I'd like to learn to do it.
tblConsult
ConsultNumber (text) PK
CreatedDate (date)
ReceivedDate (date)
Reason (text)
VetSSN (text)
VendorID (number)
tblVeteran
VetSSN (text) PK
VetLastName (text)
VetFirstName (text)
VetDOB (date)
tblVendor
VendorID (autonumber) PK
VendorName (text)
VendCity (text)
VendState (text)
VendPhone (text)
tblDoctor
DoctorID (autonumber) PK
DocLast (text)
DocFirst (text)
DocType (text)
DocPhone (text)
tblAppointment
AppointmentID (autonumber) PK
AptType (text)
AptDate (date)
Complete (Y/N)
Notes (text)
ConsultNumber (text)
DoctorID (number)
VendorID (number)
:
Great, let me know if you have questions or problems.
--
Dave Hargis, Microsoft Access MVP
:
I am working on your solution. It looks better. I'll keep you posted.
:
Your users will love you if you use a Combo Box control to do the searching.
It is naturally suited to that. It will present a list of the possible
selections and they can either scroll through them or start typing until they
get a match. There is also provisions for handling a situation where the
user has typed in an entry that is not in the combo's list.
To set up a combo box, it needs a row source. This is usually a query that
will include one or more columns to present a readable list to the user and
to provide a value for the code to locate the record. In your example, you
are looking for an SSN. It also appears you will have a student name. For
example purposes, I will assume you have a table named tblStudent and a text
field named SSN that is the unique primary key field for the table, and a
text field named StudentLastName and a text field name StudentFirstName. You
would have a query like this:
SELECT SSN, StudentLastName & ", " & StudentFirstName FROM tblStudent ORDERY
BY StudentLastName, StudentFirstName;
Now we need to set some properties for the combo box.
Name: cboStudentSearch
Row Source Type: Table/Query
Row Source: the above query
Column Count: 2
Column Widths: 0"; 3" (the 0 will hide the SSN, modify the 3" as needed
Bound Column: 1
Limit To List: Yes
Now when the user enters a student's name, you use the combo's After Update
event to locate the record. If the record does not exist, the Not In List
event will fire.
Private Sub cboStudentSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[SSN] = """ & Me.cboStudentSearch & """""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
Now, if the record doesn't exist, we need to tell the user. This code
example will also allow the user to add the SSN to the table and make the new
record the current record so they can complete the student information:
Private Sub cboStudentSearch_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
Me.cboStudentSearch.Undo
strSQL = "INSERT INTO tblStudent ( SSN )SELECT """ & NewData & """
AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[SSN] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboStudentSearch.Undo
Response = acDataErrContinue
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
I have a form that I have coded with a search feature. Currently when the
search doesn't find a record it just shows the first record. When that
happens I would like a prompt to ask if the user would like to enter a new
record. If the user selects yes then the form would show blank allow them to
enter the new record.
The search code below is from
www.databasedev.co.uk
This is coded to the on click event of the search button
Private Sub cmdSearchButton_Click()
Dim strStudentRef As String
Dim strSearch As String
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
DoCmd.ShowAllRecords
DoCmd.GoToControl ("strSSN")
DoCmd.FindRecord Me!txtSearch
strSSN.SetFocus
strStudentRef = strSSN.Text
txtSearch.SetFocus
strSearch = txtSearch.Text
If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
strSSN.SetFocus
txtSearch = ""
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", ,
"Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
Any help is greatly appreciated.
Thanks,
Patrick
