Please Help: Browse to Record VBA

[dodyryda]

Hi..

I really need some help with my desperate vba skills!. When I click a date on the calendar control on my form a new record is added to the database, this works great. Trouble I have is when I click a date I have already entered I am trying to get the database to retrieve the record and display it on my form by searching through my calendar table. The code I have is below. with comments where i'm loosing it!... the sql select is the fields i need to display on my form but unsure how to link them together.. I've uploaded the db at as it was too large to upload here... http://rapidshare.com/files/221951336/drive_schedule.mdb.html

can anyone help?... Many thanks

Dim rs As DAO.Recordset
Dim sql As String
sql = "SELECT [Job Spec].Branch, [Job Spec].[Deliver Time], [Job Spec].[Job Type], [Job Spec].[Company Name], [Job Spec].[Site Location], [Job Spec].Equipment, [Job Spec].[Purchase Goods], [Job Spec].Driver FROM Calendar INNER JOIN [Job Spec] ON Calendar.ID = [Job Spec].ID WHERE (((Calendar.[Job Date])= " & Me![JD] & "));"
Set rs = CurrentDb.OpenRecordset("Calendar", dbOpenDynaset)
If rs.BOF And rs.EOF Then
 rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Me.Refresh
  Else
    rs.MoveFirst
    Do While Not rs.EOF
        If rs![Job Date] = [Forms]![Main]![JD] Then 
        CurrentDb.OpenRecordset(sql, dbOpenDynaset) '*** Open the sql select fields on my form called main How????***
        Me.Refresh
        Else
     rs.MoveNext 
        End If
    Loop
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Me.Refresh
End If

 

[Kafrin]

The way Access usually sends you to a specified record is using bookmarks and the RecordsetClone.

1) Identify the record you want to go to.
2) Find it in the RecordsetClone.
3) Match the form to the RecordsetClone.

The code you need is:

With Me.RecordsetClone

.FindFirst "RecordID=" & the record ID
If Not .NoMatch Then

Me.Bookmark = .Bookmark
Me!MyControl.SetFocus

End If

End With

 

[dodyryda]

Hi Kafrin..

thanks for the tip actually had just been looking at it this way.. code I have is shown below but at .NoMatch (add a new record). I'm getting errors relating to duplicating my indexed record (job date)... any suggestions?

Dim rs As Object
Set rs = Me.Recordset.Clone
If rs.BOF And rs.EOF Then
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Me.Refresh
Else
  rs.FindFirst "[Job Date]=" & Me![JD]
   If rs.NoMatch Then
   rs.AddNew
    rs![Job Date] = [Forms]![Main]![JD]
    rs![NextBusinessDay] = [Forms]![Main]![NBD]
    rs.Update
    Me.Requery
    Me.Refresh
   Else
    Me.Bookmark = rs.Bookmark
    Me![Subform]![Branch].SetFocus
   End If
End If

 

[Kafrin]

Is [Job Date] a numeric field? If not then your FindFirst needs changing; the problem is the Find isn't finding the record but it does exist. If [Job Date] is in fact a date then I think you need:

rs.FindFirst "[Job Date]=#" & Format(Me![JD], "m/d/yy") & "#"

If it is a string instead you need apostrophes in stead of hashes and you don't need the Format part:

rs.FindFirst "[Job Date]='" & Me![JD] & "'"

 

[dodyryda]

Nailed it finally to anyone thats interested : thanks kafrin for the help

Private Sub Calendar5_Click()
Me![JD] = Me!Calendar5.Value
Me![NBD] = GetBusinessDay([JD], 1, "23456", 1, "Holidays", "Holiday Dates")
Me.Refresh

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
If rs.BOF And rs.EOF Then
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Me.Refresh
Else
  rs.FindFirst "[Job Date] = " & "'" & Me![JD] & "'"
   If rs.NoMatch Then
    rs.AddNew
    rs![Job Date] = Me![JD]
    rs![NextBusinessDay] = Me![NBD]
    rs.Update
    Me.Requery
    rs.MoveLast
    Me.Bookmark = rs.Bookmark
    Me.Refresh
   Else
    Me.Bookmark = rs.Bookmark
    Me.Refresh
   End If
End If
    
'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
End If

ExitProc:
   Exit Sub
ProcError:
   MsgBox "Error: " & Err.Number & ". " & Err.Description
   Resume ExitProc
End Sub

 

[dodyryda]

thanks kafrin.. nailed it finally using code below. you were right.. but instead i just set the field to a text value in the table rather than use the code above.. nice one thanks!

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
If rs.BOF And rs.EOF Then
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Me.Refresh
Else
  rs.FindFirst "[Job Date] = " & "'" & Me![JD] & "'"
   If rs.NoMatch Then
    rs.AddNew
    rs![Job Date] = Me![JD]
    rs![NextBusinessDay] = Me![NBD]
    rs.Update
    Me.Requery
    rs.MoveLast
    Me.Bookmark = rs.Bookmark
    Me.Refresh
   Else
    Me.Bookmark = rs.Bookmark
    Me.Refresh
   End If
End If

 

[dodyryda]

solved thanks Kafrin... set job date field back to a text only field and used code below

Private Sub Calendar5_Click()
Me![JD] = Me!Calendar5.Value
Me![NBD] = GetBusinessDay([JD], 1, "23456", 1, "Holidays", "Holiday Dates")
Me.Refresh

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
If rs.BOF And rs.EOF Then
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Me.Refresh
Else
  rs.FindFirst "[Job Date] = " & "'" & Me![JD] & "'"
   If rs.NoMatch Then
    rs.AddNew
    rs![Job Date] = Me![JD]
    rs![NextBusinessDay] = Me![NBD]
    rs.Update
    Me.Requery
    rs.MoveLast
    Me.Bookmark = rs.Bookmark
    Me.Refresh
   Else
    Me.Bookmark = rs.Bookmark
    Me.Refresh
   End If
End If
    
'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
End If

ExitProc:
   Exit Sub
ProcError:
   MsgBox "Error: " & Err.Number & ". " & Err.Description
   Resume ExitProc
End Sub

 

[Kafrin]

Glad I could help