Hello All,

I am in the process of developing a simple database that will be used for tracking personnel scheduling. I have only two tables:

Employee Table
    -Autonumber for EmpID (PK)
    -This table holds all Employee specific data
Calendar Table
    -Autonumber for EntryID (PK)
    -EmpID(FK)
    -This table holds calendar dates input from the scheduling forms as well as status (Unavailable, Available)

My problem lies with the Scheduling form. I want to make it easy to use so I decided to use the calendar control feature for the selection of the Date. The form has two parts:

-Main (Displays Employee Name, Hidden RepID, locatio0n etc)

-Subform (Houses the calendar control and has data entry fields for EntryID(Hidden), Date, Available (Chkbx), Unavailable(chkbx)

What I would like the user to be able to do is to click on date on the calendar and the following process would occur:

    -1-VBA code will check to see if there is a record in the Calendar table with the same EntryID, EmpID, and Date

    -2-If True (There is a matching record) then Find and pull up that record in the form for editing

    -3-If False (There is no matching record) then go to a new record, populate date field with the date that was clicked on the control,populated the RepID_Bx with the repID value from the main form, thus the auto number EntryID will be created. At this point the user can select the status (Available/Unavailable)


I have a little bit of a coding background, but my experience is limited so all help is greatly appreciated.

Here is a sloppy bit of code that I have been hacking at. It is really poor code, because I am not used to coding behind forms so I feel a mental block when designing.



NOTE: The If condition in the second section is incomplete as I am only trying to check agains EntryID (this is wrong). I was going to try using an array to look for matching records but am not quite sure how to set that up. I ran some tests with stops in the code just to see what was going on but naturally it would only pull up the EntryID


<<
Private Sub Calendar_Click()

<-- This section populates the date field with the date clicked on the calendar control
If Me.Dirty = True Then
    Me.Dirty = False
End If
-->
<--This is the section I am hacking at to get the control to function as described above
If Me!EntryID <> [Calendar!EntryID] Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.FindRecord Me!EntryID, acEntire, True, acSearchAll, , acAll, True
Me!Date.Requery
Me!Unavailable.Requery
Me!Available.Requery
End If
End Sub
-->
>>

Hey All,

Here is an udate on where I am at. I figured out a possible way to search the Calendar table to see if a Date already exists for a repID by using a Dlookup. I forgot that it will allow me to search for a field value using a set of criteria. However I am getting a type mismatch now. I have a feeling it is due to the fact that I am using table values along with a form value (RepID_Bx). Is there a way around this? Am I moving in the right direction?

Here is the code:
<--
Private Sub Calendar_Click()


Me!EntryID.Requery
If Me.Dirty = True Then
    Me.Dirty = False
End If
If Me!Date <> DLookup([Calendar]![Date], Calendar, Forms![Scheduling]![RepID_bx] = [Calendar]![RepID]) Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.FindRecord Me!EntryID, acEntire, True, acSearchAll, , acAll, True
Me!Date.Requery
Me!Unavailable.Requery
Me!Available.Requery
End If
End Sub
-->

Hey All,

Another update. I realized I did not take into account if the dlookup produced a null value so I added it to the code. I also updated the Dlookup expression and managed to sort out the type mismatch. Should I be putting this in a Watch statement to catch the Null values?

Also I am having a new problem it seems that the only value I am getting in my Dlookup is the RepID and not date. I dont even get a value of null for date any suggestions?

Private Sub Calendar_Click()
Me!EntryID.Requery
If Me.Dirty = True Then
    Me.Dirty = False
End If
If Me!Date_bx <> DLookup("[Date]", "Calendar", "Forms![Scheduling]![RepID_bx] = '" & [RepID] & "'") Then
DoCmd.GoToRecord , , acNewRec
Else
If Me!Date_bx <> DLookup([Date], Calendar, Forms![Scheduling]![RepID_bx] = [RepID]) Is Null Then
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.FindRecord Me!EntryID, acEntire, True, acSearchAll, , acAll, True
Me!Date.Requery
Me!Unavailable.Requery
Me!Available.Requery
End If
End If
End Sub

Correction I AM getting a value for [Date] which is 4/12/2007 but then I am getting 4/14/2007 for "Calendar" which is the table??? That is the same value for what is in Date_bx. What's going on here?

Ok I think I fixed the first part where it is looking for a record that already exists even though [Date] and "Calendar are different dates. We'll see how it goes, but now I need to sort out a way to find the record for the date selected. The only values I have to use for a look up are the RepID and the Date. This would work fine for a dlookup expression but that won't help since I believe I have to use DoCmd.FindRecord to find a record. I wonder if I can use a Dlookup() to find the EntryID and then pass the entryID to a variable which I can use in the FindRecord? Any suggestions?

Thanks,
Kenneth

Ok last post for the day.

New status:

Code Compiles
Still weird thing happening with [Date] and "Calendar"
No errors during run-time
Set up dlookup to give a variable the EntryID to FindRecord


New Problems:
When a date is clicked only the date box changes
The EntryID Box (I unhide to see what is populated) does not update)
The check boxes do not update

