Browse/add record based on Calendar (1 Viewer)

dodyryda · Apr 9, 2009

Hi..

hoping someone may be able to help What I am attempting to do is create a schedule database for our daily jobs.. My question relates to the calendar active x control. Currently when I click the calendar it updates my job date field and finds the next working day excluding a list of holiday dates. What I would like to do is when a user clicks the calendar (see attached pic), access will query for the selected job date from the database and if present display the data in my related subform else if this date is not present create a new record.. ..my vba skills are noob..

I would upload database but as it is access 2007 file type is not accepted..

dodyryda · Apr 9, 2009

what I have currently ..attached

dodyryda · Apr 9, 2009

think I'm way off here but this is what I have so far when the calendar is clicked.. get sql errros..

Code:

Private Sub Calendar5_Click()

Me![Job Date] = Me!Calendar5.Value
Me![Job Date].Requery
Me![NextBusinessDay] = GetBusinessDay([Job Date], 1, "23456", 1, "Holidays", "Holiday Dates")
Me.Refresh

Dim rs As DAO.Recordset
Dim sql As String
sql = "SELECT * FROM Calendar INNER JOIN [Job Spec] ON Calendar.[ID] = [Job Spec].[ID] WHERE (((Calendar.[Job Date])= " & Me![Job Date].Value & "));"
Set rs = CurrentDb.OpenRecordset("Calendar", dbOpenSnapshot)
 If rs.BOF And rs.EOF Then 'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE
 rs.AddNew
  rs![Job Date] = [Forms]![Main]![Job Date]
  rs![NextBusinessDay] = [Forms]![Main]![NextBusinessDay]
  rs.Update
 Else '
   Do Until rs.EOF 'DO UNTIL END OF RECORDSET
'BELOW MIGHT NOT BE THE CONDITION YOU ARE AFTER, EXAMPLE ONLY
If rs![Job Date] = [Forms]![Main]![Job Date] Then 'NOTE FIELD NAMES ARE REFERENCED BY ![]
DoCmd.RunSQL sql
Else
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![Job Date]
  rs![NextBusinessDay] = [Forms]![Main]![NextBusinessDay]
  rs.Update
End If
rs.MoveNext 'THIS MOVES TO THE NEXT RECORD IN THE RECORDSET
Loop
'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
End If

End If

End Sub

dodyryda · Apr 14, 2009

Hi .. Ive been playing around and nearly there but keep getting a no current record error when i hit rs.movenext can anyone suggest a possible fix?

Code:

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

Dim rs As DAO.Recordset
Dim sql As String
sql = "SELECT * FROM Calendar INNER JOIN [Job Spec] ON Calendar.[ID] = [Job Spec].[ID] WHERE (((Calendar.[Job Date])= " & Me![JD] & "));"
Set rs = CurrentDb.OpenRecordset("Calendar", dbOpenDynaset)
rs.MoveFirst
If rs.BOF And rs.EOF Then
  rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
  Else
     Do While Not rs.EOF
    If rs![Job Date] = [Forms]![Main]![JD] Then 'NOTE FIELD NAMES ARE REFERENCED BY ![]
    Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
    End If
rs.MoveNext 'THIS MOVES TO THE NEXT RECORD IN THE RECORDSET
Loop
End If
If rs.EOF Then
rs.AddNew
  rs![Job Date] = [Forms]![Main]![JD]
  rs![NextBusinessDay] = [Forms]![Main]![NBD]
  rs.Update
  Me.Requery
End If

'CLEAN UP MEMORY AT END
If Not rs Is Nothing Then
   rs.Close
   Set rs = Nothing
End If
End Sub

Browse/add record based on Calendar (1 Viewer)

dodyryda

Registered User.

dodyryda

Registered User.

Attachments

dodyryda

Registered User.

dodyryda

Registered User.

Similar threads

Users who are viewing this thread