I got a bit of a problem with the search function using queries. Now I have a unbound form to display a query results, and a unbound text box where you enter the text in to search - this auto updates the query of the results - the working sample was posted by a user here ref "cool serach tool" - and the code:
Option Compare Database
Option Explicit
Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt
DoCmd.Requery
Me.RecordsetClone.FindFirst "[Name] = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If
End Sub
Private Sub Search_Change()
Dim vSearchString As String
But here is the problem, I have tweaked it to queries all current live records using the yes/no funtion by adding True in the critrea of new Coulum that extracts only the up the current "live data" that has yes on it, When i type in the text field the code I am looking at its returning the correct result in the unbound form, but when I click on it to show the results in th form it is showing the information for the delisted ingredient instead of the current live ingredent!! - both sharing the same ingredient code, but different ID codes. The Primary key (ID Code) is done by autonumber - what is going wrong and how do I stop it?
My Coding on the form:
Public Sub SendMail()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer
strSubject = "Latest Job Outcomes"
strEmailAddress = "[Mail Addresses Go Here]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")
If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub
Else
rst.MoveFirst
Do Until rst.EOF
strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
& "Below are the details that have been submitted by the student:" _
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
& "Graham"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1"
WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
DoCmd.SetWarnings True
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
End If
End Sub
Option Compare Database
Option Explicit
'ghudson 11/27/2002
'For those of us not using Access XP, we have a challenge to prevent
'our users from advancing to another record if they do not use the controls
'we want them to use or to prevent them from bypassing our validation
'procedures too ensure the current record is okay to be saved.
'The trick in my form is the value in the tbProperSave text box. The user will
'not be able to advance to another record using their scrolling mouse wheel nor
'will they be able to use the Page Up or Page Down keys nor will they be able
'to use the Shift-Enter keys to save the record. The user is forced to save
'or undo the modified record using my custom save or undo buttons before
'they can advance to another record or before they can close the form.
Private Sub bQuit_Click()
On Error GoTo Err_bQuit_Click
Me.tbHidden.SetFocus
'Prompts the user to save the current record if it needs to be saved.
If Me.Dirty Then
Beep
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not close this form until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
Else
'DoCmd.OpenForm "fMainMenu"
DoCmd.Close acForm, Me.Name
End If
Private Sub bSave_Click()
On Error GoTo Err_bSave_Click
Me.tbHidden.SetFocus
If IsNull(FirstName) Then
Beep
MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"
Exit Sub
End If
Beep
Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & " Yes: Saves Changes" & vbCrLf & " No: Does NOT Save Changes" & vbCrLf & " Cancel: Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Save Current Record?")
Case vbYes: 'Save the changes
Me.tbProperSave.Value = "Yes"
DoCmd.RunCommand acCmdSaveRecord
Case vbNo: 'Do not save or undo
'Do nothing
Case vbCancel: 'Undo the changes
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "No"
Case Else: 'Default case to trap any errors
'Do nothing
End Select
Exit_bSave_Click:
Exit Sub
Err_bSave_Click:
If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_bSave_Click
End If
End Sub
Private Sub bUndo_Click()
On Error GoTo Err_bUndo_Click
Me.tbHidden.SetFocus
'Resets the record if it has been modified by the user.
If Me.Dirty Then
Beep
DoCmd.RunCommand acCmdUndo
Me.tbProperSave.Value = "No"
Else
Beep
MsgBox "There were no modifications made to the current record.", vbInformation, "Invalid Undo"
End If
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Me.tbHidden.SetFocus
If Me.tbProperSave.Value = "No" Then
Beep
MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
DoCmd.CancelEvent
Exit Sub
End If
Exit_Form_BeforeUpdate:
Exit Sub
Err_Form_BeforeUpdate:
If Err = 3020 Then 'Update or CancelUpdate without AddNew or Edit
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_Form_BeforeUpdate
End If
End Sub
Private Sub Form_Current()
On Error GoTo Err_Form_Current
= '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If
End Sub
Private Sub QuickSearch_Click()
End Sub
Private Sub Search_Change()
Dim vSearchString As String
vSearchString = Search.Text
Search2.Value = vSearchString
Me.QuickSearch.Requery
End Sub
Private Sub Search_Click()
End Sub
[/quote]
Thanks