This all indicates two things:
     -The If statement to check for existing is not working
     -And the FindRecord function is not working

I know this because even when I click on a date I know does not exist in the table, nothing happens other than the Date box being updated. And when I click on a date I do know exists in the table the record does not come up. Any suggestions??

Latest Code:

<--
Option Compare Database

Private Sub Calendar_Click()
Dim vart As Variant
Dim Entry_ID As Integer

Me!EntryID.Requery
If Me.Dirty = True Then
    Me.Dirty = False
End If
If Me!Date_bx <> DLookup("[Date]", "Calendar", "Forms![Scheduling]![RepID_bx] = '" & [RepID] & "'") Then
DoCmd.GoToRecord , , acNewRec
Else
vart = DLookup("[EntryID]", "Calendar", "Forms![Scheduling]![RepID_bx] = '" & [RepID] & "' AND Forms![Scheduling]![Date_bx]= '" & [Date] & "'")

If Not IsNull(vart) Then
   EntryID = vart
End If
DoCmd.FindRecord EntryID, acEntire, True, acSearchAll, , acAll, True
End If
Me!Date_bx.Requery
Me!Unavailable.Requery
Me!Available.Requery
End Sub
-->



Thanks!

Ok I got the Dlookup function to return the correct result for me. It turns out that it was pulling the current date because my field name was [Date]. So I modified the field in the table to [Datefld] and renamed the table Calendartbl. I did not realize this was necessary, but now it is returning the correct date based on the criteria I am giving it.

New problem:
Even when If statement for adding a new record should validate to False and skip to findrecord, it seems to validate to true every time and adds a new record???? Here's the code:


If Me!Date_bx <> DLookup([Datefld], "Calendartbl", [RepID] = Forms![Scheduling]![RepID_bx] And [Datefld] = [Forms]![Scheduling]![Calendar_subform].[Form]![Date_bx]) Then
DoCmd.GoToRecord , , acNewRec
Else
vart = DLookup("[EntryID]", "Calendartbl", Forms![Scheduling]![RepID_bx] = [RepID] And Me!Date_bx = [Datefld])
If Not IsNull(vart) Then
Entry_ID = vart
End If
DoCmd.FindRecord EntryID, acEntire, True, acSearchAll, , acAll, True
End If



I decided to assign the dlookup value to a variable to see what the dlookup was being evaluated as using the code below:

Private Sub Calendar_ctl_Click()
Dim strSQL As String

strSQL = DLookup([Datefld], "Calendartbl", [RepID] = Forms![Scheduling]![RepID_bx] And [Datefld] = [Forms]![Scheduling]![Calendar_subform].[Form]![Date_bx])
If Me.Dirty = True Then
    Me.Dirty = False
