Access form problem

[ebo2006]

Hi. I am relatively new in MS Access. I want to make an entry/edit form to enter the number of hours a person took off for a given day. In the form I have a person search button which I am able to choose the person from a tblPerson table, and a calendar to get a specific date. The table I want to update is tblHoursOff which contain the PersonID, DateOff, ( both are part of the primary key), HoursOff, Reason, Comment fields.

My problem is how to use the PersonID from the person search and the date I got from the calendar to show the record from the tblHoursOff if it is existing or enter a new record if not in it yet.

[MMcCarthy]

The easiest way is to create a button. Lets call it cmdAddPerson.

Then you need the following code:

 
Private Sub cmdAddPerson_OnClick()
Dim pID As Integer
 
	pID = Dlookup("[PersonID]","tblHoursOff","[DateOff]=" & Me.DateOnForm)
 
	If pID = 0 Or IsNull(pID) Then
		strSQL = "INSERT INTO tblHoursOff (PersonID, DateOff, HoursOff, Reason, Comment) " & _
						"VALUES (" & Me.PersonID, Me.DateOnForm, Me.HoursOff, Me.Reason, Comment & ");"
		DoCmd.RunSQL
	Else
		MsgBox " This is already entered", vbOkOnly
	End If
 
End Sub

I don't know what the textboxes on your form are called so I just used the field names. Change them as appropriate.

[ebo2006]

Thanks, mmccarthy. I'll try it out and will let you know the result. Thanks again.

[ebo2006]

Hi mmccarthy. Thanks agian. I placed your statements in. It works, but it pops up a window asking for a date. Sorry I didn’t make myself clear. Maybe, I should give more detailed specs. I have these tables and forms:

Tables.
tblPeople with the following fields PeopleID - autonum primary key, CheckinID, LastName, FirstName, Super, Type.

tblOffDetail with ChekinID , DateOff (these two make up the primary key), OffHours, OffType, Comments.

Forms:
frmPeople using tblPeople fields and a command button cmdPersonSearch to open another frmSearchPeople form when clicked.

frmSearchPeople . Used in frmPeople to select the specific person by typing the letters of the last name.

frmDateDropDown . Used in frmPeople to select the date using a calendar control. It has a Text2 textbox where the delected date is passed.

sfrmOffDetail is subform in the frmPeople with same fields as the tbleOffDet. This is linked to the main form by the CheckinID. So when I choose a record in the tblPeople records associated with it displays in the sub form.

My problem is how to use the date in Text2 in frmDateDropDown to show only the records for the current person for the selected date in the subform. Hope it is much clearer now. Thanks.

[MMcCarthy]

re my original code if you change

Me.DateOnForm

to

Me.Text2

it would work

Anyway ...

What you are trying to do is to filter records on a subform. Each subform has a name property as well as its name as a form. You will need to go to the other tab in the property list for the subform to find out what that is. I'm using the form name for now.

use a command button to set the filter.

Me.sfrmOffDetai l.Form.Filter = "DateOff=" & Me.frmDateDropD own.Form.Text2
Me.sfrmOffDetai l.Form.FilterOn = True

[ebo2006]

Thank you. I am learning a lot.

Here is the script in on click event procedure with names in the Other tab of the forms. However it is telling me that it is giving me the message "You didn't specifity search criteria with a FindRecord Action.

Private Sub cmdGetRecord_Cl ick()
Me.sfrmOffDetai l.Form.Filter = "DateOff=" & Me.frmDateDropD own2.Form.Text2
Me.sfrmOffDetai l.Form.FilterOn = True

[MMcCarthy]

Is frmDateDropDown 2 a subform of frmPeople or a popup form.

Thank you. I am learning a lot.

Here is the script in on click event procedure with names in the Other tab of the forms. However it is telling me that it is giving me the message "You didn't specifity search criteria with a FindRecord Action.

Private Sub cmdGetRecord_Cl ick()
Me.sfrmOffDetai l.Form.Filter = "DateOff=" & Me.frmDateDropD own2.Form.Text2
Me.sfrmOffDetai l.Form.FilterOn = True

[ebo2006]

Hi.Sorry for the late reply, been side tracked due to end and beginning of month tasks at work.
Anyways, frmDateDropDown 2 (source object is frmDateDropDown ) form with in frmPeople. It ahs the following objects: cmdCal- command button to show (pop up) the calendar calCtl1 where I choose the date, and and text2 where the date is passed to. So, I frmDateDropDown is subform with a pop calendar. Hope this helps.

[MMcCarthy]

Thank you. I am learning a lot.

Here is the script in on click event procedure with names in the Other tab of the forms. However it is telling me that it is giving me the message "You didn't specifity search criteria with a FindRecord Action.

Private Sub cmdGetRecord_Cl ick()
Me.sfrmOffDetai l.Form.Filter = "DateOff=" & Me.frmDateDropD own2.Form.Text2
Me.sfrmOffDetai l.Form.FilterOn = True

Try this:

 
Private Sub cmdGetRecord_Click()
  Me.sfrmOffDetail.Form.Filter = "DateOff=" &  Forms![frmPeople]![frmDateDropDown2].Form![Text2]
Me.sfrmOffDetail.Form.FilterOn = True

[ebo2006]

Hi, I tried it. It is giving me the same error message. Is it possible to send you the access file so you can look at it? Let me know.

[MMcCarthy]

Hi, I tried it. It is giving me the same error message. Is it possible to send you the access file so you can look at it? Let me know.

In the design view of frmPeople click on the frame of one of the subforms and open the properties window. Under the other tab what is in the Name property. Is it what you expected? Now do the same with the other subform.

[ebo2006]

Hi.

Subform sfrmOffDetail has Property Name sfrmOffDetail

and for subform frmDateDropDown the Property Name is frmDateDropDown 2.

What I expect to happened is that in the subfrom sfrmOffDetail the records from tblOffDet table (correction: the for this table is compound key is CheckInID and Dateoff; not PeopleID and DateOff) for the person with CheckInID I got from people search, with date (text2) I got in frmDateDropDown

[Killer42]

Try this:

Private Sub cmdGetRecord_Click()
  Me.sfrmOffDetail.Form.Filter = [B]"DateOff=" &  Forms![frmPeople]![frmDateDropDown2].Form![Text2][/B]
Me.sfrmOffDetail.Form.FilterOn = True

Is it possible that we're simply missing "#" delimiters around the date here? Perhaps this would work...

Private Sub cmdGetRecord_Click()
  Me.sfrmOffDetail.Form.Filter = [B]"DateOff=#" &  Forms![frmPeople]![frmDateDropDown2].Form![Text2] & "#"[/B]
Me.sfrmOffDetail.Form.FilterOn = True

[NeoPa]

I know one can usually add delimiter characters in SQL by including them in the literal strings

MyField = "WHERE [MyDate]=#" & Format(MyDate,'m/d/yyyy') & "#"

but there are sometimes situations where you want to include the delimiter chars within the string returned from the Format() function. To do this use the escape character '\' before your delimiters.

MyField = "WHERE [MyDate]=" & Format(MyDate,'\#m/d/yyyy\#')

[ebo2006]

Hey, thank you guys, with the delimeter it works. One stumbling block down. I can try another feature now. Thank you again.