Trying to find a specific Record using VBA

[FormerJarHead]

I understood what you meant Orange...Just seemed like a lot of extra work when I could just as easily create an unbound combo box to look up the BK# field. I was just hoping to get something that looked a little more pleasing to the eye when they did hit a bump...

[orange]

I'm still here. I'm having difficulty seeing the extra load of work.

Consider going into your bank and asking for a loan for $10000.
Before they start filling in the application form, a standard operating procedure will be invoked that says something like --
hmm... before we get to that let's see what your current status and a little history shows.

So, the chaplain or someone says, before we add something new, Let's just check up on latest status (or last 10 services)
and then move from there based on what is found?

You could have a query or a button that invokes a query that will
-take the BookingNumber,
-identify the ClientId,
-pick up the latest (latest 10 or whatever) service records for this inmate based on ClientId.

Could be a separate Form with a list of items.


Just thinking as I type.

[ssanfu]

Thinking again...

Is the bookingnumber the first control data is entered into? Have you tried putting the code in the before update event of the bookingnumber control? You can check for duplicate, undo the entry, cancel the event if duplicate and move to the existing record.

[orange]

Good pick Steve, I didn't even look at which event was being discussed
BeforeUpdate is the one!

[ssanfu]

Just saw Orange's post. I agree... the first step should be to search for the bookingnumber. If found, goto the record (main form) to see the inmates details and a subform to see the tblReportCodeDetails data (visits, requests, etc).
If not found, goto a form to enter a new inmate....

[ssanfu]

@ FormerJarHead

I just looked at your code again. You are using the before update event. But you had a couple of errors in the code. And I made some changes.
You were missing an opening double quote and you didn't have the CANCEL command.

Try this:

Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
   Dim SID As String
   Dim stLinkCriteria As String
   Dim rsc As DAO.Recordset

   Set rsc = Me.RecordsetClone
   SID = Me.BookingNumber
   stLinkCriteria = "[BookingNumber] = " & "   '" & SID & "'"    '<<- look here

   'Check tblClient table for duplicate BookingNumber
   If DCount("*", "tblClientInfo", stLinkCriteria) > 0 Then
      'Undo duplicate entry
      Me.Undo
      Cancel = True
      'Message box warning of duplication
      MsgBox "Warning Booking Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
             vbInformation, "Duplicate Information"
      'Go to record of original Booking Number
      rsc.FindFirst stLinkCriteria
      'always check the NoMatch property after a Find
      If rsc.NoMatch Then
         ' record not found
         MsgBox "There should be a record"
      Else
         'go to the record
         Me.Bookmark = rsc.Bookmark
      End If
   End If
   Set rsc = Nothing

End Sub

[FormerJarHead]

Thanks fellas...I was stuck in a meeting the later half of the afternoon and was not able to view your posts. Steve, I'll give this a shot first thing Monday morning and see how things go. Tomorrow is a very special day to all Marines as we celebrate our 237th birthday. To all the men and women who are and have served, thank them!! Thanks fellas stay safe...until Monday!! Semper Fi...

[ssanfu]

To you and all your brothers-in-arms:

Thank you for your service.. can't be said enough!!

[FormerJarHead]

Happy Monday fellas...
Steve, I copied your code as written and it returned a Run Time Error 3464. Data Type Mismatch in Criteria Expression...?

[ssanfu]

I hate cut and paste!! Sorry

What line is causing the error??

If SID is a number, these lines should be

Dim SID As Long

stLinkCriteria = "[BookingNumber] = " & SID

Here is the full code:

Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
   Dim stLinkCriteria As String
   Dim rsc As DAO.Recordset
   Dim SID As Long
   
   Set rsc = Me.RecordsetClone
   SID = Me.BookingNumber
   stLinkCriteria = "[BookingNumber] = " & SID
   
   'Check tblClient table for duplicate BookingNumber
   If DCount("*", "tblClientInfo", stLinkCriteria) > 0 Then
      'Undo duplicate entry
      Me.Undo
      Cancel = True
      'Message box warning of duplication
      MsgBox "Warning Booking Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
             vbInformation, "Duplicate Information"
             
      'Go to record of original Booking Number
      rsc.FindFirst stLinkCriteria
      'always check the NoMatch property after a Find
      If rsc.NoMatch Then
         ' record not found
         MsgBox "There should be a record"
      Else
         'go to the record
         Me.Bookmark = rsc.Bookmark
      End If
   End If
   Set rsc = Nothing

End Sub

[FormerJarHead]

YOU ARE THE GURU!!!! Works like a champ.

[ssanfu]

Thanks..

Now if only I could fix my problems as easily...