End If
If Me!Date_bx <> strSQL Then
DoCmd.GoToRecord , , acNewRec
Else
vart = DLookup("[EntryID]", "Calendartbl", Forms![Scheduling]![RepID_bx] = [RepID] And Me!Date_bx = [Datefld])
If Not IsNull(vart) Then
   Entry_ID = vart
End If
DoCmd.FindRecord EntryID, acEntire, True, acSearchAll, , acAll, True
End If
End Sub




The value of strSQL is 1.24564025909317E -04 ?????????

Fixed the Scientific notation problem by putting " " around [Datefld] guess I needed to make sure it was taken from the Dlookup as a string before assigning the value to strSQL (didn't bother renaming it after trying to use SQL to get the value).

I am trying to use GotoRecord instead of FindRecord using this code:

DoCmd.GoToRecord , Calendartbl.EntryID, acGoTo, vart

But I am positive that I do not have it set up correctly, and I still can't get the GoToRecord to work the way it should. Please help.

Thanks,
Ken

Ok in recess from trying to get the GotoRecord to work I discovered another issue. I decided to seperate the code that updates the Date_bx and the code that goes to a record of creates a new record. Now when I click the Calendar it just updates the Date_bx control for the current record, and when you click the date box it runs the second code. The problem with that is that the user would be changing the date of a record if a current record was up and not a blank record, and when you click date boz it keeps the new record already created and creates a new one with noe date. I hope this issue makes sense. I know it has to do with the structure of the code I just don't know the proper way to set it up.

When I put the code in the Calendar click section it creates a new record but the Date_bx control is not populated with a date, and the last record that was edited inherits the newly clicked calendar date.


Here's the latest code:

Private Sub CalendarCtl_Click()
If Me.Dirty = True Then
    Me.Dirty = False
End If


End Sub
Private Sub Date_bx_Click()
Dim strSQL As Variant

strSQL = DLookup("[Datefld]", "Calendartbl", [RepID] = Forms![Scheduling]![RepID_bx] And [Datefld] = [Forms]![Scheduling]![Calendar_subform].[Form]![Date_bx])

If Me!Date_bx <> strSQL Then
DoCmd.GoToRecord , , acNewRec
Else
vart = DLookup("[EntryID]", "Calendartbl", [RepID] = Forms![Scheduling]![RepID_bx] And [Datefld] = [Forms]![Scheduling]![Calendar_subform].[Form]![Date_bx])
If Not IsNull(vart) Then
Entry_ID = vart
End If
DoCmd.GoToRecord acActiveDataObject, "Calendar_subform", acGoTo, vart
End If

End Sub

Ok now I am really close. I can now see that the condition to look for existing records in functioning because if it exists it tries to go to the record(if I specify the index id in the offset the form goes to that record).

The problem now is that I can not set a Where condition using GoToRecord. I would need it to set a condition to find the record who's entryID = vart (the entryID of the current record on the form). The reason I'd need to do it this way is because the entryID is an auto number. The only other solution would be to find a way to grab the index number of the record and pass that value into vart which would go to the correct record.


Here's the latest code:

Private Sub CalendarCtl_Click()

Dim strSQL As Variant
Dim vart As Variant

If Me.Dirty = True Then
    Me.Dirty = False
End If


strSQL = DLookup("[Datefld]", "Calendartbl", [RepID] = Forms![Scheduling]![RepID_bx] And [Datefld] = [Forms]![Scheduling]![Calendar_subform].[Form]![Date_bx])

If Me!Date_bx <> strSQL Then
DoCmd.GoToRecord , , acNewRec

Else
vart = DLookup("[EntryID]", "Calendartbl", [RepID] = Forms![Scheduling]![RepID_bx] And [Datefld] = [Forms]![Scheduling]![Calendar_subform].[Form]![Date_bx])
If Not IsNull(vart) Then
   Entry_ID = vart
End If
DoCmd.GoToRecord , , acGoTo, [EntryID] = vart <--- Can't Do This
End If

End Sub