Solved: Search For Existing Record

[JustJerry]

Hello Everyone,

I have a Sub that I've built that does several items. What I'm having troulbe with is the part that searches for an existing record, and if that record exists, to just GO to that record. For some reason it will not go. The VBA code does everything it should, except that instead of going to the existing record, it just sits on the existing record, and does not show the new record for the next month.

Here is my code:

Private Sub FinalizeMonth_Click()
Dim cmonthyr As Date 'Variable for current month year
Dim nmonthyr As Date 'Variable for next month year
Dim ctruck As String 'Variable for current TruckID
Dim cendst As String 'Variable for current End State
Dim intAnswerMe As Integer


cmonthyr = Me.MonthYr.Value 'Assign current month to variable
'Assign variable to be one month from current record
nmonthyr = DateAdd("m", 1, cmonthyr)
ctruck = Me.TruckID.Value 'Assign current TruckID to variable

'Verify finalization of record, this can not be undone.
intAnswerMe = MsgBox("Do you want to finalize record for " & ctruck & " for " & _
Format(cmonthyr, "mmm yyyy") & ". This WILL disable data " & _
"entry and create entry for next month", vbYesNo + vbCritical, _
"Verify Finalization!")

Select Case intAnswerMe
Case vbYes
If IsNull(Me.EndST) Then
intAnswerMe = MsgBox("Ending State Must Be Entered!", _
vbOKOnly + vbInformation, "Empty Field!")
DoCmd.GoToControl "EndST"

Else
cendst = Me.EndST.Value 'Assign current End State to variable
Me.Finalized.Value = True
Me.FinalizeLabel.BackColor = 255
Me.FinalizeLabel.Caption = "Month has been Finalized"
Me.ClosefrmMainMile.SetFocus

Me.FinalizeMonth.Enabled = False
Me.subfrmMileDetail.Enabled = False

'Extract mileage data to be calculated, store in temp table
DoCmd.OpenQuery "qryMileTotal"

'Calculate extracted mileage and store in temp table
DoCmd.OpenQuery "qryMileTotalCalc"

'Append calculated mileage and store in final table
DoCmd.OpenQuery "qryFinalTotalMile"

'Delete temp table's data ran by qryMileTotal
DoCmd.OpenQuery "qryDeletetblMileTotal"

'Delete temp table's data ran by qryMileTotalCalc
DoCmd.OpenQuery "qryDeletetblMileTotalDetail"

'Check to see if next month's record already exists.
'If So, open to that record.
'If not, open to new record

If Not IsNull(DLookup("[MileID]", "tblMainMile", _
"[TruckID]='" & Me![TruckID] & "' AND [MonthYr]=" & _
"#" & nmonthyr & "#")) Then

intAnswerMe = MsgBox("Record Exists For " & Format(nmonthyr, "mmm yyyy") & _
" Do you want to go to this record?", _
vbYesNo + vbQuestion, "Record Exists")

Select Case intAnswerMe
Case vbYes
Me.RecordsetClone.FindFirst "[MileID]=" & DLookup("[MileID]", _
"tblMainMile", "[TruckID]='" & Me![TruckID] & _
"' AND [MonthYr]=" & "#" & nmonthyr & "#")
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.EndST.SetFocus

Case vbNo
Me.ClosefrmMainMile.SetFocus
End Select

Else
intAnswerMe = MsgBox("Record does not exist for " & _
Format(nmonthyr, "mmm yyyy") & _
" Do you want to create a new record?", _
vbYesNo + vbQuestion, "No Record Exists")

Select Case intAnswerMe
Case vbYes
DoCmd.GoToRecord , , acNewRec

Me.TruckID.Value = ctruck
Me.MonthYr.Value = nmonthyr
Me.BeginST.Value = cendst
Me.EndST.SetFocus

Case vbNo
Me.ClosefrmMainMile.SetFocus
End Select
End If
End If

Case vbNo
intAnswerMe = MsgBox("Record Finalization has been canceled", _
vbOKOnly + vbInformation, "Cancel Finalization")
Me.ClosefrmMainMile.SetFocus
End Select
End Sub

This is the part that should go to the existing record:

Select Case intAnswerMe
Case vbYes
Me.RecordsetClone.FindFirst "[MileID]=" & DLookup("[MileID]", "tblMainMile", _
"[TruckID]='" & Me![TruckID] & "' AND [MonthYr]=" & "#" & nmonthyr & "#")
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.EndST.SetFocus

Any suggestions? Thank you

[OBP]

Are you sure that the Dlookup is returning the desired value?
Have you checked each section of the dlookup to make sure it returns something before adding the next?

[JustJerry]

Hello OBP,

Well, it's a strange thing. This WAS working once, before I added the Message box portions. But, I've checked over and over and nothing I did was different concerning the Dlookup portion. The first time it stopped working, I added the Me.EndST.Setfocus code, and it worked again.

I assume the Dlookup works still, or should, because I get the message box coming up that tells me the record exists, etc.

I've tried going this route as well: (condensed)

Set rst = Me.RecordsetClone
rst.FindFirst "[MileID]=" & DLookup("[MileID]", "tblMainMile", "[TruckID]='" & Me![TruckID]_
& "'And [MonthYr]=" & "#" & nmonthyr & "#")
If rst.NoMatch Then
intAnswerMe = MsgBox("Record Exists", vbOKOnly)
Me.Bookmark = rst.Bookmark

I get the Message box that says Record Exists, yet it will NOT go to the record. I'm just very confused right now.

Jerry

[alimcpill]

I'd be interested to hear if you solve this because I can't even replicate the problem. However, I'm sure this is just a typo, but in the code you just posted, it looks like the "Record Exists" message would appear if the record is not present (should be if not rst.NoMatch presumably...)

[JustJerry]

Actually, I tried using If Not rst.NoMatch, and it's completely the opposite, so I'm confused still. I use the same search string, right now anyway, from my Start Form. User selects a month, and a Truck ID, and the form opens to the specific form:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmMainMile"

stLinkCriteria = "[TruckID]='" & Me![cboTruckID] & "' AND [MonthYr]= #" & Me![SearchDate] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Same Idea sort of, it DOES open to the entered record, except that if the record doesn't exist, it opens anyway. I want to change THIS as well once I figure out the reason behind my original question.

WHY it worked before is beyond me!